Database Queries and Code

Aug 22, 2015

CIS 310

One of the classes that’s not only a required course for CIS students, but also essentially required for any CIS student hoping to find an internship is CIS 310 – Database Design. During this extremely intensive class, students learn how to make database diagrams, understand concepts such as normalization, and ultimately write complex SQL queries, including triggers and stored procedures. While I’ve certainly had much more interaction with databases and SQL queries outside of class – including, but not limited to, databases with over a thousand tables, several-hundred-line queries, and even Oracle and noSQL databases – my CIS 310 assignments are probably my single broadest collection of queries that I’ve written, and should serve to indicate what a solid foundation my program has given me.

SQL Queries

--EXERCISE 44
SELECT *
FROM LGDEPARTMENT

--EXERCISE 45
SELECT PROD_SKU, PROD_DESCRIPT, PROD_TYPE, PROD_BASE, PROD_CATEGORY, PROD_PRICE
FROM LGPRODUCT
WHERE PROD_BASE = 'WATER' AND PROD_CATEGORY = 'SEALER'

--EXERCISE 46
SELECT EMP_FNAME, EMP_LNAME, EMP_EMAIL
FROM LGEMPLOYEE
WHERE EMP_HIREDATE >= 'JANUARY 1, 2001' AND EMP_HIREDATE <= 'DECEMBER 31, 2010'

--EXERCISE 47
SELECT EMP_FNAME, EMP_LNAME, EMP_PHONE, EMP_TITLE, DEPT_NUM
FROM LGEMPLOYEE
WHERE DEPT_NUM = '300' OR EMP_TITLE = 'CLERK I'

--EXERCISE 48
SELECT E.EMP_NUM, EMP_LNAME, EMP_FNAME, SAL_FROM, SAL_END, SAL_AMOUNT
FROM LGEMPLOYEE E, LGSALARY_HISTORY
WHERE E.EMP_NUM = '83731' OR E.EMP_NUM = '83745' OR E.EMP_NUM = '84039'
ORDER BY E.EMP_NUM, SAL_FROM

--EXERCISE 50
SELECT E.EMP_NUM, EMP_LNAME, EMP_EMAIL, EMP_TITLE, DEPT_NAME
FROM LGEMPLOYEE E, LGDEPARTMENT
WHERE EMP_TITLE = 'ASSOCIATE'
ORDER BY DEPT_NAME, EMP_TITLE

--EXERCISE 51 (INCORRECT)
SELECT BRAND_NAME, COUNT(DISTINCT P.BRAND_ID) AS NUMPRODUCTS
FROM LGPRODUCT P, LGBRAND B
GROUP BY BRAND_NAME

--EXERCISE 52
SELECT PROD_CATEGORY, COUNT(DISTINCT PROD_SKU) AS NUMPRODUCTS
FROM LGPRODUCT
WHERE PROD_BASE = 'WATER'
GROUP BY PROD_CATEGORY

--EXERCISE 53
SELECT *, COUNT(*) AS NUMPRODUCTS
FROM  (SELECT PROD_BASE, PROD_TYPE
       FROM LGPRODUCT) AS INTERNALQUERY
GROUP BY PROD_BASE, PROD_TYPE

--EXERCISE 54
SELECT BRAND_ID, SUM(BRAND_ID) AS TOTALINVENTORY
FROM  LGPRODUCT
GROUP BY BRAND_ID
ORDER BY BRAND_ID DESC

--EXERCISE 55 (INCORRECT)
SELECT B.BRAND_ID, BRAND_NAME, AVG(PROD_PRICE) AS AVGPRICE
FROM  (SELECT B.BRAND_ID, BRAND_NAME, PROD_PRICE
       FROM LGBRAND B, LGPRODUCT P) AS INTERNALQUERY
GROUP BY B.BRAND_ID, BRAND_NAME
ORDER BY BRAND_NAME

--EXERCISE 56
SELECT DEPT_NUM, MIN(EMP_HIREDATE) AS MOSTRECENT
FROM LGEMPLOYEE
GROUP BY DEPT_NUM
ORDER BY DEPT_NUM

