# Joins and Clauses - classicmodels Database

{% file src="/files/R4aDfTBnb7BmwObAuyHS" %}

{% embed url="<https://files.gitbook.com/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FCAQ4m4veZXutSlrX8aix%2Fuploads%2FXSfDfNeHKjJKr4nNdYlp%2FGLAB%20-%20304.6.1%20-%20Joins%20and%20Clauses%20-%20classicmodels%20Database.pdf?alt=media&token=8088c9eb-370a-4cfb-a3bb-c318913e26c9>" %}

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:&#x20;

![](/files/YXVheqB2dFkrDd5YF9b7)

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:

```sql
select c.customerName as 'Customer Name',
concat(e.lastName, ', ', e.firstName) as 'Sales Rep'
from customers c JOIN employees e
on c.salesRepEmployeeNumber = e.employeeNumber
order by c.customerName asc;

```

### 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:

```sql
select p.productName as 'Product Name', sum(od.quantityOrdered) as 'Total # Ordered', sum(od.quantityOrdered * od.priceEach) as 'Total Sale'
from products p LEFT JOIN orderdetails od
on p.productCode=od.productCode
group by p.productName, p.buyPrice
order by 3 desc

```

### 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:

```sql
select status as 'Order Status', count(status) as 'Total Orders'
from orders
group by status
order by status;

```

### 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:

```sql
select pl.productLine as 'Product Line', count(od.productCode) as 'total Sold'
from productLines pl join products p
on pl.productLine=p.productLine
JOIN orderdetails od on p.productCode=od.productCode
group by pl.productLine
order by 2 desc;

```

### 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:

```sql
select monthname(paymentDate) AS Month, year(paymentDate) AS Year, format(sum(amount), 2) AS 'Payments Received'
from payments
group by year(paymentDate), monthname(paymentDate)
order by paymentDate; 
```

### 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

```sql
SELECT p.productName Name, p.productLine AS `Product Line`, p.productScale AS `Scale`, p.productVendor AS `Vendor` FROM productlines l NATURAL JOIN products p WHERE l.productLine = "Classic Cars" OR l.productLine = "Vintage Cars" ORDER BY p.productLine DESC, p.productName ASC;
-- Sort order is vintage before classic, and then alphabetically. 
```

{% file src="/files/tLwye7bon35TDafFcQcj" %}

{% file src="/files/Vj7y32qtZluGOy2NwX6q" %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://peterlulu666.gitbook.io/tek-system-java-developer/week-5/lab/joins-and-clauses-classicmodels-database.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.