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 4

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


## Question # 1
Write a query to display the average collection in millions of producers who have produced more than one movie.

To answer this question, we need to shortlist only those producers whose name appears more than once in the __Movies__ table. This is hinting towards grouping the results by producers:

```mysql
SELECT Producer 
FROM Movies
GROUP BY Producer;
```


Since we are only interested in producers who have produced more than one movie, we will add a restriction on the number of times a producer's name appears in the __Movies__ table. The __HAVING__ clause used with the __GROUP BY__ clause will give us the desired result. 

```mysql
SELECT Producer 
FROM Movies
GROUP BY Producer
HAVING COUNT(Producer) > 1;
```
We are now left with two produces who have multiple movies. The last step is to find the average collection in millions of the films by these two producers:

```mysql
SELECT Producer AS Producer_Name, AVG(CollectionInMillions) AS Average_Collection_In_Millions
FROM Movies
GROUP BY Producer
HAVING COUNT(Producer) > 1;
```


## Question # 2
Find all those actors who have not worked with producer Ryan Seacrest.

__Approach 1: Joining three tables in a single SQL query.__

The information on actors, movies and producers is scattered in three tables; __Actors__, __Cast__ and __Movies__. We need to join the tables together to find the answer. Joining three tables in a single SQL query can be a tricky concept. The first table is related to the second table and the second table is related to the third table. In our case the __Actors__ table is related to the __Cast__ table and the __Cast__ table is related to the __Movies__ table. We want the names of the actors from the __Actors__ table and information on producers from the __Movies__ table.

The __Cast__ table joins the two tables __Actors__ and __Movies__ together and contains the primary key of both tables. The primary key (PK) of the __Actors__ table is a foreign key (FK) in the __Cast__ table. Similarly, the primary key of the __Movies__ table is the foreign key in the __Cast__ table. Understanding these table relationships is the key to joining multiple tables in a single MySQL query.

The basic syntax of joining three tables in MySQL is as follows:

>SELECT **table1.col**, **table3.col** 
>
>FROM **table1** JOIN **table2** 
>
>ON **table1.PK** = **table2.FK**
>
>JOIN **table3**
> 
>ON **table2.FK** = **table3.PK**

First we join table1 and table2 and then the combined data of the two tables is joined with table3. Here table2 is the table that connects table1 and table3. In our case this will translate to the following query:

```mysql
SELECT CONCAT (FirstName, " ", SecondName) AS Actors, MovieId, Producer 

FROM Actors JOIN Cast 
ON Actors.Id = Cast.ActorId

JOIN Movies
ON Cast.MovieId = Movies.Id;
```

This query joins the __Actors__ and __Movies__ table using the connecting __Cast__ table. We need only those actors who have not worked with producer Ryan Seacrest. Elinimation of the unwanted rows can be accomplished by adding a condition at the end of our multiple join query as:

```mysql
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';
```

The query now gives us 10 rows after removing three rows with Ryan Seacrest as producer. A glance at the results shows that some actors have worked in multiple movies and thus their name appears more than once. Since we are only interested in the names of those actors who have not worked with a particular producer, we need to apply __DISTINCT__ clause. The final query looks like this:

```mysql
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';
```


__Approach 2: Without using joins.__

The information required to answer this question is scattered across three tables; __Actors__, __Cast__ and __Movies__. First we will find the producers of the movies by combining data from __Cast__ and __Movies__ tables on the common column, movie ID.

```mysql
SELECT c.ActorID, c.MovieId, m.Producer 
FROM Cast c, Movies m 
WHERE c.MovieId = m.Id;
```

The query adds the producers to each row of the __Cast__ table. Next step is to find those actors who have not worked with producer Ryan Seacrest. This can be done by adding a condition Producer <> 'Ryan Seacrest'.

```mysql
SELECT c.ActorID, c.MovieId, m.Producer 
FROM Cast c, Movies m 
WHERE c.MovieId = m.Id 
  AND m.Producer <> 'Ryan Seacrest';
```

We are left with 10 rows now and it can be seen that some actors are appearing multiple times having acted in more than one movie. The last step is to display the names of the actors after removing duplicate entries. The __DISTINCT__ clause will remove duplicates. For the actor names, we will add columns from __Actors__ table to the query created so far:

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



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

Introduction

Basic SQL

Joins

Nested Queries

Multi Table Operations

Views

Stored Procedures

Triggers

Miscellaneous

Practice & Prep

Epilogue

Solution Practice Set 4