Solution to SQL Programming Assignment in Oracle

Solution.

create_structure.sql
--------------------------------- STEP 1 ---------------------------------

-- Campus
CREATE TABLE Campus (
    CampusID          varchar2(5),
    CampusName        varchar2(100),
    Street            varchar2(100),
        City              varchar2(100),
        State             varchar2(100),
        Zip               varchar2(100),
        Phone             varchar2(100),
        CampusDiscount decimal (2,2)
);

ALTER TABLE Campus ADD
    CONSTRAINT Campus_CampusID_PK
        PRIMARY KEY (CampusID);

-- Position
CREATE TABLE Position (
    PositionID        varchar2(5),
    Position          varchar2(100),
        YearlyMembershipFee decimal (7,2)
);

ALTER TABLE Position ADD
    CONSTRAINT Position_PositionID_PK
        PRIMARY KEY (PositionID);

-- Members
CREATE TABLE Members (
    MemberID          varchar2(5),
    LastName          varchar2(100),
        FirstName         varchar2(100),
        CampusAddress     varchar2(100),
        CampusPhone       varchar2(100),
        CampusID          varchar2(5),
        PositionID        varchar2(5),
        ContractDuration INTEGER
);

ALTER TABLE Members     ADD
    CONSTRAINT Members_MemberID_PK
        PRIMARY KEY (MemberID);

ALTER TABLE Members ADD
        CONSTRAINT Members_CampusID_FK
        FOREIGN KEY (CampusID)
                    REFERENCES Campus;

ALTER TABLE Members ADD
    CONSTRAINT Members_PositionID_FK
            FOREIGN KEY (PositionID)
                    REFERENCES Position;

-- Sequence
CREATE SEQUENCE Prices_FoodItemTypeID_SEQ
    START WITH 1
        INCREMENT BY 1
        NOCACHE
        NOCYCLE;

-- Prices
CREATE TABLE Prices (
    FoodItemTypeID    varchar2(5) ,
        MealType          varchar2(100),
        MealPrice         decimal(7,2)
);

ALTER TABLE Prices ADD
        CONSTRAINT Prices_FoodItemTypeID_PK
                           PRIMARY KEY (FoodItemTypeID);

-- Food Items
CREATE TABLE FoodItems (
    FoodItemID      varchar2(5),
        FoodItemName    varchar2(100),
        FoodItemTypeID varchar2(5)
);

ALTER TABLE FoodItems ADD
        CONSTRAINT FoodItems_FoodItemID_PK
        PRIMARY KEY (FoodItemID);

ALTER TABLE FoodItems ADD
        CONSTRAINT FoodItems_FoodItemID_FK
        FOREIGN KEY (FoodItemTypeID)
                    REFERENCES Prices;

-- Orders
CREATE TABLE Orders (
    OrderID      varchar2(5),
        MemberID     varchar2(5),
        OrderDate    varchar2(25)
);

ALTER TABLE Orders ADD
        CONSTRAINT Orders_OrderID_PK
        PRIMARY KEY (OrderID);

ALTER TABLE Orders ADD
        CONSTRAINT Orders_MemberID_FK
        FOREIGN KEY (MemberID)
                    REFERENCES Members;

-- Order Line
CREATE TABLE OrderLine (
    OrderID      varchar2(5),
        FoodItemsID varchar2(5),
        Quantity     INTEGER
);

ALTER TABLE OrderLine ADD
        CONSTRAINT OrderLine_ORDERFOODIDS_PK
        PRIMARY KEY (OrderID, FoodItemsID);

ALTER TABLE OrderLine ADD
        CONSTRAINT Orders_OrderID_FK
        FOREIGN KEY (OrderID)
                    REFERENCES Orders;

ALTER TABLE OrderLine ADD
        CONSTRAINT Orders_FoodItemsID_FK
        FOREIGN KEY (FoodItemsID)
                    REFERENCES FoodItems(FoodItemID);

--------------------------------- STEP 2 ---------------------------------
-- Campus
INSERT INTO Campus
VALUES ('1', 'IUPUI', '425 University Blvd.','Indianapolis', 'IN', '46202', '317-274-4591',.08 );

INSERT INTO Campus
VALUES ('2', 'Indiana University', '107 S. Indiana Ave.','Bloomington', 'IN', '47405', '812-855-4848',.07 );

INSERT INTO Campus
VALUES ('3', 'Purdue University', '475 Stadium Mall Drive','West Lafayette', 'IN', '47907', '765-494-1776',.06 );

-- Position
INSERT INTO Position
VALUES ('1', 'Lecturer', 1050.50);

INSERT INTO Position
VALUES ('2', 'Associate Professor', 900.50);

INSERT INTO Position
VALUES ('3', 'Assistant Professor', 875.50);

INSERT INTO Position
VALUES ('4', 'Professor', 700.75);

INSERT INTO Position
VALUES ('5', 'Full Professor', 500.50);

-- Members
INSERT INTO Members
VALUES ('1', 'Ellen', 'Monk', '009 Purnell', '812-123-1234', '2', '5', 12);

