SQL Basics

April 3, 2026

Introduction

SQL (Structured Query Language) is the standard language for interacting with relational databases. Whether you’re building a web application, analyzing data, or preparing for a technical interview, SQL is a skill you’ll use constantly. It lets you create tables, insert data, query records, update values, and delete rows — all with a readable, declarative syntax.

SQL is pronounced either “sequel” or “S-Q-L” — both are widely accepted. The language has been around since the 1970s and remains the most widely used database language in the world.

Setting Up

The easiest way to get started is with SQLite, a lightweight database that runs as a single file on your machine. There’s no server to install or configure.

SQLite comes pre-installed on macOS. Open a terminal and run:

sqlite3 tutorial.db

Install via your package manager:

# Ubuntu/Debian
sudo apt install sqlite3

# Fedora
sudo dnf install sqlite

sqlite3 tutorial.db

Download the precompiled binaries from sqlite.org, extract them, and add the folder to your PATH. Then open a terminal:

sqlite3 tutorial.db

This creates (or opens) a database file called tutorial.db and drops you into the SQLite shell where you can run SQL commands.

Creating a Table

Tables are the foundation of a relational database. Each table has columns (fields) with defined types, and rows (records) that hold the actual data.

CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    age INTEGER,
    enrolled_date TEXT DEFAULT CURRENT_DATE
);

This creates a students table with five columns. id is the primary key — a unique identifier for each row. NOT NULL means the column can’t be empty, and UNIQUE ensures no two rows share the same email.

Common SQL data types include INTEGER, TEXT, REAL (floating point), and BLOB (binary data). Other databases like PostgreSQL and MySQL offer more specific types like VARCHAR, TIMESTAMP, and BOOLEAN.

Inserting Data

Use INSERT INTO to add rows to a table:

INSERT INTO students (name, email, age) VALUES ('Alice', 'alice@example.com', 22);
INSERT INTO students (name, email, age) VALUES ('Bob', 'bob@example.com', 25);
INSERT INTO students (name, email, age) VALUES ('Charlie', 'charlie@example.com', 20);
INSERT INTO students (name, email, age) VALUES ('Diana', 'diana@example.com', 23);

We don’t need to specify id (it auto-increments) or enrolled_date (it defaults to today).

Querying Data with SELECT

SELECT is the most commonly used SQL statement. It retrieves data from one or more tables.

Select all columns

SELECT * FROM students;

id	name	email	age	enrolled_date
1	Alice	alice@example.com	22	2026-04-03
2	Bob	bob@example.com	25	2026-04-03
3	Charlie	charlie@example.com	20	2026-04-03
4	Diana	diana@example.com	23	2026-04-03

Select specific columns

SELECT name, age FROM students;

name	age
Alice	22
Bob	25
Charlie	20
Diana	23

Filtering with WHERE

WHERE lets you filter rows based on conditions:

SELECT name, age FROM students WHERE age > 21;

name	age
Alice	22
Bob	25
Diana	23

You can combine conditions with AND and OR:

SELECT * FROM students WHERE age >= 22 AND name != 'Bob';

id	name	email	age	enrolled_date
1	Alice	alice@example.com	22	2026-04-03
4	Diana	diana@example.com	23	2026-04-03

Other useful operators include LIKE for pattern matching and IN for matching a set of values:

-- Names starting with 'A'
SELECT * FROM students WHERE name LIKE 'A%';

-- Specific ages
SELECT * FROM students WHERE age IN (20, 25);

Sorting with ORDER BY

ORDER BY sorts results by one or more columns. The default is ascending (ASC). Use DESC for descending:

SELECT name, age FROM students ORDER BY age DESC;

name	age
Bob	25
Diana	23
Alice	22
Charlie	20

You can also limit the number of results:

SELECT name, age FROM students ORDER BY age DESC LIMIT 2;

Updating Data

UPDATE modifies existing rows. Always use a WHERE clause — without it, every row in the table gets updated.

UPDATE students SET age = 26 WHERE name = 'Bob';

Running UPDATE or DELETE without a WHERE clause affects every row in the table. Always double-check your conditions before executing these statements.

Deleting Data

DELETE removes rows from a table:

DELETE FROM students WHERE name = 'Charlie';

To verify:

SELECT * FROM students;

id	name	email	age	enrolled_date
1	Alice	alice@example.com	22	2026-04-03
2	Bob	bob@example.com	26	2026-04-03
4	Diana	diana@example.com	23	2026-04-03

A Preview of JOINs

Real applications use multiple related tables. For example, a courses table and an enrollments table that links students to courses:

CREATE TABLE courses (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL
);

CREATE TABLE enrollments (
    student_id INTEGER,
    course_id INTEGER,
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

INSERT INTO courses (title) VALUES ('Intro to SQL');
INSERT INTO courses (title) VALUES ('Web Development');

INSERT INTO enrollments (student_id, course_id) VALUES (1, 1);
INSERT INTO enrollments (student_id, course_id) VALUES (1, 2);
INSERT INTO enrollments (student_id, course_id) VALUES (2, 1);

A JOIN combines rows from multiple tables based on a related column:

SELECT students.name, courses.title
FROM enrollments
JOIN students ON enrollments.student_id = students.id
JOIN courses ON enrollments.course_id = courses.id;

name	title
Alice	Intro to SQL
Alice	Web Development
Bob	Intro to SQL

We’ll cover JOINs in depth in a dedicated article.

Practical Example: Building a Bookstore Database

Let’s put it all together by building a small bookstore inventory:

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    author TEXT NOT NULL,
    price REAL,
    stock INTEGER DEFAULT 0
);

INSERT INTO books (title, author, price, stock) VALUES ('The Pragmatic Programmer', 'David Thomas', 49.99, 12);
INSERT INTO books (title, author, price, stock) VALUES ('Clean Code', 'Robert Martin', 39.99, 8);
INSERT INTO books (title, author, price, stock) VALUES ('Design Patterns', 'Gang of Four', 54.99, 3);
INSERT INTO books (title, author, price, stock) VALUES ('Refactoring', 'Martin Fowler', 44.99, 15);
INSERT INTO books (title, author, price, stock) VALUES ('The Mythical Man-Month', 'Fred Brooks', 29.99, 6);

-- Books under $45, sorted by price
SELECT title, author, price FROM books
WHERE price < 45.00
ORDER BY price;

-- Total inventory value
SELECT SUM(price * stock) AS total_value FROM books;

-- Books low on stock
SELECT title, stock FROM books WHERE stock < 5;

-- Apply a 10% discount to books over $50
UPDATE books SET price = price * 0.9 WHERE price > 50.00;

-- Remove out-of-stock books (none in this case, but the pattern matters)
DELETE FROM books WHERE stock = 0;

Summary

Here’s a quick reference of the SQL statements covered:

Statement	Purpose
`CREATE TABLE`	Define a new table
`INSERT INTO`	Add rows
`SELECT`	Query data
`WHERE`	Filter rows
`ORDER BY`	Sort results
`UPDATE`	Modify existing rows
`DELETE`	Remove rows
`JOIN`	Combine related tables

SQL is a deep language with much more to explore — aggregate functions, subqueries, window functions, and indexing. But with these fundamentals, you can create, populate, query, and manage a database for any small project.

Table of Contents