- You can download a
.docx
version of this exercise here
Prerequisites:
-
These exercises utilize the sample database from located at the root of this project named
mysqlsampledatabase.sql
. -
Ensure that you have ran this
.sql
before attempting the exercise below -
Deliverable:
-
Please write each solution in its respective
partN.sql
file. For example:- place the solution to
Part 1
inpart1.sql
- place the solution to
Part 2
inpart2.sql
- place the solution to
Part 3
inpart3.sql
- place the solution to
-
Database Schema
- Write a query to display each customer’s name (as
Customer Name
) alongside the name of the employee who is responsible for that customer’s orders. - The employee name should be in a single
Sales Rep
column formatted aslastName, firstName
. The output should be sorted alphabetically by customer name.
- Determine which products are most popular with our customers.
- For each product, list the total quantity ordered along with the total sale generated (total quantity ordered * priceEach) for that product.
- The column headers should be
Product Name
,Total # Ordered
andTotal Sale
. - List the products by
Total Sale
descending.
- Write a query which lists order status and the # of orders with that status.
- Column headers should be
Order Status
and# Orders
. - Sort alphabetically by
status
.
- Write a query to list, for each product line, the total # of products sold from that product line.
- The first column should be
Product Line
and the second should be# Sold
. - Order by the second column descending.
- For each employee who represents customers, output the total # of orders that employee’s customers have placed alongside the total sale amount of those orders.
- The employee name should be output as a single column named
Sales Rep
formatted aslastName, firstName
. - The second column should be titled
# Orders
and the third should beTotal Sales
. - Sort the output by
Total Sales
descending. - Only (and all) employees with the job title
Sales Rep
should be included in the output, and if the employee made no sales theTotal Sales
should display as0.00
.
- Your product team is requesting data to help them create a bar-chart of monthly sales since the company’s inception.
- Write a query to output the month (January, February, etc.), 4-digit year, and total sales for that month.
- The first column should be labeled
Month
, the secondYear
, and the third should bePayments Received
. - Values in the third column should be formatted as numbers with two decimals – for example:
694,292.68
.