cyf_ecommerce_queries_exercise.sql

select name, address from customers where country = 'United States'

select * from customers order by name 

/* Retrieve all the products which cost more than 100 */

select * from products where unit_price > 100

/* Retrieve all the products whose name contains the word socks */

select * from products where product_name like '%socks%'

/* Retrieve the 5 most expensive products */

select * from products order by unit_price desc limit 5

/* Retrieve all the products with their corresponding suppliers.
 *  The result should only contain the columns product_name, unit_price and supplier_name
 */

select p.product_name, p.unit_price, s.supplier_name 
from products p join suppliers s on p.supplier_id  = s.id

/* Retrieve all the products sold by suppliers based in the United Kingdom. 
 * The result should only contain the columns product_name and supplier_name.
 * */

select p.product_name, s.supplier_name 
from products p join suppliers s on p.supplier_id  = s.id
where s.country = 'United Kingdom'

/* Retrieve all orders from customer ID 1
*/

select * from orders where customer_id = 1

/* Retrieve all orders from customer named Hope Crosby */
select o.*
from orders o join customers c on o.customer_id = c.id 
where c.name = 'Hope Crosby'

/* Retrieve all the products in the order ORD006. 
 * 
 * The result should only contain the columns product_name, unit_price and quantity.
 * */

select p.product_name, p.unit_price, oi.quantity
from (orders o join order_items oi on o.id = oi.order_id )
	join products p on oi.product_id = p.id
where order_reference = 'ORD006' 

/* 
 * 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.
 */


	
select customers.name, order_reference, order_date, product_name, supplier_name, quantity
from customers join orders on customers.id = orders.customer_id 
join order_items on orders.id = order_items.order_id 
join products on products.id = order_items.product_id 
join suppliers on products.supplier_id = suppliers.id

/* Retrieve the names of all customers who bought a product from a supplier from China.*/

select distinct customers.name
from customers join orders on customers.id = orders.customer_id 
join order_items on orders.id = order_items.order_id 
join products on products.id = order_items.product_id 
join suppliers on products.supplier_id = suppliers.id
where suppliers.country = 'China'