I don't understand what key word or phrase that indicates a sub-query should be used for #2, and #3.
A. I noticed that whenever a field is mentioned in group by, it has to be mentioned in the select filed, but in the solution below, the reason why "film_id" was not mentioned in the first "select" statement is because it was under another "select" statement with parenthesis? B. Also my solution gave me a completely different answer. Why are we counting by "id"?
-- 2. Select the maximum number and the minimum number of screenings for a particular film.
My solution: SELECT max(id), min(id) from (id, count(film_id) as no_films from screenings group by film_id) a;
The solution: SELECT MAX(id), MIN(id) FROM (SELECT film_id, COUNT(id) AS id FROM screenings GROUP BY film_id) a;
For #3, I don't understand the thought process behind this. Why is "screenings" not defined or joined? When does one use the "as" when using the count statement?
-- 3. Select each film name and the number of screenings for that film.
My solution:
select f.name,
(select count(id) as id from screenings s)
from films f
join films f ON s.film_id = f.id;
The solution:
SELECT name,
(SELECT COUNT(id) FROM screenings
WHERE film_id = f.id)
FROM films f;