🗃️ SQL Syntax and Queries

What SQL Is (and Isn't)

SQL (Structured Query Language, often pronounced "sequel") is the standard language for talking to relational databases — databases that store data in tables of rows and columns, like a collection of very strict spreadsheets that know how to reference each other. Born at IBM in the 1970s from Edgar Codd's relational model, SQL is now an ISO standard implemented by PostgreSQL, MySQL/MariaDB, SQL Server, Oracle, and the tiny-but-mighty SQLite.

The single most important idea about SQL is that it is declarative. You describe what data you want, not how to fetch it. You never write a loop to walk rows one at a time; you state a condition and the database's query planner figures out the most efficient way to satisfy it.

💡 Analogy — the library assistant: Imagine a library with millions of books across many floors. You don't wander the stacks yourself. You hand a precise request to an expert assistant — "every science-fiction title published since 2020, newest first" — and they return exactly that. SQL is how you phrase the request; the database is the assistant who knows where everything lives and how to get it fastest.

The Five Sublanguages of SQL

People say "SQL" as if it were one thing, but the commands split into five functional groups. Knowing which group a keyword belongs to helps you reason about what it does — and how dangerous it is.

This lesson lives almost entirely in DQL — the humble SELECT. It's the command you'll write most often in your career by a wide margin, so it's worth learning deeply. The next lesson covers the DML trio (INSERT/UPDATE/DELETE) that make up CRUD.

Anatomy of a Query

A SELECT query is built from clauses that always appear in the same written order. Not every clause is required, but when present they must follow this sequence:

SELECT   column_a, column_b        -- which columns to return
FROM     table_name                -- where the data lives
WHERE    condition                 -- which rows to keep
ORDER BY column_a DESC             -- how to sort them
LIMIT    10;                        -- how many to return

Syntax rules that trip people up

• Keywords are case-insensitive (SELECT = select), but by convention keywords are uppercased. Table and column names may be case-sensitive depending on the database and operating system.
• Whitespace is ignored, so indent generously for readability.
• String literals use single quotes: 'USA'. Double quotes mean something different — a quoted identifier (a column or table name).
• Statements end with a semicolon ; — required when running several statements together.
• Comments: -- to end of line, or /* block */.

SELECT: Projecting Columns

Choosing which columns to return is called projection. Start with everything, then learn to be specific.

-- Every column of every row (fine for exploring, avoid in production)
SELECT * FROM customers;

-- Only the columns you actually need (faster, clearer, safer)
SELECT customer_name, email
FROM customers;

Column aliases

Aliases rename a column in the result set — useful for readable headers or for labeling computed values.

SELECT
    first_name AS "First Name",
    last_name  AS "Last Name",
    unit_price * units_in_stock AS inventory_value   -- a computed column needs a name
FROM products;

The AS keyword is optional — unit_price "Price" works too — but including it makes your intent obvious. Note that because SELECT runs after WHERE (Figure 1), you usually cannot filter on inventory_value in the same query's WHERE clause; you'd repeat the expression or wrap the query.

Filtering with WHERE

The WHERE clause keeps only the rows that make its condition true. This is where SQL earns its keep.

Comparison operators

Combining conditions with AND, OR, NOT

-- Both must be true
SELECT product_name, unit_price, category
FROM products
WHERE unit_price > 50 AND category = 'Electronics';

-- Either may be true
SELECT first_name, last_name
FROM employees
WHERE department = 'Sales' OR department = 'Marketing';

-- Negate a condition
SELECT order_id, status
FROM orders
WHERE NOT status = 'Delivered';

SELECT product_id, product_name, unit_price
FROM products
WHERE (category = 'Beverages' OR category = 'Condiments')
  AND unit_price > 10
  AND units_in_stock > 0;

BETWEEN, IN, LIKE & NULL

Four tools that make filters shorter and more expressive.

BETWEEN — an inclusive range

SELECT product_name, unit_price
FROM products
WHERE unit_price BETWEEN 10 AND 20;   -- same as >= 10 AND <= 20

-- Works for dates too
SELECT order_id, order_date
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31';

IN — match any value in a list

SELECT customer_name, country
FROM customers
WHERE country IN ('USA', 'Canada', 'Mexico');   -- shorthand for three ORs

-- IN also accepts a subquery
SELECT product_name
FROM products
WHERE category_id IN (
    SELECT category_id FROM categories WHERE category_name = 'Beverages'
);

LIKE — pattern matching on text

Two wildcards: % matches any run of characters (including none), and _ matches exactly one character.

SELECT customer_name FROM customers WHERE customer_name LIKE 'A%';      -- starts with A
SELECT product_name  FROM products  WHERE product_name  LIKE '%coffee%'; -- contains coffee
SELECT last_name     FROM employees WHERE last_name     LIKE '_____';    -- exactly 5 chars

NULL — the absence of a value

NULL means "unknown," not "zero" or "empty string." Because unknown compared to anything is still unknown, = NULL never matches. Use IS NULL / IS NOT NULL.

-- Employees with no assigned manager
SELECT employee_id, first_name, last_name
FROM employees
WHERE manager_id IS NULL;

-- Customers who DO have a phone on file
SELECT customer_name, phone
FROM customers
WHERE phone IS NOT NULL;

Sorting, DISTINCT & Paging

ORDER BY

Result order is not guaranteed unless you ask for it. ASC (ascending) is the default; DESC reverses it. You can sort by several columns — the first is primary, the next breaks ties.

-- Alphabetical by name
SELECT customer_name, city FROM customers ORDER BY customer_name;

-- Group by department, highest paid first within each
SELECT first_name, last_name, department, salary
FROM employees
ORDER BY department ASC, salary DESC;

DISTINCT — remove duplicate rows

DISTINCT collapses identical result rows into one. It applies to the whole selected row, not a single column.

SELECT DISTINCT country FROM customers ORDER BY country;
SELECT DISTINCT city, country FROM customers ORDER BY country, city;

Limiting and paging results

The standard evolved unevenly, so the syntax differs by engine. PostgreSQL, MySQL, and SQLite all use LIMIT; SQL Server uses TOP; strict ANSI SQL and Oracle 12c+ use FETCH.

-- PostgreSQL / MySQL / SQLite
SELECT product_name, unit_price FROM products
ORDER BY unit_price DESC
LIMIT 10;

-- Pagination: page 2 of 10 rows (skip the first 10)
SELECT product_name FROM products
ORDER BY product_name
LIMIT 10 OFFSET 10;

-- SQL Server
SELECT TOP 10 product_name, unit_price FROM products ORDER BY unit_price DESC;

-- ANSI SQL / Oracle 12c+
SELECT product_name FROM products ORDER BY unit_price DESC
FETCH FIRST 10 ROWS ONLY;

Hands-on Exercise

CREATE TABLE books (
    book_id      INTEGER PRIMARY KEY,
    title        VARCHAR(120) NOT NULL,
    author       VARCHAR(80)  NOT NULL,
    genre        VARCHAR(40),
    price        DECIMAL(6,2) NOT NULL,
    published    DATE,
    copies_left  INTEGER      NOT NULL
);

INSERT INTO books (book_id, title, author, genre, price, published, copies_left) VALUES
(1, 'The Silent Orbit',    'A. Vega',    'Sci-Fi',  14.99, '2023-04-10', 3),
(2, 'Garden of Bytes',     'M. Cruz',    'Sci-Fi',  22.50, '2021-09-01', 0),
(3, 'Quiet Mornings',      'A. Vega',    'Poetry',   9.95, '2024-01-15', 12),
(4, 'The Last Ledger',     'R. Santos',  'Mystery', 18.00, '2020-06-30', 4),
(5, 'Debugging the Heart', 'M. Cruz',    'Romance', 12.99, '2024-03-05', 7);

-- 1
SELECT title, price FROM books ORDER BY price DESC;

-- 2
SELECT title, price FROM books
WHERE genre = 'Sci-Fi' AND price < 20;

-- 3
SELECT DISTINCT genre FROM books ORDER BY genre;

-- 4
SELECT title, copies_left FROM books
WHERE copies_left BETWEEN 1 AND 4;

-- 5  (PostgreSQL)
SELECT title FROM books WHERE title ILIKE '%the%';
-- Portable form:
SELECT title FROM books WHERE LOWER(title) LIKE '%the%';

Best Practices

Summary & Quiz

📚 Further Reading

• PostgreSQL — Querying a Table
• MySQL — SELECT Statement reference
• SQLite — SELECT syntax

🚀 What's Next?

You can now read data fluently. Next up is CRUD Operations in SQL, where you'll learn to create, update, and delete rows with INSERT, UPDATE, and DELETE — and how to avoid the classic mistake that wipes an entire table.