--EXERCISE 57
SELECT *
FROM  (SELECT E.EMP_NUM, EMP_FNAME, EMP_LNAME, SAL_AMOUNT
       FROM LGEMPLOYEE E, LGSALARY_HISTORY
       WHERE DEPT_NUM = '200') AS INTERNALQUERY
ORDER BY SAL_AMOUNT DESC

--EXERCISE 59
SELECT D.DEPT_NUM, DEPT_NAME, DEPT_PHONE, D.EMP_NUM, EMP_LNAME
FROM LGDEPARTMENT D, LGEMPLOYEE E
WHERE D.EMP_NUM = E.EMP_NUM
ORDER BY DEPT_NAME

--EXERCISE 62
SELECT MAX(AVERAGES) AS LARGEST_AVERAGE
FROM  (SELECT AVG(PROD_PRICE) AS AVERAGES
       FROM LGPRODUCT
       GROUP BY PROD_PRICE) AS INTERNALQUERY

--EXERCISE 63
SELECT MAX(AVGPRICE)
FROM  (SELECT B.BRAND_ID, BRAND_NAME, BRAND_TYPE, AVG(PROD_PRICE) AS AVGPRICE
       FROM LGBRAND B, LGPRODUCT
       GROUP BY B.BRAND_ID, BRAND_NAME, BRAND_TYPE) AS INTERNALQUERY

--1
SELECT  Y.SERIALNUMBER, Y.MODELTYPE, Y.ORDERDATE, I.PRICEPAID
FROM    (((BIKE..BICYCLE Y INNER JOIN BIKE..BIKEPARTS B ON Y.SERIALNUMBER = B.SERIALNUMBER)
           INNER JOIN BIKE..COMPONENT C ON B.COMPONENTID = C.COMPONENTID) 
           INNER JOIN BIKE..PURCHASEITEM I ON C.COMPONENTID = I.COMPONENTID)
           INNER JOIN BIKE..PURCHASEORDER P ON I.PURCHASEID = P.PURCHASEID
WHERE   P.ORDERDATE = '2003' AND
        Y.MODELTYPE = 'RACE' AND
        I.PRICEPAID > (SELECT   AVG(I.PRICEPAID)
                       FROM     BIKE..PURCHASEORDER P INNER JOIN BIKE..PURCHASEITEM I ON I.PURCHASEID = P.PURCHASEID
                       WHERE    P.ORDERDATE = '2002'
                      )

--2
SELECT  B.SERIALNUMBER, B.MODELTYPE, P.ORDERDATE, DAY(RECEIVEDATE - P.ORDERDATE) AS BUILDELAY
FROM    BIKE..BICYCLE B, BIKE..PURCHASEORDER P
WHERE   P.ORDERDATE BETWEEN '1-1-2002' AND '12-31-2002' AND
        DAY(RECEIVEDATE - P.ORDERDATE) > 2 * (SELECT    AVG(DAY(RECEIVEDATE - ORDERDATE))
                                            FROM    BIKE..PURCHASEORDER
                                            WHERE   ORDERDATE BETWEEN '1-1-2002' AND '12-31-2002') 
                                            
--3
SELECT  Y.MODELTYPE, SUM(I.PRICEPAID) AS SUMOFSALEPRICE, (SUM(I.PRICEPAID) / (SELECT    SUM(PRICEPAID)
                                                                              FROM      BIKE..PURCHASEITEM)) AS PCTSALES 
FROM    (((BIKE..BICYCLE Y INNER JOIN BIKE..BIKEPARTS B ON Y.SERIALNUMBER = B.SERIALNUMBER)
           INNER JOIN BIKE..COMPONENT C ON B.COMPONENTID = C.COMPONENTID) 
           INNER JOIN BIKE..PURCHASEITEM I ON C.COMPONENTID = I.COMPONENTID)
           INNER JOIN BIKE..PURCHASEORDER P ON I.PURCHASEID = P.PURCHASEID
