CASE

We want to set the category columns value to 'Senior', in case the age
value is greater than 65, 'Adult' in case it's in the range of 25 to 64,
and 'Youth', if it's under 25.
This is done using the CASE statement.
Here is one condition:
CODE PLAYGROUNDSQL
SELECT firstname, lastname,
CASE
WHEN age >= 65 THEN 'Senior'
END AS category
FROM Customers
Click to run
As you can see, the CASE statement includes the condition in
the WHEN clause and sets the value using the THEN keyword.

The CASE statement has to close with the END keyword.

We can add multiple conditions using multiple WHEN clauses:

Here is the second condition:
CODE PLAYGROUNDSQL
SELECT firstname, lastname,
CASE
WHEN age >= 65 THEN 'Senior'
WHEN age >= 25 AND age < 65 THEN 'Adult'
END AS category
FROM Customers
Click to run

The first condition that gets satisfied is used to set the value.

For all other cases, we can set a value using the ELSE keyword:
CODE PLAYGROUNDSQL
SELECT firstname, lastname,
CASE
WHEN age >= 65 THEN 'Senior'
WHEN age >= 25 AND age < 65 THEN 'Adult'
ELSE 'Youth'
END AS category
FROM Customers
Click to run
Do not forget the END keyword.
Run the code to see the result.

Lesson Takeaways
You are almost done with the first module!
Here is a summary of the lesson:
- The CASE statement is used to set a value for a column based on
conditions.
- The conditions are set in WHEN clauses.
- The first WHEN clause that satisfies the condition is set as the value.
- The CASE statement should close with the END keyword.

Taxes
You are working on the Employees table, which stores the names and
salaries of employees.
You need to calculate the taxes for the salaries and output them as a new
column.
The tax percentage is based on the salary amount:
 0 - 1500: 10%
 1501 - 2000: 20%
 2001+: 30%
Output the firstname, lastname, salary and tax columns of the table,
sorted by the lastname column in ascending order.

Hint: To calculate the percentage of a number, simply multiply it by the

percentage divided by 100. For example, to get 10%, multiply the number
by 0.1.

Identity
Often, the id is an integer (a whole number) which is incremented with
each new row.
SQL allows you to create a column that gets automatically incremented
with each new row.
That is done using the AUTO_INCREMENT keyword.

When a column is set as AUTO_INCREMENT, the value is automatically

set when new rows are inserted, without the need for us to specify it.
Here is an example for our Customers table:
CREATE TABLE Customers (
id int NOT NULL AUTO_INCREMENT,
firstname varchar(255),
lastname varchar(255)
);
SQLCopy

As you can see, we simply specify the id column to

be AUTO_INCREMENT when creating the table.

Now when inserting a new row, we do not need to specify the value of the
id column, as it will automatically be set.
For example:
CODE PLAYGROUNDSQL
INSERT INTO Customers (firstname, lastname, city, age)
VALUES
('demo', 'demo', 'Paris', 52),
('test', 'test', 'London', 21);
Click to run

Run the code to see the result.

By default, the AUTO_INCREMENT column starts with the value 1.

This can be changed if needed, using the following:
ALTER TABLE Customers
AUTO_INCREMENT=555
SQLCopy
Now, when a new row is inserted, the id column will start from the given
value:
CODE PLAYGROUNDSQL
INSERT INTO Customers (firstname, lastname, city, age)
VALUES
('test', 'test', 'London', 21)
Click to run

Lesson Takeaways
Great progress!
Now you know that most of the tables have an identity column, often
named id.
This column is a number, which is automatically incremented with each
new row.
The identity column can be created using
the AUTO_INCREMENT keyword, defined next to the column, when
creating the table.
You will learn about keys in the next lesson!

Adding Data
There are new employees that need to be added to the Employees table.
Here is their data:
Firstname: Wang
Lastname: Lee
Salary: 1900
Firstname: Greta
Lastname: Wu
Salary: 1200
The Employees table has an identity column called id, which is set
to AUTO_INCREMENT.
Add the data to the table, then select the id, firstname,
lastname and salary columns sorted by the id column in descending
order.

Primary Key
Before looking at how the data will look in these tables, let's first create
the relationship between them using keys!
The primary key constraint is used to uniquely identify rows of a table.
In most cases, the primary key is the auto_increment column.
So, for our Customers and PhoneNumbers tables, it's the id column.
It is set when creating the table:
CREATE TABLE Customers (
id int NOT NULL AUTO_INCREMENT,
firstname varchar(255),
lastname varchar(255),
PRIMARY KEY (id)
);
SQLCopy

Here are some rules for primary keys:

- A primary key must contain unique values.
- A primary key column cannot have NULL values.
- A table can have only one primary key.

This is why the AUTO_INCREMENT column is a good fit, as it satisfies

these conditions.
Foreign Key
Another type of constraint is the Foreign Key.
A Foreign Key is a column in one table that refers to the Primary Key in
another table.

This constraint is used to prevent actions that would destroy links

between tables.

In our case, the customer_id column in the PhoneNumbers table is the

foreign key, which refers to the primary key id in the Customers table.
CREATE TABLE PhoneNumbers (
id int NOT NULL AUTO_INCREMENT,
customer_id int NOT NULL,
number varchar(55),
type varchar(55),
PRIMARY KEY (id),
FOREIGN KEY (customer_id) REFERENCES Customers(id)
);
SQLCopy

The Foreign Key constraint prevents invalid data from being inserted into
the foreign key column, because it has to be one of the values contained
in the linked table.

Keys
Here is how some example data in
the Customers and PhoneNumbers table would look:
Customers:

PhoneNumbers:
This is how relationships between tables are created. The foreign key
column is referencing the primary key column of another table, thus
linking the data in these tables.
This way, a customer can have any number of phone numbers associated
with them.

You can have tables referencing multiple other tables in a database.

Lesson Takeaways
Awesome! Now you know how to create keys in tables, linking the data.
The primary key is used to uniquely identify each row of a table. It is
usually the identity column.
The foreign key is used to reference an identity column in another table.
This allows you to link the data between multiple tables and prevent
actions that would break the relationship.
Learn how to make data in a column unique in the next lesson!

Unique
The UNIQUE constraint ensures that all values in a column are different.
A PRIMARY KEY constraint automatically has a UNIQUE constraint.

However, you can have many UNIQUE constraints per table, but only
one PRIMARY KEY constraint per table.

Let's make the lastname column of our Customers unique:

ALTER TABLE Customers
ADD UNIQUE (lastname)
SQLCopy

You can make multiple columns UNIQUE.

Now when we try to insert a Customer with a lastname that is already

present in the table, we will get an error:
CODE PLAYGROUNDSQL
INSERT INTO Customers (firstname, lastname, city, age)
VALUES
('demo', 'Anderson', 'London', 24)
Click to run

NULL values are ignored by UNIQUE, meaning you can have

multiple NULL values in a UNIQUE column.

Summary
Let's summarze what we have learned about keys:
The Primary key uniquely identifies each record of a table. It is usually
set as an auto increment integer.
Foreign keys are used to create relationships between tables. They refer
to the primary key in other tables.
A table can have multiple foreign keys, but only one single primary key.
The UNIQUE constraint is used to make values in a column unique.

In the next module we will learn how to select data from multiple tables
and perform calculations on linked data.

Multiple Tables
Often, data is stored in multiple linked tables.
As an example, consider our Customers and PhoneNumbers tables.
The Customers table includes information about customers, while
the PhoneNumbers table contains the phone numbers of the customers.

Real-life databases can store millions of records.

SQL enables you to work with multiple tables using a single query.
For example, let's say we want to select the phone numbers of the
customers in our Customers table that have a certain type and a certain
age.

This means we need to have conditions on both tables and select the
linked data.
Let's practice some grouping!
Drag & drop to group the Houses table by the city column and calculate
the average price of the houses for each city.
SELECT city, AVG (price) From Houses Group BY city

Here is an example of the result that we want to get with our query:

As you can see, the first columns are from the Customers table, while the
next ones are from the PhoneNumbers table.

We can select data from multiple tables by comma separating them in

a SELECT statement:
CODE PLAYGROUNDSQL
SELECT firstname, lastname, city, number, type
FROM Customers, PhoneNumbers
WHERE [Link] = PhoneNumbers.customer_id
Click to run
Note the WHERE condition: it tells SQL to combine only those rows that
have the corresponding customer_id.

Without it we would get all possible variants of the first table linked with
the second table.

When working with multiple tables, it's common practice to define the
columns by their full name – the table name, followed by a dot and the
column name.
For example: [Link] is the id column of the Customers table,
while [Link] is the id column of the PhoneNumbers table.
This makes the code more readable and avoids mistakes, when both
tables have a column with the same name.

So, here is what our query would look like with the full column names:
CODE PLAYGROUNDSQL
SELECT [Link], [Link], [Link],
[Link], [Link]
FROM Customers, PhoneNumbers
WHERE [Link] = PhoneNumbers.customer_id
Click to run

Lesson Takeaways
Selecting data from multiple tables is easy!
Just separate their names in the SELECT statement with a comma and
specify the condition for the linked columns.
In the next lesson we will learn a better and cleaner way to combine data
in multiple tables.

Books and Authors

You are working with a library database that stores data on books.
The Books table has the columns id, name, year, author_id.
The author_id column connects to the Authors table, which stores
the id, name columns for the book authors.
You need to select all the books with their authors, ordered by the author
name alphabetically, then by the year in ascending order.
The result set should contain only 3 columns: the book name, year and
its author (name the column author).

Use the full column names, as both tables have a column called name.
Answer:
SELECT [Link], [Link], [Link]
As author FROM Books, Authors WHERE Books.author_id = [Link]
order by author, year asc;

JOINS
A better way of combining data is the JOIN clause.
It allows you to combine multiple tables based on a condition.
For example:
CODE PLAYGROUNDSQL
SELECT firstname, lastname, city, number, type
FROM Customers JOIN PhoneNumbers
ON [Link] = PhoneNumbers.customer_id
Click to run

We JOIN the specified tables based ON the condition.

The image below demonstrates how JOIN works:

Only the records matching the join condition are returned.

Alias
Because you use the full column names when joining tables, the query can
get really long.
To make it easier and shorter, we can provide nicknames for our tables:
CODE PLAYGROUNDSQL
SELECT [Link], [Link], [Link], [Link], [Link]
FROM Customers AS C JOIN PhoneNumbers AS PN
ON [Link] = PN.customer_id
Click to run

After giving the nicknames (also called table aliases), we can use them in
the query, both in the select list and in the condition.

LEFT JOIN
Another type of JOIN is the LEFT JOIN.
The LEFT JOIN returns all rows from the left table (first table), even if
there are no matches in the right table (second table).
This means that if there are no matches for the ON clause in the table on
the right, the join will still return the rows from the first table in the result.
The image below demonstrates how LEFT JOIN works:

For example, in our case, the Customers table includes customers that
do not have any records in the PhoneNumbers table:
CODE PLAYGROUNDSQL
SELECT [Link], [Link], [Link], [Link], [Link]
FROM Customers AS C LEFT JOIN PhoneNumbers AS PN
ON [Link] = PN.customer_id
Click to run
The result set contains all the rows from the left table and matching data
from the right table.

If no match is found for a particular row, NULL is returned for the columns
of the right table.

3
13 Comments
The table A contains 3 rows in the id column with the values 1, 2, 3. The B
table has an id column containing 3 rows with the values 1, 2, 1.
How many rows will the following query return?
SELECT [Link], [Link] FROM
A LEFT JOIN B
ON [Link] = [Link]
SQLCopy
3
6
2
4
RIGHT JOIN
Similarly, the RIGHT JOIN returns all the rows from the right table, even if
there are no matches in the left table.
For example, we could rewrite the previous query this way:
CODE PLAYGROUNDSQL
SELECT [Link], [Link], [Link], [Link], [Link]
FROM PhoneNumbers AS PN RIGHT JOIN Customers AS C
ON [Link] = PN.customer_id
ORDER BY [Link]
Click to run
We also sorted the results by the id column.

You can also use WHERE conditions or any other clause as you would with
a simple SELECT query.

Lesson Takeaways
Awesome!
Joins allow you to combine data from multiple tables based on conditions.
The LEFT JOIN returns all rows from the left table (first table), even if there
are no matches in the right table (second table).
Similarly, RIGHT JOIN returns all the rows from the right table, even if
there are no matches in the left table.
You will learn how to combine results of SELECT statements into one
single data set in the next lesson, so stay tuned!

Number of Books
You are working on the library database, which contains the Books and
Authors tables.
Columns of the Books table: id, name, year, author_id.
Columns of the Authors table: id, name.

Write a query to get the author names and the number of books they
have in the Books table.
Note that some authors do not have any books associated with them. In
this case, the result needs to include their names and have 0 as the
count. The count column should be called books in the result.

Sort the result by the number of books, from highest to lowest.

Answer:
SELECT [Link], count ([Link]) As books
FROM Books As B RIGHT JOIN Authors As A
ON B.author_id = [Link]
Group BY [Link]
ORDER BY books Desc;

UNION
Occasionally, you might need to combine data from multiple similar tables
into one comprehensive dataset.
For example, you might have multiple tables storing Customers data and
you want to combine them into one result set.

This can be done using the UNION statement.

The UNION operator is used to combine the result-sets of two or more

SELECT statements.
Consider having a Customers and Contacts tables, both
having firstname, lastname and age columns:
CODE PLAYGROUNDSQL
SELECT firstname, lastname, age FROM Customers
UNION
SELECT firstname, lastname, age FROM Contacts
Click to run
All SELECT statements within the UNION must have the same number of
columns. The columns must also have the same data types. Also, the
columns in each SELECT statement must be in the same order.

UNION removes the duplicate records.

UNION ALL
UNION ALL is similar to UNION, but does not remove the duplicates:
CODE PLAYGROUNDSQL
SELECT firstname, lastname, age FROM Customers
UNION ALL
SELECT firstname, lastname, age FROM Contacts
Click to run

Run the code to see the result.

