Solution Practice Set 2

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

-- Question # 1, Query 1
SELECT Id, FirstName, SecondName, MovieId
FROM Actors 
INNER JOIN Cast
ON Id = ActorId;

-- Question # 1, Query 2
SELECT Id, COUNT(*)
FROM Actors 
INNER JOIN Cast
ON Id = ActorId
GROUP BY Id;

-- Question # 1, Query 3
SELECT Id, 
COUNT(*) AS MovieCount
FROM Actors 
INNER JOIN Cast
ON Id = ActorId
GROUP BY Id
HAVING MovieCount > 1;

-- Question # 1, Query 4
SELECT CONCAT (FirstName, " ", SecondName)
AS Actor_Names, 
Movie_Count 
FROM Actors a
INNER JOIN (SELECT Id, 
            COUNT(*) AS Movie_Count
            FROM Actors 
            INNER JOIN Cast
            ON Id = ActorId
            GROUP BY Id
            HAVING Movie_Count > 1) AS tbl
ON tbl.Id = a.Id;

-- Question # 2, Query 1
SELECT Id 
FROM Movies 
WHERE Name="Mr & Mrs. Smith";

-- Question # 2, Query 2
SELECT ActorId
FROM Cast
WHERE MovieId IN (SELECT Id 
                  FROM Movies 
                  WHERE Name="Mr & Mrs. Smith");

-- Question # 2, Query 3
SELECT CONCAT(FirstName, " ", SecondName) 
AS "Cast Of Mr. & Mrs. Smith"
FROM Actors
WHERE Id IN ( SELECT ActorId
              FROM Cast
              WHERE MovieId IN (SELECT Id 
                                FROM Movies 
                                WHERE Name="Mr & Mrs. Smith"));

-- Question # 2, Query 4
SELECT CONCAT(FirstName, " ", SecondName) 
AS "Cast Of Mr. & Mrs. Smith"
FROM Actors
INNER JOIN (SELECT ActorId
            FROM Cast
            INNER JOIN Movies
            ON MovieId = Id
            WHERE Name="Mr & Mrs. Smith") AS tbl
ON tbl.ActorId = Id;

-- Question # 3, Query 1
SELECT Name, ActorId
FROM Movies
INNER JOIN Cast
On Id = MovieId; 

-- Question # 3, Query 2
SELECT tbl.Name AS Movie_Name,
CONCAT(FirstName, " ", SecondName) AS Actor_Name
FROM Actors
INNER JOIN (SELECT Name, ActorId
            FROM Movies
            INNER JOIN Cast
            On Id = MovieId) AS tbl 
ON tbl.ActorId = Id
ORDER BY tbl.Name ASC;

-- Question # 4, Query 1
SELECT tbl.Name, COUNT(*)
FROM Actors
INNER JOIN (SELECT Name, ActorId, MovieId
            FROM Movies
            INNER JOIN Cast
            On Id = MovieId) AS tbl 
ON tbl.ActorId = Id
GROUP BY tbl.MovieId;

-- Question # 4, Query 2
SELECT MovieId, COUNT(*)
FROM Cast
GROUP BY MovieId;

-- Question # 4, Query 3
SELECT Name AS Movie_Name, 
Actor_Count
FROM Movies
INNER JOIN (SELECT MovieId, COUNT(*) AS Actor_Count
FROM Cast
GROUP BY MovieId) AS tbl
ON tbl.MovieID = Id;

-- Question # 5, Query 1
SELECT Id
FROM Actors 
WHERE FirstName = "Tom"
AND SecondName = "Cruise";

-- Question # 5, Query 2
SELECT MovieId
FROM Cast
WHERE ActorId = (SELECT Id
                  FROM Actors 
                  WHERE FirstName = "Tom"
                  AND SecondName = "Cruise");

-- Question # 5, Query 3
SELECT DISTINCT Producer 
FROM Movies 
WHERE Id IN (SELECT MovieId
             FROM Cast
             WHERE ActorId = (SELECT Id
                               FROM Actors 
                               WHERE FirstName = "Tom"
                               AND SecondName = "Cruise"));

-- Question # 5, Query 4
SELECT DISTINCT Producer 
FROM Movies 
WHERE Id NOT IN (SELECT MovieId
             FROM Cast
             WHERE ActorId = (SELECT Id
                               FROM Actors 
                               WHERE FirstName = "Tom"
                               AND SecondName = "Cruise"));

-- Question # 5, Query 5
SELECT DISTINCT Producer
FROM Movies
WHERE Producer
NOT IN (SELECT Producer 
        FROM Movies 
        WHERE Id IN (SELECT MovieId
                     FROM Cast
                     WHERE ActorId = (SELECT Id
                                      FROM Actors 
                                      WHERE FirstName = "Tom"
                               AND SecondName = "Cruise")));