Solution Practice Set 1

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

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

-- Question # 2, Query 1
SELECT * FROM Movies a 
INNER JOIN Movies b;

-- Question # 2, Query 2
SELECT concat(a.FirstName," ",b.SecondName) 
FROM Actors a 
INNER JOIN Actors b 
ON a.SecondName = b.SecondName 
WHERE a.ID != b.ID;

-- Question # 2, Query 3
SELECT DISTINCT concat(a.FirstName," ",b.SecondName) 
AS Actors_With_Shared_SecondNames
FROM Actors a 
INNER JOIN Actors b 
ON a.SecondName = b.SecondName 
WHERE a.Id != b.Id;

-- Question # 3, Query 1
SELECT b.SecondName 
FROM Actors a 
INNER JOIN Actors b 
ON a.SecondName = b.SecondName 
WHERE a.Id != b.Id
GROUP BY b.SecondName;

-- Question # 3, Query 2
SELECT a.SecondName, 
COUNT(DISTINCT a.FirstName) 
FROM Actors a 
INNER JOIN Actors b 
ON a.SecondName = b.SecondName 
WHERE a.Id != b.Id 
group by a.SecondName;

-- Question # 3, Query 3
SELECT a.SecondName AS Actors_With_Shared_SecondNames, 
COUNT(DISTINCT a.Id) AS Count
FROM Actors a 
INNER JOIN Actors b 
ON a.SecondName = b.SecondName 
WHERE a.Id != b.Id 
group by a.SecondName;

-- Question # 4, Query 1
SELECT DISTINCT CONCAT(FirstName, " ", SecondName) AS Actors_Acted_In_Atleast_1_Movies 
FROM Actors
INNER JOIN Cast
ON Id = ActorId;

-- Question # 5, Query 1
SELECT Id, CONCAT(FirstName, " ", SecondName) AS Actors_With_No_Movies
FROM Actors
WHERE Id NOT IN (SELECT Id 
                 FROM Actors
                 INNER JOIN Cast
                 ON Id = ActorId);

-- Question # 5, Query 2
SELECT * 
FROM Actors
LEFT JOIN Cast
ON Id = ActorId;

-- Question # 5, Query 3
SELECT CONCAT(FirstName, " ", SecondName)  AS Actors_With_No_Movies 
FROM Actors
LEFT JOIN Cast
ON Id = ActorId
WHERE MovieId IS NULL;