INSERT INTO Members
VALUES ('2', 'Joe', 'Brady', '008 Statford Hall', '765-234-2345', '3', '2', 10);

INSERT INTO Members
VALUES ('3', 'Dave', 'Davidson', '007 Purnell', '812-345-3456', '2', '3', 10);

INSERT INTO Members
VALUES ('4', 'Sebastian', 'Cole', '210 Rutherford Hall', '765-234-2345', '3', '5', 10);

INSERT INTO Members
VALUES ('5', 'Michael', 'Doo', '66C Peobody', '812-548-8956', '2', '1', 10);

INSERT INTO Members
VALUES ('6', 'Jerome', 'Clark', 'SL 220', '317-274-9766', '1', '1', 12);

INSERT INTO Members
VALUES ('7', 'Bob', 'House', 'ET 329', '317-278-9098', '1', '4', 10);

INSERT INTO Members
VALUES ('8', 'Bridget', 'Stanley', 'SI 234', '317-274-5678', '1', '1', 12);

INSERT INTO Members
VALUES ('9', 'Bradley', 'Wilson', '334 Statford Hall', '765-258-2567', '3', '2', 10);

-- Prices
INSERT INTO Prices
VALUES (Prices_FoodItemTypeID_SEQ.NEXTVAL, 'Beer/Wine', 5.50);

INSERT INTO Prices
VALUES (Prices_FoodItemTypeID_SEQ.NEXTVAL, 'Dessert', 2.75);

INSERT INTO Prices
VALUES (Prices_FoodItemTypeID_SEQ.NEXTVAL, 'Dinner', 15.50);

INSERT INTO Prices
VALUES (Prices_FoodItemTypeID_SEQ.NEXTVAL, 'Soft Drink', 2.50);

INSERT INTO Prices
VALUES (Prices_FoodItemTypeID_SEQ.NEXTVAL, 'Lunch', 7.25);

-- FoodItems
INSERT INTO FoodItems
VALUES ('10001', 'Lager', '1');

INSERT INTO FoodItems
VALUES ('10002', 'Red Wine', '1');

INSERT INTO FoodItems
VALUES ('10003', 'White Wine', '1');

INSERT INTO FoodItems
VALUES ('10004', 'Coke', '4');

INSERT INTO FoodItems
VALUES ('10005', 'Coffee', '4');

INSERT INTO FoodItems
VALUES ('10006', 'Chicken a la King', '3');

INSERT INTO FoodItems
VALUES ('10007', 'Rib Steak', '3');

INSERT INTO FoodItems
VALUES ('10008', 'Fish and Chips', '3');

INSERT INTO FoodItems
VALUES ('10009', 'Veggie Delight', '3');

INSERT INTO FoodItems
VALUES ('10010', 'Chocolate Mousse', '2');

INSERT INTO FoodItems
VALUES ('10011', 'Carrot Cake', '2');

INSERT INTO FoodItems
VALUES ('10012', 'Fruit Cup', '2');

INSERT INTO FoodItems
VALUES ('10013', 'Fish and Chips', '5');

INSERT INTO FoodItems
VALUES ('10014', 'Angus and Chips', '5');

INSERT INTO FoodItems
VALUES ('10015', 'Cobb Salad', '5');

-- Orders
INSERT INTO Orders
VALUES ( '1', '9', 'March 5, 2005' );

INSERT INTO Orders
VALUES ( '2', '8', 'March 5, 2005' );

INSERT INTO Orders
VALUES ( '3', '7', 'March 5, 2005' );

INSERT INTO Orders
VALUES ( '4', '6', 'March 7, 2005' );

INSERT INTO Orders
VALUES ( '5', '5', 'March 7, 2005' );

INSERT INTO Orders
VALUES ( '6', '4', 'March 10, 2005' );

INSERT INTO Orders
VALUES ( '7', '3', 'March 11, 2005' );

INSERT INTO Orders
VALUES ( '8', '2', 'March 12, 2005' );

INSERT INTO Orders
VALUES ( '9', '1', 'March 13, 2005' );

-- OrderLine
INSERT INTO OrderLine
VALUES ( '1', '10001', 1 );

INSERT INTO OrderLine
VALUES ( '1', '10006', 1 );

INSERT INTO OrderLine
VALUES ( '1', '10012', 1 );

INSERT INTO OrderLine
VALUES ( '2', '10004', 2 );

INSERT INTO OrderLine
VALUES ( '2', '10013', 1 );

INSERT INTO OrderLine
VALUES ( '2', '10014', 1 );

INSERT INTO OrderLine
VALUES ( '3', '10005', 1 );

INSERT INTO OrderLine
VALUES ( '3', '10011', 1 );

INSERT INTO OrderLine
VALUES ( '4', '10005', 2 );

INSERT INTO OrderLine
VALUES ( '4', '10004', 2 );

INSERT INTO OrderLine
VALUES ( '4', '10006', 1 );

INSERT INTO OrderLine
VALUES ( '4', '10007', 1 );

INSERT INTO OrderLine
VALUES ( '4', '10010', 2 );

