Introduction to SQL

Training: Introduction to SQL Level: Beginner to intermediate

Table of Contents

1. Introduction

• Introduction
• Before writing any SQL
• Aside: SQL vs. “Sequel”

2. Database Fundamentals

• Why databases exist
• The Evolution of Relational Database Management Systems
• The objective of each database
• Entities and tables
• Rows and columns
• The importance of data types
• NULL values
• Primary Keys
• Foreign Keys and Relationships
• Naming conventions

3. Module 3 — Writing SQL Queries

• The SELECT clause
• Aside: Where to write SQL?
• Filter data with WHERE
• Complex WHERE conditions with AND and OR
• BETWEEN and IN: search for multiple values
• Aside: Is SQL case sensitive?
• Aside: SQL and whitespace
• LIKE: pattern matching
• Use NULL in a SELECT

4. ORDER BY, GROUP BY and aggregate functions

• Sort results with ORDER BY
• Aggregate Functions
• Group data with GROUP BY
• GROUP BY and HAVING
• Remove duplicates with DISTINCT
• Improve your way of thinking in SQL

5. Joins (JOIN Tables)

• How to perform JOINs in a query
• OUTER JOIN: LEFT, RIGHT, FULL
• NATURAL JOIN
• Table aliases

6. Subqueries and Sets

• Create Subquery
• Column aliases with AS
• Combine data with UNION and UNION ALL
• Aside: UNION vs. JOIN

7. Module 7 — Add, modify and delete data

• INSERT: add new data
• UPDATE: modify existing data
• DELETE: delete lines

8. Module 8 — Additional Topics

• Understand DML, DDL and DCL
• SQL and Generative AI
• Where to go next?

1. Introduction

Introduction

SQL is different from other programming languages ​​— and that’s precisely what makes it so valuable.

Here are the main reasons why SQL is considered the most important and useful language in the world of software development and data analysis:

1. SQL is universal and omnipresent

SQL is used on all platforms and in all types of software: web development, mobile development, desktop applications. It’s found in old mainframe systems that have been running for three or four decades, and it’s just as common in newer cloud and artificial intelligence software. SQL is a fundamental skill for any type of software development, data science, or data analysis.

2. SQL is useful even without being a developer

Unlike many other languages, SQL remains extremely useful even if you are not a software developer or data scientist. Simply use it in existing desktop and web applications like Tableau, Power BI or Google Sheets — it’s now part of the business landscape.

3. SQL is simple and compact

SQL is one of the simplest and smallest computer languages ​​available. It is not like learning a foreign language where you would have to build a vocabulary of hundreds or thousands of words before you can say anything useful. You can accomplish almost anything you’ll ever need with just 20 to 25 words of SQL. But you really have to understand the impact of these words, the ideas they represent and how to put them together correctly.

4. SQL is for working with databases — and that’s it

SQL is a language for working with databases. That’s what he does. That’s all he does. If you don’t have a database and don’t want one, there’s little point in learning SQL. But the reality is that most organizations already have multiple existing databases.

What SQL allows you to do with a database:

• Access data from a database and extract only the relevant parts
• Finding information when you don’t know exactly where it is
• Group, sort and summarize data
• Combine data from different sources
• Asking questions of data and extracting insights
• Retrieve information from an existing database
• Manipulate data: add new data, modify it or delete it

This course primarily focuses on how to write SQL to work with an existing database. SQL can also be used to create a new database, administer it, manage users and permissions — but that’s not the main goal here, because most people don’t need to do that.

The instructor is Simon Allardyce, who has been writing SQL for almost 40 years and teaching it for over 20 years.

Before writing any SQL

Before writing the first line of SQL, it is necessary to understand the fundamental concepts of databases. Here’s why:

The air traffic control analogy

SQL is like communication in air traffic control (ATC). When we hear: “Shamorck 472 VFR approach, runway 27 left report middle marker,” it’s English, but with a very specific usage — certain words in a certain order. SQL works the same: English words like SELECT, UPDATE, ORDER, GROUP, in a specific sequence, otherwise it doesn’t work.

If someone said “all you have to do to become an air traffic controller is recite ‘Cleared for ILS runway 24 left, hold short of Delta’”, that would be absurd. We cannot say this sentence without understanding what it is talking about. For ATC communication to make sense, one must be comfortable with aviation concepts: aircraft call signs, airspace rules, landing sequences.

Likewise, for SQL to make sense, one must first be comfortable with database concepts and terms:

• Tables, rows, columns
• Indexes, data types
• Primary keys
• Foreign keys
• Relationships
• Referential integrity

You don’t need to become an expert on all of these topics, but you do need to understand what they mean. You can’t “put aside everything about databases and just focus on SQL” — it’s like wanting to learn ATC communication without talking about planes, runways or airports. Everything we say in SQL is about these database ideas.

Aside: SQL vs. “Sequel”

Both pronunciations

When we see these three letters, we will sometimes hear the pronunciation “S-Q-L” and sometimes the word “sequel”. For example:

• At Microsoft, we generally say “sequel”: “Sequel server”, “aftermath of sequel”
• In the open source community, we hear “My-S-Q-L” more often

There is often a common style within a particular technical community, but even then the two pronunciations coexist — like “GIF” vs. “jif”, or “rooter” vs. “router”.

The story behind the two pronunciations

It’s easy to think that pronouncing it like “sequel” would just be a quaint way of turning the letters S-Q-L into a word. It’s actually the other way around: in the 1970s, when this language was being developed, it was called “Sequel”, spelled out in full as one word. It was an acronym for Structured English Query Language. Then we realized that another company already owned the “Sequel” brand, and we had to change the name. But this language was called “Sequel” before being renamed SQL. The two ways of saying it have therefore coexisted from the very beginning.

Instructor Agreement

The instructor usually says “S-Q-L” when talking about the language by itself, but will say “sequel” when talking about specific products that are usually pronounced that way, such as Microsoft SQL Server.

2. Database Fundamentals

Why databases exist

Generic definitions are not useful

If you search online for a basic definition of what a database is, most answers are vague and generic: “a database is a collection of information” or “a repository of data.” These definitions are true, but useless — because we could just as easily describe a folder on the desktop where we have grouped some documents, or PDFs copied to a memory card.

What we really need

We want to work with database software — this whole category of products and technologies, including:

• Microsoft SQL Server
• Oracle Database
• IBM Db2
• MySQL
• PostgreSQL
• and many more

We all interact with databases every day, whether consciously or not: when visiting a website, using a mobile application, in HR, sales and inventory systems, customer relationship management, when shopping online, on social media, or when checking in for a flight.

The common misunderstanding

The most common reflex when we ask professionals what a database is for is: “It’s a place to store important information. » This is not a good answer. Thinking of a database primarily as a storage location misses the whole point.

We don’t need a database just for that. If we have information about a new client, we could simply write it in a text file or put it in a spreadsheet and save it. But if we try to manage a company, a team or a project with this data, it doesn’t scale. Maybe we could keep track of 100 customers — certainly not 200,000 or a million.

The real problems that databases solve:

What a database actually is

It’s not just about having a place to put the data. It’s about having an organized system in which we put the data. When you put the data into this system, it helps solve all these other problems.

This is why we use database software — what is typically called a DBMS (Database Management System) or Database Management System (SGBD). Microsoft SQL Server, Oracle Database, MySQL, etc. are all DBMS. We use a DBMS to create, manage and work with one or more databases. The DBMS is the software that continually runs in the background to keep these databases operational, control access, and ensure that all rules are enforced.

Even if we don’t understand how this database was designed, we must understand at least a little how it was defined: what its structure is and what rules exist inside it.

The Evolution of Relational Database Management Systems

The main DBMS

Any list of popular DBMS includes names like:

• Microsoft SQL Server
• MySQL
• PostgreSQL
• Oracle Database
• IBM DB2
• SQLite
• MongoDB
• Redis
• Microsoft Access

Many of these products have been available for a very long time:

• Oracle Database: first version in 1979
• Microsoft SQL Server: released in the 1980s
• IBM DB2 and PostgreSQL: also from 1980s

The term SQL is literally in the name of many of these products.

DBMS Categories

Although there are many different DBMSs, they can be grouped into a few broad categories. The most common is RDBMS (Relational Database Management Systems).

Edgar Codd’s relational model

