SQL Introduction

SQL (Structured Query Language) is the standard language used to create, read, update, and delete data stored in a relational database. Instead of telling the computer how to fetch data step by step, you describe what data you want, and the database engine figures out the most efficient way to get it. Almost every application you use — banking apps, online stores, social networks — stores its data in a relational database and uses SQL to talk to it, which is why SQL remains one of the most valuable and widely used skills in software.

Overview: How SQL and Relational Databases Work

A relational database organizes data into tables. Each table represents one type of entity — for example, a students table or an orders table. A table is made up of rows (also called records, one per real-world item) and columns (also called fields, one per attribute of that item, such as name or grade). Every column has a data type, such as INTEGER, TEXT, or REAL, which constrains what kind of value can be stored in it.

SQL itself is declarative: you write a statement describing the result you want, and a piece of software called the query engine (or query optimizer/planner) decides the actual execution plan — which table to scan first, whether to use an index, how to join tables together, and so on. This is different from procedural languages like Python or JavaScript, where you write out every step yourself. Two people can write different-looking SQL that produces the same result, and the engine may even choose different internal strategies to run them, but the output is guaranteed to be correct according to the query’s logic.

SQL is implemented by many different database systems — SQLite, MySQL, PostgreSQL, Microsoft SQL Server, Oracle, and others. They all support a large common core defined by the SQL standard (ISO/IEC 9075), which is what this lesson focuses on, but each also has its own extensions and quirks. We’ll note the most common differences as they come up.

Categories of SQL Commands

SQL statements are traditionally grouped into a few categories based on what they do:

Category	Stands for	Purpose	Example commands
DDL	Data Definition Language	Defines the structure of the database	CREATE, ALTER, DROP
DML	Data Manipulation Language	Changes the data inside tables	INSERT, UPDATE, DELETE
DQL	Data Query Language	Reads data from tables	SELECT
DCL	Data Control Language	Controls permissions/access	GRANT, REVOKE
TCL	Transaction Control Language	Groups statements into safe units of work	COMMIT, ROLLBACK

As a beginner, you’ll spend most of your early lessons on DDL (building tables) and DQL (the SELECT statement), since reading data is the most common operation you’ll perform.

Syntax

A basic SQL statement is made up of keywords (like SELECT, FROM, WHERE), identifiers (table and column names), literal values, and operators, usually terminated with a semicolon ;. The general shape of a query looks like this:

SELECT column1, column2
FROM table_name
WHERE condition
ORDER BY column1;

• SELECT — lists the columns you want back. SELECT * means “all columns.”
• FROM — names the table to read from.
• WHERE — an optional filter; only rows matching the condition are kept.
• ORDER BY — an optional clause that sorts the result rows.
• ; — the statement terminator. Most tools require it when running multiple statements in sequence.

A few important rules about SQL syntax:

• SQL keywords are case-insensitive — SELECT, select, and Select all work the same. This lesson uses uppercase for keywords purely as a readability convention.
• String data stored in your tables generally is case-sensitive when compared (e.g. 'Lagos' does not match 'lagos' in most default configurations), even though the keywords around it aren’t.
• Text literals use single quotes ('like this'). Double quotes are typically reserved for identifiers (table/column names) in standard SQL, though some databases like MySQL treat double quotes as strings by default — this is a common source of confusion when moving between systems.

Examples

Let’s build a small table and run some real queries against it. First, we create the table and add a few rows.

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT,
  grade INTEGER,
  city TEXT
);

INSERT INTO students (id, name, grade, city) VALUES
  (1, 'Amara', 9, 'Lagos'),
  (2, 'Liam', 10, 'Dublin'),
  (3, 'Sofia', 9, 'Madrid'),
  (4, 'Kenji', 11, 'Tokyo');

SELECT * FROM students;

Result:

id	name	grade	city
1	Amara	9	Lagos
2	Liam	10	Dublin
3	Sofia	9	Madrid
4	Kenji	11	Tokyo

CREATE TABLE defines the structure, INSERT adds data, and SELECT * FROM students; retrieves every column of every row. Without an ORDER BY clause, most engines (including SQLite in this simple case) return rows in the order they were physically inserted, but the SQL standard does not guarantee any particular order unless you explicitly ask for one with ORDER BY — never rely on unspecified ordering in real applications.

Now let’s filter and sort the same data:

SELECT name, grade
FROM students
WHERE grade = 9
ORDER BY name ASC;

Result:

name	grade
Amara	9
Sofia	9

The WHERE grade = 9 clause keeps only rows where the grade column equals 9 (Amara and Sofia), and ORDER BY name ASC sorts the surviving rows alphabetically by name. ASC (ascending) is the default sort direction, so it could be omitted, but writing it explicitly makes the intent clear.

