Skip to content

I honed my SQL skills in this repository, progressing from beginner-level challenges to advanced mastery.

License

Notifications You must be signed in to change notification settings

AvGeekGupta/PracticeSQLonSakila

Repository files navigation

PracticeSQLonSakila

This repository contains the solutions to the practice question on sakila database. The questions are divided into 3 levels: Beginner, Intermediate, Advanced and Expert. The questions are listed below.

Beginner Level

  1. Retrieve all columns from the customer table.

  2. List the first and last names of customers in the customer table.

  3. Show all the distinct cities in the city table.

  4. Display all the film titles from the film table.

  5. List the titles of films along with their release years.

  6. Retrieve the names and emails of active customers.

  7. Get the total number of films available in the inventory.

  8. Show the rental rate and rental duration for films.

  9. Display all the categories of films.

  10. List the names, emails and phone numbers of customers.

  11. Retrieve the titles of films that are available for rent.

  12. List the names of all employees.

  13. Show the number of films in each category.

  14. Get the rental rates of films with a rating of 'PG'.

  15. List all the staff members.

  16. Display the rental duration and rental rate for films in a specific category.

  17. List the names and emails of customers who have rented films.

  18. Show the cities in which customers live.

  19. List the titles of films released in a specific year.

  20. Retrieve the staff members and their contact details.

  21. Show the films that are not available for rent.

  22. List the top 10 customers who have spent the most.

  23. Get the names of actors who have appeared in more than 30 films.

  24. List the rental dates for a specific customer.

  25. Show the films that have a replacement cost of $20.

Intermediate Level

  1. Retrieve the names of customers who have rented more than 5 films.

  2. List the film titles and their respective language names.

  3. Show the films that are not in stock.

  4. List the actors who have appeared in the same film.

  5. Retrieve the names of customers who live in the same city as a specific customer.

  6. Show the total revenue generated from each film.

  7. Get the films that have a replacement cost greater than the average replacement cost.

  8. List the customers who have rented the same film.

  9. Show the staff members and their total sales.

  10. List the top 5 films with the highest total rental revenue.

  11. Retrieve the customers who have rented more than one film on the same day.

  12. Display the average rental duration for each rating category.

  13. List the actors who have appeared in the most rented films.

  14. Show the customers who have rented the most films in each category.

  15. Retrieve the films that were rented more than 10 times in the last month.

  16. List the staff members and the number of rentals they have handled.

  17. Show the films that were rented by customers living in a specific city.

  18. Retrieve the films that have not been rented yet.

  19. List the actors who have appeared in films with the same rating.

  20. Show the total rental revenue for each customer.

  21. List the customers who have rented all the films in a specific category.

  22. Retrieve the films with the highest total rental revenue.

  23. Display the number of rentals for each actor.

  24. List the staff members who have handled rentals on a specific date.

  25. Show the customers who have rented the most films in a month.

Advanced Level

  1. Retrieve the films that are available for rent and have not been rented yet.

  2. List the customers who have rented the same film multiple times.

  3. Show the films that have been rented at least once on each day of the week.

  4. Retrieve the actor who has appeared in the most diverse categories of films.

  5. List the customers who have rented films with the same rating as a specific film.

  6. Show the top 5 actors with the highest total rental revenue.

  7. Retrieve the films with the highest rental duration in each category.

  8. List the top 10 customers who have rented the most diverse films.

  9. Display the average rental revenue for each day of the week.

  10. Show the actors who have appeared in films with the same language as a specific film.

  11. Retrieve the film titles and the names of the actors who have appeared in them.

  12. List the films that are only available for rent in a specific store.

  13. Show the total rental revenue for each category.

  14. Retrieve the top 5 customers who have spent the most on rentals.

  15. List the customers who have rented the same set of films as another customer.

  16. Display the actors who have appeared in films with the same director.

  17. Show the total rental revenue for each actor.

  18. Retrieve the customers who have rented a film from every category.

  19. List the films that have the same rental duration and rental rate as a specific film.

  20. Show the customers who have not rented any films.

  21. Retrieve the actors who have appeared in more films than any other actor from the same country.

  22. List the films that are frequently rented together (in the same rental).

  23. Show the customers who have rented a film that another customer is currently renting.

  24. Display the actors who have appeared in films with the same actor as themselves.

  25. Retrieve the films that have been rented the most times on a single day.

  26. List the films that have never been rented.

  27. Show the customers who have rented the most films in a single day.

  28. Retrieve the actors who have appeared in films with the same title.

  29. List the films that have not been rented in the last 30 days.

  30. Show the customers who have rented a film that another customer is currently renting and are from the same city.

  31. Retrieve the actors who have appeared in films that have the same keyword in their descriptions.

  32. List the films that have been rented the most times in a single month.

  33. Show the customers who have not rented any films in the last 30 days.

  34. Display the actors who have appeared in the most expensive films.

  35. Retrieve the films that have been rented by the same customer on the same day.

  36. List the films that have been rented by a specific customer on the same day as a given film.

  37. Show the actors who have appeared in films with the same title and have worked with the same director.

  38. Retrieve the films that have been rented by a customer living in the same city as a specific customer.

  39. List the films that have been rented by a specific customer and are currently rented by other customers.

  40. Show the actors who have appeared in films that have been rented by the same customer on the same day.

  41. Retrieve the actors who have appeared in films that have been rented by the same customer in the same month.

  42. List the films that have been rented by a customer who has also rented a given film.

  43. Show the actors who have appeared in films that have been rented by the same customer in the same month and have worked with the same director.

  44. Retrieve the actors who have appeared in films that have been rented by the same customer on the same day and are from the same city.

  45. List the films that have been rented by a customer who has also rented a given film and is from the same city.

