Scribd
Upload
182 views2 pages

SQL Practice Answer Key

The document provides SQL practice tasks with corresponding answer keys for the Sakila and World databases. It includes queries for counting customer rentals, identifying highest payments, finding overdue rentals, calculating revenue by store, and averaging payments per film, as well as tasks related to cities and countries in the World database. Each task is presented with a SQL SELECT statement demonstrating the required data retrieval methods.

Uploaded by

cymbelinelignes
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
182 views2 pages

SQL Practice Answer Key

The document provides SQL practice tasks with corresponding answer keys for the Sakila and World databases. It includes queries for counting customer rentals, identifying highest payments, finding overdue rentals, calculating revenue by store, and averaging payments per film, as well as tasks related to cities and countries in the World database. Each task is presented with a SQL SELECT statement demonstrating the required data retrieval methods.

Uploaded by

cymbelinelignes
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

SQL Practice Tasks - Answer Key (Sakila

& World DB)


Task 1: Count Customer Rentals
SELECT c.customer_id, c.first_name, c.last_name, COUNT(r.rental_id) AS total_rental
FROM customer c
JOIN rental r ON c.customer_id = r.customer_id
GROUP BY c.customer_id
ORDER BY total_rental DESC;

Task 2: Highest Payment by Each Customer


SELECT c.customer_id, c.first_name, c.last_name, MAX(p.amount) AS max_payment
FROM customer c
JOIN payment p ON c.customer_id = p.customer_id
GROUP BY c.customer_id;

Task 3: Find Overdue Rentals


SELECT c.customer_id, c.first_name, c.last_name, COUNT(r.rental_id) AS overdue_count
FROM customer c
JOIN rental r ON c.customer_id = r.customer_id
WHERE r.return_date IS NULL
GROUP BY c.customer_id
HAVING overdue_count > 0;

Task 4: Revenue by Store


SELECT s.store_id, SUM(p.amount) AS total_revenue
FROM payment p
JOIN staff sf ON p.staff_id = sf.staff_id
JOIN store s ON sf.store_id = s.store_id
GROUP BY s.store_id;

Task 5: Average Payment per Film


SELECT f.film_id, f.title, AVG(p.amount) AS average_payment
FROM payment p
JOIN rental r ON p.rental_id = r.rental_id
JOIN inventory i ON r.inventory_id = i.inventory_id
JOIN film f ON i.film_id = f.film_id
GROUP BY f.film_id;
Task 6 (World DB): Cities in a Region
SELECT ci.ID AS city_id, ci.Name AS city_name, co.Name AS country_name, co.Region
FROM city ci
JOIN country co ON ci.CountryCode = co.Code
WHERE co.Region = 'Southeast Asia';

Task 7 (World DB): Count Countries per Continent


SELECT Continent, COUNT(*) AS country_count
FROM country
GROUP BY Continent;

Task 8 (World DB): Top 5 Most Populated Cities


SELECT ID AS city_id, Name AS city_name, Population
FROM city
ORDER BY Population DESC
LIMIT 5;

You might also like