Search⌘ K
Home
Courses
Getting Started with SQL and Relational Databases

Practice Some More Queries

Explore how to practice advanced SQL queries including joins, left joins, and subqueries to combine customer, order, and order item data. Understand grouping and aggregation to calculate totals by customer and identify top customers. Gain practical skills in managing complex relational data.

We'll cover the following...

Let’s take a look at groups and subqueries.

Show customers along with their order totals

We need to come back with a result like the following:

+-------------+-----------+----------+--------------+----------+------------------+
| customer_id | name      | identity | order_number | order_id | amount_per_order |
+-------------+-----------+----------+--------------+----------+------------------+
|           4 | John Woo  | JW001    | ABC001       |        2 |           100.00 |
|           5 | Maria Foo | MF001    | ABC002       |        3 |            69.80 |
|           5 | Maria Foo | MF001    | ABC003       |        4 |            99.00 |
|           6 | Jim Papas | JP001    | ABC004       |        5 |             5.00 |
|           6 | Jim Papas | JP001    | ABC005       |        6 |            10.00 |
|           6 | Jim Papas | JP001    | ABC006       |        7 |            19.80 |
+-------------+-----------+----------+--------------+----------+------------------+
6 rows in set (0.00 sec)

Let’s take this slow. We initially see that it contains information from the customers table:

mysql> select customers.id as customer_id, customers.name, customers.identity from customers;
+-------------+-------------+----------+
| customer_id | name        | identity |
+-------------+-------------+----------+
|           4 | John Woo    | JW001    |
|           5 | Maria Foo   | MF001    |
|           6 | Jim Papas   | JP001    |
|           7 | Jessy Romeo | JR001    |
|           8 | Arya Stark  | AS001    |
+-------------+-------------+----------+
5 rows in set (0.00 sec)

We used the verbose way to enlist the properties of the customers because we had to alias the customers.id to customer_id. The desired result set references the order_number and the order_id. Cool! We can get them from the orders table, of course, by joining:

mysql> select customers.id as customer_id, customers.name, customers.identity, orders.order_number, orders.id as order_id from customers join orders on orders.customer_id = customers.id;
+-------------+-----------+----------+--------------+----------+
| customer_id | name      | identity | order_number | order_id |
+-------------+-----------+----------+--------------+----------+
|           4 | John Woo  | JW001    | ABC001       |        2 |
|           5 | Maria Foo | MF001    | ABC002       |        3 |
|           5 | Maria Foo | MF001    | ABC003       |        4 |
|           6 | Jim Papas | JP001    | ABC004
...