Introduction
1. Introduction
Exercises with artist, museums and their pieces of art
Exercises with the band members table
Exercises with tables of Japanese 100 Yen store
Exercises with employees, salaries and benefits

Instruction

Welcome to the third part of our SQL Practice Set. Here we'll focus on strengthening our JOINing skills with different tables. Before we continue, here's a brief reminder.

You can query more than one table with JOIN. In the ON clause you specify the joining condition.

SELECT first_name, last_name, brand
FROM person
JOIN car
ON person.id = car.owner_id

This query selects the first and last name of the person, and the brand of the car they own. We use the dot (.) notation to reference the columns of specific tables.

If a person doesn't have a car and we want to list them, we can use a LEFT JOIN. It selects ALL rows from the left table, even if there are no corresponding rows in the right table.
Here's an example:

SELECT first_name, last_name, brand
FROM person
LEFT JOIN car
ON person.id = car.owner_id

We can also rename tables in a JOIN query using aliases, like so:

SELECT c1.model as newer, c2.model as older
FROM car c1
JOIN car c2
ON c1.age < c2.age

Here, we joined the car table twice to list new and old models together. Note the use of table aliases in the query. Observe that the JOIN condition is a bit non-standard: it's a comparison instead of an equality. Such JOINs are called non-equi JOINs.

Exercise

Click the Next exercise button to continue.

Console

Code editor

Result

TableConsole