Master-Level Questions in SQL

Summary

This web content provides a series of advanced SQL practice questions based on the Sakila sample database, designed to enhance the query-writing skills of data scientists and analysts.

Abstract

The article presents a set of SQL challenges aimed at data professionals who wish to refine their query skills in SQL. These questions range from basic to complex and cover a wide array of SQL operators and structures. The practice questions are to be executed on the Sakila database, a popular sample database that simulates a DVD rental business's data structure, including tables for films, actors, stores, inventory, staff, customers, and their interrelations. The Sakila database schema is visually represented in the article, and a link to download the database is provided for hands-on practice. The questions task the reader with counting distinct actor last names, listing actors from a specific movie, quantifying film copies in inventory, summarizing customer rental payments, inventorying film categories per store, calculating store revenues, identifying the most prolific actor, finding actor pairs who have co-starred, listing the top-rented films, and checking the rental availability of a particular film at a specific store. Solutions to these questions are available through a provided link.

Opinions

The article positions the Sakila database as a valuable tool for SQL practice due to its comprehensive representation of a real-world business scenario.
The structured progression from easy to hard questions is designed to methodically build and test the reader's SQL competency.
The inclusion of a visual schema diagram and a download link for the Sakila database indicates a consideration for the reader's convenience in engaging with the practice material.
The challenge of finding pairs of actors that have co-starred, with a specific expected row count in the result, suggests an emphasis on advanced SQL concepts such as joins and subqueries.
Providing solutions to the questions offers a means for self-assessment and learning reinforcement, which is beneficial for readers' educational development.

Hone your SQL query skills with these challenging questions

SQL is one of the most important languages you need to know as a data scientist or a data analyst. This article provides you with practice questions that cover most of the operators and structures that you need to know in SQL.

The queries you need to write deal with the Sakila database, which is one of the most widely used sample databases. It models a DVD rental store business, and contains data on films, actors, stores, inventory, staff, customers, and the relationships between them.

The schema of the database is depicted in the following diagram:

Sakila is one of the sample databases that are installed as part of the standard installation of MySQL. You can also download it directly from the following link: https://downloads.mysql.com/docs/sakila-db.zip.

Write the following SQL queries on the Sakila database. The questions are organized from easy to hard.

How many distinct last names of actors are there?

Which actors participated in the movie ‘Academy Dinosaur’? Print their first and last names.

How many copies of the film ‘Hunchback Impossible’ exist in the inventory system?

What is the total amount paid by each customer for all their rentals? For each customer print their name and the total amount paid.

How many films from each category each store has? Print the store id, category name and number of films. Order the results by store id and category name.

Calculate the total revenue of each store.

Which actor participated in the most films? Print their full name and in how many movies they participated.

Find pairs of actors that participated together in the same movie and print their full names. Each such pair should appear only once in the result. (You should have 10,385 rows in the result)

Display the top five most popular films, i.e., films that were rented the highest number of times. For each film print its title and the number of times it was rented.

Is the film ‘Academy Dinosaur’ available for rent from Store 1? You should check that the film exists as one of the items in the inventory of Store 1, and that there is no outstanding rental of that item with no return date.

You can find the solutions to these questions here.