fc

/*
SOURCE /Users/davidayliffe/Google Drive/AGU/GD Docs/Blockbusters/answers.sql
*/

/* save as CRLF for windows */


show databases;
/*
+--------------------+
| Database           |
+--------------------+
| information_schema |
| blockbusters       |
+--------------------+
6 rows in set (0.06 sec)
*/

use blockbusters;

select customer_id, first_name, last_name
from customer
where active = 1;


show tables;
/*
+----------------------------+
| Tables_in_blockbusters     |
+----------------------------+
| actor                      |
| actor_info                 |
| address                    |
| category                   |
| city                       |
| country                    |
| customer                   |
| customer_list              |
| film                       |
| film_actor                 |
| film_category              |
| film_list                  |
| film_text                  |
| inventory                  |
| language                   |
| nicer_but_slower_film_list |
| payment                    |
| rental                     |
| sales_by_film_category     |
| sales_by_store             |
| staff                      |
| staff_list                 |
| store                      |
+----------------------------+
23 rows in set (0.04 sec)
*/

desc customer;
/*
+-------------+----------------------+------+-----+-------------------+-----------------------------+
| Field       | Type                 | Null | Key | Default           | Extra                       |
+-------------+----------------------+------+-----+-------------------+-----------------------------+
| customer_id | smallint(5) unsigned | NO   | PRI | NULL              | auto_increment              |
| store_id    | tinyint(3) unsigned  | NO   | MUL | NULL              |                             |
| first_name  | varchar(45)          | NO   |     | NULL              |                             |
| last_name   | varchar(45)          | NO   | MUL | NULL              |                             |
| email       | varchar(50)          | YES  |     | NULL              |                             |
| address_id  | smallint(5) unsigned | NO   | MUL | NULL              |                             |
| active      | tinyint(1)           | NO   |     | 1                 |                             |
| create_date | datetime             | NO   |     | NULL              |                             |
| last_update | timestamp            | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------+----------------------+------+-----+-------------------+-----------------------------+
9 rows in set (0.01 sec)
*/

/*
            SELECT STATEMENTS
Usage:
    select <the information I want>
    from <table/tables>
    where <criteria>;

For example:
    select customer_id, first_name, last_name
    from customer
    where active = 1;

*/

/*  ***************************************************
    *** LEVEL 1 - Academy ninja                     ***
    ***                                             ***
    *** Focus:  simple select statements            ***
    ***         simple aggregation of data          ***
    ***         using count(), sum(), min()         ***
    ***         and avg(), NULL and NOT NULL        ***
    ***                                             ***
    ***         ordering data                       ***
    ***                                             ***
    *** Limits: Joining not more than two tables    ***
    ***         Single clause WHERE statements      ***
    ***                                             ***
    *** Estimated time: 1 hour                      ***
    *************************************************** */

/*
1.1. How many categories of films are there?
(±2 lines, 1 table)
Answer: 16
*/


select count(*)
from category;



/*
1.2. Which database table has the most records?  How many?
(±2 lines, 1 table)
Answer:  payment = 205305
*/





/*
1.3. In which year was the film ‚"Gold River" released?
(±3 lines, 1 table)
Answer = 1998
*/





/*
1.4. How many films are exactly 3 hours in length?
(±3 lines, 1 table)
Answer: 7
*/

select * from film WHERE length = 180;





/*
1.5. Which are the THREE longest films?
(±3 lines, 1 table)
Answer: POND SEATTLE 190, CRYSTAL BREAKING 188, WORST BANGER 186
*/





/*
1.6. Which are the TWO most expensive films to replace?
(±4 lines, 1 table)
Answer: FANTASIA PARK   34.99, FLOATS GARDEN    34.99
*/

SELECT title, replacement_cost FROM film ORDER BY replacement_cost DESC;



/*
1.7. How many films does blockbusters have in all of their stores?
(±4 lines, 2 tables, hint: it's NOT 1000)
Answer: 71025
*/

SELECT count(*) FROM inventory;



/*
1.8. How many films are out on rent now?
(±4 lines, 1 table, hint: use the "now()" function )
Answer: 0
*/
SELECT * from rental WHERE returned_date > NOW() and returned_date is null;





