Task:
There is a set of tables owned by
“csc264r3” available on the server at La Salle (password and host string same
as for csc264r1). The schema for the tables is given below. Your task is to
develop queries to answer the questions given below, and run them against the
demo database. Capture the execution/results in a (spool) file, and turn in the
file – showing the commands and the successful execution of them.
Schema:
REGION
·
ID Name
DEPT
·
ID Name Region_ID
·
FK Region_ID to REGION
(N:1)
EMP
·
ID Last_Name First_Name UserID Comments Manager_ID Start_Date Title
Salary Commission_Pct Dept_ID
·
FK
Manager_ID to EMP (N:1)
·
FK
Dept_ID to DEPARTMENT (N:1)
PRODUCT
·
ID Name Short_Desc Longtext_ID Image_ID Suggested_Whlsl_Price Whlsl_Units
CUSTOMER
·
ID Name Address City State Country Zip_Code Phone Sales_Rep_ID Credit_Rating Region_ID Comments
·
FK
Sales_Rep_ID to EMPLOYEE (N:1)
·
FK
Region_ID to REGION (N:1)
ORD
§
ID Customer_ID Date_Ordered Date_Shipped Sales_Rep_ID Total Payment_Type Order_Filled
§
FK
Customer_ID to CUSTOMER (N:1)
§
FK
Sales_Rep_ID to EMPLOYEE (N:1)
ITEM
§
Ord_ID Item_ID Product_ID Price Quantity Quantity_Shipped
§
FK
Order_ID to ORD (N:1)
§
FK
Product_ID to PRODUCT (N:1)
WAREHOUSE
§
ID Region_ID Address City State Country ZIP_Code Phone Manager_ID
§
FK
Region_ID to REGION (N:1)
§
FK
Manager_ID to EMP (1:1 ?)
INVENTORY
·
Product_ID Warehouse_ID Amount_In_Stock Reorder_Point Max_In_Stock Out_Of_Stock_Explanation Restock_Date
·
FK
Product_ID to PRODUCT (N:1)
·
FK
Warehouse_ID to WAREHOUSE (N:1)
Queries:
1)
Show for
all customers with an EXCELLENT credit rating, their name, city, country, and region.
2)
Show for
all employees with a salary greater than 1000, their last name, first name,
department NAME, start date, title, and salary – sorted by salary from high to
low
3)
Show for
all sales reps who work with customers in Asia, their last name, first name, title, department number, and salary.
Don’t cheat and use region_ID = 4. I want to see ‘Asia’ in your query.
4)
Show for
the product named ‘World Cup Soccer Ball’, all orders – including customer
NAME, date ordered, quantity ordered, price, and suggested wholesale price – sorted
by date ordered. I want to see ‘World
Cup Soccer Ball’ in your query.
5)
Show for
each product, the total amount in inventory in all warehouses combined.
6)
Show for
each customer, the customer name, and the number of orders they have made, the
total of all their order amounts, and the average amount of their orders.