The Project | About Us | Contribute | Donations | License

HOME

Before learning what each SQL statement means it's worth learning how it is written. Just as in any language, there are spelling and grammar rules that, if you don't know them, will cost you hours staring at cryptic error messages. This lesson takes a SQL statement apart into its elementary pieces —keywords, identifiers, literals, operators and expressions— and explains the rules of the language: the semicolon, the treatment of upper and lower case, comments, professional style conventions, operator precedence and, very importantly, how to interpret a PostgreSQL error message so you can pinpoint the fault in seconds.

Here we'll use very simple statements as a vehicle. The goal is not yet to learn what SELECT or WHERE do —that's module 2—; the goal is to understand the spelling rules that any SQL statement is written with.

Anatomy of a SQL statement
The semicolon
Upper and lower case
Quoted identifiers
Comments
Style and formatting conventions
Operators and their precedence
Literals: strings, numbers, dates, booleans and NULL
How to read a PostgreSQL error message
Common Mistakes and Tips
Exercises
Conclusion

Anatomy of a SQL statement

Every SQL statement is made up of a handful of element types. Let's look at one and label each piece:

SELECT name, price * 1.21 AS price_with_vat
FROM products
WHERE active = TRUE AND price > 10.00;

Element	In the example	What it is
Keywords	`SELECT`, `FROM`, `WHERE`, `AS`, `AND`, `TRUE`	Reserved terms of the language
Identifiers	`name`, `price`, `products`, `active`, `price_with_vat`	Names of tables, columns, aliases, schemas
Literals	`1.21`, `10.00`, `TRUE`	Values written directly in the text
Operators	`*`, `=`, `>`, `AND`	Symbols or words that combine values
Expressions	`price * 1.21`, `active = TRUE AND price > 10.00`	Combinations of the above that produce a value
Clauses	`SELECT ...`, `FROM ...`, `WHERE ...`	Blocks that structure the statement
Terminator	`;`	Marks the end of the statement

It's worth pausing on the difference between a keyword and an identifier, because it's the source of half of all beginner errors:

A keyword is part of the language. You don't choose it: SELECT always means the same thing.
An identifier is a name somebody gave to an object: the products table is called that because whoever designed GreenStore decided so.

Rules for identifiers in PostgreSQL:

They start with a letter or _, and continue with letters, digits, _ or $.
Maximum length: 63 characters (anything beyond that is silently truncated).
They can't coincide with a reserved word... unless you quote them (section 4).
Lowercase snake_case is recommended: order_lines, order_date, referred_by_id.

An expression is anything that evaluates to a value. You can check that without any table at all:

SELECT 2 + 3;

?column?
5

In PostgreSQL, SELECT without FROM is perfectly valid and it's the best way to experiment with standalone expressions. We'll use it a lot in this lesson.

The semicolon

The ; marks the end of a statement. Its role is twofold:

To separate several statements in the same file or block.
To tell the client it can now send the statement to the server.

SELECT 1;
SELECT 2;
SELECT 3;

Those are three independent statements. Without the semicolons, PostgreSQL would read SELECT 1 SELECT 2 SELECT 3 and report a syntax error.

If in psql you press Enter without having closed the statement, the prompt changes:

greenstore=# SELECT name
greenstore-# FROM products
greenstore-# ;