WHERE   P.ORDERDATE = '2003'
GROUP BY Y.MODELTYPE

--4
SELECT  (COUNT(COMPONENTID) / (SELECT   COUNT(COMPONENTID)
                              FROM      BIKE..COMPONENT)) AS CAMPYPERCENTAGE
FROM    BIKE..COMPONENT
WHERE   PRODUCTNUMBER = 'RD-CAMPY-R2' AND
        CATEGORY = 'REAR DERAILLEUR' AND
        YEAR = '2002'

--5
SELECT  A.CUSTOMERID
FROM    BIKE..BICYCLE A INNER JOIN BIKE..BICYCLE B ON A.CUSTOMERID = B.CUSTOMERID
WHERE   A.MODELTYPE = 'MOUNTAIN' AND
        B.MODELTYPE = 'ROAD'

--6
SELECT  CUSTOMERID, FIRSTNAME, LASTNAME
FROM    BIKE..CUSTOMER
WHERE   CUSTOMERID IN (
            SELECT  CUSTOMERID
            FROM    BIKE..BICYCLE
            WHERE   MODELTYPE = 'MOUNTAIN' AND
                    MODELTYPE != 'MOUNTAIN FULL')

--7
SELECT  B.CUSTOMERID, C.FIRSTNAME, C.LASTNAME, B.MODELTYPE
FROM    BIKE..BICYCLE B FULL OUTER JOIN BIKE..CUSTOMER C ON B.CUSTOMERID = C.CUSTOMERID
WHERE   B.MODELTYPE = 'MOUNTAIN' AND
        B.MODELTYPE != 'MOUNTAIN FULL'

--8 (INCOMPLETE)
SELECT  M.MANUFACTURERNAME, C.PRODUCTNUMBER, C.CATEGORY, (LISTPRICE - ESTIMATEDCOST) AS VALUE, C.COMPONENTID
FROM    (BIKE..MANUFACTURER M INNER JOIN BIKE..COMPONENT C ON M.MANUFACTURERID = C.MANUFACTURERID)
        INNER JOIN BIKE..PURCHASEITEM P ON C.COMPONENTID = P.COMPONENTID
WHERE   P.COMPONENTID != C.COMPONENTID

SELECT DISTINCT C.ComponentID, C.ProductNumber, C.Category, MAX(EstimatedCost*QuantityOnHand) AS [Value]
FROM BIKE..Component C INNER JOIN BIKE..BikeParts P ON C.ComponentID = P.ComponentID
WHERE P.ComponentID NOT IN
        (SELECT ComponentID
         FROM BIKE..BikeParts
         WHERE Year(DateInstalled) = '2004')
GROUP BY C.ComponentID, ProductNumber, Category

--9
SELECT  DISTINCT M.MANUFACTURERNAME, RS.PHONE
FROM    (BIKE..MANUFACTURER M INNER JOIN BIKE..CITY C ON M.CITYID = C.CITYID)
        INNER JOIN BIKE..RETAILSTORE RS ON C.CITYID = RS.CITYID
WHERE   C.STATE = 'CA'
UNION
SELECT  MODELTYPE, CUSTOMNAME
FROM    BIKE..BICYCLE
WHERE   ORDERDATE = '2004'

--10
SELECT  EMPLOYEEID, FIRSTNAME, LASTNAME, CURRENTMANAGER
FROM    BIKE..EMPLOYEE
WHERE   CURRENTMANAGER IN (
            SELECT  EMPLOYEEID
            FROM    BIKE..EMPLOYEE
            WHERE   LASTNAME = 'VENETIAAN')

--11
CREATE VIEW QUANTITYUSED AS
SELECT  (I.QUANTITYRECEIVED - C.QUANTITYONHAND) AS QUANTITYUSED
         FROM BIKE..PURCHASEORDER O INNER JOIN BIKE..PURCHASEITEM I ON O.PURCHASEID = I.PURCHASEID
         INNER JOIN BIKE..COMPONENT C ON I.COMPONENTID = C.COMPONENTID 
         WHERE ReceiveDate < '6-30-2000'

SELECT C.COMPONENTID, C.MANUFACTURERID, C.PRODUCTNUMBER, C.CATEGORY
FROM BIKE..COMPONENT C INNER JOIN BIKE..PURCHASEITEM I ON C.COMPONENTID = I.COMPONENTID, QUANTITYUSED
WHERE QUANTITYRECEIVED * 0.25 > QUANTITYUSED

--15
SELECT  EMPLOYEEID, LASTNAME, FIRSTNAME, TITLE
FROM    BIKE..EMPLOYEE
WHERE   WORKAREA IN ('ASSEMBLY', 'PAINT', 'FRAME')
UNION
SELECT  E.EMPLOYEEID, LASTNAME, FIRSTNAME, TITLE
FROM    BIKE..BICYCLE B INNER JOIN BIKE..EMPLOYEE E ON B.EMPLOYEEID = E.EMPLOYEEID
WHERE   MODELTYPE = 'RACE' AND 
        ORDERDATE BETWEEN '2003-7-1' AND '2003-7-3'

--18
SELECT  COUNT(DISTINCT A.CUSTOMERID) AS NUMCUSTOMERS
FROM    BIKE..BICYCLE A INNER JOIN BIKE..BICYCLE B ON A.CUSTOMERID = B.CUSTOMERID
WHERE   A.MODELTYPE = 'ROAD' OR A.MODELTYPE = 'RACE' AND
        B.MODELTYPE = 'MOUNTAIN'

Complex Stored Procedures and Triggers

