Design Elements & Query Practice

Entity Relationship Model

Normalization #2: Table Dependency Chart

Query Design Practice #1

What films has the customer "Scott Armstrong" rented so far? Produce a list of film titles and rental date.

SELECT film_id, rental_id, rental_date. COUNT (*)

FROM customer_id Scott Armstrong

JOIN film_id, rental_id

ORDER BY COUNT (*)rental_date DESC;

What "Action" films starring "Tom Cruise" other than "Mission Impossible" are available from the rental service? Produce a list of film titles with release year, length, rental duration, and rental rate, including both those in stock and those currently rented out.

SELECT release_year, length, rental_duration, rental_rate, film_id title,

FROM category_id action, actor_id Tom Cruise, film_id

MINUS film_id mission impossible;

Which customers got rentals overdue? Produce a sorted list of customers (lastname + firstname) with overdue film titles and rental dates. Assume that the field "RETURN DATE" is optional and its "NULL" value means having not been returned yet. Hint: "overdue" means that time elapse (current date - rental date) is greater than rental duration.

SELECT customer_id lastname, customer_id firstname, rental_id title, rental_date overdue

FROM rental_id AND customer_id, rental_date overdue <1

HAVING rental_date overdue <=1;

How many customers have rented the film "Inception" in 2016?

SELECT film_title inception, rental_date =2016 COUNT (*)

FROM film_id, rental_id,

ORDER BY COUNT (*) rental_date DESC;