SQL subqueries on the Sakila database.
/* Find the full name and email address of all customers that have rented an action movie. */
SELECT CONCAT(first_name, " ", last_name) AS name, email
FROM customer WHERE customer_id IN
(SELECT customer_id FROM rental WHERE inventory_id IN
(SELECT inventory_id FROM inventory WHERE film_id IN
(SELECT film_id FROM film_category JOIN category USING (category_id) WHERE category.name="Action")));
/* Find the name of each film, its category, and the aggregate number of films that fall within that category*/
SELECT film.title, category.name, `count`.count_of_category FROM film
JOIN film_category USING(film_id)
JOIN category USING (category_id)
JOIN (SELECT count(*) AS count_of_category, category_id FROM film_category fc GROUP BY category_id) `count` USING (category_id);
/* Average payment for each customer */
SELECT customer_id, first_name, last_name, AVG(amount) FROM customer JOIN payment USING(customer_id) GROUP BY customer_id;
/* All payments that exceed the average for each customer along with the total count of payments exceeding the average. */
SELECT customer_id, CONCAT(first_name, " ", last_name) AS name, amount, COUNT(amount) FROM customer
JOIN payment p1 USING(customer_id) WHERE amount >
(SELECT AVG(amount) FROM payment p2 WHERE p2.customer_id=p1.customer_id)
GROUP BY customer_id;
/* Create a new column classifying existing columns as either or high value transactions based on the amount of payment. */
SELECT payment_id, amount,
CASE WHEN amount >= 0 AND amount < 5 THEN 'low'
WHEN amount >= 5 THEN 'high'
END AS category
FROM payment;