Description
Objective
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 db.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
-
How many territories are in each region?
-
Which products need to be reordered?
-
How many orders have not yet been shipped?
-
Which orders were shipped to a city di erent than the city of the customer’s headquarters?
1
-
How many orders were sent by each shipper?
-
How many customers did each employee get an order from in the rst quarter of 1998? Note: the rst quarter of the year is January 1st to March 31st.
-
What is the cost of order 10256? Note: all discounts are currently 0 in the database.
-
What is the total $ value of orders in 1997 that were sent via each shipper?
Part 3 – Queries needing a bit more thought
-
What are the 3 most ordered categories of product
By number of units? By value of sales?
-
Who is the top salesperson in the fourth quarter of 1997?
-
How many people directly report to each of the supervisors?
-
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
-
What strategy can you use to develop a query for a given question?
-
How do you make a query e cient?
2
