For this homework, we will use the Wine Database and the Northwind Database from within the textbook practice environment.
So go to: http://www.pdbmbook.com/practice
or https://ds220pe.ist.psu.edu on the IST WinLabs , or on Penn StateCisco Anyconnect VPN.
NOTE ON ACADEMIC INTEGRITY: Homework is to be completed by you. If you need to ask for help, that is fine, but try to then do the homework yourself and get your own output. The best way to learn to code is to sometimes make mistakes and learn how to fix them. Homework that is copied from another source will not be graded, and sanctions for academic integrity violations per College and University policy will be pursued.
For these problems, Show a screenshot of each query and some of the output. Try to include something unique from your computer desktop in your screenshot.
Open the Wine database and develop the following queries.
1. Write an SQL query that returns the average price and variance per product.
2. Write an SQL query to retrieve the supplier number, supplier name, and supplier status of each supplier who has a higher value for supplier status than supplier number 21 does.
3. Write a query to select all supplier numbers, together with their supplier name and total number of outstanding orders for each supplier. Include all suppliers in the result, even if there are no outstanding orders for that supplier at the moment. Explain how you could run this query using two different types of outer joins.
4. Using the Insert SQL statement, try to Insert a wine supplier into the Supplier table. Be sure to include values for all attributes. Make up anything you want (Note: All attributes in the Supplier table are characters or character strings, except for us-status which is an integer) Include the output from a select statement that shows your addition
The next part uses The Northwind Database
As a reference, here is the basic relational data model for the Northwind Database (shown in a UML diagram style with only maximum cardinalities) NOTE that the version in your Playground environment does not contain all the attributes in each table:
5. Select and display the 10 most expensive products in the database in order from highest to lowest price.
6. How many employees have handled at least two orders? Show the Employee ID and number of orders.
7. Write a query to return the shipper ID and a count of the number of orders shipped by each shipper. Order this list from lowest number to highest number of orders.
8. Write a query to return the Customer ID, Customer Name and number of orders. Include all customers, even if they don’t have any orders.
EXTRA CREDIT (5 points)
Choose ONLY ONE of the following for extra credit (a maximum of 5 points of extra credit will be offered for one problem).
9a. Recall your answer to question #7 above. Write a query that returns the same list, except does not retrieve the product with the lowest total quantity on order. Explain how your query works. Must include explanation to receive extra credit.
OR
9b. Return a list of the names of employees who have BA degrees. Highlight any potential shortcomings of the query method you used. Must do both parts and show results to receive extra credit.