Gain insights into relational databases and SQL. Learn about creating, managing, and editing tables using MySQL. Explore complex queries, indexes, and join functions for efficient data handling.

testmysql.tar.gz

sql spa

Relational databases store data in a row-based table structure. Structured Query Language (SQL) is a core language used to retrieve data from the relational database. One of its most widely used extensions, MySQL, also gives you the power to edit, create, and manage the queried data.

In this course, you’ll be introduced to the basics of relational database management with SQL. You’ll use MySQL to create a table, edit the data, and even change the structure. You’ll also create indexes to quickly access data for faster performance on common queries. Additionally, you’ll explore more complex queries to extract useful data with aggregate functions, grouping, ordering, and creating limits and filters in output tables. Finally, you’ll learn how to combine data from different tables with join functions.

By the end of this course, you’ll be able to create, manage, edit, and merge relational databases with confidence. MySQL will prepare you for data projects large and small.

Getting Started with SQL and Relational Databases



# Why do we need to group? 
This is going to be a little bit more difficult, but we’ll answer this question in small steps.

If we list the `orders_items`, we see that we have the `price` and the `quantity` columns.


```
mysql> select * from order_items;
+----+----------+------------+-------+----------+
| id | order_id | product_id | price | quantity |
+----+----------+------------+-------+----------+
|  4 |        2 |          1 | 50.00 |        2 |
|  5 |        3 |          1 | 50.00 |        1 |
|  6 |        3 |          2 | 19.80 |        1 |
|  7 |        4 |          2 | 19.80 |        5 |
|  8 |        5 |          3 |  5.00 |        1 |
|  9 |        6 |          3 |  5.00 |        2 |
| 10 |        7 |          2 | 19.80 |        1 |
+----+----------+------------+-------+----------+
7 rows in set (0.00 sec)
```
The total amount of the first order with an `id` value of `2` is 2 x 50.00 = 100.00. The total amount of the order with an `id` value of `3` is 1 x 50.00 + 1 x 19.80 = 69.80, and the total amount of the order with an `id` value of `4` is 5 x 19.80 = 99.00.

This means that the `order_items` table has all the necessary information to build a result set like the following.

```
mysql> 
+----------+------------------+
| order_id | amount_per_order |
+----------+------------------+
|        2 |           100.00 |
|        3 |            69.80 |
|        4 |            99.00 |
|        5 |             5.00 |
|        6 |            10.00 |
|        7 |            19.80 |
+----------+------------------+
6 rows in set (0.01 sec)
```
How can we get there? Let’s try the following first:
```
mysql> select order_items.order_id, order_items.price * order_items.quantity as amount_per_order_item from order_items;
+----------+-----------------------+
| order_id | amount_per_order_item |
+----------+-----------------------+
|        2 |                100.00 |
|        3 |                 50.00 |
|        3 |                 19.80 |
|        4 |                 99.00 |
|        5 |                  5.00 |
|        6 |                 10.00 |
|        7 |                 19.80 |
+----------+-----------------------+
7 rows in set (0.00 sec)
```
The result set above doesn’t differ much from the following:

```

mysql> select * from order_items;
+----+----------+------------+-------+----------+
| id | order_id | product_id | price | quantity |
+----+----------+------------+-------+----------+
|  4 |        2 |          1 | 50.00 |        2 |
|  5 |        3 |          1 | 50.00 |        1 |
|  6 |        3 |          2 | 19.80 |        1 |
|  7 |        4 |          2 | 19.80 |        5 |
|  8 |        5 |          3 |  5.00 |        1 |
|  9 |        6 |          3 |  5.00 |        2 |
| 10 |        7 |          2 | 19.80 |        1 |
+----+----------+------------+-------+----------+
7 rows in set (0.00 sec)
```

However, there are a few differences, as listed below:

- It doesn’t display the `id` column.
- It doesn’t display the `product_id` column.
- The two `price` and `quantity` columns have been multiplied and now give the `amount_per_order_item`.

As we can see, the snippet `order_items.price * order_items.quantity as amount_per_order_item` multiplies the values of the two columns and sets a label to the result. This is why the new derived-value column is displayed with the label `amount_per_order_item`.

Cool! Now, let’s try another concept:

```
mysql> select sum(price), sum(quantity) from order_items;
+------------+---------------+
| sum(price) | sum(quantity) |
+------------+---------------+
|     169.40 |            13 |
+------------+---------------+
1 row in set (0.01 sec)
```

The `sum()` function can be used on a column. Then, SQL will sum the values of that column for all the rows that match the result set criteria. The `sum()` is an aggregate SQL function. We’ll learn more about that later on.

# Show the total quantity of each order

```
mysql> select order_items.order_id, sum(price), sum(quantity) from order_items group by order_id;
+----------+------------+---------------+
| order_id | sum(price) | sum(quantity) |
+----------+------------+---------------+
|        2 |      50.00 |             2 |
|        3 |      69.80 |             2 |
|        4 |      19.80 |             5 |
|        5 |       5.00 |             1 |
|        6 |       5.00 |             2 |
|        7 |      19.80 |             1 |
+----------+------------+---------------+
6 rows in set (0.00 sec)
```
This is even better because it applies the aggregate functions (the two `sum()` function calls) to all rows that match the criteria, but separately for each `order_id`. Double-check the amounts. Do the `prices` of `order_id 2` sum up to `50.00`? Do the `prices` of `order_id 3` sum up to `69.80`? What about the sums on the `quantity` columns? Do they match?

It seems that we’re getting closer and closer to our target. We’re only left to combine the information from `price` and `quantity` before summing up. The combination needs to be a multiplication.


# Show the total amount of each order

```
mysql> select order_items.order_id, sum(price * quantity) as amount_per_order from order_items group by order_id;
+----------+------------------+
| order_id | amount_per_order |
+----------+------------------+
|        2 |           100.00 |
|        3 |            69.80 |
|        4 |            99.00 |
|        5 |             5.00 |
|        6 |            10.00 |
|        7 |            19.80 |
+----------+------------------+
6 rows in set (0.00 sec)
```

Perfect! We now display the sums of prices multiplied by quantities, but we limit the summation/aggregation to the matching `order_ids` so that there’s one summation/aggregation for each `order_id`.

Practice in the given widget below for better understanding:


Getting Started

Manage MySQL Server

Basic SQL Commands

Add Columns and Indexes

Count and Sort Records

Foreign Keys

Joins, Left Joins And Subqueries

Conclusion

Group-Based Aggregation

Why do we need to group?