ASSIGNMENT 31

Introduction

The SELECT statement in SQL is used to retrieve data from a database. In this exercise, we explore different ways of querying the DVD Rental database, such as sorting, filtering, renaming columns, and finding unique values.

These queries demonstrate essential SQL concepts like:
•Column selection
•Aliasing (AS)
•Sorting (ORDER BY)
•Removing duplicates (DISTINCT)
•Aggregation (MIN)
•Limiting results (LIMIT)

Queries with Code & Explanation

1. Film titles and rental rates

SELECT title AS "Movie Title",rental_rate AS "Rate"
FROM film;

Retrieves movie titles and rental rates with user-friendly column names.

1. Customer names and email

SELECT first_name AS "First Name",last_name AS "Last Name",email
FROM customer;

Displays customer details with renamed columns.

1. Films sorted by rental rate

SELECT title, rental_rate
FROM film
ORDER BY rental_rate DESC, title ASC;

Sorts films by highest rental rate, then alphabetically.

1. Actor names sorted

SELECT first_name, last_name
FROM actor
ORDER BY last_name ASC, first_name ASC;

Lists actors in proper alphabetical order.

1. Unique replacement cost

SELECT DISTINCT replacement_cost
FROM film;

Removes duplicate replacement cost values.

1. Film title and duration

SELECT title,length AS "Duration (min)" FROM film;

Shows movie duration with a clear column name.

1. Customer active status

SELECT first_name,last_name,
active AS "Is Active"
FROM customer;

Displays whether customers are active.

1. Film categories

SELECT name
FROM category
ORDER BY name ASC;

Lists categories alphabetically.

1. Films by length

SELECT title, length
FROM film ORDER BY length DESC;

Shows longest films first.

1. Actors sorted by first name

SELECT first_name, last_name FROM actor
ORDER BY first_name DESC;

Sorts actors in reverse alphabetical order by first name.

1. Unique ratings

SELECT DISTINCT rating
FROM film;

Displays all available movie ratings.

1. Unique rental durations

SELECT DISTINCT rental_duration
FROM film;

Shows different rental duration values.

1. Customer ID with active status

SELECT DISTINCT customer_id,active
FROM customer
ORDER BY customer_id;

Displays unique customer IDs with their active status.

1. Earliest rental date per customer

SELECT customer_id,
MIN(rental_date) AS rental_date
FROM rental
GROUP BY customer_id
ORDER BY customer_id;

Finds each customer’s first rental.

1. 10 shortest films

SELECT title, length
FROM film
ORDER BY length ASC
LIMIT 10;

Lists the shortest movies.

1. Top 5 customers

SELECT title, length
FROM film
ORDER BY length ASC
LIMIT 10;

Retrieves customers with highest IDs.

1. Unique store IDs

SELECT DISTINCT store_id
FROM inventory;

Shows all store IDs.

1. Replacement cost sorted

SELECT DISTINCT replacement_cost
FROM film
ORDER BY replacement_cost ASC;

Displays sorted unique replacement costs.

1. First rental per store

SELECT i.store_id,
MIN(r.rental_date) AS rental_date
FROM rental r
JOIN inventory i ON r.inventory_id = i.inventory_id
GROUP BY i.store_id
ORDER BY i.store_id;

Finds earliest rental date for each store.

1. Unique film ratings sorted

SELECT DISTINCT rating
FROM film
ORDER BY rating ASC;

Displays ratings alphabetically.

1. Films by rating and length

SELECT title, rating, length
FROM film
ORDER BY rating ASC, length DESC;

Sorts by rating, then longest films first.

1. Actor sorting variation

SELECT first_name, last_name
FROM actor
ORDER BY last_name ASC, first_name DESC;

Sorts actors with mixed order conditions.

1. Films by cost and rate

SELECT title, replacement_cost, rental_rate
FROM film
ORDER BY replacement_cost ASC, rental_rate DESC;

Sorts by cost and rental rate.

1. Customer sorting variation

SELECT first_name, last_name
FROM customer
ORDER BY last_name ASC, first_name DESC;

Orders customers by last name and reverse first name.

1. Rentals sorted

SELECT *
FROM rental
ORDER BY customer_id ASC, rental_date DESC;

Shows all rentals sorted by customer and latest date.

1. Films by rental duration

SELECT title, rental_duration
FROM film
ORDER BY rental_duration ASC, title DESC;

Sorts films by duration and reverse title order.

Conclusion

These queries demonstrate how SQL can efficiently:
•Retrieve specific data
•Sort and organize results
•Remove duplicates
•Perform basic analysis