/*
1.9. What are the oldest films?  What are the most recent films?
(±3 lines, 1 table)
Answer: MENAGERIE RUSHMORE 1959, ARMY FLINTSTONES 2017
*/

SELECT * FROM film ORDER BY release_year LIMIT 1;
SELECT * FROM film ORDER BY release_year DESC LIMIT 1;


/*
1.10. What is the average price of a film rental?
(±2 lines, 1 table)
Answer: 19.994000
*/

SELECT avg(replacement_cost) FROM film;




/*
1.11. How many customers gave not provided an email?
(±3 lines, 1 table)
Answer: 10
*/

SELECT count(*) FROM customer WHERE email IS null;

/*
1.12. How many customers did not return the DVD they rented?
(±3 lines, 1 table)
Answer: 1446
*/

  SELECT count(*) FROM rental WHERE returned_date is NULL;



/*
1.13. How many films were returned late?
(±4 lines, 1 table)
Answer: 1141
*/

SELECT count(*) FROM rental WHERE due_date < returned_date;



/*
1.14. How many Turkish cities are in the database?
(±4 lines, 2 tables)
Answer: 15
*/

SELECT count(*) FROM city WHERE country_id = 97;



/*
1.15. Which store has the most films?
(±3 lines, 1 table)
Answer: 2
*/
SELECT store_id, count(*) AS total FROM inventory GROUP BY store_id ORDER BY total;




/*
1.16. Which film can be rented for the shortest length of time?
(±3 lines, 1 table)
Answer: AGENT TRUMAN
*/

SELECT * FROM film ORDER BY rental_duration LIMIT 1;


/*  ***************************************************
    *** LEVEL 2 - Genin                             ***
    ***                                             ***
    *** Focus:  simple select statements            ***
    ***         simple aggregation of data          ***
    ***         using count(), sum(), min()         ***
    ***         and avg(), NULL and NOT NULL        ***
    ***                                             ***
    ***         grouping data                       ***
    ***                                             ***
    *** Limits: Joining not more than five tables   ***
    ***         Multi parameter WHERE clause        ***
    ***                                             ***
    *** Estimated time: 1 hour                      ***
    *************************************************** */

use blockbusters;

/* save as CRLF for windows */

/*
2.0. Which staff member has rented the most films?
(±4 lines, 2 tables)
Answer:
*/
select staff.staff_id, first_name, last_name, count(*) as films_rented
from staff, rental
where staff.staff_id = rental.staff_id
group by staff.staff_id
order by films_rented;


/*
2.1. How many Turkish cities are in the database?
(±4 lines, 2 tables)
Answer: 15
*/
SELECT country.country, count(*) AS no_of_cities
  FROM city, country
  WHERE city.country_id = country.country_id AND country = "Turkey"
  GROUP BY country.country
  ORDER BY country.country DESC;



/*
2.3. What is the most popular language of films?
(±4 lines, 2 tables)
Answer: Japanese
*/

SELECT language.name, count(*) AS no_of_pop
  FROM film, language
  WHERE film.language_id = language.language_id
  GROUP BY language.name
  ORDER BY no_of_pop DESC;

/*
2.4. Which customers have rented the most films?
(±4 lines, 2 tables)
Answer: BOBBY   BOUDREAU
*/

  SELECT customer.first_name, customer.last_name, count(*) AS number_of_rent
    FROM customer, rental
    WHERE customer.customer_id = rental.customer_id
  GROUP BY customer.first_name, customer.last_name
  ORDER BY number_of_rent DESC;


/*
2.5. Which is the busiest store?
(±6 lines, 3 tables)
Answer: Rentertainment
*/

SELECT store.name, count(*) AS work
  FROM store, rental, inventory
  WHERE rental.inventory_id = inventory.inventory_id AND inventory.store_id = store.store_id
  GROUP BY store.name
  ORDER BY work DESC;


/*
2.6. Find all actors in the film 'Sky Miracle'.
(±7 lines, 3 tables)
Answer: 12
  ELVIS MARX
SISSY   SOBIESKI
WOODY   JOLIE
KIRSTEN AKROYD
WALTER  TORN
WARREN  JACKMAN
MERYL   GIBSON
GRETA   MALDEN
LAURA   BRODY
LAURENCE    BULLOCK
JON CHASE
AUDREY  BAILEY
*/

SELECT title, count(*)
  FROM film, film_actor
  WHERE film_actor.film_id = film.film_id
  AND title = "Sky Miracle"
  GROUP BY title;


SELECT first_name, last_name, title
  FROM actor, film_actor, film
  WHERE film.film_id = film_actor.film_id AND film_actor.actor_id = actor.actor_id
  AND title = "Sky Miracle";

/*
2.7. In how many films was "Jeff Silverstone" an actor?
(±7 lines, 3 tables)
Answer: 25
*/
SELECT first_name, last_name, count(*)
  FROM actor, film_actor, film
  WHERE film.film_id = film_actor.film_id AND film_actor.actor_id = actor.actor_id
  AND first_name = "Jeff" AND last_name = "Silverstone";




/*
2.8. Which country has the most customers? How many?
(±7 lines, 4 tables)
Answer: India   60
*/

SELECT country, count(*) AS numb
FROM country, city, address, customer
WHERE address.city_id = city.city_id
  AND city.country_id = country.country_id
  AND address.address_id = customer.address_id
  GROUP BY country.country
  ORDER BY numb DESC;


/*
2.9. What is the most popular comedy film?
(±9 lines; 5 tables)
Answer: STRICTLY SCARFACE   325
*/

SELECT film.title, count(*) AS rent_numb
  FROM film, inventory, rental, category, film_category
  WHERE rental.inventory_id = inventory.inventory_id
  AND film.film_id = inventory.film_id
  AND film_category.film_id = film.film_id
  AND film_category.category_id = category.category_id
  AND category.name = "comedy"
  GROUP BY title
  ORDER BY rent_numb DESC;

/*
2.10. How many customers live in Turkey?
(±7 lines, 4 tables)
Answer: 15
*/

SELECT count(*)
  FROM customer, address, city, country
  WHERE address.address_id = customer.address_id
  AND address.city_id = city.city_id
  AND city.country_id = country.country_id
  AND country = "Turkey";



/*  ***************************************************
    *** LEVEL 3 - Chunin                            ***
    ***                                             ***
    *** Focus:  Joining not more than six tables    ***
    ***         Multi parameter WHERE clause        ***
    ***         Functions beyond count(*)           ***
    ***                                             ***
    *** Estimated time: 2 classes                   ***
    *************************************************** */

use blockbusters;

/* save as CRLF for windows */

/*
3.0. suitable / How / Bangkok / films / for / are / children / store / at / the / many / of / branch / the ?
(±8 lines, 5 tables)
Question:  How many films are suitable for children at the Bangkok branch of the store?
Answer: 1162
*/


select count(*)
from film, inventory, store, address, city
where film.film_id = inventory.film_id
and inventory.store_id = store.store_id
and store.address_id = address.address_id
and address.city_id = city.city_id
and city.name = 'Bangkok'
and film.rating = 'PG';


/*
3.1. starred / Which / in / has / films / the / actor / most ?
(±7 lines; 3 tables)
Question: Which actor has starred in the most films?
Answer: 42  GINA    DEGENERES
*/
SELECT actor.actor_id, count(*) AS numb, actor.first_name, actor.last_name
  FROM actor, film, film_actor
  WHERE film_actor.actor_id = actor.actor_id
  AND film.film_id = film_actor.film_id
  GROUP BY actor.actor_id
  ORDER BY numb DESC;




/*
3.2.  were / DVDs / returned / How / late / many / (or not at all) ?
(±4 lines, 1 table, hint: use the now() function; is null)
Question: How many DVDs were returned late?
Answer: 2591
*/

SELECT count(*)
  FROM rental
  WHERE due_date < returned_date
  OR returned_date IS null;

/*
3.3. rented / they / not / returned / has / DVD(s) / the / Who ?
(±8 lines, 4 tables, hint: use the now() function; is null)
Question: Who rented the DVD(s) they has not returned?
Answer:
*/
SELECT customer.first_name, customer.last_name
  FROM customer, rental
  WHERE customer.customer_id = rental.customer_id
  AND rental.returned_date IS null
  AND due_date <= now();



/*
3.4. popular / is / to / most / Which / the / film / steal?
(±8 lines, 4 tables, hint: use the now() function; is null)
Question: Which film is the most popular to steal?
Answer: SUMMER SCARFACE 8
*/