INSERT INTO OrderLine
VALUES ( '5', '10003', 1 );

INSERT INTO OrderLine
VALUES ( '6', '10002', 2 );

INSERT INTO OrderLine
VALUES ( '7', '10005', 2 );

INSERT INTO OrderLine
VALUES ( '8', '10005', 1 );

INSERT INTO OrderLine
VALUES ( '8', '10011', 1 );

INSERT INTO OrderLine
VALUES ( '9', '10001', 1 );

------------------------- Create View For the Report: IFC_Report -----------------------

CREATE VIEW IFC_Report AS
SELECT Orders.OrderID, OrderDate, FirstName || ' ' || LastName MemberName, CampusName, FoodItemName,
    MealType, MealPrice, Quantity, (MealPrice * Quantity) Total
FROM FoodItems, Orders, OrderLine, Members, Campus, Prices
WHERE FoodItems.FoodItemID = OrderLine.FoodItemsID AND
    OrderLine.OrderID = Orders.OrderID AND Orders.MemberID = Members.MemberID AND
        Members.CampusID = Campus.CampusID AND Prices.FoodItemTypeID = FoodItems.FoodItemTypeID
ORDER BY Orders.OrderID DESC;

Queries.sql

--------------------------------- STEP 3 ---------------------------------
-- Query1a
PROMPT All records from Campus table
SELECT *
FROM Campus;

-- Query1b
PROMPT All records from Position table
SELECT *
FROM Position;

-- Query1c
PROMPT All records from Members table
SELECT *
FROM Members;

-- Query1d
PROMPT All records from Prices table
SELECT *
FROM Prices;

-- Query1e
PROMPT All records from FoodItems table
SELECT *
FROM FoodItems;

-- Query1f
PROMPT All records from Orders table
SELECT *
FROM Orders;

-- Query1g
PROMPT All records from OrderLine table
SELECT *
FROM OrderLine;

-- Query2a
PROMPT All constraints in the database
SELECT CONSTRAINT_NAME
FROM USER_CONSTRAINTS;

-- Query3a
PROMPT All table names in the database
SELECT TABLE_NAME
FROM USER_TABLES;

-- Query4a
PROMPT Sequence name in the database
SELECT SEQUENCE_NAME
FROM USER_SEQUENCES;

-- Query5a
PROMPT Columns and Datatypes for Campus table
SELECT COLUMN_NAME, DATA_TYPE
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'CAMPUS';

-- Query5b
PROMPT Columns and Datatypes for Position table
SELECT COLUMN_NAME, DATA_TYPE
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'POSITION';

-- Query5c
PROMPT Columns and Datatypes for Members table
SELECT COLUMN_NAME, DATA_TYPE
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'MEMBERS';

-- Query5d
PROMPT Columns and Datatypes for Prices table
SELECT COLUMN_NAME, DATA_TYPE
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'PRICES';

-- Query5e
PROMPT Columns and Datatypes for FoodItems table
SELECT COLUMN_NAME, DATA_TYPE
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'FOODITEMS';

-- Query5f
PROMPT Columns and Datatypes for Orders table
SELECT COLUMN_NAME, DATA_TYPE
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'ORDERS';

-- Query5g
PROMPT Columns and Datatypes for OrderLine table
SELECT COLUMN_NAME, DATA_TYPE
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'ORDERLINE';

-- Query6a
PROMPT Listing of all Faculty Members
SELECT FirstName, LastName, Position, CampusName, (YearlyMembershipFee / 12 ) Monthly_Dues
FROM Members, Position, Campus
WHERE Members.PositionID = Position.PositionID AND
    Members.CampusID = Campus.CampusID
ORDER BY CampusName DESC, LastName ASC;

-- Query7a
PROMPT Listing of all food items
SELECT FoodItemName, MealType, MealPrice
FROM FoodItems, Prices
WHERE FoodItems.FoodItemTypeID = Prices.FoodItemTypeID AND
    MealType NOT LIKE '%Beer%' AND MealType NOT LIKE '%Wine%'
ORDER BY MealPrice ASC;

-- Query8a
SELECT Orders.OrderID, OrderDate, FirstName || ' ' || LastName MemberName, CampusName, FoodItemName,
    MealType, MealPrice, Quantity, (MealPrice * Quantity) Total
FROM FoodItems, Orders, OrderLine, Members, Campus, Prices
WHERE FoodItems.FoodItemID = OrderLine.FoodItemsID AND
    OrderLine.OrderID = Orders.OrderID AND Orders.MemberID = Members.MemberID AND
        Members.CampusID = Campus.CampusID AND Prices.FoodItemTypeID = FoodItems.FoodItemTypeID
ORDER BY Orders.OrderID DESC;

Solution to SQL Programming Assignment in Oracle - XP

Posted by: Mindra

HOME PAGE

Popular

Analyzing E.F. Codd's paper - "A Relational Model of Data for Large Shared Data Banks".

Network Design Life Cycle (NDLC)

BLOG ARCHIVE

Favorite Sites

Translate This BLog

Subscribe Us

Contact Form