How SQL Works: Statements, Command Types and Query Order

SQL is a small language with a few grammar rules that every query obeys. Learn them once and a whole class of confusing errors disappears: why "Dhaka" works in one database and fails in another, why an alias you just defined is "unknown", why a comment changes a result. This page covers those rules, the five families of SQL commands (with your first CREATE TABLE), and the order in which a database really evaluates a query — which is not the order you write it in.

These rules matter more, not less, in AI work: SQL written by a language model (text-to-SQL) often runs on SQLite and then fails on PostgreSQL for exactly the reasons below, and you are the one who has to spot it.

What you will learn

• Statements and semicolons, keywords and identifiers, case, whitespace and comments
• Single quotes for text and double quotes for names, and how SQLite, MySQL and PostgreSQL differ
• The five families of commands — DDL, DML, DQL, DCL, TCL — with one runnable example each
• Creating a database on a server versus in SQLite, and writing your first CREATE TABLE
• The logical order of a query: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

Statements and semicolons

A statement is one complete command. A semicolon ends it, so one script can hold many statements. This block runs three, and you get three results:

SELECT COUNT(*) AS customers FROM customers;
SELECT COUNT(*) AS products FROM products;
SELECT COUNT(*) AS orders FROM orders;

+-----------+
| customers |
+-----------+
|         8 |
+-----------+
+----------+
| products |
+----------+
|       11 |
+----------+
+--------+
| orders |
+--------+
|     14 |
+--------+

Tools differ in how strict they are. Python's execute() takes one statement, so a trailing ; is optional there. The sqlite3, mysql and psql shells wait until they see a ; before running anything — if the prompt just sits there, you probably forgot it. Between two statements the semicolon is never optional.

Keywords, identifiers and case

A statement is built from three kinds of words: keywords that belong to SQL (SELECT, FROM, WHERE), identifiers that name your things (customers, city), and literals that are values ('Dhaka', 42). Keywords are case-insensitive everywhere, and whitespace — spaces, tabs, new lines — is free. This ugly query works:

select NAME, City
from   CUSTOMERS     where customer_id = 2;

+--------------+------------+
| name         | city       |
+--------------+------------+
| Tanvir Ahmed | Chattogram |
+--------------+------------+

Identifiers are where dialects differ:

-- PostgreSQL
SELECT NAME FROM Customers WHERE customer_id = 1;
SELECT "Name" FROM customers WHERE customer_id = 1;

+--------------+
| name         |
+--------------+
| Nadia Rahman |
+--------------+
Error: column "Name" does not exist

The first query works because PostgreSQL lowercases NAME and Customers; the second asks for a column literally called Name, which does not exist. The MySQL table-name rule bites people who develop on Windows and deploy on Linux. The professional convention avoids all of it: keywords in UPPERCASE, names in lowercase snake_case, one clause per line. That is the style used in this tutorial.

Quotes: 'text' versus "names"

Standard SQL is simple: single quotes are text ('Dhaka'), double quotes are names ("order", "unit price") — needed only when a name has spaces, capitals you want to keep, or clashes with a keyword. To put a single quote inside text, double it:

SELECT 'Rafiq''s order' AS label, "name" FROM customers WHERE customer_id = 4;

+---------------+-------------+
| label         | name        |
+---------------+-------------+
| Rafiq's order | Rafiq Islam |
+---------------+-------------+

Now the trap. For compatibility with old code, SQLite accepts a double-quoted word as text if no column has that name, and MySQL treats double quotes as text by default. So this looks fine in SQLite:

SELECT name FROM customers WHERE city = "Dhaka" ORDER BY name;

-- the same habit, but the "text" happens to be a column name
SELECT COUNT(*) AS customers_in_city FROM customers WHERE city = "city";

+---------------+
| name          |
+---------------+
| Farhana Akter |
| Mitu Das      |
| Nadia Rahman  |
+---------------+
+-------------------+
| customers_in_city |
+-------------------+
|                 7 |
+-------------------+

The first query works by accident. In the second, "city" is the column, so the condition is city = city, true for every customer who has a city: 7 rows instead of 0, and no error. PostgreSQL follows the standard and refuses the first one outright:

-- PostgreSQL
SELECT name FROM customers WHERE city = "Dhaka" ORDER BY name;

Error: column "Dhaka" does not exist

The rule: always single quotes for text. For names, MySQL uses backticks (`order`) and SQL Server square brackets ([order]); SQLite accepts all three styles.