SELECT film.title, count(*) AS times
  FROM rental, film, inventory
  WHERE film.film_id = inventory.film_id
  AND rental.inventory_id = inventory.inventory_id
  AND rental.returned_date IS null
  AND due_date <= now()
  GROUP BY film.title
  ORDER BY times DESC;



/*
3.5. many / English / How / documentaries / films / are ?
(±8 lines, 4 tables)
Question: How many films are English documentaries?
Answer: 11
*/

SELECT count(*)
  FROM film, film_category, category, language
  WHERE language.name= "English" AND category.name = "Documentary"
  AND film_category.category_id = category.category_id
  AND film_category.film_id = film.film_id
  AND film.language_id = language.language_id;



/*
3.6. $1200 / have / Which / customers / film / more / on / than / rentals / spent ?
(±8 lines, 3 tables; hint:use the sum() function; use having() )
Question: Which customers have spent more than $1200 on film rentals?
Answer:
526 KARL    SEAL    1260.10
148 ELEANOR HUNT    1257.13
331 ERIC    ROBERT  1246.27
16  SANDRA  MARTIN  1240.07
21  MICHELLE    CLARK   1220.10
146 JAMIE   RICE    1204.31
210 ELLA    OLIVER  1202.25
293 MAE FLETCHER    1201.39
*/

SELECT customer.customer_id, customer.first_name, customer.last_name, SUM(payment.amount) AS spent
  FROM customer, payment
  WHERE payment.customer_id = customer.customer_id
  GROUP BY customer.customer_id
  ORDER BY spent DESC;

/*
3.7. popular / category / the / of / is / film / Which / most ?
(±8 lines, 5 tables)
Question: Which category film is the most popular?
Answer: Sports, 14873 film.
*/

SELECT category.name, count(category.name) AS amo
  FROM film, rental, inventory, category, film_category
  WHERE film.film_id = inventory.film_id
  AND inventory.inventory_id = rental.inventory_id
  AND film.film_id = film_category.film_id
  AND film_category.category_id = category.category_id
  GROUP BY category.name
  ORDER BY amo DESC;





/*
3.8. customers / rented / movies / horror / Which / have / >= 30  ?
(±10 lines; 6 tables)
Question: Which customers have rented >=30 horror movies?
Answer:
346 ARTHUR  SIMPKINS    Horror  38
16  SANDRA  MARTIN  Horror  35
592 TERRANCE    ROUSH   Horror  32
275 CAROLE  BARNETT Horror  31
578 WILLARD LUMPKIN Horror  31
406 NATHAN  RUNYON  Horror  30
374 JEREMY  HURTADO Horror  30
508 MILTON  HOWLAND Horror  30
*/

SELECT customer.customer_id, customer.first_name, customer.last_name, category.name, count(category.name) AS per
  FROM customer, film, rental, inventory, category, film_category
  WHERE rental.customer_id = customer.customer_id
  AND rental.inventory_id = inventory.inventory_id
  AND inventory.film_id = film.film_id
  AND film.film_id = film_category.film_id
  AND film_category.category_id = category.category_id
  AND category.name = "HORROR"
  GROUP BY customer.customer_id
  ORDER BY per DESC;




/*
3.9. Which / income / generated / rental / category / film / the / highest ?
(±9 lines, 6 tables)
Question: Which category generated the highest rental income?
Answer: Sports  47090.27
*/

SELECT category.name, sum(payment.amount) AS dadada
  FROM category, film_category, film, inventory, rental, payment
  WHERE film.film_id = film_category.film_id
  AND film_category.category_id = category.category_id
  AND film.film_id = inventory.film_id
  AND inventory.inventory_id = rental.inventory_id
  AND payment.rental_id = rental.rental_id
  GROUP BY category.name
  ORDER BY dadada DESC;


/*
3.10. blockbusters / actor / has / money / the / earned / Which / most ?
(±9 lines, 6 tables; hint use the 'sum()' function; see Question 3.1 for help)
Question: Which ghostbusters actor has earned the most money?
Answer: 107 GINA    DEGENERES   28360.98
*/


