Write a query to display all records in the orders table
SELECT * FROM orders;
Show me the employees whose name starts with A.
SELECT * FROM employees WHERE first_name ILIKE 'A%';
Show me the employees whose first name starts with A or last name ends with N.
SELECT * FROM employees WHERE first_name ILIKE 'A%' OR last_name ILIKE '%N';
Show only the order id for every order detail in the order details table
SELECT DISTINCT(order_id) FROM order_details;
Use an inner join to combine the order_details and products tables
SELECT * FROM order_details INNER JOIN products ON order_details.product_id = products.id;
Show only the order id, the product name, and the product's category id for every order detail in the order details table
SELECT order_details.order_id, products.product_name, products.category_id FROM order_details JOIN products ON order_details.product_id = products.id;
Show only the order id, the product name, and the product's category name for every order detail in the order details table
SELECT order_details.order_id, products.product_name, categories.category_name FROM order_details JOIN products ON order_details.products_id = products.id JOIN categories ON categories.id = products.category_id;
I want to see a list of all employee's with their boss's name. It is Ok, if you exclude the employee that has no boss.
SELECT reportee.first_name AS employee_first_name, reportee.last_name AS employee_last_name, boss.first_name AS boss_first_name, boss.last_name AS boss_last_name FROM employees AS reportee INNER JOIN employees AS boss ON reportee.reports_to_id = boss.id;
What if I want to see that but in reverse alphabetical order of boss's last name?
SELECT reportee.first_name AS employee_first_name, reportee.last_name AS employee_last_name, boss.first_name AS boss_first_name, boss.last_name AS boss_last_name FROM employees AS reportee INNER JOIN employees AS boss ON reportee.reports_to_id = boss.id ORDER BY boss.last_name DESC;
List all the orders taken by the sales representative Anne Dodsworth from the companies based in London along with the unit price and quantity of each order.
SELECT orders.id AS order_id, customers.company_name, products.product_name, order_details.unit_price, order_details.quantity FROM employees JOIN orders ON employees.id = orders.Employee_id JOIN customers ON customers.id = orders.customer_id JOIN order_details ON orders.id = order_details.order_id JOIN products ON products.id = order_details.product_id WHERE employees.first_name = 'Anne' AND employees.last_name = 'Dodsworth' AND customers.city = 'London';