That hyphen (-#) means "still waiting". It isn't an error: you can spread a statement over as many lines as you like.

Situation	Is `;` needed?
A single statement in a graphical client	Optional, but advisable
Several statements in a `.sql` file	Mandatory
`psql` metacommands (`\dt`, `\l`, `\q`)	No, they aren't SQL
The last statement of a file	Advisable (avoids surprises when concatenating files)

Tip: always put it in. It's free and it saves you from debugging why a script ran "halfway".

Upper and lower case

There are two different rules here and it's best not to mix them up.

3.1. Keywords are case-insensitive

These four statements are identical as far as PostgreSQL is concerned:

SELECT name FROM products;
select name from products;
Select Name From Products;
sELeCt name FrOm products;

Even so, the universal convention is to write keywords in UPPERCASE, because it makes the query far more readable: at a glance you can tell the structure of the language from the names in your schema.

3.2. Unquoted identifiers are folded to lowercase

This is the part that surprises people. The SQL standard says unquoted identifiers should be folded to uppercase; PostgreSQL, for historical reasons, folds them to lowercase. The practical effect is that:

SELECT Name FROM Products;

is exactly the same as:

SELECT name FROM products;

because PostgreSQL internally turns Name → name and Products → products.

Compare across engines:

DBMS	Keywords	Unquoted identifiers	Table names
PostgreSQL	Insensitive	Folded to lowercase	Insensitive (because of the folding)
MySQL on Linux	Insensitive	Kept as written	Case-sensitive
MySQL on Windows/macOS	Insensitive	Kept as written	Insensitive (because of the file system)
SQLite	Insensitive	Kept as written	Insensitive
Oracle	Insensitive	Folded to UPPERCASE	Insensitive (because of the folding)

The practical moral: always use lowercase and snake_case in your identifiers. That way your SQL behaves the same on every engine and you dodge the whole problem.

Careful: case-insensitivity affects the syntax, not the data. Values are case-sensitive:

SELECT 'Valencia' = 'valencia';

?column?
false

Quoted identifiers

If you surround an identifier with double quotes, PostgreSQL takes it literally: it respects capitals, spaces and special characters.

-- These two tables would be DIFFERENT as far as PostgreSQL is concerned
CREATE TABLE products (...);     -- internally called: products
CREATE TABLE "Products" (...);   -- internally called: Products

And from then on, any reference to the second one forces you to repeat the quotes:

SELECT * FROM "Products";   -- works
SELECT * FROM Products;     -- ERROR: relation "products" does not exist

That error is one of the most baffling: the table exists, you can see it in \dt, and yet the engine says it can't find it. The cause is always the same: somebody created it quoted with capitals.

Use of double quotes	When it's legitimate
An identifier that clashes with a reserved word (`"order"`, `"user"`)	Acceptable, though renaming it is better
An identifier with spaces (`"customer name"`)	Always avoid
An identifier with capitals (`"OrderDate"`)	Always avoid
An alias you want to look nice in a report (`AS "Price with VAT"`)	A reasonable use

Course rule: every table and column in GreenStore is lowercase snake_case precisely so that you never need double quotes. The only acceptable exception will be presentation aliases.

And don't forget: double quotes = identifier; single quotes = text string. They aren't interchangeable (we'll come back to this in section 8).

Comments

SQL supports two forms of comment:

-- Single-line comment: from the two hyphens to the end of the line

/* Block comment:
   it can span several lines
   and it's handy for switching off chunks of a query */

SELECT name,           -- the commercial name of the product
       price           /* retail selling price, VAT excluded */
FROM products;

Form	Syntax	Typical use
Line	`-- text`	Annotating a column or a condition
Block	`/* text */`	Script headers, temporarily switching off several lines

In PostgreSQL block comments can be nested, something not every engine allows:

/* level 1 /* level 2 */ back in level 1 */
SELECT 1;

Good practice with comments:

Comment the why, not the what. -- we filter the active ones is redundant if the line already says WHERE active = TRUE. On the other hand -- inactive ones are discontinued products we keep for historical reasons does add something.
Head long scripts with a block explaining what they do and who maintains them.
Use -- to switch off a line while you debug, but clean up those leftovers before you call the query done.

Style and formatting conventions

SQL ignores line breaks and extra spaces, so formatting is entirely for humans. And yes, it matters: a badly formatted twenty-line query is impossible to review.

Before (all on one line, keywords in lowercase, no structure):

select p.name, c.name, p.price from products p join categories c on p.category_id = c.id where p.active = true and p.price between 5 and 20 order by p.price desc;

After (one clause per line, keywords in uppercase, clear alignment):

SELECT p.name,
       c.name AS category,
       p.price
FROM products AS p
JOIN categories AS c ON p.category_id = c.id
WHERE p.active = TRUE
  AND p.price BETWEEN 5 AND 20
ORDER BY p.price DESC;

Both do exactly the same thing. The second one can be read, reviewed and modified.

Conventions this course follows (and which are the most widespread in the industry):

Rule	Example
Keywords in UPPERCASE	`SELECT`, `FROM`, `WHERE`, `AND`
Identifiers in lowercase `snake_case`	`order_lines`, `order_date`
One main clause per line	`SELECT` / `FROM` / `WHERE` / `ORDER BY` on their own lines
Extra conditions indented under `WHERE`	`AND p.price > 10`
Long column lists, one per line	Makes changes easy to see in Git
Tables plural, columns singular	Table `customers`, column `name`
Foreign keys as `<singular_table>_id`	`customer_id`, `product_id`, `category_id`
No spaces or non-ASCII characters in identifiers	`order_lines`, not `"Order Lines"`
Never `SELECT *` in production code	Name the columns explicitly

Notice that every identifier sticks to plain ASCII, lowercase letters and underscores. That's deliberate: anything else —capitals, spaces, accented characters— forces you into double quotes for the rest of the object's life, and quoted identifiers hurt portability between engines, are awkward to type on any keyboard and are a constant source of "relation does not exist" errors.

Operators and their precedence

7.1. Arithmetic

Operator	Meaning	Example	Result
`+`	Addition	`SELECT 10 + 3;`	13
`-`	Subtraction	`SELECT 10 - 3;`	7
`*`	Multiplication	`SELECT 10 * 3;`	30
`/`	Division	`SELECT 10 / 3;`	3
`%`	Modulo (remainder)	`SELECT 10 % 3;`	1
`^`	Exponentiation	`SELECT 2 ^ 10;`	1024

The trap is /: if both operands are integers, the division is integer division.

SELECT 10 / 3 AS integer_division,
       10.0 / 3 AS decimal_division;

integer_division	decimal_division
3	3.3333333333333333

Just by writing 10.0 instead of 10 you change the type of the literal and with it the result. It's a classic source of mismatches when calculating percentages.

7.2. Comparison

Operator	Meaning	Example
`=`	Equal	`price = 10`
`<>` or `!=`	Not equal	`status <> 'cancelled'`
`<`, `>`	Less than, greater than	`stock > 0`
`<=`, `>=`	Less or equal, greater or equal	`price <= 20`
`BETWEEN ... AND ...`	Within a range, bounds included	`price BETWEEN 5 AND 20`
`IN (...)`	Belongs to a list	`country IN ('Spain','France')`
`IS NULL` / `IS NOT NULL`	Is (or isn't) null	`employee_id IS NULL`

<> is the standard form of "not equal"; != works in PostgreSQL and in almost every engine, but <> is more portable. The BETWEEN, IN, LIKE and IS NULL operators are studied thoroughly in module 4.

7.3. Logical

Operator	Returns true when...
`AND`	Both conditions are true
`OR`	At least one condition is true
`NOT`	The condition is false

7.4. Precedence

When you mix operators, PostgreSQL evaluates them in this order (from highest to lowest priority):

Level	Operators
1	`()` parentheses
2	`::` type cast
3	`-` unary (negative sign)
4	`^` exponentiation
5	`*`, `/`, `%`
6	`+`, `-`
7	`BETWEEN`, `IN`, `LIKE`
8	`=`, `<>`, `<`, `>`, `<=`, `>=`
9	`IS NULL`, `IS NOT NULL`
10	`NOT`
11	`AND`
12	`OR`

What causes the most trouble is that AND has higher priority than OR. Compare:

SELECT TRUE OR FALSE AND FALSE;    -- evaluated as: TRUE OR (FALSE AND FALSE)
SELECT (TRUE OR FALSE) AND FALSE;  -- we force a different order with parentheses

Query	Result
`TRUE OR FALSE AND FALSE`	true
`(TRUE OR FALSE) AND FALSE`	false

Translated into a real GreenStore case: "I want the orders from France or Portugal that have been delivered". Written naively:

-- WRONG: interpreted as  country='France' OR (country='Portugal' AND status='delivered')
... WHERE country = 'France' OR country = 'Portugal' AND status = 'delivered'

-- RIGHT
... WHERE (country = 'France' OR country = 'Portugal') AND status = 'delivered'

The first version would give you every French order, delivered or not. It doesn't raise an error: it silently gives a wrong result, which is far worse.

Professional tip: even if you know the precedence table by heart, add parentheses whenever you mix AND and OR. They cost two characters and they remove the ambiguity for whoever reads your query later.

Literals: strings, numbers, dates, booleans and NULL

A literal is a value written directly into the statement.

8.1. Text strings: SINGLE quotes

SELECT 'Food';
SELECT 'Valencia';

Strings go in single quotes. Always. This is probably the number one mistake of people coming from other programming languages, where "text" and 'text' are equivalent:

SELECT "Food";

ERROR:  column "Food" does not exist
LINE 1: SELECT "Food";
               ^

The message gives away what happened: PostgreSQL read the double quotes as an identifier, looked for a column called Food and didn't find it.

And what if the text contains a single quote, as in "L'Eliana"? You double it:

SELECT 'L''Eliana' AS town;

town
L'Eliana

PostgreSQL also supports dollar-quoted strings, very convenient for text full of quotes:

SELECT $$The comment said: 'I didn't like it'$$;

8.2. Numeric

SELECT 42;         -- integer
SELECT -17;        -- negative integer
SELECT 12.50;      -- exact decimal (numeric type)
SELECT 1.2e3;      -- scientific notation: 1200

Numbers take no quotes. If you write '12.50' you're creating a text string that PostgreSQL will sometimes convert for you and sometimes not, with surprising results.

8.3. Dates and times

Dates are written as text strings, in single quotes, in ISO 8601 format (YYYY-MM-DD):

SELECT DATE '2026-02-14' AS the_date;
SELECT TIMESTAMP '2026-02-14 18:30:00' AS the_moment;
SELECT '2026-02-14'::DATE AS date_with_cast;

the_date
2026-02-14

Always write in ISO format. If you write '14/02/2026', the engine will interpret it according to its regional setting (DateStyle), and on a server configured the American way '03/04/2026' can mean 3 April or 4 March. It's a mistake that never trips an alarm: it simply produces incorrect data. Date functions are covered thoroughly in module 6.

8.4. Booleans

SELECT TRUE, FALSE;

PostgreSQL accepts several ways of writing them: TRUE/FALSE, 't'/'f', 'yes'/'no', '1'/'0'. Use plain TRUE and FALSE: it's the clearest.

Dialect note: MySQL has no real boolean type; BOOLEAN is an alias for TINYINT(1) where TRUE is 1 and FALSE is 0. SQLite has no native boolean either: it stores them as 0 and 1.

8.5. NULL

NULL isn't a value: it's the absence of a value. In GreenStore, orders.employee_id is NULL when the order came in through the web and no sales rep handled it.

What matters right now, syntactically, is that NULL is not compared with =:

SELECT NULL = NULL AS with_equals,
       NULL IS NULL AS with_is_null;

with_equals	with_is_null
(null)	true

NULL = NULL gives neither true nor false: it gives NULL, because comparing two unknowns lets you conclude nothing. That's why the IS NULL operator exists. The full semantics of nulls is studied in lesson 04-03; here it's enough to hold on to the rule: for nulls, IS NULL; never = NULL.

8.6. Summary of literals

Type	How it's written	Correct example	Typical mistake
String	Single quotes	`'Natural cosmetics'`	`"Natural cosmetics"` (that would be an identifier)
Number	No quotes	`12.50`	`'12.50'`
Date	Single quotes, ISO format	`'2026-02-14'`	`'14/02/2026'`
Boolean	No quotes	`TRUE`	`'TRUE'`
Null	The `NULL` keyword	`IS NULL`	`= NULL`

How to read a PostgreSQL error message

PostgreSQL's messages are among the best in the industry, but you have to know how to read them. They have up to four parts:

ERROR:  syntax error at or near "FORM"
LINE 2: FORM products;
        ^

Part	Meaning
`ERROR:`	The description of the problem
`at or near "FORM"`	The token where the parser got stuck
`LINE 2:`	The line of your statement
`^`	The exact column

The key is the phrase "at or near". PostgreSQL points at where it detected the problem, which very often is one position after where the error actually is. If the cursor points at something that looks correct, always look at the previous token.

A real example:

SELECT name price FROM products;

ERROR:  syntax error at or near "FROM"
LINE 1: SELECT name price FROM products;
                          ^

The cursor points at FROM, but FROM is spelled perfectly. The real error is a missing comma between name and price: PostgreSQL read price as an alias for name (an alias can be written without AS), and by the time it reached FROM nothing else fitted.

The most frequent errors when you start, and what they translate to:

Message	What it really means	How to fix it
`syntax error at or near "X"`	Broken grammar at X or just before	Check the previous token: commas, parentheses, misspelled words
`relation "products" does not exist`	The table doesn't exist, or it's in another schema, or it was created with quotes and capitals	`\dt` to see the real name; check which database you're connected to
`column "Food" does not exist`	You used double quotes for a text string	Switch to single quotes
`column "prcie" does not exist`	Typo in the column name	`\d products` to see the exact names
`operator does not exist: text > integer`	You're comparing incompatible types	Check the column's type; use `CAST` (module 6)
`unterminated quoted string at or near "'..."`	An unclosed single quote	Count the quotes; if you're in `psql`, the `'#` prompt warns you
`division by zero`	You're dividing by 0	Protect the divisor (`NULLIF`, module 6)

Recommended procedure for any error:

Read the whole message, not just the first word.
Locate the line and the column with the ^.
If what's flagged looks correct, look at what comes before.
Check the basics: commas, balanced parentheses, closed quotes, type of quote.
If it's a name, verify it with \d.
If the query is long, shrink it: remove clauses until it works and add them back one by one.

Common Mistakes and Tips

Using double quotes for text. "Valencia" is an identifier; 'Valencia' is a string. If the error talks about a "column ... does not exist" with the text you wanted to search for, this is it.
Forgetting the comma between columns. It produces a syntax error at or near "FROM" that throws you off badly, because FROM is fine.
Creating objects with "QuotedCapitals". It condemns you to repeating the quotes forever. Use lowercase snake_case.
Trusting AND/OR precedence. It doesn't raise an error, it gives wrong results. Add parentheses.
Unexpected integer division. 10 / 3 is 3. Convert to decimal before calculating percentages or averages.
Dates in local format. Always write 'YYYY-MM-DD'.
Comparing with = NULL. It never returns rows, and it never warns you. Use IS NULL.
Tip: format before you debug. A single-line query is impossible to review; spread it over several lines and the error usually leaps out.
Tip: test expressions with SELECT and no FROM. Before dropping a calculation into a big query, check it in isolation: SELECT 12.50 * 1.21;.
Tip: when something fails, simplify. Remove half the query and see whether the error persists. It's the fastest way to narrow things down.

Exercises

Exercise 1

Find and fix four syntax errors in the following statement. State what message PostgreSQL would give in each case.

SELECT name price, stock
FORM products
WHERE city = "Valencia"
  AND active = TRUE

Exercise 2

Without running anything, predict the result of these five expressions and explain why. Then check them with SELECT.

SELECT 7 / 2;
SELECT 7.0 / 2;
SELECT TRUE OR FALSE AND FALSE;
SELECT 'Valencia' = 'valencia';
SELECT NULL = NULL;

Exercise 3

Rewrite this query applying the course's style conventions (keywords in uppercase, one clause per line, conditions indented) and fix the logical flaw it contains: the intention was to get the orders that are paid or shipped whose shipping costs are above €5.

select id, status, shipping_cost from orders where status = 'paid' or status = 'shipped' and shipping_cost > 5;

Solutions

Solution 1

The four errors, in order of appearance:

#	Error	PostgreSQL message	Fix
1	Missing comma between `name` and `price`	`syntax error at or near "price"` (or near `FORM`)	`SELECT name, price, stock`
2	`FORM` instead of `FROM`	`syntax error at or near "FORM"`	`FROM products`
3	Double quotes around the string `"Valencia"`	`column "Valencia" does not exist`	`= 'Valencia'`
4	Missing final semicolon	In `psql`, the prompt stays at `-#` waiting	Add `;`

There's also a semantic problem: GreenStore's products table has no city column (that column lives in customers and in employees). PostgreSQL would answer column "city" does not exist. Corrected version:

SELECT name, price, stock
FROM products
WHERE active = TRUE;

Solution 2

Expression	Result	Reason
`7 / 2`	`3`	Both operands are integers → integer division, truncated (not rounded)
`7.0 / 2`	`3.5000000000000000`	`7.0` is `numeric`, so the division is decimal
`TRUE OR FALSE AND FALSE`	`true`	`AND` has higher priority: it evaluates `TRUE OR (FALSE AND FALSE)` = `TRUE OR FALSE`
`'Valencia' = 'valencia'`	`false`	The data is case-sensitive, even though the syntax isn't
`NULL = NULL`	`NULL`	Comparing two absences of value lets you conclude nothing; you have to use `IS NULL`

Notice the contrast between rows 3, 4 and 5: in SQL case-insensitivity is a matter of grammar, never of values, and NULL doesn't behave like a normal value.

Solution 3

The logical flaw is precedence: AND is evaluated before OR, so the original query means status = 'paid' OR (status = 'shipped' AND shipping_cost > 5), and it would return every paid order, including the ones with free shipping. Corrected and formatted version:

SELECT id,
       status,
       shipping_cost
FROM orders
WHERE (status = 'paid' OR status = 'shipped')
  AND shipping_cost > 5;

The parentheses group the alternative statuses before the amount filter is applied. In module 4 you'll see an even more readable way of writing that first condition: status IN ('paid', 'shipped').

Conclusion

You now know the spelling rules of the language:

A statement is made up of keywords, identifiers, literals, operators and expressions, organised into clauses and closed by ;.
Keywords are case-insensitive, but unquoted identifiers are folded to lowercase in PostgreSQL: that's why the course uses lowercase snake_case and avoids double quotes.
You comment with -- and /* */, and you format with one clause per line because SQL is written once and read many times.
You've got a grip on the arithmetic, comparison and logical operators, and you know AND comes before OR: that's why you add parentheses.
You can tell the literals apart: single quotes for text and ISO dates, no quotes for numbers and booleans, and IS NULL for nulls.
You know how to read a PostgreSQL error: message, token, line and cursor, remembering that the fault is usually just before what's flagged.

In the next lesson, Understanding Databases and Tables, we'll move from form to content: what exactly a database, a schema, a table, a row and a column are; what data types PostgreSQL offers and how to choose the right one (including why money must never be stored in a FLOAT); and how to inspect the structure of tables that already exist.

Basic SQL Syntax

Contents

Anatomy of a SQL statement

The semicolon

Upper and lower case

3.1. Keywords are case-insensitive

3.2. Unquoted identifiers are folded to lowercase

Quoted identifiers

Comments

Style and formatting conventions

Operators and their precedence

7.1. Arithmetic

7.2. Comparison

7.3. Logical

7.4. Precedence

Literals: strings, numbers, dates, booleans and NULL

8.1. Text strings: SINGLE quotes

8.2. Numeric

8.3. Dates and times

8.4. Booleans

8.5. NULL

8.6. Summary of literals

How to read a PostgreSQL error message

Common Mistakes and Tips

Exercises

Exercise 1

Exercise 2

Exercise 3

Solutions

Conclusion

SQL Course

Module 1: Introduction to SQL

Module 2: Basic SQL queries

Module 3: Working with multiple tables

Module 4: Advanced data filtering

Module 5: Data manipulation

Module 6: Advanced SQL functions

Module 7: Subqueries and nested queries

Module 8: Indexes and performance tuning

Module 9: Transactions and concurrency

Module 10: Advanced topics

Module 11: SQL in practice

Module 12: Final project