commerce-data

 --SELECT products.product_name, p.unit_price, s.supplier_name
 ---FROM product p
 --INNER JOIN suppliers
 ----ON p.id= s.products_id;

que 1. SELECT *FROM customers WHERE country = 'United States';
Que2. SELECT * FROM customers ORDER BY name; or SELECT * FROM customers ORDER BY name asc;
 Que3. SELECT * FROM products WHERE unit_price > 100;

 que4. SELECT * FROM products WHERE product_name LIKE '%socks%';

 Que5. SELECT * from products ORDER BY unit_price DESC LIMIT 5;
 Que6. SELECT product_name, unit_price, supplier_name  FROM products, suppliers WHERE products.supplier_id = suppliers.id;



 Que7. SELECT product_name, supplier_name  FROM products, suppliers WHERE products.supplier_id = suppliers.id AND suppliers.country = 'United Kingdom';

 Q8. SELECT * FROM orders, customers WHERE orders.customer_id = customers.id AND customers.id = 1;
 cyf_ecommerce=> SELECT * FROM  orders;
 id | order_date | order_reference | customer_id
----+------------+-----------------+-------------
  1 | 2019-06-01 | ORD001          |           1
  2 | 2019-07-15 | ORD002          |           1
  3 | 2019-07-11 | ORD003          |           1
  4 | 2019-05-24 | ORD004          |           2
  5 | 2019-05-30 | ORD005          |           3
  6 | 2019-07-05 | ORD006          |           4
  7 | 2019-04-05 | ORD007          |           4
  8 | 2019-07-23 | ORD008          |           5
  9 | 2019-07-24 | ORD009          |           5
 10 | 2019-05-10 | ORD010          |           5
(10 rows)

 q9. SELECT * FROM orders, customers WHERE orders.customer_id = customers.id AND customers.name = 'Hope Crosby';
 
cyf_ecommerce=> SELECT * FROM products;
 id |      product_name       | unit_price | supplier_id 
----+-------------------------+------------+-------------
  1 | Tee Shirt Olympic Games |         20 |           1
  2 | Tee Shirt Olympic Games |         18 |           2
  3 | Tee Shirt Olympic Games |         21 |           3
  4 | Mobile Phone X          |        299 |           1
  5 | Mobile Phone X          |        249 |           4
  6 | Super warm socks        |         10 |           1
  7 | Super warm socks        |          5 |           2
  8 | Super warm socks        |          8 |           3
  9 | Super warm socks        |         10 |           4
 10 | Le Petit Prince         |         10 |           1
 11 | Le Petit Prince         |         10 |           4
 12 | Ball                    |         14 |           1
 13 | Ball                    |         15 |           4
 14 | Ball                    |         20 |           2
 15 | Javascript Book         |         40 |           1
 16 | Javascript Book         |         39 |           3
 17 | Javascript Book         |         41 |           2
 18 | Coffee Cup              |          3 |           1
 19 | Coffee Cup              |          4 |           2
 20 | Coffee Cup              |          4 |           3
 21 | Coffee Cup              |          5 |           4
(21 rows)

Retrieve all the products in the order `ORD006`. The result should only contain the columns `product_name`, `unit_price` and `quantity`.
10. SELECT * FROM products,orders,order_items WHERE order_items.order_id=orders.id AND order_items.product_id=products.id AND orders.order_reference='ORD006';

 id |   product_name   | unit_price | supplier_id | id | order_date | order_reference | customer_id | id | order_id | product_id | quantity
----+------------------+------------+-------------+----+------------+-----------------+-------------+----+----------+------------+----------
 19 | Coffee Cup       |          4 |           2 |  6 | 2019-07-05 | ORD006          |           4 | 10 |        6 |         19 |        3
 17 | Javascript Book  |         41 |           2 |  6 | 2019-07-05 | ORD006          |           4 | 11 |        6 |         17 |        1
 11 | Le Petit Prince  |         10 |           4 |  6 | 2019-07-05 | ORD006          |           4 | 12 |        6 |         11 |        1
  9 | Super warm socks |         10 |           4 |  6 | 2019-07-05 | ORD006          |           4 | 13 |        6 |          9 |        3
(4 rows)


Q11. Retrieve all the products with their supplier for all orders of all customers. The result should only contain the columns `name` (from customer), `order_reference` `order_date`, `product_name`, `supplier_name` and `quantity`.
11.  SELECT customers.name, order_reference, order_date, product_name, supplier_name, quantity FROM customers, suppliers, orders, products, order_items WHERE order_items.order_id=orders.id AND order_items.product_id=products.id AND customers.id=orders.customer_id AND suppliers.id=products.id;

  

12. SELECT customers.name FROM customers,suppliers,orders,products,order_items WHERE order_items.order_id=orders.id AND order_items.product_id=products.id AND customers.id=orders.customer_id AND suppliers.id=products.id AND  suppliers.country='China';

     name
--------------
 Guy Crawford
(1 row)