Project.

This project is an individual effort. You need to write your SQL query statement for each question and put them all in only one SQL file (or a .txt file) and upload them to the designated folder on D2L.

How to Open SQL files in MySQL Workbench: If you are using MySQL workbench on your computer, first download the W3Schools.SQL file and then open the file in MySQL Workbench, to open it in MySQL workbench click on File, Open SQL Script and select the file.

If you don't have MySQL Workbench on your computer: Use http://mysqltutorial.org//tryit/ here you have access to all content of the database. On this website, you can write your SQL statements and then copy each command to a .txt file.

The following is the ERD of the database.

Answer the following questions. Make sure you use comments in your SQL file to clarify/show which SQL statement is the answer to which question. The questions are:

1. Write a query statement to show the CustomerName, state, and country columns. (5 Points)
2. Write a query statement to show the employees who are living in the USA. (Hint: use subquery) (10 Points)
3. Write a query statement to show the customer name and the name of the products they have ordered. (hint: Join tables customers, orders, orderdetails and products.) (10 Points)
4. Write a query statement to show the details of the most expensive product in the table of products. (Hint: Use subquery) (10 Points)
5. Write a query statement to show the total order values per customer. Hint: Use the orders & orderdetails tables and use group by clause and an aggregation function. (10 Points)
6. Use subquery and show details of the customers that have not placed any orders. Hint: If a customer (i.e., its CustomerNumber) is not in the table of orders, that means the customer has not placed any orders. (10 Points)
7. Use the Delete statement to remove products that have not been ordered by any customers. Hint: A product that is not in the table of order_details means the product has not been ordered, find (without duplicate) those products. (10 Points)
8. Use Update and Case Statements to increase the retail price (under column buyprice) of products that are from productline classic cars and vintage cars by 10% and the rest of the products by 5%. (10 Points)
9. Show the total payments of each customer in each year. (5 Points)
10. Show the number of orders from each US customer. (10 Points)
11. Show the details of the customer(s) who paid the highest total money. (10 Points)

Due on Jul 5, 2026 11:59 PM

Available on Jul 10, 2023 1:00 AM. Access restricted before availability starts.

Available until Jul 5, 2026 11:59 PM. Access restricted after availability ends.