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

select name from customers order by name asc;

select product_name from products where product_name ilike '%socks%';

select products.id, products.product_name, product_availability.unit_price, product_availability.supp_id from products inner join product_availability on product_availability.unit_price>100;

select products.id, products.product_name, product_availability.unit_price, product_availability.supp_id from products inner join product_availability on product_availability.unit_price>100 order by product_availability.unit_price desc limit 5;

select products.product_name,product_availability.unit_price,suppliers.supplier_name from ((products inner join product_availability on products.id = product_availability.prod_id) inner join suppliers on suppliers.id = product_availability.supp_id);

select products.product_name,suppliers.supplier_name from ((products inner join product_availability on products.id = product_availability.prod_id) inner join suppliers on suppliers.id = product_availability.supp_id) where suppliers.country = 'United Kingdom';

SELECT order_items.order_id, orders.order_reference, orders.order_date, products.product_name, order_items.quantity * product_availability.unit_price AS total_cost

FROM orders

INNER JOIN order_items

ON CAST(SUBSTRING(orders.order_reference, 4, LENGTH(orders.order_reference) - 3) AS INT) = order_items.order_id

INNER JOIN products

ON products.id = order_items.product_id

INNER JOIN product_availability

ON order_items.product_id = product_availability.prod_id

WHERE orders.customer_id = 1;

SELECT orders.id,orders.order_date,order_reference, products.product_name FROM orders

INNER JOIN customers ON orders.customer_id=customers.id

INNER JOIN order_items

ON CAST(SUBSTRING(orders.order_reference, 4, LENGTH(orders.order_reference) - 3) AS INT) = order_items.order_id

INNER JOIN products ON order_items.product_id=products.id

WHERE customers.name = 'Hope Crosby';