Search⌘ K
Home
Courses
An Introductory Guide to SQL

Solution Practice Set 1

Explore practical SQL exercises that cover sorting results, self joins to compare data within the same table, and aggregation functions to count shared attributes. Learn to write queries that find top entries, identify shared last names among actors, and distinguish actors with or without movie roles. This practice set helps reinforce key relational database query skills.

We'll cover the following...

Solution Practice Set 1

The database relationship model is reprinted below for reference.

widget

Connect to the terminal below by clicking in the widget. Once connected, the command line prompt will show up. Enter or copy and paste the command ./DataJek/Lessons/quiz.sh and wait for the MySQL prompt to start-up.

MySQL
-- 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;
Terminal 1
Terminal
Loading...

Question # 1

Write a query that prints the top three movies by box office collection?

This asks for us to print the top three movies and should hint towards sorting. The sort key should be the column CollectionInMillions. However, remember that by default, ORDER BY sorts in ascending order so we’ll need to sort in descending order. The last piece to the puzzle is to apply the LIMIT clause so that we only retrieve the top three rows.

SELECT Name 
FROM Movies 
ORDER BY CollectionInMillions DESC 
LIMIT 3;

Question # 2

Can you write a query to determine if any two actors share the same second name?

The information we want to extract is contained within the Actors table. However, we need a way to compare the second name of the first actor with all the other actors in the table except with itself. How can you make the comparison? The answer is using a self join.

Whenever you hear yourself thinking in terms of picking up a row from a table and comparing it to another row from the same table or another table, you are looking for a join. Without further ado, we’ll perform an inner join of the ...