- 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 LIKE 'A%';
Note: ILIKE can be used for case-insensitive search.
- Show me the employees whose first name starts with A or last name ends with N.
SELECT * FROM employees WHERE first_name LIKE 'A%' OR last_name LIKE '%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;
See also:
- http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/
- http://en.wikipedia.org/wiki/Join_%28SQL%29
- 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.product_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, manager.first_name AS manager_first_name, manager.last_name AS manager_last_name FROM employees AS reportee INNER JOIN employees AS manager ON reportee.reports_to_id = manager.id;
NOTE: That query only shows employees with managers.
SELECT reportee.first_name AS employee_first_name, reportee.last_name AS employee_last_name, manager.first_name AS manager_first_name, manager.last_name AS manager_last_name FROM employees AS reportee LEFT OUTER JOIN employees AS manager ON reportee.reports_to_id = manager.id;
NOTE: This shows all employees, regardless of whether they report to anyone.
- 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, manager.first_name AS manager_first_name, manager.last_name AS manager_last_name FROM employees AS reportee LEFT OUTER JOIN employees AS manager ON reportee.reports_to_id = manager.id ORDER BY manager.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, name, and quantity of each product.
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';