After going through the basic problems on HackerRank, I moved on to practicing with the Sakila database. It is a sample DVD rental database that comes with MySQL and it is great for practicing real world style queries. This post covers a bunch of problems I worked through around column aliases, sorting, and pulling unique values. Nothing too heavy, just building good habits with clean queries.
Renaming Columns with Aliases
The first few problems were about making query results more readable using aliases. The AS keyword lets you rename a column in the output without changing anything in the actual table.
Fetching film titles and rental rates with friendlier column names:
SELECT title AS "Movie Title", rental_rate AS "Rate"
FROM film;
Getting customer names with aliased columns:
SELECT first_name AS "First Name", last_name AS "Last Name"
FROM customer;
Aliases are mostly for making results easier to read, especially when you are sharing output with someone who is not looking at the raw table.
Sorting Results with ORDER BY
A big chunk of these problems were about sorting. ORDER BY is how you control the order rows come back in. DESC means highest to lowest or Z to A, and ASC is the default which goes lowest to highest or A to Z.
Films sorted by rental rate from highest to lowest, then alphabetically if the rate is the same:
SELECT title, rental_rate
FROM film
ORDER BY rental_rate DESC, title ASC;
Actor names sorted by last name first, then first name:
SELECT first_name, last_name
FROM actor
ORDER BY last_name ASC, first_name ASC;
Films sorted by length from longest to shortest:
SELECT title, length
FROM film
ORDER BY length DESC;
Actor names sorted by first name in descending order:
SELECT first_name, last_name
FROM actor
ORDER BY first_name DESC;
Film ratings sorted alphabetically with only unique values:
SELECT DISTINCT rating
FROM film
ORDER BY rating ASC;
Films sorted by rating ascending and length descending:
SELECT title, rating, length
FROM film
ORDER BY rating ASC, length DESC;
Actors sorted by last name ascending and first name descending:
SELECT first_name, last_name
FROM actor
ORDER BY last_name ASC, first_name DESC;
Films sorted by replacement cost ascending and rental rate descending:
SELECT title, replacement_cost, rental_rate
FROM film
ORDER BY replacement_cost ASC, rental_rate DESC;
Customer names sorted by last name ascending and first name descending:
SELECT first_name, last_name
FROM customer
ORDER BY last_name ASC, first_name DESC;
Rentals sorted by customer ID ascending and rental date descending:
SELECT *
FROM rental
ORDER BY customer_id ASC, rental_date DESC;
Films sorted by rental duration ascending and title descending:
SELECT title, rental_duration
FROM film
ORDER BY rental_duration ASC, title DESC;
When you have two ORDER BY columns, the second one only kicks in when two rows have the same value in the first column. That is the thing worth remembering here.
Pulling Unique Values with DISTINCT
These problems were about using DISTINCT to remove duplicate values from results.
Unique replacement costs from the film table:
SELECT DISTINCT replacement_cost
FROM film;
Unique rental durations:
SELECT DISTINCT rental_duration
FROM film;
Unique store IDs from inventory:
SELECT DISTINCT store_id
FROM inventory;
Unique replacement costs sorted in ascending order:
SELECT DISTINCT replacement_cost
FROM film
ORDER BY replacement_cost ASC;
Using Aliases with More Columns
Aliasing the length column to something more readable:
SELECT title, length AS "Duration (min)"
FROM film;
Customer names with their active status aliased:
SELECT first_name, last_name, active AS "Is Active"
FROM customer;
Film Categories Sorted Alphabetically
SELECT name
FROM category
ORDER BY name ASC;
Using LIMIT to Get Top or Bottom Results
The 10 shortest films:
SELECT title, length
FROM film
ORDER BY length ASC
LIMIT 10;
Top 5 customers with the highest customer ID:
SELECT first_name, last_name
FROM customer
ORDER BY customer_id DESC
LIMIT 5;
Grouping and Ordering Together
First unique customer ID based on active status:
SELECT DISTINCT customer_id, active
FROM customer
ORDER BY customer_id ASC;
Earliest rental date for each customer:
SELECT customer_id, MIN(rental_date) AS rental_date
FROM rental
GROUP BY customer_id
ORDER BY customer_id ASC;
First rental date for each store:
SELECT store_id, MIN(rental_date) AS rental_date
FROM rental
GROUP BY store_id
ORDER BY store_id ASC;
What I Took Away from All This
These problems look simple on the surface but they cover a lot of ground. Aliases keep your output clean and readable. ORDER BY with multiple columns gives you fine control over how results are sorted. DISTINCT is your go to when you only care about unique values. And LIMIT is great when you only need the top or bottom few rows.
Sakila is a really solid database to practice on because the tables actually relate to something real, which makes it easier to think through what your query should be doing instead of just memorizing syntax.
Top comments (0)