Gain insights into SQL by exploring database creation, data manipulation, and advanced queries. Learn about practical applications and practice for SQL interviews to build confidence and readiness.

sql.tar.gz

The ability to work SQL is becoming an increasingly in-demand skill, both for software developers and people in less technical roles. If you’re interested in learning SQL and have no prior experience with it, then this course will be your light in a dark tunnel.

You’ll start by covering the basics of SQL such as how to create a database, how to insert, query, and update data. You’ll also learn fundamental concepts that developers and data scientists use everyday such as multi-table operations, nested queries, and how to set up views. 

Throughout, you’ll get to execute SQL queries in your browser and see results in real-time - you won’t need to worry about set-up.

At the end of this course, you’ll also get some hands-on practice with common SQL interview questions, so when the time comes, you’ll be ready and confident to answer any question that comes your way. Let’s get started!

An Introductory Guide to SQL

# Solution Practice Set 1

The database relationship model is reprinted below for reference.

>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.


-- 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;


## 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.



```mysql
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 Actors table.

```mysql
SELECT * FROM Movies a 
INNER JOIN Movies b;
```

Only part of the join is shown below because of limited space.


Get the solution to the basic exercise of viewing the information from a database.

Introduction

Basic SQL

Joins

Nested Queries

Multi Table Operations

Views

Stored Procedures

Triggers

Miscellaneous

Practice & Prep

Epilogue

Solution Practice Set 1