-- Q1 return average price of all dvds, as Average_Price 

SELECT AVG(Price) AS Average_Price
FROM nza.DVD;

-- Q2 return the name of director, movie title and price as DVD_Price
-- for the most expensive DVD

SELECT Name, Title, Price AS DVD_Price
FROM nza.DVD NATURAL JOIN nza.Director NATURAL JOIN nza.Movie
WHERE Price = (SELECT MAX(Price) FROM DVD);

-- Q3 list of director names, movie titles and prices of DVDs as DVD_Price of 
-- the movies they directed (separately for each DVD)

SELECT Name, Title, Price AS DVD_Price
FROM nza.DVD NATURAL JOIN nza.Director NATURAL JOIN nza.Movie
GROUP BY Name, Price, Title;

-- Q4

-- list of director names and total price of DVDs as
-- Total_DVD_Price of the movies each director directed
-- where this total price is strictly greater than 10 pounds

SELECT Name, SUM(Price) AS Total_DVD_Price
FROM nza.DVD NATURAL JOIN nza.Director NATURAL JOIN nza.Movie
GROUP BY Name
HAVING SUM(Price) > 10;

-- Q5 list of director names and total price of DVDs as 
-- Total_DVD_Price of the movies each director directed 
-- ordered by the total price in descending order

SELECT Name, SUM(Price) AS Total_DVD_Price
FROM nza.DVD NATURAL JOIN nza.Director NATURAL JOIN nza.Movie
GROUP BY Name
ORDER BY Total_DVD_Price DESC;

