MỞ ĐẦU
ORACLE TABLE
ORACLE QUERY
ORACLE CONDITION
ORACLE CLAUSES
ORACLE OPERATORS
ORACLE JOINS
ORACLE REFERENCE
CÁC CHỦ ĐỀ
BÀI MỚI NHẤT
Dự án mới của mình là gamehow.net, mời anh em ghé thăm và góp ý ạ.

Giải bài tập tổng hợp Oracle SQL 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
-- 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
-- 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
-- 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
-- Sequence

CREATE SEQUENCE Prices_FoodItemTypeID_SEQ

    START WITH 1

        INCREMENT BY 1

        NOCACHE

        NOCYCLE;

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

 

banquyen png
Bài viết này được đăng tại freetuts.net, không được copy dưới mọi hình thức.

 

 

 

 

 

 

------------ STEP 2 ------------

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

 

 

Cùng chuyên mục:

Hàm Hàm ASCII trong Oracle

Hàm Hàm ASCII trong Oracle

Cách sử dụng hàm Hàm ASCII trong 150

Hàm Hàm ASCIISTR trong Oracle

Hàm Hàm ASCIISTR trong Oracle

Cách sử dụng hàm Hàm ASCIISTR trong 150

Hàm Hàm CHR trong Oracle

Hàm Hàm CHR trong Oracle

Cách sử dụng hàm Hàm CHR trong 150

Hàm Hàm COMPOSE trong Oracle

Hàm Hàm COMPOSE trong Oracle

Cách sử dụng hàm Hàm COMPOSE trong 150

Hàm Hàm CONCAT trong Oracle

Hàm Hàm CONCAT trong Oracle

Cách sử dụng hàm Hàm CONCAT trong 150

Hàm Hàm CONVERT trong Oracle

Hàm Hàm CONVERT trong Oracle

Cách sử dụng hàm Hàm CONVERT trong 150

Hàm Hàm DECOMPOSE trong Oracle

Hàm Hàm DECOMPOSE trong Oracle

Cách sử dụng hàm Hàm DECOMPOSE trong 150

Hàm Hàm DUM trong Oracle

Hàm Hàm DUM trong Oracle

Cách sử dụng hàm Hàm DUM trong 150

Hàm Hàm INITCAP trong Oracle

Hàm Hàm INITCAP trong Oracle

Cách sử dụng hàm Hàm INITCAP trong 150

Hàm Hàm INSTR trong Oracle

Hàm Hàm INSTR trong Oracle

Cách sử dụng hàm Hàm INSTR trong 150

Hàm Hàm INSTR2 trong Oracle

Hàm Hàm INSTR2 trong Oracle

Cách sử dụng hàm Hàm INSTR2 trong 150

Hàm Hàm INSTR4 trong Oracle

Hàm Hàm INSTR4 trong Oracle

Cách sử dụng hàm Hàm INSTR4 trong 150

Hàm Hàm INSTRB trong Oracle

Hàm Hàm INSTRB trong Oracle

Cách sử dụng hàm Hàm INSTRB trong 150

Hàm Hàm INSTRC trong Oracle

Hàm Hàm INSTRC trong Oracle

Cách sử dụng hàm Hàm INSTRC trong 150

Hàm Hàm LENGTH trong Oracle

Hàm Hàm LENGTH trong Oracle

Cách sử dụng hàm Hàm LENGTH trong 150

Hàm Hàm LENGTH2 trong Oracle

Hàm Hàm LENGTH2 trong Oracle

Cách sử dụng hàm Hàm LENGTH2 trong 150

Hàm Hàm LOWER trong Oracle

Hàm Hàm LOWER trong Oracle

Cách sử dụng hàm Hàm LOWER trong 150

Hàm Hàm LPAD trong Oracle

Hàm Hàm LPAD trong Oracle

Cách sử dụng hàm Hàm LPAD trong 150

Hàm Hàm LTRIM trong Oracle

Hàm Hàm LTRIM trong Oracle

Cách sử dụng hàm Hàm LTRIM trong 150

Hàm Hàm NCHR trong Oracle

Hàm Hàm NCHR trong Oracle

Cách sử dụng hàm Hàm NCHR trong 150

Top