SELECT actor.actor_id, actor.first_name, actor.last_name, SUM(payment.amount) AS sasas
  FROM actor, film_actor, film, inventory, rental, payment
  WHERE actor.actor_id = film_actor.actor_id
  AND film_actor.film_id = film.film_id
  AND film.film_id = inventory.film_id
  AND inventory.inventory_id = rental.inventory_id
  AND rental.rental_id = payment.rental_id
  GROUP BY actor.actor_id
  ORDER BY sasas DESC;




/*  *******************************************************************************
    *** LEVEL 4 - Jonin                                                         ***
    ***                                                                         ***
    *** Focus:  in()/not in()                                                   ***
    ***                                                                         ***
    *** Limits: Joining not more than six tables                                ***
    ***         Multi parameter WHERE clause                                    ***
    ***                                                                         ***
    *** Estimated time: 2 lessons                                               ***
    ******************************************************************************* */

use blockbusters;

/* save as CRLF for windows */

/*
4.0. or / or / this / customers / rented / Which / from / have / not / month / a movie / "Movie Madness Video" / "Cinefile" / "Video Paradiso" ?
(±14 lines, 5 tables, hint: not in(); date_format() )
Question: Which customers have not rented a movie from "Movie Madness Video" or "Cinefile" or "Video Paradiso" this month?
Answer: 537 customers
*/


select customer.customer_id, first_name, last_name
from customer
where (customer_id, first_name, last_name) not in
(
    select distinct customer.customer_id, first_name, last_name
    from rental, customer, inventory, store
    where rental.customer_id = customer.customer_id
    and rental.inventory_id = inventory.inventory_id
    and inventory.store_id = store.store_id
    and date_format(rental_date, '%Y-%m') = date_format(now(), '%Y-%m')
    and store.name in ('Movie Madness Video', 'Cinefile', 'Video Paradiso')
)
order by customer_id, first_name, last_name;


/*
4.1. any / actors / not / Which / Animation / have / acted / or / Sports / in / films?
(±10 lines, 6 tables, hint: not in)
Question: Which actors have not acted in and Animation or Sports films?
Answer: ADAM    HOPPER
        MINNIE  KILMER
        CHRIS   DEPP
*/

SELECT actor.actor_id, actor.first_name, actor.last_name
  FROM actor
  WHERE (actor.actor_id, actor.first_name, actor.last_name) NOT IN
(
SELECT actor.actor_id, actor.first_name, actor.last_name
  FROM actor, film, category, film_actor, film_category
  WHERE actor.actor_id = film_actor.actor_id
  AND category.category_id = film_category.category_id
  AND film_actor.film_id = film_category.film_id
  AND category.name in ("Sports", "Animation")
);


/*
4.2. films / Per / yet / rented / store / not / which / have / been ?
(±9 lines, 7 tables, hint: not in)
Question:Per store which films have not been rented yet?
Answer: 2
*/

SELECT store.name, film.title
  FROM film, store, inventory
  WHERE (store.name, film.title) NOT IN
  (
SELECT store.name, film.title
  FROM store, film, inventory, rental
  WHERE inventory.store_id = store.store_id
  AND inventory.film_id = film.film_id
  AND inventory.inventory_id = rental.inventory_id
  )
  AND film.film_id = inventory.film_id
  AND inventory.store_id = store.store_id;



/*
4.3. "Mr. Video" / copies / film / not / Which / does / have / any / of ?
(±10 lines, 4 tables, hint: not in)
Question: Which film does "Mr. Video" not have any copy of ?
Answer:SPLASH GUMP, DISTURBING SCARFACE
*/

SELECT film.title
  FROM film
  WHERE (film.title) NOT IN
  (
SELECT film.title
  FROM film, store, inventory
  WHERE store.name = "Mr. Video"
  AND film.film_id = inventory.film_id
  AND store.store_id = inventory.store_id
  );


/*
4.4. available / only / film / Which / is / in / "Movie Star Planet"?
(±10 lines, 5 tables, hint: not in)
Question: Which film is available only in "Movie Star Planet"?
Answer: SPLASH GUMP
*/
SELECT film.title
  FROM film
  WHERE (film.title) NOT IN
  (
SELECT film.title
  FROM film, inventory, store
  WHERE film.film_id = inventory.film_id
  AND inventory.store_id = store.store_id
  AND store.name != "Movie Star Planet"
  );



