1.Recap
The SQL JOIN keyword helps SQL developers to answer questions about their data faster. Remember that in order to create a SQL JO1N, two or more tables need to have a key in common. This key in common is part of SQL constraints and is called FOREIGN KEY. A FOREIGN KEY is usually an integer number that REFERENCES the id of another table column It is important to know that foreign keys need to have the same data type configuration in order to be valid reference keys. On the other hand, the SQL UNIQUE KEY allows no repeated values in the same table column fields.
2. What we'll learn
In this unit, you will learn how to improve your SQL SELECT queries by using the SQL JOIN keyword together with SQL aggregate functions.
SQL aggregate functions allow SQL developers to manipulate number, string and date values in a SELECT query.
In this unit, we will learn how to use the following functions by working with an e_store database:
AVG() COUNT() MAX() MIN() SUM() GROUP BY
3. E-store DB
E-commerce is one of the most popular business-models on the internet.
Regardless of the products that the e-commerce platform may sell, its data structure may be similar to the e_store database that we are going to use for this unit.
Inspect the e_store tables in the mysql> prompt:
SHOW COLUMNS FROM e_store.products;
Field
| Type
| Null | Key | Default Extra
id name price stock created at
int(3) unsigned 1 NO varchar(255) I NO float(8,2) NO int(3) unsigned | NO datetime I NO
PRI
NULL NULL 0.00 I 0 | NULL
SHOW COLUMNS FROM e_store.reviews;
Field
Type
Null Key
Default
Extra
id product id stars
int(3) unsigned NO int(3) unsigned NO tinyint(1) unsigned | NO
PRI NULL MUL NULL I 1
au
3 rows in set (0.00 sec)
3. 1. Unit approach
Now that you've reviewed the e_store database table structure, it's time to know how you are going to work with its data. Imagine that the user wants to get from the product catalog:
The product with the lowest price The product with the highest price The most popular product The product with most stock The most recent product
While the user is viewing a single product, he/she may want to:
Get the average rating of the product
The user creates a wishlist with two or more products and he/she may want to:
: Get the average rating of each product in the wishlist
Finally, after the user adds products to the shopping cart, he/she may want to:
Get the product count
Get the total price
All right! Time to learn how to use SQL aggregate functions!
4. SQL MINO
The first e_store use case will be:
Get the product with the lowest price First,do a SELECT * FROM e_store.products; to get all the records from the e_store.products table.
Now, let's get the product with the lowest price in the mysq1> command line:
mysql> SELECT MIN(price) -> FROM e_store.products;