-- Q1 return movie title and year ordered by year

SELECT Title, Year
FROM nza.Movie
ORDER BY Year;

-- Q2 return year and the number of movies per year as Year_NUMBER

SELECT Year, COUNT(Title) AS Year_Number
FROM nza.Movie
GROUP BY Year;

-- Q3 return the average number of movies per year as AVG_NUMBER
-- (this does not take into account the years where there were 0 movies - sorry,
-- did not make this clear in the question)

SELECT AVG(COUNT(Title)) As Avg_Number
FROM nza.Movie
GROUP BY Year;

-- Q4 return in a single query MOVIE_ID and price for the cheapest and
-- for the most expensive DVD

SELECT Movie_ID, Price 
FROM nza.DVD
WHERE Price <= ALL (SELECT Price FROM nza.DVD) OR
      Price >= ALL (SELECT Price FROM nza.DVD);

-- other answers are of course also possible, using max and min or
-- using union instead of OR

-- Q5 return pairs of movie_IDs for which the DVDs have the same price,
-- ordered by price (cheapest first).

SELECT DVD1.Movie_ID AS ID1, DVD2.Movie_ID as ID2
FROM nza.DVD DVD1, nza.DVD DVD2
WHERE DVD1.Price = DVD2.Price AND DVD1.Movie_ID <> DVD2.Movie_ID
ORDER BY ID1, ID2;





