Practice Subqueries and Joins.
A. Cut and paste the questions below into a Word document (or your favorite text editor if you do not have Word)
1. List EmployeeID, FirstName, LastName of employees who have ordered animals from suppliers located in ZipCodes starting with ‘4’.
2. List all fields from the Merchandise table for Merchandise Orders ordered in September, 1998.
3. List all fields from the Merchandise table for items supplied by David Love.
4. List AnimalID of all Animals sold more than once. (Hint: requires a ‘Having’ clause)
5. List the AnimalID of the Animals that have not been sold.
6. List Employee LastName and sale dates for employees living in zip code 06101
7. List Employee LastName and the number of Merchandise orders made by this employee for all employees who made one or more merchandise orders.
8. List the SupplierID and the total of all shipping costs for all animal orders from this supplier for all suppliers from whom an animal was ordered.
9. Add the name of the supplier to the data listed in the last query. (Hint: requires a join.)
10. List the AnimalID, Name, Breed, SaleDate, SalePrice sorted by Name then AnimalID of all animals sold. (Hint: requires multiple joins.)
11. List the SupplierID and the number of orders from this supplier for all suppliers from whom any animal or merchandise has been ordered. (Hint: requires a union.)
B. Here is a zip file containing initialization files for the version of the petStore database used in this lab. Use the same process you did in labs 1 and 2 to unpack, edit and use these files to initialize the lab 4 petStore database. (Note: this script rebuilds the entire database from scratch. You will get “1265 Data truncated” warnings on several tables, and ‘1261 Row 1 doesn't contain data for all columns’ for the Customer table. It is safe to ignore these warnings.)
In MySQL Workbench, create SQL statements that will answer the questions shown above. Paste the SQL below the question it answers in your document.
C. Add an answer to the following synthesis question to the end of your document:
1. How is the way a join works different than the way a subquery works? (Support your answer with a small example.)
D. Submit your assignment on moodle by clicking on the Lab 4 assignment. NOTE: You can continue to add to your assignment as long as you click the Edit Submission button, but you must click the Submit Assignment button, then the ‘my own work’ checkbox, then the Continue button to finalize your submission.
1. Here is diagram showing the table relationships in crow’s foot notation.
