Data Management Concepts

IS 361 Database Management Systems -- Assignment 5

Fall 2015

Due: 11:59pm, Friday, October 9, 2015

Last modified: September 30, 2015

Purpose

The purpose of this exercise is to practice SQL queries on multiple tables.

Assignment (45 points)

1) Practice the following tutorials at SQLZoo.net:

· More JOIN operations http://sqlzoo.net/wiki/More_JOIN_operations. Questions 1-11.

· Using NULL http://sqlzoo.net/wiki/Using_Null. Questions 1-10.

Generate a .pdf of your stats page, or one showing your queries and results, if you can’t log in.

2) On SQLZoo.net, look at the sample queries http://sqlzoo.net/wiki/Musicians , then complete the 5 easy queries on http://sqlzoo.net/wiki/Musicians_easy_questions . These results don’t get saved on your stats pages, so generate a .pdf showing your queries and results. Resize the query window so your entire query is visible in the .pdf.

3) Perform the PVFC queries listed below. Use MySQL Workbench and save all your queries to a single text file (yourlastname.sql or .txt). Label each query with the comment containing the question number and analysis, for example:

/* #1.

Steps

For Each step:

Table:

Columns:

Condition:

*/

Problems 4, 7 and 8 worth 4 points each, and all other questions worth 3 points each.

1. List the total cost of order number 51

2. List the total cost of all orders placed by customer #1

3. List the total cost of all orders placed by Eastern Furniture

4. List the customers who have not placed any orders (JOIN)

5. List the customers who have not placed any orders (Nested SELECT)

6. List all customers and number of orders placed (0 if none), sort by number of orders

7. List all customers and total number of items shipped (0 if none), sort by customer name (Attention: not total number of orders, nor total number of shipments)

8. List all items that were purchased on the same order as a desk. Only list each item once. (will answer a business question like “customers who bought this also bought …”)

9. List total number of each item that was purchased on the same order as a desk. (with further processing <add order by> will answer a business question like “popular items” among “customers who bought this also bought …” )

Notes

All queries should return a result. I have rebuilt the PVFC11e database so that it is much larger than before. Note that orderIDs now start at 1 instead of 1001.

These queries are significantly harder that those from chapter 6. You have been warned.

Consider working in pairs for this assignment so you have someone to bounce ideas off of.

Rubric

Task	Points
Part 1 - Entire SQLzoo more JOIN tutorial	3
Part 1 - Entire SQLzoo Using NULL tutorial	2
Part 2 - Queries from Musicians (sqlzoo) · 5 queries, 2 pts for each query	10
Part 3 – Big PVFC queries (9 questions) · 4 pts each for question 4, 7, 8 · 3 pts each for all other questions Each query from PVFC	30

Turn In

Upload your .sql file of queries (PVFC on MySQL) and three .pdf (SQLzoo) to HW 5 on Canvas.