Giải bài tập tổng hợp Oracle SQL căn bản - Oracle căn bản

Dưới đây là bài giải bài tập SQL Oracle căn bản của bài trước, bài giải này mình sưu tầm trên internet để các bạn tham khảo.

---------- 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;