The table called 'A' contains 4 rows.

How many records will the following query return?
SELECT * FROM A
UNION
SELECT * FROM A
SQLCopy
8
none
4

Remember, the SELECT statements need to have the same columns for
the UNION to work. In case one of the tables has extra columns that we
need to select, we can simply add them to the second select as NULL:
CODE PLAYGROUNDSQL
SELECT firstname, lastname, age, city FROM Customers
UNION
SELECT firstname, lastname, age, NULL FROM Contacts
Click to run
Here, the Customers table has an extra city column.

We can also use other constant values for the extra columns. Just
remember, that the value has to have the same data type as the column
of the first table.

We can also set conditions for each select in the UNION.

For example:
CODE PLAYGROUNDSQL
SELECT firstname, lastname, age FROM Customers
WHERE age > 30
UNION
SELECT firstname, lastname, age FROM Contacts
WHERE age < 25
Click to run

Each SELECT statement can have its specific conditions.

Lesson Takeaways
Great progress!
To summarize this lesson:
- UNION allows you to combine records from
multiple SELECT statements into one dataset.
 - For UNION to work, each SELECT statement needs to have the same
number of columns and matching data types.
- UNION removes duplicate records, while UNION ALL does not remove
them.
- Each SELECT statement in a UNION can have its own conditions.
Next you will learn how to solve a real-life SQL challenge!

New Arrivals
You are working with the library books database.
The Books table has the columns id, name, year.
The library has new books whose information is stored in another table
called "New", however they do not have a year column.
Write a query to select the books from both tables, Books and New,
combining their data. For the year column of the New books use the value
2022.
Also, select only the books that are released after the year 1900.
The result set should contain the name and year columns only, ordered
by the name column alphabetically.

Answer;
SELECT name, year from books
WHERE year > 1900
UNION SELECT name, 2022 from New
ORDER BY name Asc;

Find the Average

In this lesson we will learn how to solve a slightly more complex problem -
we need to find the average number of phone numbers the Customers in
our table have.
Here is the data of our Customers and PhoneNumbers tables:
CODE PLAYGROUNDSQL
SELECT * FROM Customers;

SELECT * FROM PhoneNumbers;

Click to run
Each customer has 0, 1, or multiple phone numbers.

The data is for demonstration only. Real-life tables could store thousands
or millions of rows.

CREATE TABLE

INSERT 0 4
CREATE TABLE

INSERT 0 5

id firstname lastname city age

1 John Smith New York 24

2 David Williams Los Angeles 42

Anderso
3 Chloe Chicago 65
n

4 Emily Adams Houston

(4 rows)

customer_i
id number type
d

1 1 (555) 123456 mobile

2 1 (943)554545 home

3 2 (331) 111111 mobile

4 2 (88) 11 22 33 work

5 2 (999) 00 11 33 emergency

(5 rows)

To calculate the average, we need to find the number of phone numbers

that each customer has, then use the AVG function over that result set.
First, let's join the tables:
CODE PLAYGROUNDSQL
SELECT [Link], [Link], [Link], [Link], [Link]
FROM Customers AS C LEFT JOIN PhoneNumbers AS PN
ON [Link] = PN.customer_id
ORDER BY [Link]
Click to run

We used a LEFT JOIN as not all customers have a phone number.

This is an important point: By simply joining the tables we would get the
wrong result for the average.

Now we can group the data based on our customers and find the number
of phone numbers each of them has:
CODE PLAYGROUNDSQL
SELECT [Link], COUNT([Link]) AS count
FROM Customers AS C LEFT JOIN PhoneNumbers AS PN
ON [Link] = PN.customer_id
GROUP BY [Link]
Click to run
Our custom 'count' column now has the number of phone numbers for
each customer, as we grouped the query by the customer id.

As you can see from the results, the count also includes 0 values: that's
because we used a LEFT JOIN and some of the customers don't have any
matching phone numbers.

Now, we need to find the average of these values.

For that, we need another SELECT query over the data of the join:
CODE PLAYGROUNDSQL
SELECT AVG(count) FROM
(SELECT [Link], COUNT([Link]) AS count
FROM Customers AS C LEFT JOIN PhoneNumbers AS PN
ON [Link] = PN.customer_id
GROUP BY [Link]) AS Numbers
Click to run
We aliased the query as 'Numbers' and selected the average value of
the count column from it.

By enclosing a SELECT query in parentheses, we are able to give it a

name and use it just like a table.
It is also important to give the custom columns name aliases, so you can
select them.
Lesson Takeaways
You learned how to solve a real-life problem!
The key takeaway from this lesson is that you are able to enclose a query
into parentheses and give it a name using the AS keyword. This enables
us to use the query as a table: select from it, use it in JOINS, run
aggregate functions, etc.

Congratulations! You have completed the last lesson of this course.