--
-- 2020/3/24
--

SELECT DISTTINCT
FROM
WHERE
GROUP BY
HAVING
ORDER BY;


SELECT DISTINCT c.name, f.film_id, f.rating -- step 3. rows -> output fields.
FROM category AS c INNER JOIN film_category AS fc ON (c.category_id = fc.category_id)
    INNER JOIN film AS f ON (fc.film_id = f.film_id)
    -- step 1. sources -> rows
WHERE f.rating = 'PG'  -- step 2 -> filter rows
ORDER BY c.name;  -- step 4 --> order results of step 3.


SELECT DISTINCT c.name, f.film_id, f.rating -- step 4. groups -> output fields.
FROM category AS c INNER JOIN film_category AS fc ON (c.category_id = fc.category_id)
    INNER JOIN film AS f ON (fc.film_id = f.film_id)
    -- step 1. sources -> rows
WHERE f.rating = 'PG'  -- step 2 -> filter rows
GROUP BY c.name -- step 3: group rows in step 2 into groups defined by unique names of c.name
ORDER BY c.name;  -- step 5 --> order results of step 3.



SELECT DISTINCT c.name, 
    COUNT(f.film_id) AS `number of PG films` -- step 4. groups -> output fields.
    -- COUNT: group function applies to a group.
FROM category AS c INNER JOIN film_category AS fc ON (c.category_id = fc.category_id)
    INNER JOIN film AS f ON (fc.film_id = f.film_id)
    -- step 1. sources -> rows
WHERE f.rating = 'PG'  -- step 2 -> filter rows
GROUP BY c.name -- step 3: group rows in step 2 into groups defined by unique names of c.name
ORDER BY `number of PG films` DESC;  -- step 5 --> order results of step 3.

-- list only the category 14 or more PG films:
--      `number of PG films` >= 14
-- 
SELECT DISTINCT c.name, 
    COUNT(f.film_id) AS `number of PG films` -- step 4. groups -> output fields.
    -- COUNT: group function applies to a group.
FROM category AS c INNER JOIN film_category AS fc ON (c.category_id = fc.category_id)
    INNER JOIN film AS f ON (fc.film_id = f.film_id)
    -- step 1. sources -> rows
WHERE f.rating = 'PG'  -- step 2 -> filter rows
AND `number of PG films` >= 14
GROUP BY c.name -- step 3: group rows in step 2 into groups defined by unique names of c.name
ORDER BY `number of PG films` DESC;  -- step 5 --> order results of step 3.

-- ERROR 1054 (42S22): Unknown column 'number of PG films' in 'where clause'

SELECT DISTINCT c.name, 
    COUNT(f.film_id) AS `number of PG films` -- step 4. groups -> output fields.
    -- COUNT: group function applies to a group.
FROM category AS c INNER JOIN film_category AS fc ON (c.category_id = fc.category_id)
    INNER JOIN film AS f ON (fc.film_id = f.film_id)
    -- step 1. sources -> rows
WHERE f.rating = 'PG'  -- step 2 -> filter rows
AND COUNT(f.film_id) >= 14  -- COUNT is a group function. No group yet.
GROUP BY c.name -- step 3: group rows in step 2 into groups defined by unique names of c.name
ORDER BY `number of PG films` DESC;  -- step 5 --> order results of step 3.

-- ERROR 1111 (HY000): Invalid use of group function

SELECT DISTINCT c.name, 
    COUNT(f.film_id) AS `number of PG films` -- step 4. groups -> output fields.
    -- COUNT: group function applies to a group.
FROM category AS c INNER JOIN film_category AS fc ON (c.category_id = fc.category_id)
    INNER JOIN film AS f ON (fc.film_id = f.film_id)
    -- step 1. sources -> rows
WHERE f.rating = 'PG'  -- step 2 -> filter rows
    -- COUNT is a group function. No group yet.
GROUP BY c.name -- step 3: group rows in step 2 into groups defined by unique names of c.name
HAVING COUNT(f.film_id) >= 14  -- after the groups are formed; step 4: filter the groups.
ORDER BY `number of PG films` DESC;  -- step 5 --> order results of step 3.

(7) For each film, lists the id, film title, the number of copies in the inventory, the number of times the film has been rented. Only list the film rented for 32 or more times.

+---------+---------------------+------------+------------+
| film_id | title               | num_copies | num_rented |
+---------+---------------------+------------+------------+
|     103 | BUCKET BROTHERHOOD  |          8 |         34 |
|     738 | ROCKETEER MOTHER    |          8 |         33 |
|     382 | GRIT CLOCKWORK      |          8 |         32 |
|     767 | SCALAWAG DUCK       |          8 |         32 |
|     489 | JUGGLER HARDLY      |          8 |         32 |
|     730 | RIDGEMONT SUBMARINE |          8 |         32 |
|     331 | FORWARD TEMPLE      |          8 |         32 |
+---------+---------------------+------------+------------+
7 rows in set (0.06 sec)

.7.1 film_id, title, num_copies
 7.2 film_id, num_rented
 
 Common table expressions (CTE)
 
7alt. film_id, num_copies, num_actors In the film.
 
-- 7alt.1 film_id, num_copies

SELECT i.film_id, COUNT(inventory_id) AS num_copies
FROM inventory AS i
GROUP BY i.film_id
ORDER BY i.film_id;

-- 7alt.2 film_id,  num_actors
SELECT fa.film_id, COUNT(fa.actor_id) AS num_actors
FROM film_actor AS fa
GROUP BY fa.film_id
ORDER BY fa.film_id;

SELECT i.film_id, COUNT(inventory_id) AS num_copies
FROM inventory AS i
GROUP BY i.film_id
ORDER BY i.film_id
LIMIT 5;

-- 7alt.2 film_id,  num_actors
SELECT fa.film_id, COUNT(fa.actor_id) AS num_actors
FROM film_actor AS fa
GROUP BY fa.film_id
ORDER BY fa.film_id
LIMIT 5;

-- 7alt 3 combine 7alt.1 and 7alt.2

WITH t1 AS
(   SELECT i.film_id, COUNT(inventory_id) AS num_copies
    FROM inventory AS i
    GROUP BY i.film_id
    ORDER BY i.film_id
),
t2 AS
(   SELECT fa.film_id, COUNT(fa.actor_id) AS num_actors
    FROM film_actor AS fa
    GROUP BY fa.film_id
    ORDER BY fa.film_id
)
SELECT t1.film_id, t1.num_copies, t2.num_actors
FROM t1 INNER JOIN t2 ON (t1.film_id = t2.film_id);