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

We’ll now execute the following commands to create the necessary data to support our query exercises.

First, we create the `customers`:



insert into customers (name, identity) values ('John Woo', 'JW001');
insert into customers (name, identity) values ('Maria Foo', 'MF001');
insert into customers (name, identity) values ('Jim Papas', 'JP001');
insert into customers (name, identity) values ('Jessy Romeo', 'JR001');
insert into customers (name, identity) values ('Arya Stark', 'AS001');

We get a `Succeeded` response, which means the queries executed successfully and there’s nothing to show.

If we select the `customers`, we should see this:

```
mysql> select * from customers;
+----+-------------+----------+
| 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)
```
Click "Run" to verify the result:

# The first customer’s order

Then, create orders and order items. Insert one order with one order item for the first customer:

```
mysql> insert into orders (order_number, ordered_at, customer_id) values ('ABC001', current_timestamp, 4);
Query OK
mysql> select * from orders;
+----+--------------+---------------------+-------------+
| id | order_number | ordered_at          | customer_id |
+----+--------------+---------------------+-------------+
|  2 | ABC001       | 2016-09-12 21:36:56 |           4 |
+----+--------------+---------------------+-------------+
1 row in set (0.02 sec)
mysql> insert into order_items (order_id, product_id, price, quantity) values (2, 1, 50.00, 2);
Query OK
```

For hands-on practice, try the code widget below:


Getting Started

Manage MySQL Server

Basic SQL Commands

Add Columns and Indexes

Count and Sort Records

Foreign Keys

Joins, Left Joins And Subqueries

Conclusion

Insert Necessary Data

The first customer’s order