Comments

Two kinds work everywhere: -- to the end of the line, and /* … */ across several lines. Use them to explain why a query does something:

/* Products worth reordering:
   sold out or running low. */
SELECT name, stock
FROM products
WHERE stock < 10   -- NULL stock (courses, gift card) is not "low"
ORDER BY stock, name;

+-----------------------------+-------+
| name                        | stock |
+-----------------------------+-------+
| USB-C Hub                   |     0 |
| 27-inch Monitor             |     5 |
| Noise-Cancelling Headphones |     8 |
+-----------------------------+-------+

MySQL has a quirk: -- starts a comment only when a space follows it (it also accepts #). Without the space, --2 means "minus minus 2":

SELECT 5 --2
    AS surprise;

+----------+
| surprise |
+----------+
|        5 |
+----------+

-- MySQL
SELECT 5 --2
    AS surprise;

+----------+
| surprise |
+----------+
|        7 |
+----------+

SQLite read --2 as a comment and returned 5; MySQL computed 5 − (−2) = 7. Always put a space after --.

The five families of commands

SQL statements fall into five groups by what they act on:

(Some books count SELECT as DML. The labels matter less than knowing which commands change structure, which change data, and which only read.) One example of each follows, building a reviews table — the kind of text you might later feed to a sentiment model.

DDL: your first CREATE TABLE

CREATE TABLE reviews (
    review_id   INTEGER PRIMARY KEY,
    product_id  INTEGER NOT NULL,
    customer_id INTEGER NOT NULL,
    rating      INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5),
    body        TEXT,
    FOREIGN KEY (product_id) REFERENCES products (product_id),
    FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);

Each line inside the brackets is a column — a name, a type, then optional rules (constraints) — or a rule about the whole table. PRIMARY KEY and FOREIGN KEY … REFERENCES are the keys from Tables, Keys and Relationships; NOT NULL forbids missing values; CHECK rejects ratings outside 1–5. The page CREATE, ALTER, DROP and Constraints covers all of them.

Write foreign keys as separate FOREIGN KEY (col) REFERENCES table (col) lines, as here and in shop.sql. The shorter form on the column itself (product_id INTEGER REFERENCES products (product_id)) works in SQLite and PostgreSQL, but MySQL 8 accepts it and then silently ignores it — no foreign key is created.

DML: put rows in

INSERT INTO reviews (review_id, product_id, customer_id, rating, body) VALUES
    (1, 1, 1, 5, 'Clear and practical, perfect first book'),
    (2, 3, 6, 4, 'Great keys, a bit loud');

DQL: read them back

SELECT review_id, product_id, rating, body
FROM reviews
ORDER BY review_id;

+-----------+------------+--------+-----------------------------------------+
| review_id | product_id | rating | body                                    |
+-----------+------------+--------+-----------------------------------------+
|         1 |          1 |      5 | Clear and practical, perfect first book |
|         2 |          3 |      4 | Great keys, a bit loud                  |
+-----------+------------+--------+-----------------------------------------+

TCL: all or nothing

A transaction groups statements so that they all take effect or none do. BEGIN starts one; ROLLBACK undoes everything since; COMMIT would keep it:

BEGIN;
DELETE FROM reviews;
SELECT COUNT(*) AS reviews_inside_transaction FROM reviews;
ROLLBACK;
SELECT COUNT(*) AS reviews_after_rollback FROM reviews;

+----------------------------+
| reviews_inside_transaction |
+----------------------------+
|                          0 |
+----------------------------+
+------------------------+
| reviews_after_rollback |
+------------------------+
|                      2 |
+------------------------+

DCL: permissions (servers only)

SQLite has no users — whoever can open the file can read it. Server databases have accounts and roles, and GRANT decides what each may do. A read-only role for an analytics dashboard or an LLM agent, on PostgreSQL:

-- PostgreSQL
DROP ROLE IF EXISTS shop_reader;
CREATE ROLE shop_reader;
GRANT SELECT ON customers TO shop_reader;

SELECT grantee, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE grantee = 'shop_reader';

-- tidy up
REVOKE SELECT ON customers FROM shop_reader;
DROP ROLE shop_reader;

+-------------+------------+----------------+
| grantee     | table_name | privilege_type |
+-------------+------------+----------------+
| shop_reader | customers  | SELECT         |
+-------------+------------+----------------+

