"List the name and price of all products that are more expensive than all products in the 'Toys' department."
SELECT name, price FROM products WHERE price > ( SELECT MAX(price) FROM products WHERE department = 'Toys' );
Subqueries can be used as:
SELECT COUNT(*) FROM products.
SELECT * FROM order.
SELECT id FROM order.
SELECT name, ( SELECT COUNT(*) from example ) as count FROM users;
SELECT name, price_weight_ratio FROM ( SELECT name, price / weight AS price_weight_ratio FROM products ) AS p WHERE price_weight_ratio > 5;
Note: a subquery must have an alias applied to it in a FROM clause.
SELECT * FROM ( SELECT MAX(price) FROM products) AS p; );
"Find the average number of orders for all users."
We can use a subquery to find the average number of orders for all users (although it is still an INTEGER in our case so far).
SELECT AVG(order_count) FROM ( -- Using GROUP BY SELECT user_id, COUNT(*) AS order_count FROM orders GROUP BY user_id ) AS p;
Note: There are far easier ways to do this than a subquery. Take notice of performance when writing these.
Here is a contrived example of a subquery in a JOIN clause.
SELECT first_name FROM users JOIN ( SELECT user_id FROM orders WHERE product_id = 3 ) AS o ON o.user_id = users.id;
Note: requires alias as well.
Here is another contrived example.
"Show the id of order that involve a product with a price/weight ration greater than 5."
SELECT id FROM orders WHERE product_id IN ( SELECT id FROM products WHERE price / weight > 50 );
Note: it was brought up here about performance and that rewriting this as a JOIN statement might actually end up the same with Performance thanks to how Postgres interprets the query.
NOT INwill return a single column, as well as operators with the
A contrived example from the quiz:
SELECT name, price FROM phones WHERE price > ( SELECT price FROM phones WHERE name = 'S5620 Monte' AND manufacturer = 'Samsung' );
SELECT name FROM products WHERE department NOT IN ( SELECT department FROM products WHERE price < 100 );
"Show the name, department and price of products that are more expensive than all products in the 'Industrial' department".
The following is a contrived example to make use of the
> ALL keyword.
SELECT name, department, price FROM products WHERE price > ALL ( SELECT price FROM products WHERE department = 'Industrial' );
SOME does as you might expect where you want the clause like
50 < SOME to evaluate such that at least one value is less than 50.
"Show the name, department and price of the most expensive product in each department."
SELECT name, department, price FROM products AS p1 WHERE p1.price = ( SELECT MAX(price) FROM products AS p2 WHERE p2.department = p1.department );
Correlated subqueries makes use of aliases to help with reducing complexity.
We are referring to a row in our top query in our subquery.
"Without using a join or a group by, print the number of orders for each product."
We can make use of correlated subqueries within select too!
SELECT name, ( SELECT COUNT(*) FROM orders AS o1 WHERE o1.product_id = p1.id ) AS num_orders FROM products AS p1
SELECT ( SELECT MAX(price) FROM products ) AS max;
Why would we ever do this? Maybe when we want to calculate one value from multiple operations.
A contrived example:
SELECT ( SELECT MAX(price) FROM products ) / ( SELECT MIN(price) FROM products ) AS max_div_min;