MySQL SQL Group By A sql-6-9.sql file has been opened for you. Write each of the

Question

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:

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)

Explanation / Answer

If you have any doubts, please give me comment...

As per Question:

SELECT P.id, name, AVG(stars)

FROM products P JOIN reviews R ON P.id = R.product_id

GROUP BY name, price, stock;

As per output:

SELECT P.id, name, AVG(stars)

FROM products P JOIN reviews R ON P.id = R.product_id

GROUP BY name, price, stock

HAVING AVG(stars)<=3.0;

Related Questions

Navigate

• Browse (All)
• Browse M
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.