Finally, let’s use an aggregate function to summarize the data:

SELECT grade, COUNT(*) AS num_students
FROM students
GROUP BY grade
ORDER BY grade;

Result:

grade	num_students
9	2
10	1
11	1

GROUP BY grade collapses the rows into one group per distinct grade value, and COUNT(*) counts how many rows fall into each group. Grade 9 has two students (Amara and Sofia), while grades 10 and 11 have one each. This kind of grouping and aggregation is one of SQL’s most powerful features and will be covered in depth in later lessons.

How a Query Executes Step by Step (Under the Hood)

Although you write a SELECT statement with SELECT listed first, the database engine does not execute the clauses in the order you typed them. Instead, it follows a logical processing order roughly like this:

1. FROM — identify the source table(s) (and perform any joins).
2. WHERE — filter out rows that don’t match the condition.
3. GROUP BY — collapse the remaining rows into groups, if grouping is used.
4. HAVING — filter groups (not individual rows), if used.
5. SELECT — compute the final list of columns/expressions to return.
6. ORDER BY — sort the result set.
7. LIMIT — cut the result down to a specific number of rows, if used.

This is why you can reference a column in WHERE that isn’t in your SELECT list, and why aliases created in SELECT (like num_students above) generally can’t be used inside WHERE — the engine hasn’t computed the SELECT list yet at the point it evaluates WHERE. Under the hood, the engine also decides whether to use an index (a fast lookup structure) instead of scanning every row, based on the table’s size and the conditions in your query — a topic covered fully in the indexing lessons later in this course.

Common Mistakes

Mistake 1: Comparing to NULL with = instead of IS NULL. NULL represents “unknown” or “missing” data, and in SQL, any comparison involving NULL using = evaluates to unknown (not true), so it silently returns zero rows instead of raising an error.

CREATE TABLE contacts (id INTEGER, name TEXT, city TEXT);
INSERT INTO contacts (id, name, city) VALUES
  (1, 'Ana', 'Lagos'),
  (2, 'Ben', NULL),
  (3, 'Cleo', 'Dublin');

-- Wrong: this returns NO rows, even though Ben has a NULL city
SELECT * FROM contacts WHERE city = NULL;

The corrected version uses the IS NULL operator, which is specifically designed to test for missing values:

SELECT * FROM contacts WHERE city IS NULL;

Result: one row — 2, Ben, NULL.

Mistake 2: Unterminated string literals. Forgetting the closing quote around a text value produces a genuine syntax error, because the parser keeps consuming the rest of the statement as part of the string.

SELECT * FROM students WHERE city = 'Lagos;

This fails with a syntax/parse error (an unterminated string literal). Always make sure every opening quote has a matching closing quote: WHERE city = 'Lagos'.

Best Practices

• Write SQL keywords in UPPERCASE and identifiers in lowercase or snake_case — it’s not required, but it makes queries far easier to read and debug.
• Always end statements with a semicolon, especially when running multiple statements together.
• Use IS NULL / IS NOT NULL to test for missing values — never = NULL.
• Never rely on row order unless you use ORDER BY — unordered results can change between runs or database versions.
• Prefer selecting only the columns you need (SELECT name, grade) over SELECT * in real applications — it’s more efficient and makes your intent explicit.
• Learn the logical query processing order (FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT); it explains a huge number of “why doesn’t this work” questions.
• Remember that SQL dialects differ slightly between systems (e.g. LIMIT in SQLite/MySQL/PostgreSQL vs TOP in SQL Server, or AUTOINCREMENT vs AUTO_INCREMENT vs SERIAL) — check your specific database’s documentation for edge cases.

Practice Exercises

• Exercise 1: Using the students table from the examples above, write a query that returns only the name and city columns for students in grade 10 or higher.
• Exercise 2: Write a query against the students table that returns all rows sorted by grade in descending order, then by name ascending within the same grade.
• Exercise 3: Using the contacts table from the Common Mistakes section, write a query that returns only the contacts whose city is not missing (hint: think about which operator correctly tests for the presence of a value).

Summary

• SQL is a declarative language for defining, querying, and modifying data in a relational database made up of tables, rows, and columns.
• SQL statements fall into categories: DDL (structure), DML (modify data), DQL (query data), DCL (permissions), and TCL (transactions).
• A query’s logical execution order is FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT, regardless of the order clauses are typed in.
• Keywords are case-insensitive, but string data usually is not; text literals use single quotes.
• Always use IS NULL/IS NOT NULL for missing values, and always use ORDER BY if row order matters to you.
• SQL dialects differ slightly between database systems (SQLite, MySQL, PostgreSQL, SQL Server), so watch for portability quirks as you learn.