FREE Data Analyst Course

Here are the 10 example SQL queries and home work problems. This is a blank file. Use it to practice and improve your SQL skills.

#10 Sample queries for SAKILA Database

# 1) All films with PG-13 films with rental rate of 2.99 or lower

# 2) All films that have deleted scenes

# 3) All active customers

# 4) Names of customers who rented a movie on 26th July 2005

# 5) Distinct names of customers who rented a movie on 26th July 2005

# H1) How many distinct last names we have in the data?

# 6) How many rentals we do on each day?

# 7) All Sci-fi films in our catalogue

# 8) Customers and how many movies they rented from us so far?

# 9) Which movies should we discontinue from our catalogue (less than 5 lifetime rentals)

# 10) Which movies are not returned yet?

# H2) How much money and rentals we make for Store 1 by day?
# What are the three top earning days so far?

Here are all the 10 queries completed against SAKILA sample database in MySQL.

# 1) All films with PG-13 RATING with rental rate of 2.99 or lower

SELECT * FROM film f
where f.rental_rate <=2.99
and f.rating = ‘PG-13’;

# 2) All films that have deleted scenes

select f.title, f.special_features, f.release_year
from film f
where f.special_features like ‘%Deleted Scenes%’
and title like ‘c%’;

# 3) All active customers

select * from customer
where active=1;

# 4) Names of customers who rented a movie on 26th July 2005

select r.rental_id, r.rental_date, r.customer_id,
concat(c.first_name,’ ‘, c.last_name) ‘Full name’ from rental r
join customer c on c.customer_id = r.customer_id
where date(r.rental_date) = ‘2005-07-26’;

# 5) Distinct names of customers who rented a movie on 26th July 2005
select distinct r.customer_id,
concat(c.first_name,’ ‘, c.last_name) ‘Full name’ from rental r
join customer c on c.customer_id = r.customer_id
where date(r.rental_date) = ‘2005-07-26’;

# H1) How many distinct last names we have in the data?

# 6) How many rentals we do on each day?

select date(rental_date) d, count(*) from rental
group by date(rental_date);

# 7) All Sci-fi films in our catalogue

SELECT * FROM CATEGORY;

SELECT * FROM film_category WHERE category_id=14;

select fc.film_id, fc.category_id,c.name, f.title, f.release_year from film_category fc
join category c on c.category_id = fc.category_id
join film f on f.film_id = fc.film_id
where c.name=’Sci-Fi’;

# 8) Customers and how many movies they rented from us so far?

select r.customer_id, c.first_name, c.email, count(*) ‘Count’
from rental r
join customer c on c.customer_id = r.customer_id
group by r.customer_id
order by count(*) desc;

select c.customer_id, c.first_name, c.email, count(r.customer_id)
from customer c
left outer join rental r on c.customer_id = r.customer_id
group by c.customer_id;

# 9) Which movies should we discontinue from our catalogue (less than 5 lifetime rentals)

// Refer to the correct query below.

with low_rentals as
(select inventory_id, count(*)
from rental r
group by inventory_id
having count(*)<=1)
select low_rentals.inventory_id, i.film_id, f.title
from low_rentals
join inventory i on i.inventory_id = low_rentals.inventory_id
join film f on f.film_id = i.film_id;

// CORRECT QUERY

with low_rentals as
(select i.film_id, count(*)
from rental r
join inventory i on i.inventory_id = r.inventory_id
group by i.film_id
having count(*)<=5)
select low_rentals.film_id, f.title
from low_rentals
join film f on f.film_id = low_rentals.film_id;

# 10) Which movies are not returned yet?

SELECT rental_date, customer_id, i.film_id, f.title
FROM RENTAL r
join inventory i on i.inventory_id = r.inventory_id
join film f on f.film_id = i.film_id
WHERE r.return_date is null
order by f.title;

# H2) How much money and rentals we make for Store 1 by day?
# What are the three top earning days so far?

# H1) How many distinct last names we have in the data?

select count(distinct last_name) from customer;

# H2) How much money and rentals we make for Store 1 by day?

select date(p.payment_date),sum(p.amount) from payment p
join rental r on r.rental_id = p.rental_id
join inventory i on i.inventory_id = r.inventory_id
where i.store_id = 1
group by date(p.payment_date)
order by date(p.payment_date);

# What are the three top earning days so far?

select date(p.payment_date),sum(p.amount) from payment p
join rental r on r.rental_id = p.rental_id
join inventory i on i.inventory_id = r.inventory_id
where i.store_id = 1
group by date(p.payment_date)
order by sum(p.amount) desc
limit 3