• Home
• Exercises
  • Chapter 1
  • Chapter 2
  • Chapter 3
  • Chapter 4
  • Chapter 5
  • Chapter 6
  • Chapter 7
  • Chapter 8
  • Chapter 9
  • Chapter 10
  • Chapter 11
  • Chapter 12
  • Chapter 13
  • Chapter 14
  • Chapter 15
  • Chapter 16
  • Chapter 17
  • Chapter 18
  • Chapter 19
  • Chapter 20
  • Chapter 21
  • Reference 1
  • Reference 2
• Skill builders
  • Chapter 1
  • Chapter 2
  • Chapter 3
  • Chapter 4
  • Chapter 5
  • Chapter 6
  • Chapter 7
  • Chapter 8
  • Chapter 9
  • Chapter 10
  • Chapter 11
  • Chapter 12
  • Chapter 13
  • Chapter 14
  • Chapter 15
  • Chapter 16
  • Chapter 17
  • Chapter 18
  • Chapter 19
  • Chapter 20
  • Chapter 21
• Support
  • Ask Rick
  • Cd case
  • Errata
  • Glossary
  • INSERT macro
  • Java
  • Labs
  • More SQL problems
  • Slides
  • SQL divide
  • SQL Playbook
  • Tables
  • Teradata
  • XML
• Instructor
  • Contact
  • Contents
  • Manual
  • Preface

Additional SQL queries

Use the ClassicModels database to answer the following requests (SQL to create the database). Your instructor has the answers to all queries.

Single entity

1. Prepare a list of offices sorted by country, state, city.
2. How many employees in the company?
3. What is the total of payments received?
4. List the product lines that contain 'Cars'.
5. Report total payments for October 28, 2004.
6. Report those payments greater than $100,000.
7. List the products in each product line.
8. How many products in each product line?
9. What is the minimum payment received?
10. List all payments greater than twice the average payment.

One to many relationship

1. Report the account representative for each customer.
2. Report total payments for Atelier graphique.
3. Report the total payments by date
4. Report the products that have not been sold.
5. List the amount paid by each customer.
6. How many orders have been placed by Herkku Gifts?
7. Who are the employees in Boston?
8. Report those payments greater than $100,000. Sort the report so the customer who made the highest payment appears first.
9. List the value of 'On Hold' orders.
10. Report the number of orders 'On Hold' for each customer.

Many to many relationship

1. List products sold by order date.
2. List all the orders for the 1940 Ford Pickup Truck.
3. Which orders have a value greater than $5,000?
4. Are there any products that appear on all orders?
5. List those orders containing items sold at less than the MSRP.
6. Reports those products that have been sold with a markup of 100% or more (i.e.,  the priceEach is at least twice the buyPrice)
7. List the products ordered on a Monday.
8. What is the quantity on hand for products listed on 'On Hold' orders?

Regular expressions

1. Find products containing the name 'Ford'.
2. List products ending in 'ship'.
3. Report the number of customers in Denmark, Norway, and Sweden.
4. What are the products with a product code in the range S700_1000 to S700_1499?
5. Which customers have a digit in their name?

General queries

1. Who is at the top of the organization (i.e.,  reports to no one).
2. Who reports to William Patterson?
3. List all the products purchased by Herkku Gifts.
4. Compute the commission for each sales representative, assuming the commission is 5% of the cost of an order. Sort by employee last name and first name.
5. What is the difference in days between the most recent and oldest order date in the Orders file?
6. Compute the average time between order date and ship date for each customer ordered by the largest difference.
7. What is the value of orders shipped in August 2004? (Hint).
8. Compute the total value ordered, total amount paid, and their difference for each customer for orders placed in 2004 and payments received in 2004 (Hint; Create views for the total paid and total ordered).
9. List the employees who report to those employees who report to Diane Murphy. Use the CONCAT function to combine the employee's first name and last name into a single field for reporting.
10. What is the percentage value of each product in inventory sorted by the highest percentage first (Hint: Create a view first).
11. Write a function to convert miles per gallon to liters per 100 kilometers.
12. Write a procedure to increase the price of a specified product category by a given percentage. You will need to create a product table with appropriate data to test your procedure. Alternatively, load the ClassicModels database on your personal machine so you have complete access. You have to change the DELIMITER prior to creating the procedure.
13. What is the value of orders shipped in August 2004? (Hint).

Correlated subqueries

1. Who reports to Mary Patterson?
2. Which payments in any month and year are more than twice the average for that month and year (ie compare all payments in Oct 2004 with the average payment for Oct 2004)? Order the results by the date of the payment. You will need to use the date functions.
3. Report for each product, the percentage value of its stock on hand as a percentage of the stock on hand for product line to which it belongs. Order the report by product line and percentage value within product line descending. Show percentages with two decimal places.
4. For orders containing more than two products, report those products that constitute more than 50% of the value of the order.

Data visualization

For each problem, use R to run an SQL query and then visualize the results. The first step in all cases is to load the MySQL library and access the database.

1. Visualize in blue the number of items for each product scale
2. Prepare a line plot with appropriate labels for total payments for each month in 2004
3. Create a histogram with appropriate labels for the value of orders received from the Nordic countries (Denmark,Finland, Norway,Sweden)
4. Create a heatmap with for product lines and Norwegian cities

Workbench file for the following image.

Date revised: February 26, 2010