USE [CIS31023]
GO
/****** Object:  Trigger [dbo].[TRG_LINE_TOTAL]    Script Date: 04/10/2013 09:20:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[TRG_LINE_TOTAL] 
   ON  [dbo].[LINE] 
   AFTER INSERT, DELETE, UPDATE
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- DECLARE CURSOR for the INSERTED table
    DECLARE INSERTED_CURSOR CURSOR FOR
    SELECT  I.INV_NUMBER, SUM(I.LINE_TOTAL) AS INSERTED_TOTAL
    FROM    INSERTED I
    GROUP BY I.INV_NUMBER

    -- DECLARE CURSOR for the DELETED table
    DECLARE DELETED_CURSOR CURSOR FOR
    SELECT  D.INV_NUMBER, SUM(D.LINE_TOTAL) AS DELETED_TOTAL
    FROM    DELETED D
    GROUP BY D.INV_NUMBER

    DECLARE @NEW INT
    DECLARE @OLD INT
    DECLARE @INV_NUMBER INT

    -- Fills instance variables with values from the CURSORs so long
    -- as both the cursor exists and it has not yet run out
    IF (EXISTS (SELECT * FROM INSERTED))
    BEGIN
        OPEN INSERTED_CURSOR
        FETCH NEXT FROM INSERTED_CURSOR INTO @INV_NUMBER, @NEW
        
        WHILE (@@FETCH_STATUS = 0)
        BEGIN
            UPDATE  INVOICE
            SET     INV_TOTAL = INV_TOTAL + @NEW
            WHERE   INV_NUMBER = @INV_NUMBER
            FETCH NEXT FROM INSERTED_CURSOR INTO @INV_NUMBER, @NEW
        END
        CLOSE INSERTED_CURSOR
        DEALLOCATE INSERTED_CURSOR
    END

    IF (EXISTS (SELECT * FROM DELETED))
    BEGIN
        OPEN DELETED_CURSOR
        FETCH NEXT FROM DELETED_CURSOR INTO @INV_NUMBER, @OLD

        WHILE (@@FETCH_STATUS = 0)
        BEGIN
            UPDATE  INVOICE
            SET     INV_TOTAL = INV_TOTAL - @OLD
            WHERE   INV_NUMBER = @INV_NUMBER
            FETCH NEXT FROM DELETED_CURSOR INTO @INV_NUMBER, @OLD
        END
        CLOSE DELETED_CURSOR
        DEALLOCATE DELETED_CURSOR
    END

END



USE [CIS31023]
GO
/****** Object:  StoredProcedure [dbo].[PRC_CUS_BALANCE_UPDATE]    Script Date: 04/10/2013 09:21:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[PRC_CUS_BALANCE_UPDATE]
    @INV_NUMBER INT = NULL
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE     @TOTINV MONEY
    DECLARE     @CUST_NUM INT
    
    -- DECLARE CURSOR by @INV_NUMBER if it exists, otherwise,
    -- simply SELECT the entire INVOICE table
    IF(@INV_NUMBER IS NULL)
    BEGIN
        DECLARE CUST_CURSOR CURSOR FOR
        SELECT  CUS_CODE, SUM(INV_TOTAL) AS TOTAL
        FROM    INVOICE
        GROUP BY CUS_CODE
    END
    ELSE
    BEGIN
        DECLARE CUST_CURSOR CURSOR FOR
        SELECT  CUS_CODE, SUM(INV_TOTAL) AS TOTAL
        FROM    INVOICE
        WHERE   INV_NUMBER = @INV_NUMBER
        GROUP BY CUS_CODE
    END
    
    -- Begin filling instance variables from the cursor
    OPEN CUST_CURSOR
    FETCH NEXT FROM CUST_CURSOR INTO @CUST_NUM, @TOTINV
    
    -- Continue filling instance variables while the cursor has
    -- not run out
    WHILE(@@FETCH_STATUS = 0)
    BEGIN
        UPDATE  CUSTOMER
        SET     CUS_BALANCE = CUS_BALANCE + @TOTINV
        WHERE   CUS_CODE = @CUST_NUM
        FETCH NEXT FROM CUST_CURSOR INTO @CUST_NUM, @TOTINV
    END
    CLOSE CUST_CURSOR
    DEALLOCATE CUST_CURSOR
END



-- Viewing Tables
SELECT  *
FROM    LINE

SELECT  *
FROM    INVOICE

SELECT  *
FROM    CUSTOMER

-- Firing the Trigger
UPDATE  LINE
SET     LINE_TOTAL = LINE_TOTAL + 1

-- Executing the Stored Procedure
EXEC    PRC_CUS_BALANCE_UPDATE  1002

-- Resetting the work done by the Stored Procedure
-- NOT NECESSARY TO REAL WORK
UPDATE  CUSTOMER
SET     CUS_BALANCE = 0
WHERE   CUS_CODE = 10011

Full Database Design

CREATE TABLE AIRPLANE_TYPE_DIM
(
    AIRPLANE_TYPE_KEY   INT     IDENTITY    NOT NULL    PRIMARY KEY,
    AC_NUMBER       VARCHAR(5),
    MOD_CODE        VARCHAR(10),
    MOD_MANUFACTURER    VARCHAR(15),
    MOD_NAME        VARCHAR(20),
    MOD_SEATS       FLOAT,
    MOD_CHG_MILE        REAL,
    MOD_CRUISE      FLOAT,
    MOD_FUEL        FLOAT,
    AC_TTAF     FLOAT,
    AC_TTEL     FLOAT,
    AC_TTER     FLOAT
);

CREATE TABLE PILOT_DIM
(
    PILOT_KEY       INT     IDENTITY    NOT NULL    PRIMARY KEY,
    EMP_NUM     INT,
    EMP_TITLE       VARCHAR(4),
    EMP_LNAME       VARCHAR(15),
    EMP_FNAME       VARCHAR(15),
    EMP_INITIAL     VARCHAR(1),
    EMP_DOB     DATETIME,
    EMP_HIRE_DATE       DATETIME,
    PIL_LICENSE     VARCHAR(25),
    PIL_RATINGS     VARCHAR(25),
    PIL_MED_TYPE        VARCHAR(1),
    PIL_MED_DATE        DATETIME,
    PIL_PT135_DA        DATETIME
);

CREATE TABLE TIME_DIM
(
    TIME_KEY        INT IDENTITY    NOT NULL    PRIMARY KEY,
    PIL_MED_DATE        DATETIME,
    PIL_PT135_DATE  DATETIME,
    EMP_HIRE_DATE       DATETIME,
    EMP_DOB     DATETIME
);

CREATE TABLE STAGING
(
    AIRPLANE_TYPE_KEY   INT,
    PILOT_KEY       INT,
    TIME_KEY        INT,
    AC_NUMBER       VARCHAR(5),
    EMP_NUM     INT,
    MOD_CODE        VARCHAR(10),
    HOURS_FLOWN     FLOAT,
    FUEL_USED       FLOAT,
    REVENUE     REAL
);

CREATE TABLE FACT
(
    AIRPLANE_TYPE_KEY   INT,
    PILOT_KEY       INT,
    TIME_KEY        INT,
    HOURS_FLOWN     FLOAT,
    FUEL_USED       FLOAT,
    REVENUE     REAL,
    PRIMARY KEY (AIRPLANE_TYPE_KEY, PILOT_KEY, TIME_KEY)
);

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE PopulateStarSchemaDB 
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.

    ALTER TABLE FACT
    DROP CONSTRAINT FK_FACT_AIRCRAFT_TYPE;

    ALTER TABLE FACT
    DROP CONSTRAINT FK_FACT_PILOT;

    ALTER TABLE FACT
    DROP CONSTRAINT FK_FACT_TIME;

    TRUNCATE TABLE AIRPLANE_TYPE_DIM;
    TRUNCATE TABLE PILOT_DIM;
    TRUNCATE TABLE TIME_DIM;
    TRUNCATE TABLE FACT;

    SET NOCOUNT ON;

    ALTER TABLE FACT
    ADD CONSTRAINT FK_FACT_AIRCRAFT_TYPE FOREIGN KEY (AIRPLANE_TYPE_KEY)
    REFERENCES AIRPLANE_TYPE_DIM (AIRPLANE_TYPE_KEY);

    ALTER TABLE FACT
    ADD CONSTRAINT FK_FACT_PILOT FOREIGN KEY (PILOT_KEY)
    REFERENCES PILOT_DIM (PILOT_KEY);

    ALTER TABLE FACT
    ADD CONSTRAINT FK_FACT_TIME FOREIGN KEY (TIME_KEY)
    REFERENCES TIME_DIM (TIME_KEY);

    SET IDENTITY_INSERT AIRPLANE_TYPE_DIM OFF;
    SET IDENTITY_INSERT PILOT_DIM OFF;
    SET IDENTITY_INSERT TIME_DIM OFF;

    INSERT INTO AIRPLANE_TYPE_DIM (AC_NUMBER, MOD_CODE, MOD_MANUFACTURER, MOD_NAME,
        MOD_SEATS, MOD_CHG_MILE, MOD_CRUISE, MOD_FUEL, AC_TTAF, AC_TTEL, AC_TTER)
    SELECT  A.AC_NUMBER, A.MOD_CODE, M.MOD_MANUFACTURER, M.MOD_NAME, M.MOD_SEATS,
        M.MOD_CHG_MILE, M.MOD_CRUISE, M.MOD_FUEL, A.AC_TTAF, A.AC_TTEL, A.AC_TTER
    FROM    AIRCRAFT A INNER JOIN MODEL M ON A.MOD_CODE = M.MOD_CODE

    INSERT INTO PILOT_DIM (EMP_NUM, EMP_TITLE, EMP_LNAME, EMP_FNAME, EMP_INITIAL,
        EMP_DOB, EMP_HIRE_DATE, PIL_LICENSE, PIL_RATINGS, PIL_MED_TYPE, PIL_MED_DATE,
        PIL_PT135_DA)
    SELECT  P.EMP_NUM, E.EMP_TITLE, E.EMP_LNAME, E.EMP_FNAME, E.EMP_INITIAL, E.EMP_DOB,
        E.EMP_HIRE_DATE, P.PIL_LICENSE, P.PIL_RATINGS, P.PIL_MED_TYPE, P.PIL_MED_DATE,
        P.PIL_PT135_DATE
    FROM    PILOT P INNER JOIN EMPLOYEE E ON P.EMP_NUM = E.EMP_NUM

    INSERT INTO TIME_DIM (PIL_MED_DATE, PIL_PT135_DATE, EMP_HIRE_DATE, EMP_DOB)
    SELECT  P.PIL_MED_DATE, P.PIL_PT135_DATE, E.EMP_HIRE_DATE, E.EMP_DOB
    FROM    PILOT P, EMPLOYEE E

    INSERT INTO STAGING (TIME_KEY, AC_NUMBER, EMP_NUM, MOD_CODE, HOURS_FLOWN, FUEL_USED, REVENUE)
    SELECT  T.TIME_KEY, A.AC_NUMBER, P.EMP_NUM, A.MOD_CODE, CHAR_HOURS_FLOWN, CHAR_FUEL_GALLONS, 
        (MOD_CHG_MILE * CHAR_DISTANCE) AS REVENUE
    FROM    AIRPLANE_TYPE_DIM A, PILOT_DIM P, TIME_DIM T, CHARTER;

    UPDATE  STAGING
    SET AIRPLANE_TYPE_KEY = A.AIRPLANE_TYPE_KEY
    FROM    STAGING AS S INNER JOIN AIRPLANE_TYPE_DIM AS A ON S.AC_NUMBER = A.AC_NUMBER

    UPDATE  STAGING
    SET PILOT_KEY = P.PILOT_KEY
    FROM    STAGING AS S INNER JOIN PILOT_DIM AS P ON S.EMP_NUM = P.EMP_NUM

    INSERT INTO FACT (AIRPLANE_TYPE_KEY, PILOT_KEY, TIME_KEY, HOURS_FLOWN, FUEL_USED, REVENUE)
    SELECT  AIRPLANE_TYPE_KEY, PILOT_KEY, TIME_KEY, HOURS_FLOWN, FUEL_USED, REVENUE
    FROM    STAGING
END
GO

--Execute the procedure
EXECUTE PopulateStarSchemaDB

--Display Dimension tables, Staging table and Fact table
SELECT  *
FROM    AIRPLANE_TYPE_DIM

SELECT  *
FROM    PILOT_DIM

SELECT  *
FROM    TIME_DIM

SELECT  *
FROM    STAGING

SELECT  *
FROM    FACT

--List the total revenue by pilot and aircraft model.
SELECT  EMP_NUM, MOD_CODE, SUM(F.REVENUE) AS TOTAL_REVENUE
FROM    STAGING, FACT F
GROUP BY MOD_CODE, EMP_NUM

--Which aircraft model has the highest fuel consumption?
SELECT  AC_NUMBER, MAX(FUEL_USED) AS MAX_FUEL_USED
FROM    (SELECT AC_NUMBER, FUEL_USED
     FROM       AIRPLANE_TYPE_DIM, FACT
    ) AS TEMP_TABLE
GROUP BY AC_NUMBER

--Which pilot has flown the most hours?
SELECT  EMP_NUM, MAX(HOURS_FLOWN) AS HOURS_FLOWN
FROM    (SELECT EMP_NUM, HOURS_FLOWN
     FROM       PILOT_DIM, FACT
    ) AS TEMP_TABLE
GROUP BY EMP_NUM

--List the hours flown by pilot and aircraft model.
SELECT  EMP_NUM, MOD_CODE, SUM(F.HOURS_FLOWN) AS TOTAL_HOURS_FLOWN
FROM    STAGING, FACT F
GROUP BY EMP_NUM, MOD_CODE

portfolio

Welcome to Jekyll!

DraftMEAN

Database Queries and Code

CIS 310

SQL Queries

Complex Stored Procedures and Triggers

Full Database Design