/*
4.5. Singapore / What / China / income / is / rented / the / films / total / from / the / which / been / have / in / the / stores / and ?
(±10 lines, 7 tables, hint: in)
Question: What is the total income from the films which have been rented in the store Singapore and China?
Answer: 40023.97
*/

SELECT sum(payment.amount) AS totall
  FROM payment, rental, inventory, store, address, country, city
  WHERE payment.rental_id = rental.rental_id
  AND rental.inventory_id = inventory.inventory_id
  AND store.store_id = inventory.store_id
  AND store.address_id = address.address_id
  AND address.city_id = city.city_id
  AND city.country_id = country.country_id
  AND country.name = "China" OR "Singapore";




/*  *******************************************************************************
    *** LEVEL 5 - ANBU                                                          ***
    ***                                                                         ***
    *** Focus:  joining to the same table many times                            ***
    ***                                                                         ***
    *** Limits:                                                                 ***
    ***                                                                         ***
    ***                                                                         ***
    *** Estimated time: 2 lessons                                               ***
    ******************************************************************************* */

use blockbusters;

/* save as CRLF for windows */


/*
5.0. Which films star both "SUSAN DAVIS" and "HARVEY HOPE"?
(±8 lines, 5 tables)
Answer:
*/

select film.film_id, title
from film, film_actor as fa1, actor as a1, film_actor as fa2, actor as a2

where a1.first_name = 'SUSAN' and a1.last_name = 'DAVIS'
and a1.actor_id = fa1.actor_id
and fa1.film_id = film.film_id

and a2.first_name = 'HARVEY' and a2.last_name = 'HOPE'
and a2.actor_id = fa2.actor_id
and fa2.film_id = film.film_id;




/*
5.1. Which actor starred in both "SECRETARY ROUGE" and "ELIZABETH SHANE"?
(±8 lines, 5 tables)
Answer:
*/

SELECT concat(actor.first_name, " ", actor.last_name) AS fullname
  FROM actor, film_actor AS ift1, film AS maiftl1, film_actor AS ift2, film AS maiftl2
  WHERE actor.actor_id = ift1.actor_id
  AND ift1.film_id = maiftl1.film_id
  AND maiftl1.title = "SECRETARY ROUGE"

  AND actor.actor_id = ift2.actor_id
  AND ift2.film_id = maiftl2.film_id
  AND maiftl2.title = "ELIZABETH SHANE";

/*
5.2. List the customers for each store.  Like this:
(±9 lines, 8 tables; hint: use the concat() function)
Answer:
+----------+-----------------+------------+----------------+-------------+----------------+-------------+--------------------+
| store_id | name            | store_city | store_country  | customer_id | name           | cust_city   | cust_name          |
+----------+-----------------+------------+----------------+-------------+----------------+-------------+--------------------+
|        1 | Five Star Films | Edinburgh  | United Kingdom |          84 | SARA PERRY     | Atlixco     | Mexico             |
|        1 | Five Star Films | Edinburgh  | United Kingdom |         106 | CONNIE WALLACE | Ivanovo     | Russian Federation |
|        1 | Five Star Films | Edinburgh  | United Kingdom |         133 | PAULINE HENRY  | Torren      | Mexico             |
|        1 | Five Star Films | Edinburgh  | United Kingdom |         148 | ELEANOR HUNT   | Saint-Denis | Runion             |
|        1 | Five Star Films | Edinburgh  | United Kingdom |         152 | ALICIA MILLS   | Nagaon      | India              |
+----------+-----------------+------------+----------------+-------------+----------------+-------------+--------------------+
*/
SELECT store.store_id, store.name, store_city.name AS store_city, store_country.name AS store_country, customer_id,
  concat(customer.first_name, " ", customer.last_name) AS full_name,
  cust_city.name AS cust_city, cust_country.name AS cust_country
  FROM store, customer, city AS store_city, country AS store_country, address AS store_address, city AS cust_city, country AS cust_country, address AS cust_address
  WHERE customer.store_id = store.store_id
  AND store.address_id = store_address.address_id
  AND store_address.city_id = store_city.city_id
  AND store_city.country_id = store_country.country_id

  AND customer.address_id = cust_address.address_id
  AND cust_address.city_id = cust_city.city_id
  AND cust_city.country_id = cust_country.country_id;


