Task:
There is a set of tables owned by “csc240r” available on the server at La Salle (password red, and host string cscora2.lasalle.edu). 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 on disk, and a print out – showing the commands and the successful execution of them.
Schema:
S_REGION
· ID Name
S_DEPT
· ID Name Region_ID
· FK Region_ID to S_REGION (N:1)
S_EMP
· ID Last_Name First_Name UserID Comments Manager_ID Start_Date Title Salary Commission_Pct Dept_ID
· FK Manager_ID to S_EMP (N:1)
· FK Dept_ID to S_DEPARTMENT (N:1)
S_PRODUCT
· ID Name Short_Desc Longtext_ID Image_ID Suggested_Whlsl_Price Whlsl_Units
S_CUSTOMER
· ID Name Address City State Country Zip_Code Phone Sales_Rep_ID Credit_Rating Region_ID Comments
· FK Sales_Rep_ID to S_EMPLOYEE (N:1)
· FK Region_ID to S_REGION (N:1)
S_ORD
§ ID Customer_ID Date_Ordered Date_Shipped Sales_Rep_ID Total Payment_Type Order_Filled
§ FK Customer_ID to S_CUSTOMER (N:1)
§ FK Sales_Rep_ID to S_EMPLOYEE (N:1)
S_ITEM
§ Ord_ID Item_ID Product_ID Price Quantity Quantity_Shipped
§ FK Order_ID to S_ORD (N:1)
§ FK Product_ID to S_PRODUCT (N:1)
S_WAREHOUSE
§ ID Region_ID Address City State Country ZIP_Code Phone Manager_ID
§ FK Region_ID to S_REGION (N:1)
§ FK Manager_ID to S_EMP (1:1 ?)
S_INVENTORY
· Product_ID Warehouse_ID Amount_In_Stock Reorder_Point Max_In_Stock Out_Of_Stock_Explanation Restock_Date
· FK Product_ID to S_PRODUCT (N:1)
· FK Warehouse_ID to S_WAREHOUSE (N:1)
Queries:
1) Show id, name, and suggested wholesale price for all products with a suggested wholesale price greater than $10, sorted by suggested wholesale price (high to low)
2) Show all unique employee titles (no duplicates, no names or IDs, just titles)
3) Show for all warehouses whose country is not equal to ‘USA’, their warehouse id, region id, city, country, and manager id.
4) Show for all customers with an EXCELLENT credit rating, their name, city, country, and region NAME.
5) 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
6) 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 (no short cutting the query by looking up key values)
7) Show the mean (average) salary for all employees
8) Show the highest quantity ordered for items (s_item) whose purchase price is greater than $20.