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;
-------------------#####-------------------
Lưu ý: bài viết có tham khảo hoặc dịch lại từ http://javatpoint.com.
Khóa học đang giảm giá:
- 34- THỰC HÀNH THIẾT KẾ DỮ LIỆU VỚI SQL QUA BÀI TẬP
- 30 – HTML CSS cơ bản
- 029- Học lập trình React js và Redux từ đầu, tạo ứng dụng fullstack với Node JS + React JS
- 27 – Lập trình back-end cơ bản với nodejs & mongodb, mongooose, postgresql.
- 25 – Thiết kế hiệu ứng bằng Javascript và illustrator
Nguồn: freetuts.net