Joins and Clauses - classicmodels Database
Lab Overview:
In this lab, we will demonstrate and utilize SQL join predicates, SQL clauses, and aggregate functions.
Prerequisites:
For this lab, you must have a “classicmodels” database. If you do not have a ‘classicmodels’ database setup, click here to download the database script file.
The database schema is:
If you run into this error with MySQL: Expression #1 of the SELECT list is not in the GROUP BY clause and contains nonaggregated column 'db.table.col,' which is not functionally dependent on columns in the GROUP BY clause; this is incompatible with sql_mode=only_full_group_by. It is likely because you have the ONLY_FULL_GROUP_BY function enabled. To fix this, you have to disable it. Run this command: SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
Problem Statement One:
Write a query to display each customer’s name (as “Customer Name”), along with 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 as “lastName, firstName.” The output should be sorted alphabetically by customer name.
Solution: Run the below query on MySQL:
Problem Statement two:
To determine which products are the 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,” and “Total Sale.” List the products by “Total Sale” descending.
Solution: Run the below query on MySQL:
Problem Statement three:
Write a query that lists order status and the number of orders with that status. Column headers should be “Order Status” and “Total Orders.” Sort alphabetically by status.
Solution: Run below query on MySQL:
Problem Statement four:
Write a query to list, for each product line, the total number of products sold from that product line. The first column should be “Product Line” and the second should be “total Sold.” Order by the second column, descending.
Solution: Run below query on MySQL:
Problem Statement five:
Your product team is requesting data to help them create a bar chart of monthly sales made since the company’s inception. Write a query to output the month (January, February, etc.), 4-digit year, and total sales for each month. The first column should be labeled ‘Month,’ the second column should be labeled ‘Year,’ and the third column should be labeled ‘Payments Received.’ Values in the third column should be formatted as numbers with two decimals (e.g., 694,292.68).
Solution: Run below query on MySQL:
Problem statement five:
Write a query to display the Name, Product Line, Scale, and Vendor of all of the Car products — both classic and vintage. The output should display all vintage cars first (sorted alphabetically by name), and all classic cars last (also sorted alphabetically by name).
Solution: Run below query on MySQL
Last updated