These systems are all based on the same concepts: they use the same terms and share the same ideas about what a database is. These ideas come from a certain Edgar Codd, who published an article in 1970 on the relational model of data. He came up with this general approach to structuring and organizing data — an incredibly influential, very effective, very successful approach, which is why these products have lasted so long.

SQL, the language, was also designed around these same ideas of relational databases. This is why SQL can be used with so many different products.

NoSQL DBMS

There are some DBMSs that do not use SQL. For example, Redis and MongoDB intentionally took a different approach to data management than the typical approach of relational databases. We can even see these products described as “NoSQL” or “NoSQL databases”. But this course is about SQL, so we’re putting those aside to focus on the majority of database products that use SQL and these relational ideas.

The purpose of each database

Multiplicity of databases in an organization

Some organizations attempt to have a single database that contains all enterprise-wide data. But in practice, it is much more common to find multiple databases in an organization, and these databases can exist for very different reasons:

• Databases supporting a specific business unit or department (HR, Finance)
• Databases organized around a business task (purchasing, CRM) that spans several departments
• Databases for real-time operations (current transactions)
• Databases for historical archives
• Databases for public website content
• Separate databases for analytics and usage statistics

In many organizations, there are not only several databases, but also several DBMS: SQL Server and Oracle, DB2 and MySQL, PostgreSQL and Azure SQL. Some of these databases may be on-premises, others in the cloud.

This is one of the reasons why this training takes a vendor-agnostic approach: it is not about showing SQL used in a single DBMS or a single application, but about showing how it can be applied to any of them.

Entities and tables

The database design process

Every database has a fundamental reason to exist. When planning and designing a database, part of the process is identifying all the different elements — the different things on which data needs to be stored.

In the early stages of database design, often called data modeling, the term entity is often used. Entities are “the things” about which we want to store data.

Examples of entities:

• For an e-commerce transaction: customer, product, order, invoice, payment
• For a training center: instructor, student, classroom, course, event

The table: fundamental brick

In a new database, we create a table for each entity. The table is the fundamental building block of every relational database. We must have at least one table. We will typically have several — sometimes dozens, sometimes hundreds in a single database. Without tables, a relational database is useless.

Not all of our interactions — saving data, reading it, updating it, deleting it — are with the database as a whole. They are done with a specific table inside the database.

This does not mean that a new table is created for each client. This means that we have a table which contains customer data, another for order data, another for all product data, etc. Each table consists of very well-defined, structured and repetitive data.

Confusion with spreadsheets

A common misunderstanding: When we see a table in a database, we often see a grid of rows and columns — and many people react: “Oh, it’s just a spreadsheet!” »

No, not quite. We’re only looking at one table, and there could be dozens or hundreds of them in the same database. More importantly, we don’t see everything the DBMS is doing in the background:

• It enforces security (read/write permissions)
• It ensures that the data is meaningful – It maintains data consistency

A table in a relational database can be viewed as rows and columns of data, but it is much more controlled and constrained than a spreadsheet, and much more powerful.

Rows and columns

The structure of a table

With any table in a relational database, we can think of the data as a grid of rows and columns:

• columns (like columns in architecture) extend vertically
• lines extend horizontally

Rows

Each row in a table represents a single individual element of the entity that table represents:

• In a Customer table: each row is a specific customer (whether there are 5 or 5 million rows)
• In an Order table: each line is an order
• In a Patient table: each row is a patient

Columns

The columns describe each distinct piece of information that we want to track for this entity. For example, for a customer:

• Customer Email
• Telephone number
• Postcode

Some tables might only have 2 or 3 columns, others might require 50 or 60.

Rules for relational tables

When we define a database, we first determine which tables must exist, then we define which columns are necessary in each of them. You cannot add new data to a table before having specified what the columns are. In each table, each row will have the same structure — the same arrangement of columns.

Difference with a spreadsheet

Tables in a relational database are intentionally not flexible. When you add a new table to a database, you don’t get the equivalent of an empty spreadsheet into which you start typing random data. You must decide on the number of columns, give a name to each column, and also specify what type of information (which data type) is authorized in this column.

For example, for an Employee table in an HR database:

• First and last name → text
• Email address → text
• Hire date → date
• Bonus percentage → number
• Full time or not → boolean (true/false)

From the beginning, every database begins to have rules and constraints to ensure that the data is always meaningful. The DBMS enforces these choices. If a column is defined as a date and we try to insert a name, an email address or a cat image, even if the SQL is written correctly, the database will refuse. It will not allow data that does not conform to the definition.

The importance of data types

Why Data Types Matter

Each column in a table has a name and a data type. What type of values ​​are allowed in this column? Even if you’re not involved in the initial decision-making, it’s still important for writing SQL — because you need to know what type of data to expect from a column, or what type of data you’re allowed to insert into it.

The main categories of data types

It is necessary to understand generally:

Even within each of these categories, there are often several options, because the DBMS wants you to be very specific about the data types to be able to store the data efficiently.

Detail of common types

Text types:

• Some columns may have a fixed length, such as a two-character State abbreviation
• Others need variable length — and you can often specify whether you expect a little text (a first name) or a lot (the content of a blog post or a legal contract)

Numerical types:

• Sometimes you just need an integer (an integer)
• Other times we need to support decimal for financial transactions or scientific data

Binary types:

• There is usually a binary data type for images and documents
• There is often a distinction between small files and large files, often called BLOBs (Binary Large Objects)

Concrete example: definition of a Product table

ProductID     INTEGER         -- Nombre entier, clé primaire
Name          VARCHAR(100)    -- Texte de longueur variable, max 100 caractères
Category      VARCHAR(50)     -- Texte de longueur variable, max 50 caractères
Description   TEXT            -- Texte sans taille maximum
StockQuantity INTEGER         -- Nombre entier
Price         DECIMAL(10, 2)  -- Jusqu'à 10 chiffres avant la virgule, 2 après
DateAdded     DATE            -- Une date
IsActive      BOOLEAN         -- Seulement true ou false

VARCHAR vs TEXT: why the distinction?

When we define a column with a fixed or at least maximum length, the database can be much more efficient in how it stores this data. With a more open type like TEXT, where any value in that column could be 10 characters or 25,000, the database has to store that differently and it’s less efficient. In practice, a search or sort on a VARCHAR column will be much faster than on a TEXT column.

The exact options vary a bit between different DBMSs, but the fundamental ideas remain the same.

NULL values

An important question

When a new row is added to a table, is a value required for each column, or is it sometimes optional?

The default state for most databases is that all columns must contain values. But sometimes it doesn’t make sense. For example:

• An Employee table with columns for first name, middle initial, and last name — some people don’t have middle initials
• Address information for a customer: AddressLine1 might be required, but not AddressLine2

These columns can be set to allow NULL — and NULL is a very important concept in relational databases.

What is NULL

NULL is not the same as a space, or a zero, or an empty string. NULL means the complete absence of a value. It is the complete absence of a value.

The decision whether or not to allow NULL in a column is up to the database designer or database administrator. But when writing SQL, we often need to know: are there any columns in this table that allow NULL? Because if we retrieve data from this table, we could get these null or non-existent values. It may be necessary to only bring back the rows where this column is NULL, or on the contrary the rows where it is not NULL.

Use in SQL queries

To check if a value is NULL:

-- Trouver les employés sans initiale du milieu
SELECT * FROM Employee WHERE MiddleInitial IS NULL;

-- Trouver les employés qui ont une initiale du milieu
SELECT * FROM Employee WHERE MiddleInitial IS NOT NULL;

Important: We use IS NULL and IS NOT NULL, not = NULL nor <> NULL. Equality is not the right concept when dealing with NULL. Something cannot be equal to or different from NULL because NULL is not a value.

Primary keys

The need to identify a specific line

Whether it’s a customer table with 1 million rows, a patient table with 100,000 patients, or even a user table with just 5 users, one always needs to be able to go directly to 1 specific row to get the data for that particular customer, or update that particular patient, or delete just that user.

What is a Primary Key

The typical method is that each table will have a column designated as primary key. For a column to be a primary key, it must contain unique values. Even though there are a million rows in this table, no two rows are allowed to have the same value in this column.

Primary key uniqueness

In an employee table:

• Some will have the same last name ✓
• Some will have the same date of birth ✓
• Some will have the same state, same city ✓
• But they cannot have the same primary key ✗

The primary key must lead to a single line.

Choose a primary key

Option 1: Use existing data

• User table: the email address could always be unique → we can use it as primary key
• Vehicle Table: Existing VIN number uniquely identifies each row

Option 2: Create a new primary key column Often there is no single data that can be guaranteed. For many tables, we define a new column just to serve as the primary key — often an ID column of some sort, such as CustomerID, OrderID, or PatientNumber.

You can also ask the DBMS to automatically generate a unique value for this primary key for each new row added. The last client had ID 1148, so this one will have ID 1149. The database itself supports incrementing this number automatically for each new row.

The primary key is such an important concept that when we look at the details of a table, we often see the words “primary key” on one of the columns, sometimes abbreviated to PK, sometimes with a small key icon.

Foreign Keys and Relationships

Use Primary Keys as references

One of the huge advantages of having these unique primary keys that can identify a single row in the table is that one can use this information in other tables as a reference.

Concrete example

Suppose we have a customer with the unique identifier CustomerID = 1148. When we create a new order, rather than copying and duplicating a whole bunch of data on this customer, we simply define a column in the Order table with CustomerID. This can act as a reference from the Order table to the Customer table.

• In the Customer table: CustomerID is the primary key — unique
• In the Order table: CustomerID does not need to be unique — the same customer can create several orders

The one-to-many relationship

What we’re describing here is a one-to-many relationship — very common in relational databases:

• One customer can have multiple orders
• But each order is always for a particular customer

In the Order table, what makes each row unique is OrderID — that’s the primary key here. The CustomerID column in the Order table is therefore a foreign key. It’s a key to a different table.

What the Foreign Key does

By indicating to the DBMS that this column is a foreign key, it will check the validity of all the data put there:

• Database will not allow creating a new order line for customer 1148 if no customer 1148 exists
• The database will also prevent deleting a customer in the Customer table if there are still existing orders that refer to that customer

Referential Integrity

The term we use for this is referential integrity, sometimes called relational integrity. This is the idea that data will be kept consistent in itself, always meaningful, even when several different tables are involved.

Naming conventions

The diversity of conventions

When working with different databases and seeing examples of SQL online, you will encounter a wide variety in the way tables and columns are named and capitalized:

• All lowercase with underscores between words
• Words capitalized and stuck together
• All caps

The historical evolution of naming

In the past: the naming was intended to be short and concise due to memory constraints. We wouldn’t use 10 characters if we could get away with 3 or 4. A table was not called Customer, but Cust. One column was not called “Employee Identification Number”, but EmpID.

As technology improved: Database names could be more descriptive — from abbreviations to full words, to multiple words to make the purpose of a table or the meaning of a column more obvious.

The three major capitalization conventions

Some styles are more common with a particular vendor or technology. Other times, the organization may have naming practices that it enforces or supports. It may even depend on the personal preference of the database administrator.

SQL does not care about the convention, except that if a column is defined as employee_id in the database, then we will write employee_id in the SQL.

Singular vs. plural

There is also a lot of debate about whether to name tables singular or plural. Is it a Customer table (singular, because each row represents a single customer) or a Customers table (plural, because it contains several customer rows)?

The pragmatic answer: the differences in naming exist — you have to deal with them. We can hope that within each database there is some consistency, but we will encounter different styles. Even in the SQL examples in this course, we will sometimes change from one style to another — because we should expect to see it.

3. Writing SQL Queries

The SELECT clause

The most important keyword

When writing SQL, the vast majority of what one does can be accomplished with just a few words. But among them, there is one that we will write more than any other — and it is often the very first word that we write in a new line of SQL. This word is SELECT.

SELECT allows you to say which columns, which pieces of data you want to retrieve from the database. It’s how we choose, how we make a selection. And it is normally used with another SQL word: FROM. We select specific columns from a specific table.

Basic syntax

-- Récupérer une seule colonne
SELECT Email FROM Customer;

-- Récupérer plusieurs colonnes (séparées par des virgules)
SELECT Email, PostCode FROM Customer;
SELECT Email, LastName, FirstName FROM Customer;