/*
5.3. For each member of staff, find out who she/he reports to.  Like this:
(±3 lines, 2 tables; hint: use the concat() function)
Answer:
+----------------------+----------------------+
| staff_name           | manager_name         |
+----------------------+----------------------+
| Jon Stephens         | Mike Hillyer         |
| Lajuana Borda        | Mike Hillyer         |
| Sherrell Mcanally    | Mike Hillyer         |
| Reyes Gwyn           | Mike Hillyer         |
+----------------------+----------------------+
*/

SELECT concat(staff.first_name, " ", staff.last_name), concat(man.first_name, " ", man.last_name)
  FROM staff, staff AS man
  WHERE staff.manager_id = man.staff_id;

/*
5.4. How many Indonesian customers have rented "TOURIST PELICAN" *AND* "AIRPORT POLLOCK" *AND* "SPOILERS HELLFIGHTERS"?
(±24 lines, 13 tables)
Answer: 2
*/
SELECT
  customer.first_name,
  customer.last_name
FROM customer,
     address,
     city,
     country,
     film AS film1,
     film AS film2,
     film AS film3,
     rental AS rental1,
     rental AS rental2,
     rental AS rental3,
     inventory AS inventory1,
     inventory AS inventory2,
     inventory AS inventory3
WHERE customer.address_id = address.address_id
AND address.city_id = city.city_id
AND city.country_id = country.country_id
AND country.name = "Indonesia"

AND rental1.customer_id = customer.customer_id
AND rental1.inventory_id = inventory1.inventory_id
AND inventory1.film_id = film1.film_id
AND film1.title = "TOURIST PELICAN"

AND rental2.customer_id = customer.customer_id
AND rental2.inventory_id = inventory2.inventory_id
AND inventory2.film_id = film2.film_id
AND film2.title = "AIRPORT POLLOCK"

AND rental3.customer_id = customer.customer_id
AND rental3.inventory_id = inventory3.inventory_id
AND inventory3.film_id = film3.film_id
AND film3.title = "SPOILERS HELLFIGHTERS";





/*
5.5. Which Chinese customer rented an action film *AND* a travel film *AND* a Music film in the first 7 days of April 2016?
(±8 lines, 19 tables; hint: '2016-04-01 00:00:00' & '2016-04-08 00:00:00')
Answer: KELLY KNOTT
*/

SELECT
  CONCAT(customer.first_name, " ", customer.last_name)
FROM customer,
     address,
     city,
     country,
     category AS category1,
     category AS category2,
     category AS category3,
     film_category AS film_category1,
     film_category AS film_category2,
     film_category AS film_category3,
     film AS film1,
     film AS film2,
     film AS film3,
     rental AS rental1,
     rental AS rental2,
     rental AS rental3,
     inventory AS inventory1,
     inventory AS inventory2,
     inventory AS inventory3
WHERE customer.address_id = address.address_id
AND address.city_id = city.city_id
AND city.country_id = country.country_id
AND country.name = "China"

AND film1.film_id = film_category1.film_id
AND film_category1.category_id = category1.category_id
AND category1.name = "ACTION"
AND rental1.customer_id = customer.customer_id
AND rental1.inventory_id = inventory1.inventory_id
AND inventory1.film_id = film1.film_id
AND rental1.rental_date >= "2016-04-01 00:00:00"
AND rental1.rental_date < "2016-04-08 00:00:00"

AND film2.film_id = film_category2.film_id
AND film_category2.category_id = category2.category_id
AND category2.name = "TRAVEL"
AND rental2.customer_id = customer.customer_id
AND rental2.inventory_id = inventory2.inventory_id
AND inventory2.film_id = film2.film_id
AND rental2.rental_date >= "2016-04-01 00:00:00"
AND rental2.rental_date < "2016-04-08 00:00:00"

AND film3.film_id = film_category3.film_id
AND film_category3.category_id = category3.category_id
AND category3.name = "MUSIC"
AND rental3.customer_id = customer.customer_id
AND rental3.inventory_id = inventory3.inventory_id
AND inventory3.film_id = film3.film_id
AND rental3.rental_date >= "2016-04-01 00:00:00"
AND rental3.rental_date < "2016-04-08 00:00:00";