Tutorial Query List tabel categori, produk, sub category pada MySQL

1.Tampilkan Nama produk,, Sub kategori, Kategori

SELECT product_name,category_name,sub_category_name FROM product,sub_category,category WHERE product.sub_category_id = sub_category.sub_category_id AND sub_category.category_id = category.category_id;

2.Nama produk, Sub kategori, Kategori yang kategori makanan

SELECT product_name,category_name,sub_category_name FROM product,sub_category,category WHERE product.sub_category_id = sub_category.sub_category_id AND sub_category. category_id = category.category_id AND category.category_name=’Makanan’;

3.Nama produk, Sub kategori, Kategori yang kategori minuman

SELECT product_name,category_name,sub_category_name FROM product,sub_category,category WHERE product.sub_category_id = sub_category.sub_category_id AND sub_category. category_id = category.category_id AND category.category_name=’Minuman’;

4.Nama produk, Sub kategori, Kategori sub kategori minuman dingin

SELECT product_name,category_name,sub_category_name FROM product,sub_category,category WHERE product.sub_category_id = sub_category.sub_category_id AND sub_category. category_id = category.category_id AND sub_category.sub_category_name=’Minuman dingin’;

5.Nama produk, Sub kategori, Kategori yang mengandung kata kacang

SELECT product_name,category_name,sub_category_name FROM product,sub_category,category WHERE product.sub_category_id = sub_category.sub_category_id AND sub_category. category_id = category.category_id AND product.product_name LIKE ’%Kacang%’;

6.Tampilkan rerata harga produk

SELECT AVG(product_price)  AS rerata_product_price FROM product;

7.Tampilkan harga paling tinggi

SELECT MAX(product_price)  AS maximum_product_price FROM product;

8.Tampilkan harga paling rendah

SELECT MIN(product_price)  AS minimum_product_price FROM product;

9.Jumlah kategori minuman

SELECT product_name,count(*) AS jumlah FROM product,sub_category,category WHERE product.sub_category_id = sub_category.sub_category_id AND sub_category.category_id = category.category_id AND category_name = ‘Minuman’;

10.Jumlah kategori makanan

SELECT product_name,count(*) AS jumlah FROM product,sub_category,category WHERE product.sub_category_id = sub_category.sub_category_id AND sub_category.category_id = category.category_id AND category_name = ‘Makanan’;