Homework Assignment Three
Consider the relational schema of (part of) a firm database below. Write a SQL query for each of the following requirement. Run the queries using the database uploaded with the assignment and display the results.
1. List the Vend_ID, Vend_Name, Vend_Contact of all vendors whose LAST name begins with 'C'. List them in descending order of Vend_City. (Query should return 2 rows)
2. List the vendor city and the number of vendors in each city where the number of vendors is greater than 2. Use the alias TotalVendors for the number of vendors.(Query should return 2 rows)
3. Display Ord_ID, Ord_Date of all orders where condition (c) is met and simultaneously either one of conditions (a) and (b) are met
a) Vend_ID starts with the letter "P"
b) Ord_Status is "Rec"
c) Ord_Tot_Cost is greater than or equal to $2000 but less than or equal to $5500
(Query should return 4 rows)
4. Display the loc_description and item_id of all items whose storage quantity is greater than 15. (Query should return 4 rows)
5. Display the Rep_ID, Rep_Date and Item_ID of all items where either the Rep_Cost is less than $50 or the Item_Buy_Datedate is between 1/1/1910 and 1/1/1915.(Query should return 4 rows)
6. Display the ID and name of vendors who have NOT placed any order.(Query should return 3 rows)
7. Display the item ID, store quantity and location description of the items having the lowest store quantity. Note: there are three such items.(Query should return 3 rows)
8. Display the vendor ID and vendor name of the items whose item quantity is the largest.(Query should return 2 rows)
9. Display the item ID and item quantity of all items whoseitem quantity is less than the average quantity. Also display the average quantity.(Query should return 6 rows)
10. How many times the vendor has placed an order for the item ID = 1 (rename it as NUMORD)? Also display the vendor name and the location description for the item_ID = 1? Write a single query to obtain the results.(Query should return 1 row)