-- Récupérer toutes les colonnes (avec l'astérisque *)
SELECT * FROM Customer;

When executing SELECT Email FROM Customer, the result is only the data from the Email column for each row in the table. If there are five rows in the Customer table, we retrieve five emails. If there are 5,000 lines, we retrieve 5,000 emails.

The result set

The results that we retrieve are called the result set — because it can be a set of data, a series of results brought together into a whole. Without limitation, the results are in no particular order.

The semicolon

The semicolon (;) in SQL is like using a period at the end of a sentence in French. This is the way of saying: this sentence is finished, it is complete. In SQL, we talk about statements rather than sentences. Some DBMSs do not require you to write the semicolon at the end of each statement — it would be understood without it — but it is a good practice. This makes things obvious.

Aside: Where to write SQL?

No single application

A very common question when learning SQL for the first time: where do you actually write it? Is there a particular software, a specific application that we are supposed to use?

The short answer: no. It’s best to realize that you have a lot of options. Because SQL is a universal standard, there are many different applications, tools, and websites where one can end up writing SQL.

Supplier specific tools

Each major database vendor generally provides its own tools:

• MySQL → MySQL Workbench
• Microsoft SQL Server → SQL Server Management Studio (SSMS)
• PostgreSQL → pgAdmin

These applications are not just a way to write SQL. To a beginner, they can seem oversized, even overwhelming, because they are fundamentally database administration tools. They have features for configuration, design, user administration, security, permissions, performance and optimization.

Important: Even if we use a particular DBMS from a particular vendor, this does not mean that we must install software from that vendor to write SQL. It’s not necessary.

General tools

There are also general-purpose database applications that you can install on your own machine, such as DBeaver and RazorSQL. These applications are vendor-agnostic (vendor independent). In DBeaver, for example, one can connect to Oracle, MySQL, SQL Server, and dozens of others. After choosing a DBMS, you provide connection information (where the database is located, authentication credentials), and you can then navigate the database and write SQL.

Web tools

There are more and more web applications that allow you to write SQL:

• Processing or analytics platforms like Snowflake, Databricks, Tableau, Power BI: you can write SQL directly in these web applications
• Databases hosted in the cloud (Amazon, Google, Microsoft Azure): providers offer web tools to interact with them directly in the browser
• SQL Fiddle, DB Fiddle, SQLiteOnline: sites to experiment without installing anything

For developers

Developers generally use their usual development environment to write SQL: Visual Studio, Eclipse, Xcode, VS Code, etc. These IDEs typically offer automatic syntax highlighting for SQL as well as options to run it directly.

Filter data with WHERE

Why filter?

Most of the time, you don’t want absolutely everything — every row in the table. We want to select some of them:

• “Give me the email address only of customers who have made a purchase in the last 30 days”
• “I want employee contact details, but only those who work in the Marketing department”
• “Just those who joined the company in the last five years”
• “Just those who have a salary between this amount and this other amount”

The WHERE clause

This is done by adding the SQL keyword WHERE. We don’t use it alone; it is used with SELECT and FROM:

-- Structure de base : SELECT ... FROM ... WHERE ...
SELECT Title FROM Movie WHERE ReleaseYear = 2005;

-- Rechercher une valeur texte (avec des guillemets simples)
SELECT * FROM Movie WHERE Title = 'Citizen Kane';

-- Lignes dont l'année n'est pas 2005
SELECT * FROM Movie WHERE ReleaseYear <> 2005;

-- Lignes dont le titre n'est pas Citizen Kane
SELECT * FROM Movie WHERE Title <> 'Citizen Kane';

-- Comparaisons numériques
SELECT * FROM Movie WHERE ReleaseYear > 2005;
SELECT * FROM Movie WHERE ReleaseYear < 2005;
SELECT * FROM Movie WHERE ReleaseYear >= 2005;
SELECT * FROM Movie WHERE ReleaseYear <= 2005;

The cornerstone of SQL

This format, SELECT, FROM, WHERE — the columns to select, the table those columns come from, and the conditions where the row should be returned — it is the fundamental, indispensable, and essential building block of SQL in any relational database. We will need it, whatever the level of advancement. It’s what everything else is built on.

Text vs. values numerical values

• If we treat a column defined as a numeric value, we write the number directly
• If processing a column defined to contain text, the value must be enclosed in single quotes (')

In SQL, we use single quotes to mark the start and end of a string value. Many other programming languages ​​use double quotes around strings, and programmers may have the reflex to type double quotes. Some database software can be configured to allow double quotes, but classic SQL uses single quotes.

Complex WHERE conditions with AND and OR

Multiple simultaneous conditions

Often, we want to retrieve rows not based on a single value in a single column, but by looking at several columns at the same time:

• “I want the names of employees who joined the company before 2020 AND who have a manager role”
• “Give me the names of all the products that are in the electronics category AND are priced over $100”
• “I want the names of products that are EITHER in the electronics category OR have a price greater than $100”

-- AND : les deux conditions doivent être vraies
SELECT FirstName, LastName, Email
FROM Employee
WHERE DateHired < '2020-01-01' AND Position = 'Manager';

SELECT ProductID, ProductName
FROM Product
WHERE Category = 'Electronics' AND Price > 100;

-- OR : au moins une condition doit être vraie
SELECT ProductID, ProductName
FROM Product
WHERE Category = 'Electronics' OR Price > 100;

Predicates

The term you may sometimes hear is predicate — each individual part of this condition can evaluate to true or false, and the entire condition can also evaluate to true or false.

The order of operations in SQL

Just as multiplication is considered more important than addition in a mathematical expression, in SQL, AND is considered more important than OR and is evaluated first.

Problematic example without parentheses:

-- ⚠️ Problème : AND est évalué avant OR
-- Résultat inattendu !
SELECT * FROM Product
WHERE Category = 'Electronics' OR Category = 'Books' AND Price < 10;

Solution with parentheses:

-- ✅ Correct : les parenthèses précisent l'ordre
-- Trouver les produits en Électronique, PLUS les produits en Livres avec prix < 10
SELECT * FROM Product
WHERE Category = 'Electronics' OR (Category = 'Books' AND Price < 10);

Parentheses in SQL serve the same purpose as parentheses in math problems. When you start building more complex conditions, it’s not a bad idea to put a few too many parentheses to ensure that the conditions are evaluated in the correct order.

BETWEEN and IN: search for multiple values

The IN keyword

We often need conditions where we look for lines that have not just one acceptable value, but several:

-- Version avec OR (verbose)
SELECT * FROM Employee
WHERE Department = 'Sales'
   OR Department = 'Marketing'
   OR Department = 'Engineering';

-- Version avec IN (plus concise)
SELECT * FROM Employee
WHERE Department IN ('Sales', 'Marketing', 'Engineering');

-- Exclure des valeurs avec NOT IN
SELECT * FROM Employee
WHERE Department NOT IN ('Sales', 'Marketing', 'Engineering');

IN allows you to use the column name only once and search for multiple values ​​in a single operation. If we want to add departments, we do not need to add new lines OR Department =, we just add the name to the list.

The keyword BETWEEN

For numeric value ranges:

-- Version sans BETWEEN
SELECT * FROM Artifacts
WHERE Year >= 1900 AND Year <= 1950;

-- Version avec BETWEEN (plus lisible)
SELECT * FROM Artifacts WHERE Year BETWEEN 1900 AND 1950;

BETWEEN is inclusive: it will return all rows with the value 1900, up to and including 1950.

BETWEEN can also be used with dates:

-- Toutes les commandes pour un mois particulier
SELECT * FROM Orders
WHERE OrderDate BETWEEN '2024-01-01' AND '2024-01-31';

Multiple conditions combined with BETWEEN

-- Moins lisible sans BETWEEN
SELECT * FROM Artifacts
WHERE (Year >= 1900 AND Year <= 1950) AND (Weight >= 100 AND Weight <= 500);

-- Plus lisible avec BETWEEN
SELECT * FROM Artifacts
WHERE Year BETWEEN 1900 AND 1950 AND Weight BETWEEN 100 AND 500;

Aside: Is SQL case sensitive?

The short answer: no

SQL is not case sensitive for SQL keywords. We could write the word SELECT in all uppercase, all lowercase, or even a weird mix of the two; that would work very well. The reason it’s often written this way is for readability — it makes it easier to quickly scan and recognize different parts of an SQL statement.

The convention

It is quite common to see SQL keywords written in ALL CAPS. When we talk about keywords, we are talking about those words that are defined as having a specific meaning in the SQL language (SELECT, FROM, WHERE, ORDER BY, etc.). They have been part of SQL since the 1970s.

The column and table names have been defined in the database — and we write them as they were defined.

The exception: text search

There is one area where case sensitivity deserves attention — when searching for a specific piece of text.

Case sensitivity for string values ​​depends on the DBMS and how it was configured. In some DBMS, a search for ‘citizen kane’ might or might not match ‘Citizen Kane’. This is something to check for the DBMS you are using.

Aside: SQL and whitespace

SQL ignores non-significant white spaces

All computer languages ​​have this notion of white spaces (characters that we can type in the code but which we do not see on the screen, such as spaces, tabs and line breaks.

Some languages, such as Python, treat white space as large and meaningful (Python uses indentation to determine code structure). But SQL treats whitespace as meaningless — SQL doesn’t care how many spaces, tabs, or newlines you put between different parts of an SQL statement. This doesn’t affect how SQL works.

-- Ces deux versions sont strictement équivalentes :

SELECT Title, Director FROM Movie WHERE ReleaseYear = 2005;

SELECT
    Title,
    Director
FROM
    Movie
WHERE
    ReleaseYear = 2005;

The only constraints

• We are not allowed to use white spaces inside a keyword — we cannot split the SELECT keyword on two lines
• At least one space is required between different parts of an instruction

The exception: text values

Whitespace makes sense inside quoted string values. If searching for the value ”Green Tea”, this space is significant — it will match values ​​in the database that are equal to ‘Green Tea’ (with one space) and not ‘GreenTea’ (with no spaces) or ‘Green Tea’ (with multiple spaces).

LIKE: pattern matching

When you want more flexibility

Often we want to match an exact value. But if we need to be a little more flexible when searching for text, we can use the LIKE keyword — and ask if a column looks like a value rather than being equal to it.

Percentage sign % as wildcard

The % sign acts as a wildcard. It will match any amount of text (or none).

-- Produits dont le nom COMMENCE par "Green"
-- Correspond à : Green Tea, Green Coffee Beans, Green Yoga Mat
-- Ne correspond PAS à : Jasmine Green Tea
SELECT * FROM Product WHERE Name LIKE 'Green%';

-- Produits dont le nom CONTIENT "green" n'importe où
-- Correspond à : Green Tea, Jasmine Green Tea, Evergreen Pine
SELECT * FROM Product WHERE Name LIKE '%green%';

-- Produits dont le nom FINIT par "green"
SELECT * FROM Product WHERE Name LIKE '%green';

-- Exclure avec NOT LIKE
SELECT * FROM Product WHERE Name NOT LIKE '%green%';

Underscore _ as a single character wildcard

If we don’t want that much flexibility — just a single character wildcard, a single character that could be different:

-- Correspondra à : Genesis G70, Genesis G80, Genesis G90
SELECT * FROM Cars WHERE Model LIKE 'Genesis G_0';

Be careful with performance

Although LIKE is sometimes necessary, it can be inefficient and time-consuming because it is very demanding on the database to iterate through the values ​​in a column. Efficiency depends on the size of the database and how the columns have been defined, but a wildcard can be significantly more or less effective depending on where it is placed:

-- Plus efficace : wildcard à la FIN (le DBMS ne regarde que les premiers caractères)
SELECT * FROM Stock WHERE InventoryCode LIKE 'ABC%';

-- Moins efficace : wildcard au DÉBUT (le DBMS doit chercher partout dans la colonne)
SELECT * FROM Stock WHERE InventoryCode LIKE '%ABC';

Just because something is easy to write in SQL, like a wildcard search, doesn’t mean it’s also easy to do for the database. Asking the database to search an entire table for a piece of text that could be anywhere is one of the most inefficient things you can ask it to do.

Use NULL in a SELECT

The problem with =NULL

Some columns can be configured to allow NULL. If we want to query lines where the middle initial has no value, we could first try:

-- ⚠️ CECI NE FONCTIONNE PAS !
SELECT * FROM Employee WHERE MiddleInitial = NULL;

This won’t work, because equality is not the right concept when dealing with NULL. Something cannot be equal to NULL or not NULL because NULL is not a value. In SQL, we use the word IS when working with NULL.

The correct syntax

-- Lignes où MiddleInitial n'a PAS de valeur (est NULL)
SELECT * FROM Employee WHERE MiddleInitial IS NULL;

-- Lignes où MiddleInitial A une valeur (n'est pas NULL)
SELECT * FROM Employee WHERE MiddleInitial IS NOT NULL;

4. ORDER BY, GROUP BY and aggregate functions

Sort results with ORDER BY

Problem: Order is not guaranteed

When writing a simple SQL statement, the rows will be returned in an order that is not guaranteed to be in any particular meaningful order. But we often want to impose an order: sort the results from lowest price to highest, or from highest to lowest, or by rating, or from newest to oldest.

The ORDER BY clause

ORDER BY is added at the end of an SQL query, after the WHERE clause:

-- Tri croissant (par défaut) : du plus petit au plus grand
SELECT * FROM Product ORDER BY ListPrice;

-- Équivalent explicite avec ASC
SELECT * FROM Product ORDER BY ListPrice ASC;

-- Tri décroissant : du plus grand au plus petit
SELECT * FROM Product ORDER BY ListPrice DESC;

Sorting on multiple columns

-- D'abord par nom de famille, puis par prénom dans le cas de noms identiques
SELECT * FROM Employees ORDER BY last_name, first_name;

Whatever the sequence in which the columns are written after ORDER BY, it is this sequence which will be used to sort the results. When several rows have the same value for the first criterion, they are sorted by the second criterion, and so on.

Aggregate Functions

Summarizer rather than list

When writing a query, sometimes the desired results are the actual individual data. But often we’re not really interested in the individual pieces of data — we want to summarize them one way or another:

• How many orders did we receive last month?
• What is the average amount a new customer spends on their first purchase?
• What is the biggest transaction of last year?
• What was the total sales revenue of all products in the electronics category?

The concept of aggregate

Think of the term aggregate in the same way that we use it in the expression “aggregate score” in sports. It’s just a way to combine the score from multiple matches into a single summary result. In SQL, an aggregate function is similar: you take several values ​​from the database and perform a calculation on all of these values ​​to obtain a single value.

The five main aggregate functions

Concrete examples

-- COUNT : compter toutes les lignes
SELECT COUNT(*) FROM Employee;

-- COUNT avec condition
SELECT COUNT(*) FROM Employee WHERE Salary > 50000;

-- SUM : total du temps de visionnage d'un utilisateur
SELECT SUM(minutes_watched) FROM user_session WHERE user_id = 42;

-- MAX : produit le plus cher
SELECT MAX(ListPrice) FROM Product;

-- MIN : produit le moins cher
SELECT MIN(ListPrice) FROM Product;

-- AVG : prix moyen
SELECT AVG(ListPrice) FROM Product;

The difference between COUNT(*) and COUNT(column): COUNT(*) counts all rows, including those with NULL values. COUNT(column) only counts rows where this column has a non-NULL value.

Group data with GROUP BY

The need for grouping

Let’s imagine thousands of products spread across dozens of categories. If we wanted a breakdown of the number of products in each category — not just books, but also electronics, clothing, office supplies — we could create several SQL statements, but it would be tedious. And we assume that we know all the categories in advance, whereas we could add new ones at any time.

The GROUP BY clause

-- Mauvaise tentative (ambigu, peut donner une erreur ou des résultats non-significatifs)
SELECT COUNT(*), Category FROM Product;

-- ✅ La bonne façon avec GROUP BY
SELECT Category, COUNT(*) FROM Product GROUP BY Category;

GROUP BY tells the database that we want to split and group the results based on the values ​​in the Category column. We get a count of the number of products in each category.

The fundamental rule

When we use GROUP BY, we also use an aggregate function.

GROUP BY is typically used with COUNT, SUM, MIN, MAX, AVG because we only use it if we want some sort of summary: grouping all rows with identical values ​​into a specific column.

Examples:

-- Somme des ventes par région
SELECT Region, SUM(SalesAmount) FROM Sales GROUP BY Region;

-- Salaire moyen par département
SELECT Department, AVG(Salary) FROM Employees GROUP BY Department;

-- Nombre de clients par pays
SELECT Country, COUNT(*) FROM Customers GROUP BY Country;

GROUP BY vs ORDER BY: common error

A very common mistake among SQL beginners: using GROUP BY when what was really needed was ORDER BY.

• ORDER BY Category: lists all individual products in order of their categories
• GROUP BY Category: does not give all individual lines in the group; gives one result per group — it is designed to summarize

-- ORDER BY : liste tous les produits triés par catégorie
SELECT ProductName, Category FROM Product ORDER BY Category;

-- GROUP BY : compte le nombre de produits par catégorie (résumé)
SELECT Category, COUNT(*) FROM Product GROUP BY Category;

GROUP BY and HAVING

The problem

GROUP BY is the ideal option when you want to divide the results into groups and then perform some sort of calculation on each group. But a challenge often arises: whether to use the calculated information (like the result of COUNT) as a way to filter the data itself.

For example, we have results and we only want product categories with more than 100 products. We might be tempted to add this in the WHERE clause:

-- ⚠️ CECI NE FONCTIONNE PAS ! Erreur de syntaxe.
SELECT Category, COUNT(*) FROM Product
WHERE Status <> 'discontinued' AND COUNT(*) > 100
GROUP BY Category;

Why doesn’t it work?

When executing any SQL statement, the WHERE part — the place where we filter the data — always occurs before the GROUP BY, before the calculations are done. So WHERE filters the data, then it is grouped, then counted. When WHERE is evaluated, the COUNT has not yet been calculated.

The solution: HAVING

HAVING exists precisely for this type of problem. It allows additional filtering based on calculated information. It is added after the GROUP BY:

-- ✅ La bonne façon : utiliser HAVING pour filtrer sur les données calculées
SELECT Category, COUNT(*) FROM Product
WHERE Status <> 'discontinued'
GROUP BY Category
HAVING COUNT(*) > 100;

When to use WHERE vs HAVING

HAVING expects to see an aggregate function like COUNT, SUM, AVG, because we use it when we want to filter on calculated data that we cannot put in a WHERE.

Eliminate duplicates with DISTINCT

The duplicate problem

Let’s imagine a large international customer base and we want a list of all the countries where the customers come from. We are not asking for a count of countries, just the list. With SELECT Country FROM Customer you would get all the countries in random order with lots of duplicate entries.

-- Sans DISTINCT : beaucoup de doublons
SELECT Country FROM Customer ORDER BY Country;

-- Avec GROUP BY : fonctionne mais un peu lourd
SELECT Country FROM Customer GROUP BY Country;

-- ✅ Solution la plus propre avec DISTINCT
SELECT DISTINCT Country FROM Customer;

DISTINCT is the SQL keyword for saying “I want the values ​​in this column, but I don’t want duplicates — just a single distinct result for each unique value.”

DISTINCT can also be used with several columns:

-- Toutes les combinaisons uniques de ville et pays
SELECT DISTINCT City, Country FROM Customer;

Improve your way of thinking in SQL

The secret to writing good SQL queries

One of the most important things you can do before typing anything is to step back from the keyboard and first try to describe the desired results in everyday language (French or English) in a single sentence.

You can say it out loud, write it on paper or on the whiteboard. The goal is to be precise and specific — not “I want customer data,” but exactly what data, what conditions, whether you want it summarized, grouped or sorted.

Examples of precise descriptions

• “I want the name and email address of all customers who placed an order over $100 in the last 48 hours, in alphabetical order by last name.” »
• “We need to know the users whose subscriptions expire in the next 15 days, including their name, email, subscription type and expiration date, ordered by expiration date.” »
• “I want to know the products sold last month, including the quantity of each product sold and the total sales amount, sorted by that amount in descending order.” »

SQL is a declarative language, not imperative

Most popular programming languages ​​(Python, Java, C#, Swift, Ruby, JavaScript) are imperative languages ​​(sometimes called procedural). When writing code in these languages, you have to think through and describe all the individual steps to achieve the desired result — you describe the process.

SQL doesn’t work like that. This is called a declarative language. In SQL, we do not describe the process. We simply declare the desired results as precisely as possible, and let the DBMS decide how to do it.

An analogy:

• An imperative language: being in the kitchen and micromanaging every step that everyone follows
• Declarative language: never set foot in the kitchen — you sit down and say what you want: “I want a 12 oz medium rare steak. »

The best way to write an SQL query is not to start writing SELECT and try to understand what you are asking as you write it. You must first clearly articulate in everyday language what you are looking for. The better one becomes at thinking through and describing precisely the desired results, the easier it becomes to write SQL.

5. Joins (JOIN Tables)

How to perform JOINs in a query

The need for multi-table queries

Most of the time, we want queries that don’t just retrieve data from a single table, but can pull from multiple tables simultaneously. In a relational database, a lot of our data is connected and related.

The most common relationships

• Employee table and Department table: a department can have several employees, but each employee works for a specific department (one-to-many relationship)
• Customer table and Order table: a customer can create several orders, but each order is for a specific customer
• Product table and Review table: a product can have several reviews, but each review concerns a specific product

How ​​to join tables

-- Requête de base sur une seule table (Employee)
-- On voit seulement le DepartmentID, pas le nom du département
SELECT EmployeeID, FirstName, DepartmentID FROM Employee;

-- Jointure pour obtenir les noms des départements
SELECT Employee.EmployeeID, Employee.FirstName, Department.Name, Department.Location
FROM Employee
JOIN Department ON Employee.DepartmentID = Department.DepartmentID;

-- En rendant explicite que c'est un INNER JOIN
SELECT Employee.EmployeeID, Employee.FirstName, Department.Name, Department.Location
FROM Employee
INNER JOIN Department ON Employee.DepartmentID = Department.DepartmentID;

The complete JOIN syntax

• JOIN (or INNER JOIN): between the names of the two tables
• ON: to explicitly specify how these tables are connected — which column in table A is linked to which column in table B

-- Structure générale
SELECT colonnes
FROM TableA
INNER JOIN TableB ON TableA.colonne_cle = TableB.colonne_cle;

Column prefixing

When a column exists in both tables (such as DepartmentID), there is ambiguity. We must specify which table the column comes from using the table.column prefix:

-- Résoudre l'ambiguïté avec le nom de la table
SELECT Employee.DepartmentID, Employee.FirstName, Department.Name
FROM Employee
INNER JOIN Department ON Employee.DepartmentID = Department.DepartmentID;

You should only prefix a column if there is a potential ambiguity — you do not have to prefix all columns.

INNER JOIN: only rows that match

An INNER JOIN (or simply JOIN) retrieves data from two tables, but only shows results when there is an exact match. It’s not just called JOIN, it’s called INNER JOIN. What this means: we will only show results where there is an exact match between the two tables.

OUTER JOIN: LEFT, RIGHT, FULL

The limit of the INNER JOIN

An INNER JOIN retrieves data from two tables, but only shows results when there is an exact match. Let’s imagine that in the Employee table, the DepartmentID column allows NULL (some floating employees do not belong to any department). With an INNER JOIN, these employees without a department would never appear in the results.

OUTER JOINs

An OUTER JOIN still allows you to obtain results from the two tables, to join them where there is a match, but specifying that if rows in table A do not have a match in table B, we still want to see these lines.

LEFT OUTER JOIN (or LEFT JOIN)

The table to the left of the word JOIN takes priority — all its rows will be returned:

-- Tous les employés, même ceux sans département
-- Les colonnes de Department seront NULL pour les employés sans département
SELECT Employee.EmployeeID, Employee.FirstName, Department.Name
FROM Employee
LEFT OUTER JOIN Department ON Employee.DepartmentID = Department.DepartmentID;

-- Version courte (OUTER est optionnel)
SELECT Employee.EmployeeID, Employee.FirstName, Department.Name
FROM Employee
LEFT JOIN Department ON Employee.DepartmentID = Department.DepartmentID;

RIGHT OUTER JOIN (or RIGHT JOIN)

The table to the right of the word JOIN takes precedence. In this example, it shows all departments, even those without employees:

SELECT Employee.EmployeeID, Employee.FirstName, Department.Name
FROM Employee
RIGHT OUTER JOIN Department ON Employee.DepartmentID = Department.DepartmentID;

FULL OUTER JOIN

The FULL OUTER JOIN is the most inclusive: it retrieves all the rows from both tables. It still joins where possible, but provides the extra rows with NULL values ​​where there is no match.

SELECT Employee.EmployeeID, Employee.FirstName, Department.Name
FROM Employee
FULL OUTER JOIN Department ON Employee.DepartmentID = Department.DepartmentID;

JOIN Type Summary

NATURAL JOIN

The idea

When we learn about INNER JOIN and OUTER JOIN, we learn that we must use the keyword ON to say how we join these tables. But we sometimes wonder: aren’t these relationships already defined in the database itself?

If a column is defined as a primary key in one table and that same column is defined as a foreign key in another table, why must we specify in SQL how these tables are connected if it is already defined in the database?

Why do we still define the relationships manually

The primary reason for defining relationships in the database is for data integrity and consistency — not just to make it easier to write SQL. It’s much more about maintaining the validity of the data.

Additionally, databases often contain complex relationships where a single table may have multiple relationships with other tables — some very well defined, others more informal, perhaps with related columns named differently or based on calculated information. When you can specify join conditions explicitly, you have a lot of control.

NATURAL JOIN syntax

-- Jointure automatique sur les colonnes de même nom et de même type
SELECT * FROM Employee NATURAL JOIN Department;

A NATURAL JOIN does not require defining the columns on which it is joined. It will automatically look at both tables and as long as they both have a column with exactly the same name and exactly the same data type, it will join the tables using that column.

NATURAL JOIN Limits

Not all DBMS support the NATURAL JOIN syntax. Microsoft SQL Server and IBM DB2, for example, do not use it. If one knows how to write INNER JOIN and OUTER JOIN with explicit join conditions, this is something that is always useful in all relational databases.

Table aliases

The problem: table name verbosity

When working with multiple tables in an SQL query (such as when doing a JOIN), we often need to use the name of each table several times: when describing which columns come from which table, and when saying exactly which columns we are using to join them.

Table aliases

A table alias allows you to provide a temporary short name for these tables — essentially a nickname — which makes the query more concise and readable.

-- Version verboseuse sans alias (Orders utilisé 8 fois, Customer 6 fois)
SELECT Orders.OrderID, Customer.FirstName, Customer.LastName,
       Orders.OrderDate, Orders.Status, Customer.Status
FROM Orders
INNER JOIN Customer ON Orders.CustomerID = Customer.CustomerID;

-- Version avec alias (une lettre par table)
SELECT o.OrderID, c.FirstName, c.LastName,
       o.OrderDate, o.Status, c.Status
FROM Orders o
INNER JOIN Customer c ON o.CustomerID = c.CustomerID;

-- Avec le mot-clé AS explicite (optionnel mais clair)
SELECT o.OrderID, c.FirstName, c.LastName,
       o.OrderDate, o.Status, c.Status
FROM Orders AS o
INNER JOIN Customer AS c ON o.CustomerID = c.CustomerID;

It is very common to use only one letter for each table. In most implementations of SQL, the AS keyword is not required — but it makes the alias very clear.

The alias can be uppercase, lowercase, one letter, or multiple letters. It’s essentially his own choice — which makes sense.

6. Subqueries and Sets

Create a subquery (Subquery)

The problem

Let’s imagine that we want to see all the products whose list price is above average. This request does not seem too difficult. We know how to write a query to request all products above a specific price. We also know how to calculate an average with the AVG aggregate function.

-- ⚠️ CECI NE FONCTIONNE PAS !
SELECT ProductID, Name, ListPrice FROM Product
WHERE ListPrice > AVG(ListPrice);

Why is it not working? Because of the way each SQL statement works: whatever is written in the WHERE clause, the conditions used to filter the data, always happens before the calculations or aggregate functions are evaluated. You cannot put an aggregate function like AVG directly inside a WHERE clause.

The solution: the subquery

A subquery means that we write a query inside another query. It’s not two SQL statements one after the other — it’s actually one inside the other:

-- ✅ Solution avec sous-requête
SELECT ProductID, Name, ListPrice FROM Product
WHERE ListPrice > (SELECT AVG(ListPrice) FROM Product);

How ​​it works

When executing this entire SQL statement, the subquery inside the parentheses is evaluated first. It works as if it had been written alone. She finds the average list price at that time. Let’s assume it’s $47.24. The results will now be as if we had written:

SELECT ProductID, Name, ListPrice FROM Product
WHERE ListPrice > 47.24;

If we run this again tomorrow, it will recalculate this average and do it again — we’re not relying on a hard-coded value that might become obsolete.

Subqueries are a very powerful technique in SQL. They allow you to construct complex queries in a more manageable and readable way. Every time we think “I know how to write SQL to do this part AND that part, I just don’t know how to combine them” — subqueries are an option.

Column aliases with AS

Give alternative names to columns

When looking at the results of a standard query, the column names you get in the result set are simply the column names as defined in the database. But sometimes it is useful to be able to change this name.

Case 1: Columns with cryptic names

For example, if a table in an old legacy system has short and cryptic column names (nm, Itm_inv, Itm_lc):

-- Sans alias : noms incompréhensibles pour quelqu'un d'autre
SELECT nm, Itm_inv, Itm_lc FROM Product;

-- Avec alias AS : noms clairs dans les résultats
SELECT nm AS Name, Itm_inv AS ItemsInStock, Itm_lc AS Location
FROM Product;

-- Nom d'alias avec espaces (entre guillemets)
SELECT nm AS 'Product Name' FROM Product;

This does not modify the database schema. It doesn’t rename anything in the table. This is purely for the output of this query.

Case 2: Calculated columns

Aliases are also very useful with calculated columns like aggregate functions:

-- Sans alias : le nom de la colonne calculée est ambigu ou défini par le DBMS
SELECT Category, AVG(ListPrice) FROM Product GROUP BY Category;

-- Avec alias AS : nom clair dans les résultats
SELECT Category, AVG(ListPrice) AS AveragePrice FROM Product GROUP BY Category;

The name displayed for any calculated value may be different depending on the DBMS used. Some do not provide any names. Others might call it “Column1”. Others might use the aggregate function name. Using AS allows you to decide the name yourself.

Combine data with UNION and UNION ALL

The UNION operator

UNION is used to combine the results of two or more SELECT statements into one larger result. It is written literally between two valid SELECT statements.

When to use UNION?

In a large organization, it is not uncommon to have separate tables that store very similar information — perhaps an old system and a new system. Let’s imagine two tables that store information about customers: CurrentCustomers and PastCustomers. These tables might have a similar but not identical schema. We just want a complete list of all customer emails, regardless of their status:

-- UNION : combine et déduplique automatiquement
SELECT Email FROM CurrentCustomers
UNION
SELECT Email FROM PastCustomers;

UNION rules

• Column names do not need to be the same in both tables
• In the results, the names from the first query will be used
• Both queries must return the same number of columns and where the data types are compatible
• We only put the semicolon once, at the end of the entire statement

UNION vs UNION ALL

By default, UNION will check the results and eliminate duplicate values. If the same email existed in both tables, we would only see it once in the results.

This deduplication has a performance impact — the DBMS has to do a lot of work to scan all results for duplicates, especially with large tables.

-- UNION : déduplique (plus lent)
SELECT Email FROM CurrentCustomers
UNION
SELECT Email FROM PastCustomers;

-- UNION ALL : ne déduplique pas (beaucoup plus rapide)
SELECT Email FROM CurrentCustomers
UNION ALL
SELECT Email FROM PastCustomers;

If we know that there are no duplicates between the tables, or if we don’t care about duplicates, or if we even want to see the duplicates for further analysis, we can use UNION ALL. With large amounts of data, UNION ALL can be substantially faster.

Aside: UNION vs. JOIN

The common confusion

If we want to get information from two different tables, we know that we can use JOIN, and we know that we can use UNION, but how do we know which one to use?

These two keywords are used to obtain information from multiple tables, but the reason for using each is extremely different.

In practice, one rarely has to choose between them because the reason for using each is so different. It’s not really about alternatives — they solve fundamentally different problems.

7. Add, modify and delete data

INSERT: add new data

The four fundamental operations (CRUD)

Any computer system that deals with data storage needs four fundamental functions. We sometimes use the acronym CRUD for this:

INSERT syntax

-- Forme de base
INSERT INTO NomTable (Colonne1, Colonne2, Colonne3)
VALUES (Valeur1, Valeur2, Valeur3);

We give the specific names of the columns for which we provide values, because we do not always need to provide data for each column of a new row:

• The table can be defined so that certain columns provide default values (like automatically generating a primary key)
• Other columns might allow NULL

INSERT rules

• Columns and values must match in number, order and type
• We cannot say we are going to provide five columns and then only provide values for four of them
• Data types must be consistent — if a column is defined as integer, a string value cannot be inserted

Concrete example

-- EmployeeID est généré automatiquement
-- Department a une valeur par défaut 'General'
INSERT INTO Employee (FirstName, LastName, Email, HireDate)
VALUES ('Marie', 'Dupont', 'marie.dupont@example.com', '2024-01-15');

-- Insérer plusieurs lignes simultanément
INSERT INTO Employee (FirstName, LastName, Email, HireDate)
VALUES ('Jean', 'Martin', 'jean.martin@example.com', '2024-01-16'),
       ('Sophie', 'Bernard', 'sophie.bernard@example.com', '2024-01-17');

In practice, we do not manually write INSERT statements for each new row in a database. It is much more likely that these insertions are done programmatically by an application, or by a bulk import. But it’s the INSERT type of statement that’s really going on behind the scenes.

UPDATE: modify existing data

UPDATE syntax

Just like a basic SELECT statement has three parts (SELECT ... FROM ... WHERE), any UPDATE also has three parts: we UPDATE a specific table, then we SET one or more columns to a new value WHERE a condition is true:

-- Mettre à jour une seule colonne
UPDATE Employee
SET Status = 'Retired'
WHERE EmployeeID = 6543;

-- Mettre à jour plusieurs colonnes en même temps
UPDATE Employee
SET Status = 'Retired',
    DepartmentID = NULL,
    LastModified = '2024-01-15'
WHERE EmployeeID = 6543;

⚠️ The importance of the WHERE clause

Technically, we are not required to have a WHERE clause in an UPDATE statement. But if we ever see an UPDATE statement without a WHERE clause, we can immediately assume that it’s an error and a potential disaster waiting to happen. Because omitting the WHERE clause in an UPDATE statement means that we are telling the database to change the value of this column for each row in the table.

WHERE in an UPDATE works exactly the same as in a SELECT. We can use all the same conditions, comparisons, AND/OR, IN, BETWEEN, etc.

Data integrity

Most data update issues come back to data integrity:

• If a column is defined as integer, an error will occur if trying to update it with a string value
• If a column is configured as a foreign key to another table, the database will ensure that any value we change is a valid reference before allowing it

DELETE: delete rows

DELETE syntax

DELETE is actually one of the simplest of all SQL statements because it all relies on the WHERE. We use the keyword DELETE with FROM to say from which table we want to delete rows:

-- Supprimer une ligne spécifique
DELETE FROM Employee WHERE EmployeeID = 6543;

-- Supprimer plusieurs lignes
DELETE FROM Employee WHERE DepartmentID = 5;

Unlike INSERT, UPDATE and SELECT, when using DELETE, we do not specify specific columns in the table. In fact, one can’t name specific columns, because DELETE always affects entire rows. If you delete something, you delete at least an entire line — you cannot delete part of a line.

⚠️⚠️ DELETE without WHERE: the fatal trap

Almost everyone who has worked with databases for a while has a horror story about a DELETE statement written a little too quickly. Because if we accidentally forget the WHERE clause:

-- ⚠️⚠️⚠️ CATASTROPHE ! Ceci supprimera TOUTES les lignes de la table Employee !
DELETE FROM Employee;

The database will simply say “you are the boss” and delete every row from the table. She won’t ask “are you sure?” ”. It will not require any confirmation. She will do it and that’s it. DELETE is a very, very powerful keyword.

Some DBMS have a “secure mode” where each DELETE statement is required to have a WHERE clause, and this WHERE clause must even use a primary key. But don’t count on that.

Best practice

A very good practice when working with DELETE (and also with UPDATE) is to **first create an equivalent SELECT to ensure that the WHERE clause you wrote will only return exactly the expected rows — and only those rows. We confirm this first before transforming it into DELETE or UPDATE.

-- Étape 1 : Vérifier ce qui sera affecté
SELECT * FROM Employee WHERE DepartmentID = 5;

-- Étape 2 : Si les résultats sont corrects, alors faire le DELETE
DELETE FROM Employee WHERE DepartmentID = 5;

8. Additional Topics

Understanding DML, DDL and DCL

DML — Data Manipulation Language

The vast majority of SQL statements that we will write begin with the word SELECT, followed perhaps by INSERT, UPDATE, DELETE. These are sometimes described as the DML part of SQL. DML stands for Data Manipulation Language.

This DML term seems official and important, but in reality it is just a convenient word to describe the part of the SQL language that deals with reading and manipulating the actual data in a database: SELECT, INSERT, UPDATE, DELETE statements, and all the other keywords used with those (FROM, WHERE, GROUP BY, ORDER BY, JOIN, etc.).

Why are we talking about it? Because we sometimes see this term in documentation or even in job postings: “We are looking for strong SQL skills, including Data Manipulation Language, DML, and Data Definition Language, DDL.” »

The three SQL sublanguages

We can think of SQL as being subdivided into a few different areas:

DDL — Data Definition Language

Sometimes we need to go into “mechanic” mode — go under the hood and affect the structure of the database itself: create a new database, change the definition of a table, define the columns, configure the primary keys and relationships.

All this can also be done with SQL. This is the part of the language sometimes called DDL:

-- CREATE TABLE : créer une nouvelle table
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName  VARCHAR(50),
    LastName   VARCHAR(50),
    Email      VARCHAR(100),
    HireDate   DATE
);

-- ALTER TABLE : ajouter une colonne à une table existante
ALTER TABLE Employees ADD DepartmentID INT;

-- DROP TABLE : supprimer une table entièrement
DROP TABLE Employees;

We don’t say “delete” to delete an entire table — we already have the keyword DELETE to delete rows of data. We use DROP to delete entire structures (tables, databases). DROP TABLE is an irreversible operation.

Why this course does not cover DDL

The real work of creating new databases and tables is not the SQL to do it (that’s the easy part that takes a few minutes). The real work is all the significant database planning and design that needs to happen before writing these instructions: understanding business rules, data normalization, indexing, and specific DBMS software. None of this is covered here because this is an introduction to SQL, not a database design course.

Additionally, even people who work on the actual database design often find it easier to use an application like MySQL Workbench or SQL Server Management Studio rather than creating hundreds of CREATE and ALTER statements.

DCL — Data Control Language

Another area of ​​SQL called DCL (Data Control Language) deals with access control — permissions and security: which users are allowed to access the database tables, can they only read the data, or can they also modify it, delete it or add new data to it, can they redefine the structure of the database itself?

-- GRANT : accorder des permissions
GRANT SELECT, INSERT ON Customer TO 'user_readonly';

-- REVOKE : retirer des permissions
REVOKE INSERT ON Customer FROM 'user_readonly';

This is a database administration task. In any enterprise-level organization, there might be hundreds or thousands of people who can write SQL to query a database, but where almost none of them have permissions to change the database definition or affect its security. These tasks are reserved for a much smaller set of people with a different set of skills.

SQL and Generative AI

The opportunity

Once one has a fundamental understanding of SQL, a useful technique now available is the option of using generative AI tools, such as ChatGPT, Google Gemini or Anthropic Claude. If you’re a software developer, you may already be using AI-driven development tools, like GitHub Copilot.

What NOT to do

You shouldn’t just have the AI ​​write all the SQL. This isn’t going to go well. If using a general purpose AI tool:

• It does not understand the database schema, any relationships or data
• Even if the table schema could be explained to them, the organization will likely frown on uploading sensitive data into a generally available AI tool
• But besides, it’s not necessary

AI as an assistant, not a replacement

We want to use AI as an assistant, like a helper. Yes, it can help with SQL syntax, but it can also help understand and reinforce what you have already learned. We still need to provide direction, and critically examine everything the AI ​​suggests.

Four Practical Ways to Use Generative AI with SQL

1. Request sample queries for problems described in everyday language

We return to the idea of ​​describing what we want from the database in one sentence. The AI ​​is asked to provide a hypothetical SQL example for this description, and is also asked to explain step by step. It’ll break down the choices she makes and why — which helps you understand and apply that to your own situation.

Exemple de prompt : 
"Donne-moi un exemple en SQL avec tes propres noms de tables et colonnes pour cette 
description : je veux les produits vendus le mois dernier, y compris la quantité de 
chaque produit vendu et le montant total des ventes, trié par ce montant en ordre 
décroissant. Explique chaque partie étape par étape."

2. Review fundamental concepts

AI can help recall fundamental concepts:

Exemple de prompt :
"Donne-moi un exemple SQL de GROUP BY versus ORDER BY"
"En SQL, y a-t-il des implications de performance pour une recherche wildcard ?"

3. Identify what is wrong in a query

If we have problems with SQL and we get an error on a particular line, we can describe the problem to the AI ​​without necessarily copying and pasting the entire query:

Exemple de prompt :
"J'obtiens une erreur quand j'essaie d'utiliser une fonction COUNT SQL dans ma 
clause WHERE. Pourquoi ?"

The AI ​​will say: we cannot use an aggregate function in a WHERE clause, here is an explanation of why, and here is what we can do instead.

4. Be questioned by AI to test your knowledge

We can reverse the relationship — instead of asking the AI ​​questions, we ask it to ask us questions:

Exemple de prompt :
"Pose-moi quelques questions sur les INNER JOINs SQL versus les OUTER JOINs et 
valide que je comprends bien ce que sont ces concepts."

Important note: With all these approaches, we stay in the loop. We’re not asking AI to replace us — we’re using AI to extend and build on what we understand, to enrich and strengthen our own skill set.

Where to next?

Choose a DBMS

The goal of this course was to stay very focused on SQL and not on a particular DBMS or vendor. But to go further, at some point you have to choose and use a specific database.

• If we already have access to a database, the easy answer is to start with that one
• If the organization already has a data analytics platform with a sandbox for experimentation, dive in
• If one is inclined to do so, one can install a local database on one’s own machine

Options for local installation

Most providers also have sample databases that can be downloaded and installed locally. They provide a set of tables with predefined relationships, filled with sample data that can be used to write SQL, test queries, and experiment without worrying about damaging anything.

Are there more SQL keywords to discover?

Absolutely. But the majority of queries use what we covered in this course.

The most important tip

Before writing SQL, first try to describe what you want from the database in plain language in a single sentence. The most important part of becoming comfortable with SQL is making sure you understand exactly what you’re asking before attempting to write it in SQL. The better you get at thinking through it and articulating it in normal language, the easier it becomes to write SQL.

9. Essential SQL Keywords Summary

Data Manipulation Language (DML) Keywords

Aggregate functions

Keywords DDL (Data Definition Language)

Keywords DCL (Data Control Language)

10. Logical order of a complete SQL SELECT statement

SELECT      [DISTINCT] colonnes [AS alias]
FROM        table [AS alias]
[JOIN       autre_table ON condition_de_jointure]
[WHERE      condition(s)]
[GROUP BY   colonne(s)]
[HAVING     condition_sur_agregat]
[ORDER BY   colonne(s) [ASC|DESC]];

Evaluation order (internal to the SQL engine):

1. FROM (and JOIN) — which tables?
2. WHERE — filter rows
3. GROUP BY — group
4. HAVING — filter groups
5. SELECT — choose columns
6. DISTINCT — eliminate duplicates
7. ORDER BY — sort

The internal evaluation order explains why we cannot use an alias defined in SELECT in the WHERE clause (the alias does not yet exist at this stage), and why we cannot use an aggregate function in WHERE (aggregates are calculated after filtering).

Documentation generated from the content of the “Introduction to SQL” training by Simon Allardyce.

Search Terms

sql · fundamentals · databases · data · join · keywords · aggregate · database · functions · group · language · order · select · aliases · dcl · ddl · delete · dml · joins · modify · null · rows · tables · union