MySQL

SQL Group By

A sql-6-9.sql file has been opened for you.

Write each of the following tasks as a SQL statement in a new line (remember that you can source the file to compare the output reference):

1 - Use the e_store database

2 - Join the products and the reviews tables that would output this:

----+------------+-----------+ | id | name | avg_stars | +----+------------+-----------+ | 2 | Smartphone | 2.3333 | | 3 | PC | 3.0000 | +----+------------+-----------+ 2 rows in set (0.01 sec)

TABLES

SELECT * FROM e_store.products; +----+------------+--------+-------+---------------------+ | id | name | price | stock | created_at | +----+------------+--------+-------+---------------------+ | 1 | Tablet | 145.50 | 8 | 2015-10-09 00:49:30 | | 2 | Smartphone | 90.80 | 9 | 2015-10-28 04:43:05 | | 3 | PC | 157.40 | 1 | 2015-10-10 18:13:09 | | 4 | Headphones | 20.00 | 6 | 2015-10-30 06:30:30 | | 5 | Speakers | 56.89 | 4 | 2015-10-27 12:24:56 | | 6 | Monitor | 125.45 | 7 | 2015-10-22 11:32:53 | +----+------------+--------+-------+---------------------+ 6 rows in set (0.00 sec)

SELECT * FROM e_store.reviews; +----+------------+-------+ | id | product_id | stars | +----+------------+-------+ | 1 | 6 | 5 | | 2 | 4 | 2 | | 3 | 2 | 1 | | 4 | 4 | 5 | | 5 | 1 | 5 | | 6 | 2 | 4 | | 7 | 3 | 3 | | 8 | 2 | 2 | | 9 | 1 | 3 | +----+------------+-------+ 9 rows in set (0.00 sec)