Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save sergiandreplace/61eb259284d6aad8795c6a123474f56b to your computer and use it in GitHub Desktop.
Save sergiandreplace/61eb259284d6aad8795c6a123474f56b to your computer and use it in GitHub Desktop.
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'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment