Top 10 Tricky Questions on SQL
Hey, this is Suraj. This is a series of Top 10 tricky questions on SQL based on the world and Sakila database
Q.1 Find the top 5 most populous cities in countries where the official language is ‘Spanish’, and list their names, population, and the name of the country they belong to. Ensure the results are sorted by population in descending order.
SELECT
city.Name AS CityName,
city.Population,
country.Name AS CountryName
FROM
City AS city
JOIN
Country AS country ON city.CountryCode = country.Code
JOIN
CountryLanguage AS countryLanguage ON country.Code = countryLanguage.CountryCode
WHERE
countryLanguage.Language = ‘Spanish’
AND countryLanguage.IsOfficial = ‘T’
ORDER BY
city.Population DESC
LIMIT 5;
Q.2 Find the top 3 customers who have rented the most films. For each customer, list their first name, last name, and the total number of films rented.
SELECT
customer.first_name,
customer.last_name,
COUNT(rental.rental_id) AS total_rentals
FROM
customer
JOIN
rental ON customer.customer_id = rental.customer_id
GROUP BY
customer.customer_id
ORDER BY
total_rentals DESC
LIMIT 3;
Q.3 Find the top 5 most rented films in the Sakila database. For each film, list the title, the number of times it has been rented, and the total rental revenue generated by that film.
SELECT
film.title,
COUNT(rental.rental_id) AS total_rentals,
SUM(payment.amount) AS total_revenue
FROM
film
JOIN
inventory ON film.film_id = inventory.film_id
JOIN
rental ON inventory.inventory_id = rental.inventory_id
JOIN
payment ON rental.rental_id = payment.rental_id
GROUP BY
film.film_id
ORDER BY
total_rentals DESC
LIMIT 5;
Q.4 List the countries that have a population greater than the average population of all countries, and also have a life expectancy greater than the average life expectancy of all countries. For each country, list the name, population, and life expectancy.
SELECT
country.Name,
country.Population,
country.LifeExpectancy
FROM
Country AS country
WHERE
country.Population > (SELECT AVG(Population) FROM Country)
AND country.LifeExpectancy > (SELECT AVG(LifeExpectancy) FROM Country)
ORDER BY
country.Population DESC;
For Free, Demo classes Call: 020-71179559
Registration Link: SQL Training in Pune!
Q.5 For each continent, find the two countries with the highest population. List the continent, country name, and population.
SELECT
Continent,
Name,
Population
FROM (
SELECT
Continent,
Name,
Population,
ROW_NUMBER() OVER (PARTITION BY Continent ORDER BY Population DESC) AS rn
FROM
Country
) AS ranked
WHERE
rn <= 2
ORDER BY rn;
Q.6 Find the top 3 films with the highest rental revenue in each category. List the category name, film title, and total rental revenue.
SELECT
category.name AS category_name,
film.title AS film_title,
SUM(payment.amount) AS total_revenue
FROM
category
JOIN
film_category ON category.category_id = film_category.category_id
JOIN
film ON film_category.film_id = film.film_id
JOIN
inventory ON film.film_id = inventory.film_id
JOIN
rental ON inventory.inventory_id = rental.inventory_id
JOIN
payment ON rental.rental_id = payment.rental_id
GROUP BY
category.name,
film.title
HAVING
ROW_NUMBER() OVER (PARTITION BY category.name ORDER BY SUM(payment.amount) DESC) <= 3
ORDER BY
category_name,
total_revenue DESC;
Q.7 Create a trigger in the Sakila database that automatically updates the ‘last_update’ column of the inventory table to the current timestamp whenever a new rental is inserted into the rental table.
DELIMITER $$
CREATE TRIGGER update_inventory_last_update
AFTER INSERT ON rental
FOR EACH ROW
BEGIN
UPDATE inventory
SET last_update = CURRENT_TIMESTAMP
WHERE inventory_id = NEW.inventory_id;
END$$
DELIMITER ;
Q.8 Create a user-defined function in the “world” database that calculates the population density of a country. The function should take the country code as input and return the population density (population per square kilometer).
DELIMITER $$
CREATE FUNCTION get_population_density(country_code CHAR(3))
RETURNS DECIMAL(10, 2)
DETERMINISTIC
BEGIN
DECLARE population INT;
DECLARE surface_area DECIMAL(10, 2);
DECLARE population_density DECIMAL(10, 2);
— Get population and surface area for the given country code
SELECT Population, SurfaceArea INTO population, surface_area
FROM Country
WHERE Code = country_code;
— Calculate population density
IF surface_area > 0 THEN
SET population_density = population / surface_area;
ELSE
SET population_density = NULL; — Handle case where surface area is zero or null
END IF;
RETURN population_density;
END$$
DELIMITER ;
Q.9 List the top 5 customers who have spent the most amount of money on rentals in each month. For each customer, show their customer ID, first name, last name, and the total amount spent on rentals in that month.
SELECT
sub.customer_id,
sub.first_name,
sub.last_name,
sub.month,
sub.total_amount_spent
FROM (
SELECT
c.customer_id,
c.first_name,
c.last_name,
MONTH(p.payment_date) AS month,
SUM(p.amount) AS total_amount_spent,
ROW_NUMBER() OVER (PARTITION BY c.customer_id, MONTH(p.payment_date) ORDER BY SUM(p.amount) DESC) AS rn
FROM
customer c
JOIN
payment p ON c.customer_id = p.customer_id
GROUP BY
c.customer_id, MONTH(p.payment_date)
) AS sub
WHERE
sub.rn <= 5
ORDER BY
sub.month, sub.total_amount_spent DESC;
Q.10 In the Sakila database, you have a table named actor that contains information about actors. Your task is to add a new column named full_name to this table, which should combine the first name and last name of each actor separated by a space. Additionally, the full_name should be in all uppercase letters. Write an ALTER TABLE query to achieve this.
ALTER TABLE actor
ADD COLUMN full_name VARCHAR(255) GENERATED ALWAYS AS (CONCAT(UPPER(first_name), ‘ ‘, UPPER(last_name))) STORED;
Do visit our channel to learn more: Click Here
Author:
Suraj Kale
Call the Trainer and Book your free demo Class For SQL Call now!!!
| SevenMentor Pvt Ltd.