Free SQL course

Six short topics that take you from your first query to joining two tables. Everything runs in the box on this page, in your own browser. There is no account to make, nothing to install and no fee.

This is a free course and not a qualification. It carries no certificate and no accreditation. If you want a regulated qualification afterwards, our data and analytics courses lead to one.

Practice box

Loading the database.

or press Ctrl and Enter

The data is a small parts supplier called Selby Vale, with five sites. You have three tables. orders has 301 rows, products has 7 and baskets has 781.

  1. What a database is and what SQL does
  2. Choosing columns and rows
  3. Sorting and limiting results
  4. Counting and grouping
  5. Joining two tables
  6. Messy data, and checking your answer

1 What a database is and what SQL does

A database keeps information in tables.

SQL is the language you use to ask a database questions.

In this topic you learn what tables, rows and columns are.

You also run your first question against real data.

Words to know

Database
A store of information held in tables.
Table
A set of rows and columns about one kind of thing.
Row
One record in a table, such as one order.
Column
One field in a table, such as the order date.
SQL
The language used to ask a database for data.
Query
One question written in SQL.
Result
The rows a query gives back.
Field name
The name at the top of a column.

By the end of this topic you can

  • say what a database is (1.1)
  • name the parts of a table (1.2)
  • say what SQL is used for (1.3)
  • run a simple query and read the result (1.4)

What a database is

What it means. A database is a store of information held in tables.

A shop keeps orders, products and customers.

Each of those is a separate table.

Keeping them apart stops the same fact being typed twice.

Selby Vale keeps three tables

  • orders, one row for each line a customer bought
  • products, one row for each part the firm sells
  • baskets, one row for each item put in a basket online

The orders table has 301 rows.

The products table has only 7.

A table can be small and still be useful.

Example

Selby Vale sells parts from five sites.

Every sale is written as a row in the orders table.

Nobody retypes the price, because it is already in products.

Check: Why keep products in its own table?

So each product is described once. The orders table can then point at it.

Rows and columns

What it means. A row is one record, and a column is one field of every record.

Read a table like a spreadsheet.

Across the top are the column names.

Down the side are the rows.

The orders table has these columns

  • order_id, the reference for the order
  • order_date, the day it was placed
  • site, the branch that sold it
  • product, what was bought
  • quantity, how many were bought
  • status, whether it was despatched, pending or returned

Each row uses every column.

A column holds the same kind of value in every row.

That is what lets you ask questions about all 301 rows at once.

Example

One row reads ORD-4001, 2026-04-01, Northgate, Control board, 12.

That is one order, for 12 control boards, from the Northgate site.

The next row is a different order with the same columns.

Check: In the orders table, is site a row or a column?

A column. Every order has a site, and site holds the same kind of value each time.

What SQL is for

What it means. SQL is the language you use to ask a database for data.

You write a question and the database answers it.

The answer comes back as rows.

You do not have to open the data or scroll through it.

SQL lets you

  • pick the columns you want to see
  • keep only the rows that match a rule
  • put the rows in an order
  • count rows and add numbers up
  • pull two tables together

The same query works on 301 rows or 3 million.

That is why analysts use SQL and not a mouse.

You write the question once and run it whenever you like.

Example

A manager asks how many orders came from Kirkburn.

Counting by hand takes an hour and goes wrong.

One line of SQL answers it in a moment, the same way every time.

Check: Give one thing SQL can do that scrolling cannot.

It can answer the same question again on new data, with no extra work.

Your first query

What it means. SELECT names the columns you want and FROM names the table.

Every query starts with SELECT.

A star means every column.

Type the query in the practice box and press Run.

Read this query in three parts

  • SELECT star, show me every column
  • FROM orders, take it from the orders table
  • LIMIT 5, stop after five rows

The result is five rows of the orders table.

LIMIT keeps the screen readable while you learn.

Without LIMIT you would get all 301 rows.

Example

Run SELECT * FROM orders LIMIT 5 in the practice box.

You see five orders with all eleven columns.

The first is ORD-4001 from Northgate.

Check: What does SELECT * FROM products do?

It shows every column of every row in the products table. That is 7 rows.

Watch out

A query needs a table name. SELECT * on its own fails, because the database does not know where to look.

SQL does not care about capital letters in keywords. select and SELECT both work, but we write keywords in capitals so they stand out.

What you have learned

  • A database stores information in tables. (1.1)
  • A row is one record and a column is one field. (1.2)
  • SQL asks a database for data. (1.3)
  • SELECT and FROM give you your first result. (1.4)

2 Choosing columns and rows

Most of the time you do not want every column.

You also want only some of the rows.

In this topic you choose columns by name.

You then cut the rows down with a rule.

Words to know

SELECT
The word that names the columns you want.
FROM
The word that names the table.
WHERE
The word that keeps only rows matching a rule.
Condition
A rule a row must pass to be shown.
Operator
A symbol that compares two values, such as the equals sign.
AND
Keeps a row only when both rules are true.
OR
Keeps a row when either rule is true.
Text value
A value in quotes, such as a site name.

By the end of this topic you can

  • choose the columns you want by name (2.1)
  • keep only the rows you want with WHERE (2.2)
  • combine two rules with AND and OR (2.3)
  • compare text and numbers correctly (2.4)

Choosing columns by name

What it means. You list the columns you want after SELECT, separated by commas.

A star gives every column.

Naming columns gives a shorter, clearer result.

The columns appear in the order you type them.

Compare these two queries

  • SELECT * FROM orders gives all eleven columns
  • SELECT site, product FROM orders gives two
  • SELECT product, site FROM orders gives the same two, swapped

Ask for what you need and no more.

A narrow result is easier to read.

It is also faster on a large table.

Example

A manager wants a list of what each site sold.

SELECT site, product, quantity FROM orders gives exactly that.

The other eight columns stay out of the way.

Check: How do you ask for just order_id and status?

SELECT order_id, status FROM orders. Put a comma between the two names.

Keeping only some rows

What it means. WHERE keeps only the rows that pass a rule.

WHERE comes after FROM.

The rule is tested against every row.

Rows that fail it are left out.

Useful operators

  • = is equal to
  • <> is not equal to
  • > is greater than
  • < is less than
  • >= is greater than or equal to

The rule can use any column.

You do not have to show the column you filter on.

The database tests all 301 rows for you.

Example

SELECT * FROM orders WHERE site = 'Kirkburn' shows Kirkburn orders only.

SELECT * FROM orders WHERE quantity > 20 shows the large orders.

Both run against the same 301 rows.

Check: Write a query for orders with a status of returned.

SELECT * FROM orders WHERE status = 'returned'.

Using two rules at once

What it means. AND needs both rules to be true, and OR needs only one.

Put AND or OR between the two rules.

AND narrows the result.

OR widens it.

Three ways to combine rules

  • WHERE site = 'Eastgate' AND status = 'pending'
  • WHERE status = 'returned' OR status = 'pending'
  • WHERE quantity > 10 AND site = 'Northgate'

AND and OR behave differently, so choose with care.

Use brackets when you mix them.

Brackets make the order of the rules plain.

Example

A manager wants pending orders at Eastgate.

Both things must be true, so the word is AND.

Using OR would return every pending order anywhere.

Check: You want orders that are pending or returned. AND or OR?

OR. A row only needs to match one of the two statuses.

Text and numbers behave differently

What it means. Text goes in single quotes and numbers do not.

Quotes tell the database the value is text.

A number without quotes can be compared with greater than.

Text in quotes must match exactly.

Watch the quotes

  • site = 'Kirkburn' is right
  • site = Kirkburn fails, because Kirkburn looks like a column name
  • quantity > 20 is right
  • quantity > '20' works but is poor practice

Exact matching matters for text.

A trailing space makes a value different.

You meet that problem in topic 6.

Example

SELECT * FROM orders WHERE site = 'Selby Vale' misses some rows.

The table also holds selby vale and Selby Vale with a space.

Those rows do not match, because the text is not identical.

Check: Why does site = Kirkburn fail?

Without quotes the database looks for a column called Kirkburn and finds none.

Watch out

WHERE goes after FROM, not before it. The order of the words in a query is fixed.

One equals sign compares in SQL. Some languages use two, but SQL uses one.

What you have learned

  • SELECT names the columns you want. (2.1)
  • WHERE keeps only the rows that pass a rule. (2.2)
  • AND narrows a result and OR widens it. (2.3)
  • Text needs quotes and numbers do not. (2.4)

3 Sorting and limiting results

A result comes back in no particular order.

You usually want the biggest or the newest first.

In this topic you sort a result.

You also cut it down to the top few rows.

Words to know

ORDER BY
The words that sort a result.
Ascending
Smallest first, or A before Z.
Descending
Largest first, or Z before A.
ASC
The short word for ascending.
DESC
The short word for descending.
LIMIT
The word that stops the result after a set number of rows.
Sort key
The column a result is sorted on.

By the end of this topic you can

  • sort a result by a column (3.1)
  • choose the direction of the sort (3.2)
  • show only the top rows (3.3)

Sorting a result

What it means. ORDER BY sorts the result on a column you name.

ORDER BY goes at the end of the query.

You name the column to sort on.

That column is the sort key.

You can sort on any column

  • ORDER BY order_date puts the oldest first
  • ORDER BY line_total puts the smallest value first
  • ORDER BY site puts the sites in alphabetical order

Without ORDER BY the order is not promised.

It may look sorted and then change.

If order matters, say so in the query.

Example

SELECT site, line_total FROM orders ORDER BY line_total.

The smallest order value comes first.

The largest is at the bottom of 301 rows.

Check: Where does ORDER BY go in a query?

At the end, after FROM and after any WHERE.

Choosing the direction

What it means. DESC sorts largest first and ASC sorts smallest first.

Ascending is the default.

Add DESC when you want the largest first.

Put the word after the column name.

The two directions

  • ORDER BY line_total ASC, smallest first
  • ORDER BY line_total DESC, largest first
  • ORDER BY order_date DESC, newest first

Most business questions want DESC.

Managers ask for the best and the worst.

Both are a sort with a direction.

Example

A manager asks which orders were the biggest.

SELECT order_id, line_total FROM orders ORDER BY line_total DESC.

The largest order value is now the first row.

Check: How do you put the newest order first?

ORDER BY order_date DESC. DESC puts the latest date at the top.

Showing only the top rows

What it means. LIMIT stops the result after the number of rows you give.

LIMIT goes last of all.

It is useful with a sort.

Together they answer top ten questions.

LIMIT in use

  • LIMIT 5 shows five rows
  • ORDER BY line_total DESC LIMIT 10 shows the ten biggest orders
  • LIMIT on its own just trims the result

LIMIT without ORDER BY is not a top ten.

It is simply the first rows the database happens to return.

Always sort before you limit.

Example

A manager wants the ten biggest orders.

SELECT order_id, line_total FROM orders ORDER BY line_total DESC LIMIT 10.

Ten rows come back, largest first.

Check: Why is LIMIT 10 on its own not a top ten?

Because nothing has been sorted. You get ten rows, but not the biggest ten.

Watch out

LIMIT without ORDER BY looks like an answer and is not one. The rows are whatever the database returned first.

ORDER BY sorts text differently from numbers. Text sorted ascending runs A to Z, so 10 can come before 9.

What you have learned

  • ORDER BY sorts a result on a column. (3.1)
  • DESC gives largest first and ASC gives smallest first. (3.2)
  • LIMIT shows only the top rows. (3.3)

4 Counting and grouping

Managers rarely want a list of rows.

They want a count or a total.

In this topic you count rows and add numbers up.

You then produce one line per site.

Words to know

COUNT
A function that counts rows.
SUM
A function that adds a number column up.
AVG
A function that gives the mean of a number column.
Function
A built in piece of SQL that works out a value.
GROUP BY
The words that give one result line per value.
Aggregate
A single value worked out from many rows.
Alias
A friendlier name you give a result column.
HAVING
The word that filters groups after they are made.

By the end of this topic you can

  • count the rows that match a rule (4.1)
  • add up and average a number column (4.2)
  • give one result line per group (4.3)
  • filter the groups you have made (4.4)

Counting rows

What it means. COUNT gives the number of rows in a result.

Put COUNT in place of a column name.

The brackets hold a star or a column.

You get one row back, not many.

Counting in practice

  • SELECT COUNT(*) FROM orders gives 301
  • SELECT COUNT(*) FROM products gives 7
  • SELECT COUNT(*) FROM orders WHERE status = 'returned' counts returns only

COUNT works with WHERE.

The rule is applied first and the rows left are counted.

That answers how many questions directly.

Example

A manager asks how many orders are still pending.

SELECT COUNT(*) FROM orders WHERE status = 'pending'.

One number comes back, and it is right every time.

Check: What does SELECT COUNT(*) FROM baskets give?

The number of rows in the baskets table, which is 781.

Adding up and averaging

What it means. SUM adds a number column up and AVG gives its mean.

Both take a column name in brackets.

Both need the column to hold numbers.

Both give one value back.

Three number functions

  • SUM(line_total) adds the order values
  • AVG(unit_price) gives the mean price
  • COUNT(*) counts the rows behind those numbers

Always show the count beside an average.

An average of three rows is not the same as an average of 300.

The count tells the reader how much to trust it.

Example

SELECT SUM(line_total) FROM orders WHERE site = 'Eastgate'.

That gives the total value Eastgate sold.

Add COUNT(*) to see how many orders made it up.

Check: Why show a count next to an average?

Because an average from very few rows can mislead. The count shows its weight.

