In this lab, you will practice making SQL queries.

This lab will not be marked. Instead, we will go through the queries together as a class in the lab.

Preparation

• Make sure that you have MySQLWorkbench working from lab 7.

Resources

• Database diagram at http://www.zentut.com/sql-tutorial/sql-sample-database/ for the data in this lab
• The csci3901 database available at cs.dal.ca.

Alternatively, you can download and install your own copy of the mysql database onto your local computer and install your own copy of the database, with data retrieved from the same web page as the database diagram.

Procedure

Set-up

1. Open a query tab in MySQLWorkbench.

Lab steps

You will develop queries for a series of questions.

Part 1 Starter queries

1. How many territories are in each region?
2. Which products need to be reordered?
3. How many orders have not yet been shipped?
4. Which orders were shipped to a city different than the city of the customers headquarters?

1

Part 2 Medium queries

1. How many orders were sent by each shipper?
2. How many customers did each employee get an order from in the first quarter of 1998? Note: the first quarter of the year is January 1st to March 31st.
3. What is the cost of order 10256? Note: all discounts are currently 0 in the database.
4. What is the total $ value of orders in 1997 that were sent via each shipper?

Part 3 Queries needing a bit more thought 1. What are the 3 most ordered categories of product

• By number of units?
• By value of sales?

2. Who is the top salesperson in the fourth quarter of 1997?
3. How many people directly report to each of the supervisors?
4. Which customers bought more than $5000 in products in 1997 that could be traced back to a single supplier? We might want to give these customers a discount to avoid having them buy directly from our supplier

Questions

1. What strategy can you use to develop a query for a given question?
2. How do you make a query efficient?