Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Providing detailed information and counting objects
2. Detail reports
Calculating metrics for multiple business objects
Understanding the difference between various count metrics
Summary

Instruction

Let's get started. The easiest type of report we can create contains a lot of detailed information about one or more business objects. The information we need may be scattered across multiple tables, so we want to bring it all together into a single report. In SQL, we use one or more JOIN clauses to do this. Take a look:

SELECT
  c.company_name AS customer_company_name, 
  e.first_name AS employee_first_name, 
  e.last_name AS employee_last_name,
  o.order_date,
  o.shipped_date,
  o.ship_country
FROM orders o
JOIN employees e
  ON o.employee_id = e.employee_id
JOIN customers c
  ON o.customer_id = c.customer_id
WHERE o.ship_country = 'France';

In the above query, we want to collect various information about orders shipped to France. We want some details about the customers and employees involved in those orders, so we need to join the orders table with the employees and customers tables. Note that each table was given a one-letter alias (such as e for employees). This reduces the amount of code we have to write and is a common practice when working with long SQL queries.

Exercise

Your turn now!

Show the following information related to all items with order_id = 10248: the product name, the unit price (taken from the order_items table), the quantity, and the name of the supplier's company (as supplier_name).

Stuck? Here's a hint!

Join the order_items table with the products and suppliers tables.