The role may read customers and nothing else. Giving an AI agent a role like this, instead of the owner's account, is the first line of defence in a text-to-SQL app; the advanced tutorial builds on it.

Creating a database: a server versus a file

A database server holds many databases, so you create one with SQL. In MySQL, DATABASE and SCHEMA mean the same thing:

-- MySQL
CREATE DATABASE bitbyte_practice;
SHOW DATABASES LIKE 'bitbyte_practice';
DROP DATABASE bitbyte_practice;

+-----------------------------+
| Database (bitbyte_practice) |
+-----------------------------+
| bitbyte_practice            |
+-----------------------------+

-- PostgreSQL
CREATE DATABASE bitbyte_practice;
SELECT datname FROM pg_database WHERE datname = 'bitbyte_practice';
DROP DATABASE bitbyte_practice;

+------------------+
| datname          |
+------------------+
| bitbyte_practice |
+------------------+

To work inside the new database you switch to it: USE bitbyte_practice; in MySQL, or a new connection in PostgreSQL (\c bitbyte_practice in psql). In PostgreSQL a database also contains schemas (the default is public) — a level MySQL and SQLite do not have.

SQLite has no CREATE DATABASE. A database is a file, and connecting to a file that does not exist creates it:

import os
import sqlite3

print("before:", os.path.exists("practice.db"))
db = sqlite3.connect("practice.db")
db.execute("CREATE TABLE notes (note_id INTEGER PRIMARY KEY, body TEXT)")
db.close()
print("after: ", os.path.exists("practice.db"))

before: False
after:  True

To delete a SQLite database, delete the file. (SQLite can also open a second file inside one connection with ATTACH DATABASE 'practice.db' AS practice;, useful for copying data between files.)

Written order versus logical order

Here is a query that uses every main clause of a single-table query — "counting only products over 2000 taka, which category has the most of them (ignoring categories with fewer than two)?":

SELECT category_id, COUNT(*) AS n
FROM products
WHERE price > 2000
GROUP BY category_id
HAVING COUNT(*) >= 2
ORDER BY n DESC, category_id
LIMIT 1;

+-------------+---+
| category_id | n |
+-------------+---+
|           2 | 3 |
+-------------+---+

You write SELECT first, but the database evaluates it almost last. The logical order defines what each clause can see:

  written order        logical order         what happens at this step
  -------------        -------------------   -------------------------------------------
  1  SELECT            1  FROM products      take all 11 rows of the table
  2  FROM              2  WHERE price > 2000 keep rows that pass         -> 7 rows
  3  WHERE             3  GROUP BY           one group per category_id  -> 4 groups
  4  GROUP BY          4  HAVING             keep groups with 2 or more -> 3 groups
  5  HAVING            5  SELECT             compute the output columns; the alias n is born
  6  ORDER BY          6  ORDER BY           sort, and here the alias n can be used
  7  LIMIT             7  LIMIT              keep the first row         -> 1 row

(The query planner may physically do the work in a cleverer order, but the result is always as if it followed these steps.) The most useful consequence: an alias created in SELECT (step 5) does not exist yet in WHERE (step 2), but does in ORDER BY (step 6):

-- PostgreSQL
SELECT name, price * 2 AS double_price
FROM products
WHERE double_price > 20000
ORDER BY double_price DESC;

Error: column "double_price" does not exist

-- MySQL
SELECT name, price * 2 AS double_price
FROM products
WHERE double_price > 20000
ORDER BY double_price DESC;

Error: ERROR 1054: Unknown column 'double_price' in 'where clause'

SQLite is lenient and accepts this query — which is precisely why code (or LLM-generated SQL) tested only on SQLite breaks in production. The portable fix is to repeat the expression in WHERE; using the alias in ORDER BY is fine everywhere:

-- PostgreSQL
SELECT name, price * 2 AS double_price
FROM products
WHERE price * 2 > 20000
ORDER BY double_price DESC;

+-------------------------+--------------+
| name                    | double_price |
+-------------------------+--------------+
| 27-inch Monitor         |        37000 |
| AI Engineering Bootcamp |        30000 |
+-------------------------+--------------+

Common mistakes

• Double quotes around text. It works in SQLite and MySQL, fails in PostgreSQL, and silently changes meaning when the word is also a column name. Use single quotes:
  SQLCopy

  SELECT COUNT(*) AS dhaka_customers FROM customers WHERE city = 'Dhaka';

  OutputCopy

  +-----------------+
  | dhaka_customers |
  +-----------------+
  |               3 |
  +-----------------+

