Lab | SQL Queries 2

Lab Content

• Lab | Description:
• Instructions
  • 1.- Select all the actors with the first name ‘Scarlett’.
  • 2.- Select all the actors with the last name ‘Johansson’.
  • 3.- How many films (movies) are available for rent?
  • 4.- How many films have been rented?
  • 5.- What is the shortest and longest rental period?
  • 6.- What are the shortest and longest movie duration? Name the values max_duration and min_duration.
  • 7.- What’s the average movie duration?
  • 8.- What’s the average movie duration expressed in format (hours, minutes)?
  • 9.- How many movies longer than 3 hours?
  • 10.- Get the name and email formatted. Example: Mary SMITH - mary.smith@sakilacustomer.org.
  • 11.- What’s the length of the longest film title?

Lab | Description:

In this lab, you will be using the Sakila database of movie rentals. You can follow the steps listed here to get the data locally: Sakila sample database - installation.

The database is structured as follows:

Instructions

1.- Select all the actors with the first name ‘Scarlett’.

Answer:

SELECT
  *
FROM
  actor
WHERE
  first_name = 'Scarlett';

2.- Select all the actors with the last name ‘Johansson’.

Answer:

SELECT
  *
FROM
  actor
WHERE
  last_name = ('Johansson');

3.- How many films (movies) are available for rent?

Answer:

SELECT
  COUNT(*) film_id
FROM
  inventory;

4.- How many films have been rented?

Answer:

SELECT
  COUNT(*) rental_id
FROM
  rental;

5.- What is the shortest and longest rental period?

Answer:

SELECT
  MIN(rental_duration),
  MAX(rental_duration)
FROM
  film;

also this can be expressed like so:

SELECT
  MAX (
    DATEDIFF (rental.return_date, rental.rental_date)
  ) AS longest_rental_period,
  (
    DATEDIFF (rental.return_date, rental.rental_date)
  ) > (0) AS shortest_rental_period
FROM
  rental;

6.- What are the shortest and longest movie duration? Name the values max_duration and min_duration.

Answer:

SELECT
  MIN(length) AS min_duration,
  MAX(length) AS max_duration
FROM
  film;

7.- What’s the average movie duration?

Answer:

SELECT
  ROUND(AVG(length)) AS average_movie_duration
FROM
  film;

8.- What’s the average movie duration expressed in format (hours, minutes)?

Answer: Using the FLOOR() and MOD function

We have to round the operation first otherwise will give 55m,127 like so:
select `CONCAT(FLOOR((avg(length))/60),'h ',MOD((avg(length)),60),'m')` from film;

SELECT
  CONCAT(
    FLOOR(ROUND((AVG(length))) / 60),
    'h ',
    MOD(ROUND((AVG(length))), 60),
    'm'
  ) AS average_movie_duration
FROM
  film;

9.- How many movies longer than 3 hours?

Answer:

SELECT
  DISTINCT COUNT(length) AS movies_longer_than_3h
FROM
  film
WHERE
  length > (3 * 60);

10.- Get the name and email formatted. Example: Mary SMITH - mary.smith@sakilacustomer.org.

Answer:

SELECT
  concat(first_name, ' ', last_name, ' ', '-', ' ', email) AS customer_contact_info
FROM
  customer;

11.- What’s the length of the longest film title?

Answer:

SELECT
  MAX(length(title)) AS longest_film_title
FROM
  film;

SQL Lab 2 script only

Back to Lab | Description: