TRẦN VĂN BÌNH MASTER: Giải bài tập tổng hợp Oracle SQL căn bản

Thứ Hai, 24 tháng 7, 2023

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.

Mục lục

---------- STEP 1 ----------
---------- STEP 2 ------------

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

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

=============================
* KHOÁ HỌC ORACLE DATABASE A-Z ENTERPRISE trực tiếp từ tôi giúp bạn bước đầu trở thành những chuyên gia DBA, đủ kinh nghiệm đi thi chứng chỉ OA/OCP, đặc biệt là rất nhiều kinh nghiệm, bí kíp thực chiến trên các hệ thống Core tại VN chỉ sau 1 khoá học.
* CÁCH ĐĂNG KÝ: Gõ (.) hoặc để lại số điện thoại hoặc inbox https://m.me/tranvanbinh.vn hoặc Hotline/Zalo 090.29.12.888
* Chi tiết tham khảo:
https://bit.ly/oaz_w
=============================
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:
📧 Mail: binhoracle@gmail.com
☎️ Mobile/Zalo: 0902912888
👨 Facebook: https://www.facebook.com/BinhOracleMaster
👨 Inbox Messenger: https://m.me/101036604657441 (profile)
👨 Fanpage: https://www.facebook.com/tranvanbinh.vn
👨 Inbox Fanpage: https://m.me/tranvanbinh.vn
👨👩 Group FB: https://www.facebook.com/groups/DBAVietNam
👨 Website: https://www.tranvanbinh.vn
👨 Blogger: https://tranvanbinhmaster.blogspot.com
🎬 Youtube: https://www.youtube.com/@binhguru
👨 Tiktok: https://www.tiktok.com/@binhguru
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhguru
👨 Podcast: https://www.podbean.com/pu/pbblog-eskre-5f82d6
👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội

=============================
oracle tutorial, học oracle database, Tự học Oracle, Tài liệu Oracle 12c tiếng Việt, Hướng dẫn sử dụng Oracle Database, Oracle SQL cơ bản, Oracle SQL là gì, Khóa học Oracle Hà Nội, Học chứng chỉ Oracle ở đầu, Khóa học Oracle online,sql tutorial, khóa học pl/sql tutorial, học dba, học dba ở việt nam, khóa học dba, khóa học dba sql, tài liệu học dba oracle, Khóa học Oracle online, học oracle sql, học oracle ở đâu tphcm, học oracle bắt đầu từ đâu, học oracle ở hà nội, oracle database tutorial, oracle database 12c, oracle database là gì, oracle database 11g, oracle download, oracle database 19c, oracle dba tutorial, oracle tunning, sql tunning , oracle 12c, oracle multitenant, Container Databases (CDB), Pluggable Databases (PDB), oracle cloud, oracle security, oracle fga, audit_trail,oracle RAC, ASM, oracle dataguard, oracle goldengate, mview, oracle exadata, oracle oca, oracle ocp, oracle ocm , oracle weblogic, postgresql tutorial, mysql tutorial, mariadb tutorial, ms sql server tutorial, nosql, mongodb tutorial, oci, cloud, middleware tutorial, hoc solaris tutorial, hoc linux tutorial, hoc aix tutorial, unix tutorial, securecrt, xshell, mobaxterm, putty

Thứ Hai, 24 tháng 7, 2023

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

---------- STEP 1 ----------

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

ĐỌC NHIỀU