Solution Practice Set 3

-- The lesson queries are reproduced below for convenient copy/paste into the terminal. 

-- Question # 1, Query 1
SELECT AVG(BudgetInMillions) 
FROM Movies;

-- Question # 1, Query 2
SELECT Name 
FROM Movies 
WHERE BudgetInMillions > (SELECT AVG(BudgetInMillions) 
                          FROM Movies);

-- Question # 2, Query 1
SELECT * FROM DigitalAssets 
RIGHT JOIN Actors 
ON Id = ActorId;

-- Question # 2, Query 2
SELECT CONCAT(FirstName, " ", SecondName)
AS Actors_With_No_Online_Presence
FROM DigitalAssets 
RIGHT JOIN Actors 
ON Id = ActorId
WHERE URL IS NULL;

-- Question # 3, Query 1
SELECT CONCAT(FirstName, " ", SecondName)
FROM Actors 
WHERE NOT EXISTS (SELECT ActorId 
                  FROM DigitalAssets 
                  WHERE ActorId = Id);

-- Question # 4, Query 1
SELECT Name, CollectionInMillions
FROM Movies
ORDER BY CollectionInMillions DESC;

-- Question # 4, Query 2
SELECT Name, 
CollectionInMillions AS Collection_In_Millions
FROM Movies
ORDER BY CollectionInMillions DESC
LIMIT 1 OFFSET 4;

-- Question # 4, Query 3
SELECT Name, 
CollectionInMillions AS Collection_In_Millions
FROM Movies
ORDER BY CollectionInMillions DESC
LIMIT 4, 1;

-- Question # 5, Query 1
SELECT LastUpdatedOn, Id 
FROM Actors 
INNER JOIN DigitalAssets 
ON ActorId = Id;

-- Question # 5, Query 2
SELECT * 
FROM Cast 
INNER JOIN (SELECT LastUpdatedOn, Id 
            FROM Actors 
            INNER JOIN DigitalAssets 
            ON ActorId = Id) AS tbl 
ON tbl.Id = ActorId;

-- Question # 5, Query 3
SELECT * 
FROM Movies AS m 
INNER JOIN (SELECT * 
            FROM Cast 
            INNER JOIN (SELECT LastUpdatedOn, Id 
                        FROM Actors 
                        INNER JOIN DigitalAssets 
                        ON ActorId = Id) AS tbl1
            ON tbl1.Id = ActorId) AS tbl2
ON tbl2.MovieId = m.Id;

-- Question # 5, Query 4
SELECT DISTINCT Name 
AS Actors_Posting_Online_Within_Five_Days_Of_Movie_Release
FROM Movies AS m 
INNER JOIN (SELECT * 
            FROM Cast 
            INNER JOIN (SELECT LastUpdatedOn, Id 
                        FROM Actors 
                        INNER JOIN DigitalAssets 
                        ON ActorId = Id) AS tbl1
            ON tbl1.Id = ActorId) AS tbl2
ON tbl2.MovieId = m.Id
WHERE ADDDATE(ReleaseDate, INTERVAL -5 Day) <= LastUpdatedOn
AND ADDDATE(ReleaseDate, INTERVAL +5 Day) >= LastUpdatedOn;