Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

SQL Basics in MS SQL Server

How to query more than one table
Creating JOINs
Referencing columns
Let's practice
12. Put your skills into practice

Instruction

Nice! To sum up, we discussed the following concepts:

  • In SQL, you can select data from multiple tables. To do so, you list comma-separated table names in the FROM clause.
  • Unfortunately, this isn't very efficient and often produces many unnecessary rows. To solve that, you should use: 
    JOIN <table>
  ON <condition>
  • If there are multiple columns with the same name, you should add a table name before the column and separate the two with a dot (Table.Column).
  • Columns can be given aliases. The syntax looks like this: <column> AS <alias>.

Let's put into practice everything we've learned so far. Are you ready? This example is going to be slightly more complicated, so make sure you remember everything from this part of the course.

Exercise

Select the Title and ProductionYear columns from the Movie table and Name and BirthYear columns from the Director table such that each movie is shown together with its director.

Show the BirthYear column as BornIn. Select only those movies that were filmed when their director was younger than 40.

Stuck? Here's a hint!

If we are supposed to find young directors, we may i.e., assume that the difference between ProductionYear and BirthYear must be less than 40.

Take a look at the correct query:

SELECT
  Movie.Title,
  Movie.ProductionYear,
  Director.Name,
  Director.BirthYear AS BornIn
FROM Movie
JOIN Director
  ON Movie.DirectorId = Director.Id
WHERE (Movie.ProductionYear - Director.BirthYear) < 40;