My Thinking and Approach
Introduction
In this task, I worked with the dvdrental database to practice SQL queries. The focus was on retrieving, sorting, and formatting data using different SQL clauses.
This helped me improve my understanding of SELECT, ORDER BY, DISTINCT, and column aliasing.
Problem Statement
- Retrieve specific data from tables
- Use aliases for better readability
- Apply sorting using ORDER BY
- Extract unique values using DISTINCT
My Initial Thought
At first, I thought:
- Queries are just simple SELECT statements
- Sorting is straightforward
But I realized:
- Proper formatting improves readability
- Sorting can involve multiple conditions
- DISTINCT is useful for unique data
Key Observation
- Aliases help make output user-friendly
- ORDER BY can sort using multiple columns
- DISTINCT removes duplicate values
- Combining clauses makes queries powerful
Solutions
1. Film titles and rental rates
SELECT title AS "Movie Title", rental_rate AS "Rate"
FROM film;
2. Customer names and email
SELECT first_name AS "First Name", last_name AS "Last Name", email
FROM customer;
3. Films sorted by rental rate and title
SELECT title, rental_rate
FROM film
ORDER BY rental_rate DESC, title ASC;
4. Actor names sorted
SELECT first_name, last_name
FROM actor
ORDER BY last_name ASC, first_name ASC;
5. Unique replacement costs
SELECT DISTINCT replacement_cost
FROM film;
6. Film title and duration
SELECT title, length AS "Duration (min)"
FROM film;
7. Customer active status
SELECT first_name, last_name, active AS "Is Active"
FROM customer;
8. Film categories sorted
SELECT name
FROM category
ORDER BY name ASC;
9. Films by length descending
SELECT title, length
FROM film
ORDER BY length DESC;
10. Actors sorted by first name descending
SELECT first_name, last_name
FROM actor
ORDER BY first_name DESC;
11. Unique ratings
SELECT DISTINCT rating
FROM film;
12. Unique rental durations
SELECT DISTINCT rental_duration
FROM film;
13. First unique customer by active status
SELECT DISTINCT customer_id, active
FROM customer
ORDER BY customer_id;
14. Earliest rental date per customer
SELECT customer_id, MIN(rental_date) AS rental_date
FROM rental
GROUP BY customer_id
ORDER BY customer_id;
15. 10 shortest films
SELECT title, length
FROM film
ORDER BY length ASC
LIMIT 10;
16. Top 5 customers with highest ID
SELECT first_name, last_name
FROM customer
ORDER BY customer_id DESC
LIMIT 5;
17. Unique store IDs
SELECT DISTINCT store_id
FROM inventory;
18. Unique replacement cost sorted
SELECT DISTINCT replacement_cost
FROM film
ORDER BY replacement_cost ASC;
19. First rental date per store
SELECT store_id, MIN(rental_date) AS rental_date
FROM rental
GROUP BY store_id
ORDER BY store_id;
20. Unique film ratings sorted
SELECT DISTINCT rating
FROM film
ORDER BY rating ASC;
21. Films sorted by rating and length
SELECT title, rating, length
FROM film
ORDER BY rating ASC, length DESC;
22. Actor names with mixed sorting
SELECT first_name, last_name
FROM actor
ORDER BY last_name ASC, first_name DESC;
23. Films by replacement cost and rental rate
SELECT title, replacement_cost, rental_rate
FROM film
ORDER BY replacement_cost ASC, rental_rate DESC;
24. Customer names sorted
SELECT first_name, last_name
FROM customer
ORDER BY last_name ASC, first_name DESC;
25. Rentals sorted
SELECT *
FROM rental
ORDER BY customer_id ASC, rental_date DESC;
26. Films by rental duration and title
SELECT title, rental_duration
FROM film
ORDER BY rental_duration ASC, title DESC;
Final Understanding
- SQL queries can be combined to perform powerful operations
- Sorting and filtering improve data usability
- Aliasing makes output more readable
Conclusion
This task helped me strengthen my SQL fundamentals by working with real-world queries on the dvdrental database. It improved my ability to retrieve, organize, and present data effectively.
Top comments (0)