I was practicing some SELECT queries in the dvdrental database to understand sorting, alias, unique values and ordering. I tried different queries and below are the ones I used and why I used them.
First I tried to retrieve film titles and rental rate but I renamed the column names so it looks better while reading.
SELECT title AS "Movie Title", rental_rate AS "Rate"
FROM film;
I used this because alias makes output more readable instead of database column names.
Then I listed customer names and emails with alias.
SELECT first_name AS "First Name", last_name AS "Last Name", email
FROM customer;
I used this to see customer contact details in a proper format.
Then I sorted films by rental rate descending and if same rate then by title.
SELECT title, rental_rate
FROM film
ORDER BY rental_rate DESC, title ASC;
I used this to see costly rental movies first and sort same price movies alphabetically.
Then I sorted actor names.
SELECT first_name, last_name
FROM actor
ORDER BY last_name, first_name;
I used this to arrange actors properly by last name.
Then I tried unique replacement cost values.
SELECT DISTINCT replacement_cost
FROM film;
I used DISTINCT to remove duplicate values.
Then I listed film title and length.
SELECT title, length AS "Duration (min)"
FROM film;
I used alias again to make column readable.
Then I checked customer active status.
SELECT first_name, last_name, active AS "Is Active"
FROM customer;
I used this to see which customers are active.
Then I listed film categories alphabetically.
SELECT name
FROM category
ORDER BY name;
I used ORDER BY to sort alphabetically.
Then I listed films by length descending.
SELECT title, length
FROM film
ORDER BY length DESC;
I used this to see longest movies first.
Then actors sorted by first name descending.
SELECT first_name, last_name
FROM actor
ORDER BY first_name DESC;
Then I checked unique ratings.
SELECT DISTINCT rating
FROM film;
Then unique rental duration.
SELECT DISTINCT rental_duration
FROM film;
Then I retrieved unique customer id by active status.
SELECT DISTINCT customer_id, active
FROM customer
ORDER BY customer_id;
Then earliest rental date per customer.
SELECT customer_id, MIN(rental_date)
FROM rental
GROUP BY customer_id
ORDER BY customer_id;
Then 10 shortest films.
SELECT title, length
FROM film
ORDER BY length
LIMIT 10;
Then top 5 customers with highest id.
SELECT first_name, last_name
FROM customer
ORDER BY customer_id DESC
LIMIT 5;
Then unique store ids.
SELECT DISTINCT store_id
FROM inventory;
Then replacement cost sorted.
SELECT DISTINCT replacement_cost
FROM film
ORDER BY replacement_cost;
Then first rental date per store.
SELECT store_id, MIN(rental_date)
FROM rental
GROUP BY store_id
ORDER BY store_id;
Then ratings sorted alphabetically.
SELECT DISTINCT rating
FROM film
ORDER BY rating;
Then films sorted by rating and length.
SELECT title, rating, length
FROM film
ORDER BY rating ASC, length DESC;
Then actors sorted last name ascending and first name descending.
SELECT first_name, last_name
FROM actor
ORDER BY last_name ASC, first_name DESC;
Then films sorted by replacement cost and rental rate.
SELECT title, replacement_cost, rental_rate
FROM film
ORDER BY replacement_cost ASC, rental_rate DESC;
Then customers sorted by last and first name.
SELECT first_name, last_name
FROM customer
ORDER BY last_name ASC, first_name DESC;
Then rentals sorted by customer and rental date.
SELECT *
FROM rental
ORDER BY customer_id ASC, rental_date DESC;
Then films sorted by rental duration and title.
SELECT title, rental_duration
FROM film
ORDER BY rental_duration ASC, title DESC;
From all these queries I understood mainly SELECT, DISTINCT, ORDER BY, LIMIT and alias. These are very basic but very important queries for fetching and sorting data from database.
Top comments (0)