• A missing semicolon between statements. The database reads the two as one broken statement:
  SQLCopy

  SELECT name FROM categories WHERE category_id = 1
  SELECT name FROM categories WHERE category_id = 2;

  OutputCopy

  Error: near "SELECT": syntax error

  Fix: end every statement with ;.
• Using a keyword as a name. order is a keyword (ORDER BY), so this fails:
  SQLCopy

  CREATE TABLE order (order_id INTEGER PRIMARY KEY);

  OutputCopy

  Error: near "order": syntax error

  Fix: choose a plain name (orders, as the shop does); quoting it as "order" works but makes every future query awkward.
• Using a SELECT alias in WHERE. Works only in SQLite. Repeat the expression, as shown above.
• --comment without a space. Harmless in SQLite and PostgreSQL, arithmetic in MySQL. Write -- comment.

Try it yourself

1. Easy: Name the family (DDL, DML, DQL, DCL or TCL) of each: (a) DROP TABLE reviews; (b) UPDATE products SET stock = 30 WHERE product_id = 1; (c) COMMIT; (d) REVOKE SELECT ON customers FROM shop_reader; (e) SELECT * FROM orders;
2. Medium: Create a table llm_calls for logging calls to a language model: call_id (primary key), model (text, required), prompt_tokens and completion_tokens (integers, required) and rating (1 to 5, optional). Insert three calls and select them with a computed total_tokens column.
3. Hard: This query runs on SQLite but fails on PostgreSQL. Explain why using the logical order, then fix it and run it on PostgreSQL:
   SELECT name, price * stock AS stock_value FROM products WHERE stock_value > 50000 ORDER BY stock_value DESC, name;

Answers

1. (a) DDL, (b) DML, (c) TCL, (d) DCL, (e) DQL.

2. One possible table; rating may be NULL because not every call gets rated:

CREATE TABLE llm_calls (
    call_id           INTEGER PRIMARY KEY,
    model             TEXT NOT NULL,
    prompt_tokens     INTEGER NOT NULL,
    completion_tokens INTEGER NOT NULL,
    rating            INTEGER CHECK (rating BETWEEN 1 AND 5)
);
INSERT INTO llm_calls VALUES
    (1, 'model-small', 420, 180, 4),
    (2, 'model-large', 950, 610, 5),
    (3, 'model-small', 300, 95, NULL);

SELECT call_id, model, prompt_tokens + completion_tokens AS total_tokens, rating
FROM llm_calls
ORDER BY call_id;

+---------+-------------+--------------+--------+
| call_id | model       | total_tokens | rating |
+---------+-------------+--------------+--------+
|       1 | model-small |          600 |      4 |
|       2 | model-large |         1560 |      5 |
|       3 | model-small |          395 | NULL   |
+---------+-------------+--------------+--------+

3. WHERE (step 2) runs before SELECT (step 5) creates stock_value, so PostgreSQL does not know the name yet. Repeat the expression in WHERE; keep the alias in ORDER BY. Products with NULL stock drop out, because NULL × price is NULL:

-- PostgreSQL
SELECT name, price * stock AS stock_value
FROM products
WHERE price * stock > 50000
ORDER BY stock_value DESC, name;

+-----------------------------+-------------+
| name                        | stock_value |
+-----------------------------+-------------+
| 27-inch Monitor             |       92500 |
| Noise-Cancelling Headphones |       60000 |
| Mechanical Keyboard         |       54000 |
| Wireless Mouse              |       54000 |
+-----------------------------+-------------+

Summary

• A statement ends with ;. Keywords are case-insensitive, whitespace is free; write keywords in UPPERCASE and names in lowercase.
• Single quotes for text, double quotes for names. SQLite and MySQL tolerate "text"; PostgreSQL does not, and in SQLite it can silently compare a column with itself.
• Comments are -- … and /* … */; in MySQL put a space after --.
• DDL changes structure, DML changes rows, DQL reads, DCL controls permissions (servers only), TCL groups changes into transactions.
• Servers use CREATE DATABASE; in SQLite a database is a file created by connecting to it.
• Logical order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. Aliases exist from SELECT on, so use them in ORDER BY, not in WHERE.

Next: SELECT: Columns, Expressions, Aliases and DISTINCT takes the first clause you write — and the fifth the database runs — and shows everything it can do: choosing columns, calculating new ones, naming them, and removing duplicates.