Expert Level

  1. Retrieve the top 5 customers who have rented films in all available categories.

  2. List the actors who have appeared in films of all the languages available in the database.

  3. Show the 10 most rented film pairs (combinations of two films) in terms of frequency.

  4. Retrieve the customers who have rented at least one film from each actor's filmography (consider all actors).

  5. List the top 5 staff members with the highest total sales, taking into account rental and late fees.

  6. Display the customers who have rented the same set of films (title and description) as another customer in the same month.

  7. Retrieve the films with the highest total rental revenue for each store location.

  8. Show the actors who have appeared in films directed by the same director multiple times.

  9. List the customers who have rented the same set of films (title and category) as another customer on the same day.

  10. Display the average rental duration for films with a distinct combination of rental rates and replacement costs.

  11. Retrieve the customers who have rented all films directed by a specific director.

  12. List the actors who have appeared in films that have not been rented in the last 30 days.

  13. Show the films that have been rented on the same day by the same customer in more than one store.

  14. Retrieve the staff members who have handled rentals for all film categories.

  15. Display the actors who have appeared in films that are currently available for rent in all stores.

  16. List the customers who have rented the same film on the same day in different months.

  17. Show the top 5 customers with the highest average rental revenue per month.

  18. Retrieve the films with the highest total rental revenue for each film category.

  19. List the staff members who have the same number of rentals as another staff member on a specific date.

  20. Display the customers who have rented films with the same keyword in their descriptions, with the same language as another customer.

  21. Retrieve the actors who have appeared in films that have been rented on the same day by the same customer and have the same language.

  22. Show the films that have been rented by a customer who has also rented a film from the same category on the same day.

  23. List the customers who have rented the same set of films as another customer in the same year.

  24. Retrieve the staff members who have handled rentals for films with the same rating as another staff member on the same day.

  25. Display the customers who have rented films with the same title as another customer on the same day.

  26. Retrieve the actors who have appeared in films directed by the same director and have the same rating as another film they've appeared in.

  27. List the films that have been rented in all store locations.

  28. Show the customers who have rented films that have not been rented by any other customer in the same category.

  29. Retrieve the staff members who have handled rentals for the same customer on the same day in different years.

  30. Display the actors who have appeared in films with the same title, description, and release year.

  31. List the customers who have rented the same set of films as another customer in the same category and language.

  32. Retrieve the actors who have appeared in films directed by the same director and have the same rating as another film they've appeared in, and are from the same country.

  33. Show the films that have been rented by a customer who has also rented a film from the same category, with the same rating and rental rate, on the same day.

  34. Retrieve the staff members who have handled rentals for the same customer on the same day, with the same rental rate.

  35. List the customers who have rented films that have not been rented by any other customer in the same category, language, and description.

  36. Display the actors who have appeared in films with the same title, description, release year, and rating.

  37. Retrieve the films that have been rented in all store locations, with the same rental rate and rental duration.

  38. Show the customers who have rented films that have not been rented by any other customer in the same category, language, description, and rental rate.

  39. List the staff members who have handled rentals for the same customer on the same day, with the same rental rate and late fees.

  40. Retrieve the actors who have appeared in films directed by the same director and have the same rating as another film they've appeared in, from the same country, and have worked with the same actor.

  41. Show the films that have been rented by a customer who has also rented a film from the same category, with the same rating, rental rate, rental duration, and release year, on the same day.

  42. List the customers who have rented the same set of films as another customer in the same category, language, description, rental rate, and rental duration.

  43. Retrieve the staff members who have handled rentals for the same customer on the same day, with the same rental rate, late fees, and rental duration.

  44. Display the actors who have appeared in films with the same title, description, release year, rating, and language.

  45. Retrieve the films that have been rented in all store locations, with the same rental rate, rental duration, and replacement cost.

  46. Show the customers who have rented films that have not been rented by any other customer in the same category, language, description, rental rate, rental duration, and release year.

  47. List the staff members who have handled rentals for the same customer on the same day, with the same rental rate, late fees, rental duration, and rental duration.

  48. Retrieve the actors who have appeared in films directed by the same director and have the same rating as another film they've appeared in, from the same country, and have worked with the same actor, and have the same language.

  49. Show the films that have been rented by a customer who has also rented a film from the same category, with the same rating, rental rate, rental duration, release year, and replacement cost, on the same day.

  50. List the customers who have rented the same set of films as another customer in the same category, language, description, rental rate, rental duration, release year, and replacement cost.

About

I honed my SQL skills in this repository, progressing from beginner-level challenges to advanced mastery.

Topics

Resources

License

Stars

Watchers

Forks