Solution Practice Set 4

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

-- Question # 1, Query 1
SELECT Producer 
FROM Movies
GROUP BY Producer;

-- Question # 1, Query 2
SELECT Producer 
FROM Movies
GROUP BY Producer
HAVING COUNT(Producer) > 1;

-- Question # 1, Query 3
SELECT Producer AS Producer_Name, AVG(CollectionInMillions) AS Average_Collection_In_Millions
FROM Movies
GROUP BY Producer
HAVING COUNT(Producer) > 1;

-- Question # 2, Query 1
SELECT CONCAT (FirstName, " ", SecondName) AS Actors, MovieId, Producer 
FROM Actors JOIN Cast 
ON Actors.Id = Cast.ActorId
JOIN Movies
ON Cast.MovieId = Movies.Id;

-- Question # 2, Query 2
SELECT CONCAT (FirstName, " ", SecondName) AS Actors, MovieId, Producer 
FROM Actors JOIN Cast 
ON Actors.Id = Cast.ActorId
JOIN Movies
ON Cast.MovieId = Movies.Id
AND Producer <> 'Ryan Seacrest';

-- Question # 2, Query 3
SELECT DISTINCT(CONCAT (FirstName, " ", SecondName)) AS Actors_Who_Have_Not_Worked_with_Ryan_Seacrest 
FROM Actors  JOIN Cast 
ON Actors.Id = Cast.ActorId
JOIN Movies
ON Cast.MovieId = Movies.Id
AND Producer <> 'Ryan Seacrest';

-- Question # 2, Query 4
SELECT c.ActorID, c.MovieId, m.Producer 
FROM Cast c, Movies m 
WHERE c.MovieId = m.Id;

-- Question # 2, Query 5
SELECT c.ActorID, c.MovieId, m.Producer 
FROM Cast c, Movies m 
WHERE c.MovieId = m.Id 
  AND m.Producer <> 'Ryan Seacrest';

-- Question # 2, Query 6
SELECT DISTINCT(CONCAT (a.FirstName, " ", a.SecondName)) AS Actors_Who_Have_Not_Worked_with_Ryan_Seacrest 
FROM Cast c, Movies m, Actors a 
WHERE c.MovieId = m.Id 
  AND m.Producer <> 'Ryan Seacrest' 
  AND c.ActorId = a.Id;

-- Question # 3, Query 1
SELECT ActorId, AssetType, LastUpdatedOn 
FROM DigitalAssets 
ORDER BY ActorId ASC, 
         LastUpdatedOn DESC;

-- Question # 3, Query 2
SELECT ActorId, MAX(LastUpdatedOn)
FROM DigitalAssets 
GROUP BY ActorId;

-- Question # 3, Query 3
SELECT ActorId, AssetType, LastUpdatedOn 
FROM DigitalAssets
WHERE (ActorId, LastUpdatedOn) IN
                                (SELECT ActorId, MAX(LastUpdatedOn) 
                                 FROM DigitalAssets 
                                 GROUP BY ActorID);

-- Question # 3, Query 4
CREATE TABLE DigitalActivityTrack (
Id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
Actor_Id INT NOT NULL,
Digital_Asset VARCHAR(20) NOT NULL,
Last_Updated_At DATETIME Not NULL DEFAULT NOW()
);

-- Question # 3, Query 5
INSERT INTO DigitalActivityTrack (Actor_Id, Digital_Asset, Last_Updated_At)
SELECT ActorId, AssetType, LastUpdatedOn FROM DigitalAssets
                WHERE (ActorId, LastUpdatedOn) In 
                                (SELECT ActorId, MAX(LastUpdatedOn) FROM DigitalAssets 
                                 GROUP BY ActorID)
             ORDER BY LastUpdatedOn DESC;

-- Question # 3, Query 6
SELECT CONCAT(a.FirstName, " ", a.SecondName) AS Actor_Name, Digital_Asset, Last_Updated_At
FROM Actors a, DigitalActivityTrack
WHERE a.Id = Actor_Id;

-- Question # 4, Query 1
SELECT CONCAT (FirstName, " ", SecondName) AS Actor_Name, NetWorthInMillions AS 3rd_Lowest_Net_Worth_In_Millions
From Actors a1
WHERE 2 = (SELECT COUNT(DISTINCT (NetWorthInMillions)) 
           FROM Actors a2
           WHERE a2. NetWorthInMillions < a1. NetWorthInMillions);

-- Question # 5, Query 1
SELECT ActorID, COUNT(ActorId)
FROM DigitalAssets
GROUP BY ActorId;

-- Question # 5, Query 2
SELECT ActorID, GROUP_CONCAT(AssetType)
FROM DigitalAssets
GROUP BY ActorId;

-- Question # 5, Query 3
SELECT CONCAT (FirstName, " ", SecondName) AS Actor_Name,                
       GROUP_CONCAT(AssetType) AS Digital_Assets
FROM Actors INNER JOIN DigitalAssets
ON Actors.Id = DigitalAssets.ActorId
GROUP BY Id;

-- Question # 5, Query 4
SELECT CONCAT (FirstName, " ", SecondName) AS Actor_Name, 
       GROUP_CONCAT(AssetType) AS Digital_Assets
FROM Actors LEFT JOIN DigitalAssets
ON Actors.Id = DigitalAssets.ActorId
GROUP BY Id;