lab-sql-2.sql

USE Sakila;

-- 1. Select all the actors with the first name ‘Scarlett’
SELECT * FROM actor WHERE first_name='Scarlett';

-- 2. Select all the actors with the last name ‘Johansson’.
SELECT * FROM actor WHERE last_name='Johansson';

-- 3. How many films (movies) are available for rent?
SELECT * FROM film;

-- 4. How many films have been rented? 
SELECT * FROM rental;

-- 5. What is the shortest and longest rental period?
SELECT *, DATEDIFF(return_date, rental_date) AS rental_duration FROM rental ORDER BY rental_duration ASC LIMIT 1; 
SELECT *, DATEDIFF(return_date, rental_date) AS rental_duration FROM rental ORDER BY rental_duration DESC LIMIT 1;

-- 6. What are the shortest and longest movie duration? Name the values max_duration and min_duration
SELECT MAX(length) AS max_duration FROM film;
SELECT MIN(length) AS min_duration FROM film;

-- 7. What's the average movie duration?
SELECT AVG(length) AS avg_duration FROM film;

-- 8. What's the average movie duration expressed in format (hours, minutes)?
SELECT 
	CONCAT(
		FLOOR(AVG(length)/60), 'hr ', 
        FLOOR(AVG(length)%60), 'm'
	) AS avg_duration FROM film;
    
-- 9. How many movies longer than 3 hours?
SELECT * FROM film WHERE length > 180;
 
-- 10. Get the name and email formatted. Example: Mary SMITH - mary.smith@sakilacustomer.org.
SELECT CONCAT(first_name, ' ', last_name,  ' - ', email, '.') AS customer_details FROM customer;

-- 11. What's the length of the longest film title?
SELECT * FROM film WHERE CHAR_LENGTH(title) = (SELECT MAX(CHAR_LENGTH(title)) FROM film);