A Short SQL Tutorial with the Aliens Table

1 Introduction

SQL is a powerful language for managing and querying databases. In this beginner tutorial, we will explore the basics of SQL using a sample table of aliens.

The SQL engine we will be using is SQLite, a lightweight and serverless database engine. It is widely used for its simplicity and ease of integration. To get us started coding SQL as quickly as possible, we embedded the SQLite engine in the browser using CodAPI, which allows us to run SQL queries directly in the browser without any server setup. (The core technology behind the scene are WASI and WebAssembly.)

Let’s check the version of SQLite we are using.

SELECT sqlite_version();

We also pre-loaded a data table with some data, the aliens table. Let’s take a look at the table structure.

PRAGMA table_info('aliens');

Different SQL engines have different flavours of SQL. For example, the PRAGMA command we justed used is specific to SQLite. However, the basic SQL syntax is quite similar across different engines. In this tutorial, we will focus on the most common SQL commands that are widely supported.

2 Retrieve

Fetch all records from the table.

SELECT * FROM aliens;

In real world situations, a data table can have many columns and millions of rows. Retrieving the entire table is not practical and most likely not what’s intended. A good practice is to always specify which columns you want to retrieve, and use the LIMIT clause to restrict the number of rows returned.

SELECT id, species, habitat, lifespan, size, weight
FROM aliens
LIMIT 5;

You can also use mathematical expressions in the SELECT statement. For example, suppose the weight column is in kilograms, and you want to convert it to pounds. You can do this by multiplying the weight by 2.20462.

SELECT id, species, weight * 2.20462 AS weight_in_pounds
FROM aliens
LIMIT 5;

3 Filter

Find all Jungle aliens. Display only id, species, and habitat columns.

SELECT id, species, habitat
FROM aliens 
WHERE habitat = 'Jungle';

--- hint: use the AND operator to combine multiple conditions

4 Sort

Sort aliens by lifespan in descending order. Display only id, species, and lifespan columns.

SELECT id, species, lifespan
FROM aliens
ORDER BY lifespan DESC;

--- hint: the default sort order is ascending

--- hint: use the WHERE clause before the ORDER BY clause

5 Aggregate

Find out how many aliens exist in each habitat.

SELECT habitat, count(*) AS count
FROM aliens
GROUP BY habitat;

--- hint: use the GROUP BY clause with multiple columns separated by commas

--- hint: use the avg() function

6 Join

You may want to combine data from multiple tables. For example, suppose we have another table called planets that contains information about the planets where the aliens come from. We can use various JOIN operations to combine the two tables.

Let’s first create the planets table and insert some data. It’s not the focus of this tutorial, so we will not go into details.

CREATE TABLE planets (
    id INTEGER PRIMARY KEY,
    planet_name TEXT NOT NULL,
    dominant_species TEXT
);

INSERT INTO planets (planet_name, dominant_species) VALUES
('Xyphos-3', 'Zentar'),
('Draxis-9', 'Gorlax'),
('Oceara', 'Draknor'),
('Zentara', 'Mentar'),
('Quorania', 'Quorin'),
('Nebulon-5', NULL); -- A planet without a dominant species;

Let’s take a look at the planets table we just created.

SELECT * FROM planets;

6.1 Inner Join

A JOIN or INNER JOIN returns only the records that have matching values in both tables. In this case, we can join the aliens and planets tables on the species column from the aliens table and the dominant_species column from the planets table.

SELECT aliens.species, aliens.habitat, planets.planet_name 
FROM aliens
JOIN planets ON aliens.species = planets.dominant_species;

6.2 Left Join

Now, let’s use a LEFT JOIN to combine the two tables. A LEFT JOIN returns all records from the left table and the matched records from the right table. If there is no match, NULL values are returned for columns from the right table.

For example, take aliens table as the left table and planets table as the right table. We can join them on the species column from the aliens table and the dominant_species column from the planets table. In that way, we find all aliens and their corresponding planets. Note that some aliens may not have a corresponding planet discovered, so we will get NULL values for those aliens.

SELECT aliens.species, aliens.habitat, planets.planet_name 
FROM aliens
LEFT JOIN planets ON aliens.species = planets.dominant_species;

7 Conclusion

In this tutorial, we covered the basics of SQL using a sample aliens table. We learned how to retrieve, filter, sort, and aggregate data, as well as how to join multiple tables. These are fundamental skills that will help you work with databases effectively.