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

Xin giới thiệu với các bạn một bài tập thực hành tổng hợp giúp các bạn thực hanh quen tay với các lệnh căn bản trong Oracle, bài tập này mình sưu tầm được trên mạng và đăng lại tại đây.

Overview

In this assignment you will build a sample database similar to the preferred solution for Assignment 2. You will create the tables in Oracle, add sample data and create several queries and a report.  

Activities

Step 1

Using Oracle, create the tables identified below in the following order: 

DATABASE TABLES:

• Campus (CampusID, CampusName, Street, City, State, Zip, Phone, CampusDiscount)
• Position (PositionID, Position, YearlyMembershipFee)
• Members (MemberID, LastName, FirstName, CampusAddress, CampusPhone, CampusID, PositionID, ContractDuration)
        FK   CampusID --> Campus(CampusID)
                PositionID --> Position(PositionID)
• Prices (FoodItemTypeID, MealType, MealPrice)
• FoodItems (FoodItemID, FoodItemName, FoodItemTypeID)
       FK    FoodItemTypeID --> Prices(FoodItemTypeID)
• Orders (OrderID, MemberID, OrderDate)
       FK   MemberID --> Members(MemberID)
• OrderLine (OrderID, FoodItemsID, Quantity)
       FK   OrderID --> Orders(OrderID)
               FoodItemsID --> FoodItems(FoodItemID)

STRUCTURE NOTES:

• Use the proper naming convention for your constraints:
  Example: Constraint TableName_FieldName_ConstraintID (Campus_CampusID_PK) 
• Set up the Primary Keys for each table with Constraints listed.
  Note: The OrderLine Table has a composite Primary Key
• Add Your Foreign Keys for each table with your Constraints listed.
• Set up your Sequence for the Prices table ONLY. Remember to follow the proper naming convention.
  The Sequence will be used in the insert commands to add your auto numbering into the Primary Key (FoodItemTypeID) fields.  Name the Sequence "Prices_FoodItemID_Seq"