One line per group

What it means. GROUP BY gives one result line for each value in a column.

Name the column in GROUP BY.

Put the same column in SELECT.

Add a function such as COUNT or SUM.

A grouped query has three parts

  • the column you group on, such as site
  • the function you want, such as COUNT(*)
  • the words GROUP BY site at the end

You get one row per site, not 301 rows.

That is the shape a manager wants.

It is the most used pattern in business SQL.

Example

SELECT site, COUNT(*) FROM orders GROUP BY site.

You get one line for each site with its number of orders.

Selby Vale appears more than once, which topic 6 explains.

Check: What do you get from SELECT status, COUNT(*) FROM orders GROUP BY status?

Three lines, one each for despatched, pending and returned, with a count.

Filtering the groups

What it means. HAVING keeps only the groups that pass a rule.

WHERE filters rows before grouping.

HAVING filters groups after grouping.

They are not the same word.

Which word to use

  • WHERE status = 'despatched' cuts rows first
  • HAVING COUNT(*) > 50 cuts groups afterwards
  • a query can use both, in that order

You cannot use COUNT in WHERE.

The count does not exist until the groups are made.

That is the reason HAVING exists.

Example

A manager wants sites with more than 50 orders.

SELECT site, COUNT(*) FROM orders GROUP BY site HAVING COUNT(*) > 50.

Only the busier sites come back.

Check: Why can WHERE not test COUNT(*)?

Because WHERE runs before the rows are grouped, so no count exists yet.

Watch out

A column in SELECT must be in GROUP BY or inside a function. Otherwise the database cannot say which row's value to show.

COUNT(*) counts rows, not products. If one product appears on 40 orders, it is counted 40 times.

What you have learned

  • COUNT gives the number of rows. (4.1)
  • SUM adds a column up and AVG gives its mean. (4.2)
  • GROUP BY gives one line per value. (4.3)
  • HAVING filters the groups you have made. (4.4)

5 Joining two tables

One table rarely holds everything you need.

The category of a product sits in a second table.

In this topic you pull two tables together.

You learn the column that links them.

Words to know

Join
Pulling two tables together into one result.
Key
A column used to match rows across tables.
Primary key
A column whose value is different in every row.
Foreign key
A column that points at a key in another table.
INNER JOIN
A join that keeps only rows found in both tables.
ON
The word that says which columns must match.
Table prefix
The table name written before a column name.

By the end of this topic you can

  • say why data is split across tables (5.1)
  • find the column that links two tables (5.2)
  • write a join and read its result (5.3)

Why data is split up

What it means. Each kind of thing gets its own table, so a fact is stored once.

The orders table records what was sold.

The products table describes each part.

The category of a part belongs with the part, not with every sale.

What each table holds

  • orders holds 301 sales lines
  • products holds 7 parts and their category
  • the part code appears in both

Storing the category once keeps it correct.

Change it in products and every order sees the change.

Copying it into all 301 orders would create 301 chances to be wrong.

Example

A pump seal is a spare.

That fact is written once, in the products table.

The 55 orders for pump seals do not repeat it.

Check: Why not put the category straight into the orders table?

It would be stored many times. Each copy is a chance for it to disagree.

The column that links them

What it means. A key is a column used to match rows across two tables.

In products, product_code is different in every row.

That makes it the primary key.

In orders the same column points back at it.

How the link works

  • products.product_code is the primary key, such as P-1002
  • orders.product_code is the foreign key
  • matching them joins the sale to the part

The two columns hold the same kind of value.

They do not have to share a name, but here they do.

Find this pair before you write any join.

Example

Order ORD-4003 has product_code P-1002.

In products, P-1002 is a pump seal in the spares category.

The code is what carries you from one table to the other.

Check: Which column links orders to products?

product_code. It is the primary key in products and the foreign key in orders.

Writing the join

What it means. INNER JOIN names the second table and ON says which columns must match.

Start as usual with SELECT and FROM.

Add INNER JOIN and the second table.

Add ON and the two columns that must be equal.

Read a join in four parts

  • FROM orders, the first table
  • INNER JOIN products, the second table
  • ON orders.product_code = products.product_code, the matching rule
  • SELECT orders.product, products.category, the columns you want

Put the table name before a column that exists in both.

Without it the database cannot tell which one you mean.

An inner join keeps only rows that match in both tables.

Example

A manager wants sales grouped by category, not by part.

Join orders to products on product_code.

You can then group the result by products.category.

Check: What does ON do in a join?

It says which two columns must be equal for rows to be put together.

Watch out

A join with no ON can return a huge result. Every row is paired with every other row, which is almost never what you want.

An inner join drops rows that do not match. An order with a code missing from products will vanish from the result.

What you have learned

  • Data is split so each fact is stored once. (5.1)
  • A key column links two tables. (5.2)
  • INNER JOIN and ON pull two tables together. (5.3)

6 Messy data, and checking your answer

Real data is rarely tidy.

The same site can be typed four different ways.

In this topic you find and fix messy text.

You also learn how a join can double your totals.

Words to know

Messy data
Data where the same thing is written in different ways.
Trailing space
A space left at the end of a value.
TRIM
A function that removes spaces from both ends of text.
LOWER
A function that puts text into small letters.
DISTINCT
The word that removes repeated values from a result.
Double counting
Adding the same value more than once by mistake.
Grain
What one row of a table represents.
Reconcile
To check a figure against a second, independent source.

By the end of this topic you can

  • spot values that look the same but are not (6.1)
  • tidy text with TRIM and LOWER (6.2)
  • explain how a join can double a total (6.3)
  • check an answer before you trust it (6.4)

When the same thing is typed four ways

What it means. Two values only match when the text is identical.

A capital letter makes a value different.

So does an extra space.

The database does not know they mean the same site.

The site column holds all of these

  • Selby Vale, the version you expect
  • selby vale, typed in small letters
  • Selby Vale, with two spaces in the middle
  • Selby Vale followed by a space

Group by site and Selby Vale appears four times.

The largest line shows 58 when the true figure is 61.

A manager reading only that line undercounts the site.

Example

SELECT DISTINCT site FROM orders lists every spelling.

Run it first on any new table.

It takes a moment and it shows you the problem.

Check: Why does site = 'Selby Vale' miss some rows?

Because rows holding selby vale or a trailing space are not identical text.

Tidying text before you group

What it means. TRIM removes spaces from the ends and LOWER puts text in small letters.

Wrap the column in the function.

Do it in both SELECT and GROUP BY.

The four spellings then become one.

Fixing the site column

  • TRIM(site) removes the leading and trailing spaces
  • LOWER(site) makes the capital letters consistent
  • LOWER(TRIM(site)) does both at once

This fixes the result, not the table.

The messy values are still stored underneath.

The real fix is to stop them being typed in the first place.

Example

SELECT LOWER(TRIM(site)), COUNT(*) FROM orders GROUP BY LOWER(TRIM(site)).

Three of the four spellings merge into one line of 60.

The row with two spaces stays apart, so 61 is not reached.

Check: Does TRIM change the data in the table?

No. It changes only what the query returns. The stored value is unchanged.

How a join can double a total

What it means. Joining to a table with more than one matching row repeats the first table's rows.

The baskets table has 781 rows.

Many baskets hold the same product code.

Join orders to baskets and each order appears many times.

What goes wrong

  • one order joins to every basket holding that product
  • 301 order rows become 40,701 joined rows
  • SUM(line_total) then counts each order many times over

The total is wrong by a large multiple.

A figure that is suspiciously round is a warning.

Check the grain of each table before you join.

Example

Eastgate's order value in the orders table is 14,563 pounds.

Join orders to baskets and the same figure reads 1,989,762.

Nothing errored, and the number is simply wrong.

Check: What should you check before writing a join?

The grain of each table, meaning what one row represents.

Checking before you trust it

What it means. A figure is not finished until you have checked it a second way.

Compare your answer with something independent.

Check the row count as well as the total.

Ask whether the size of the number is believable.

Four quick checks

  • run SELECT COUNT(*) before and after a join
  • run SELECT DISTINCT on any column you group by
  • add the parts up and see if they make the whole
  • compare the total against a figure from somewhere else

These take seconds.

They catch the errors that do not raise an error message.

Those are the ones that reach a manager.

Example

Your join says Eastgate sold 1,989,762 pounds.

The orders table on its own says 14,563.

The rows multiplied, so the join is repeating them.

Check: Your total grew many times over after a join. What is the likely cause?

The second table holds several matching rows per order, so rows repeated.

Watch out

A query that runs is not a query that is right. SQL reports broken syntax, never a wrong question.

Fixing text in a query does not fix the table. The next person to run a different query meets the same mess.

What you have learned

  • Values only match when the text is identical. (6.1)
  • TRIM and LOWER tidy text for a query. (6.2)
  • A join can repeat rows and double a total. (6.3)
  • Check a figure a second way before you trust it. (6.4)

Where to go next

You can now read a table, filter it, sort it, count it and join it.

If you want to take this further as a regulated qualification, look at our data and analytics courses, or see the whole catalogue.