SQL is a Programming language that is used by most relational database management systems (RDBMS) to manage data stored in tabular form (i.e. tables).
A relational database consists of multiple tables that relate to each other. The relation between tables is formed in the sense of shared columns.
In the previous post, we designed and created a sales database that contains 4 relational tables. The following figure displays the structure of the database and tables.
We have created the tables but left them empty. In this post, we will populate these tables with appropriate data and then run queries to retrieve data from them.
Inserting data into the tables
Let’s first take a look at the tables. I’m using the terminal to connect to the sales database I created.
$ sudo mysql -u rootmysql> use sales; #connecting to the sales databasemysql> show tables;
+-----------------+
| Tables_in_sales |
+-----------------+
| customer |
| item |
| purchase |
| store |
+-----------------+We have 4 tables but all of them are empty. As I have also mentioned in the previous post, some columns are marked as primary keys or foreign keys.
- Primary key is the column that uniquely identifies each row. It is like the index of a pandas dataframe.
- Foreign key is what relates a table to another one. Foreign key contains the primary key of another table. For instance, the "item_id" in the purchase table is a foreign key. It stores the rows from the primary key in the item table.
Since the tables contain foreign keys, we need to be careful when inserting data. For instance, if we first insert values to the customer table, we need to set the store_id column as NULL because it refers to the primary key of the store table. After creating store_id values in the store table, we can update the values in the customer table.
It will become clear with the following example. We first insert a row in the customer table.
mysql> INSERT INTO customer VALUES(1, "John", "Doe", "M", NULL);mysql> select * from customer;
+---------+--------+--------+--------+----------+
| cust_id | f_name | l_name | gender | store_id |
+---------+--------+--------+--------+----------+
| 1 | John | Doe | M | NULL |
+---------+--------+--------+--------+----------+The value in the store_id column is NULL. We can update it after creating the store_id primary key in the store table.
mysql> INSERT INTO store VALUES(
-> 1, "1234 Delaware Avenue", "Ashley");mysql> INSERT INTO store VALUES(2, "1234 West Street", "Max");mysql> INSERT INTO store VALUES(3, "1234 Kirkwood Street", "Emily");This retail business owns 3 stores. Here is the store table.
mysql> select * from store;
+----------+----------------------+---------+
| store_id | address | manager |
+----------+----------------------+---------+
| 1 | 1234 Delaware Avenue | Ashley |
| 2 | 1234 West Street | Max |
| 3 | 1234 Kirkwood Street | Emily |
+----------+----------------------+---------+We can now update the customer table and change the store_id in the first row.
mysql> UPDATE customer SET store_id=1 WHERE cust_id=1;mysql> select * from customer;
+---------+--------+--------+--------+----------+
| cust_id | f_name | l_name | gender | store_id |
+---------+--------+--------+--------+----------+
| 1 | John | Doe | M | 1 |
+---------+--------+--------+--------+----------+Please note that we could have inserted data into the store table first and then into the customer table. In this way, we would not need to go back and update NULL values. I wanted to also show this way for practicing.
Let’s add a few more customers in the customer table.
mysql> INSERT INTO customer VALUES(2, "Jane", "Doe", "F", 1);
mysql> INSERT INTO customer VALUES(3, "Elaine", "Smith", "F", 2);
mysql> INSERT INTO customer VALUES(4, "Adam", "Gelvin", "M", 3);
mysql> INSERT INTO customer VALUES(5, "Robert", "Sam", "M", 1);
mysql> INSERT INTO customer VALUES(6, "Betty", "J.", "F", 2);
mysql> INSERT INTO customer VALUES(7, "Alisha", "T.", "F", 3);Here is the current version of the customer table.
mysql> select * from customer;
+---------+--------+--------+--------+----------+
| cust_id | f_name | l_name | gender | store_id |
+---------+--------+--------+--------+----------+
| 1 | John | Doe | M | 1 |
| 2 | Jane | Doe | F | 1 |
| 3 | Elaine | Smith | F | 2 |
| 4 | Adam | Gelvin | M | 3 |
| 5 | Robert | Sam | M | 1 |
| 6 | Betty | J. | F | 2 |
| 7 | Alisha | T. | F | 3 |
+---------+--------+--------+--------+----------+I have populated the item and purchase tables similarly. You can come up with your own made-up values.
Here is the item table.
mysql> select * from item;
+---------+----------------+-------+----------+
| item_id | description | price | store_id |
+---------+----------------+-------+----------+
| 1 | apple | 2.45 | 1 |
| 2 | banana | 3.45 | 1 |
| 3 | cereal | 4.20 | 2 |
| 4 | milk 1 liter | 3.80 | 2 |
| 5 | cheddar cheese | 4.50 | 2 |
| 6 | icecream | 6.10 | 2 |
| 7 | water 2 liters | 1.10 | 3 |
| 8 | tomato | 0.95 | 1 |
| 9 | egg 15 | 4.40 | 3 |
| 10 | sprite 1 liter | 1.60 | 3 |
+---------+----------------+-------+----------+Finally, the purchase table is as below.
Retrieve data with SELECT statement
The tables in the sales database contain some data now. We can retrieve all or some of the data stored in these tables.
The SELECT statement of SQL is quite flexible so we can create simple or advanced queries easily.
In the following examples, we will write SELECT statements to retrieve the desired data.
Example 1: The IDs of customers who made a purchase on 2020–05–10
We will select the cust_id column and set a condition on date column using the WHERE keyword.
mysql> SELECT cust_id FROM purchase
-> WHERE date = "2020-05-10";
+---------+
| cust_id |
+---------+
| 2 |
| 1 |
| 4 |
| 7 |
| 3 |
| 3 |
| 1 |
| 5 |
+---------+As you can see in the output, some IDs are repeated. We can use the DISTINCT keyword to only display unique elements.
mysql> SELECT DISTINCT cust_id FROM purchase
-> WHERE date = "2020-05-10";
+---------+
| cust_id |
+---------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 7 |
+---------+Example 2: Number of purchases at each store
We will select the store_id and apply the count function. We also need to group the count by store_id.
mysql> SELECT store_id, COUNT(store_id) FROM purchase
-> GROUP BY store_id;
+----------+-----------------+
| store_id | COUNT(store_id) |
+----------+-----------------+
| 1 | 6 |
| 2 | 3 |
| 3 | 1 |
+----------+-----------------+Example 3: Cost of the most expensive item
We will select the price from the item table and apply the max function.
mysql> SELECT MAX(price) FROM item;
+------------+
| MAX(price) |
+------------+
| 6.10 |
+------------+Conclusion
In the previous article, we designed and created the schema of a relational database. In this article, we have populated the tables using the INSERT statement. After, we have run some queries with SELECT statements to retrieve data.
In the next article, I will write about creating more advanced queries. We will see the UNION and JOIN operators.
We will also cover how to edit tables in terms of adding and removing columns and also deleting rows.
Thank you for reading. Please let me know if you have any feedback.