• Make the Data Types for all the Primary Keys and their corresponding Foreign Keys Varchar2(5).
• Make the Data Type for OrderDate Varchar2(25). (we won't worry about the date format, way too complicated for what we are doing).
• Make the Data Types for the MealPrice and YearlyMembershipFee Decimal, 7 digits maximum with 2 digits to the right of the decimal place, so that we can perform calculations on them.
• Make the Data Types for ContractDuration, and Quantity Integer with 3 digits maximum for calculation purposes.
• Make the Data Type for CampusDiscount Decimal, 2 digits maximum with 2 digits to the right of the decimal place.

Step 2. 

Use the Insert Into Command to add your data to each table. Add data to your primary tables first and then to your secondary tables. Also, remember to use the sequence code with your insert statement to add the auto number value to each primary key field.

DATA TO BE INSERTED:

Campus:

• '1','IUPUI','425 University Blvd.','Indianapolis', 'IN','46202', '317-274-4591',.08
• '2','Indiana University','107 S. Indiana Ave.','Bloomington', 'IN','47405', '812-855-4848',.07
• '3','Purdue University','475 Stadium Mall Drive','West Lafayette', 'IN','47907', '765-494-1776',.06

Position:

• '1','Lecturer', 1050.50
• '2','Associate Professor', 900.50
• '3','Assistant Professor', 875.50
• '4','Professor', 700.75
• '5','Full Professor', 500.50

Members:

• '1','Ellen','Monk','009 Purnell', '812-123-1234', '2', '5', 12
• '2','Joe','Brady','008 Statford Hall', '765-234-2345', '3', '2', 10
• '3','Dave','Davidson','007 Purnell', '812-345-3456', '2', '3', 10
• '4','Sebastian','Cole','210 Rutherford Hall', '765-234-2345', '3', '5', 10
• '5','Michael','Doo','66C Peobody', '812-548-8956', '2', '1', 10
• '6','Jerome','Clark','SL 220', '317-274-9766', '1', '1', 12
• '7','Bob','House','ET 329', '317-278-9098', '1', '4', 10
• '8','Bridget','Stanley','SI 234', '317-274-5678', '1', '1', 12
• '9','Bradley','Wilson','334 Statford Hall', '765-258-2567', '3', '2', 10

Prices:  Note - Remember that these Primary Key Values should be entered using the Sequence (autonumber)

• '1','Beer/Wine', 5.50
• '2','Dessert', 2.75
• '3','Dinner', 15.50
• '4','Soft Drink', 2.50
• '5','Lunch', 7.25

FoodItems:

• '10001','Lager', '1'
• '10002','Red Wine', '1'
• '10003','White Wine', '1'
• '10004','Coke', '4'
• '10005','Coffee', '4'
• '10006','Chicken a la King', '3'
• '10007','Rib Steak', '3'
• '10008','Fish and Chips', '3'
• '10009','Veggie Delight', '3'
• '10010','Chocolate Mousse', '2'
• '10011','Carrot Cake', '2'
• '10012','Fruit Cup', '2'
• '10013','Fish and Chips', '5'
• '10014','Angus Beef Burger', '5'
• '10015','Cobb Salad', '5'

Orders:

• '1', '9', 'March 5, 2005'
• '2', '8', 'March 5, 2005'
• '3', '7', 'March 5, 2005'
• '4', '6', 'March 7, 2005'
• '5', '5', 'March 7, 2005'
• '6', '4', 'March 10, 2005'
• '7', '3', 'March 11, 2005'
• '8', '2', 'March 12, 2005'
• '9', '1', 'March 13, 2005'

OrderLine:

• '1','10001',1
• '1','10006',1
• '1','10012',1
• '2','10004',2
• '2','10013',1
• '2','10014',1
• '3','10005',1
• '3','10011',1
• '4','10005',2
• '4','10004',2
• '4','10006',1
• '4','10007',1
• '4','10010',2
• '5','10003',1
• '6','10002',2
• '7','10005',2
• '8','10005',1
• '8','10011',1
• '9','10001',1
   

Step 3. 

Create the queries listed below:

NOTE: Before you begin to run Queries 1 - 8, Enter the following two commands at the SQL Prompt:

Set Linesize 110
Set Pagesize 90

This will allow you to see most, if not all, of your data without it wrapping.

1. Select all records from each table
2. List all of your constraints in the database
3. List all of your table names in the database
4. List your sequence name in the database
   select sequence_name 
   from user_sequences;
5. List the Columns and Datatypes of each table
6. Create a listing of all Faculty Members (First and Last), their Faculty Position and the University that they are affiliated with (Name), along with their Monthly_Dues (Calculated Field with a column alias). Sort the records in descending order by University and then by Faculty's last name in ascending order.
7. Create a listing that shows the various food items that the faculty club serves (Name of the food item, type of food item and the price of the food item). Note: List no alcoholic beverages. Sort the records in ascending order by price.
8. List the OrderID, Order Date, Faculty Member's Name, Campus Name, each FoodItem that makes up a given order, the type of meal, cost of the meal, quantity ordered and the total line total (calculated field and column alias). Sort by Order IDs in descending order.

Step 4. 

Create 1 formatted Report following the examples given in the SQL Book. The report should be similar to the following in format: 

View Requirements:

• You will have to create a view that contains your report data. View Name: IFC_Report
• Sort your data in ascending order by the OrderID within your view.
• This view is similar in structure to Query 8 in Step 3.
• The grouping information within the report that does not repeat contains 5 fields concatenated together.
  Include Fields: OrderID, OrderDate, FirstName, LastName and CampusName)
• The last field of the report (Totals) is a calculated field. This field takes into consideration the following:
  Line Item total with the Campus Discount and tax of 6% figured in.

Report Requirements:

• Make sure that you list the report title and headings as listed on the report.
• Make sure that you set your formatting for each column in your report. Remember that there are different formats for Character and Number Datatypes.
• Sums (totals) are calculated for each order grouping (info that does not repeat) and for the entire report.
• Remember that the linesize, pagesize, amount of characters that you allot to each field's format and the number of lines that you skip for your grouping will all affect your report's outputted display.

In the final text file that you will submit, name your queries Query 1 through Query 8, matching the criteria above. NOTE: Query 1 would be labeled Query1a, Query1b, Query1c depending on the number of tables that you have in your database.