Important topics: Filtering and functions

1.1 Every filter is an expression

A WHERE clause is one expression that SQLite evaluates for each candidate row, and the row survives only when the result is TRUE. Comparisons (=, <>, <, <=, >, >=) produce that result, and so do the boolean operators AND, OR and NOT. The operators combine smaller expressions into bigger ones, exactly like arithmetic.

Precedence decides what binds first: comparison operators bind tighter than NOT, NOT binds tighter than AND, and AND binds tighter than OR. So city = 'Delhi' OR city = 'Mumbai' AND rating >= 4 means city = 'Delhi' OR (city = 'Mumbai' AND rating >= 4), which is probably not what the author meant. Parentheses cost nothing and remove the doubt. Write them whenever a mixed AND/OR condition appears, and let the reader check the logic instead of the precedence table.

NOT is easy to misuse with NULL. NOT (rating = 5) is not TRUE for a row whose rating is NULL, because rating = 5 is UNKNOWN and NOT UNKNOWN is still UNKNOWN. The row disappears from both the positive and the negative filter, which is a common source of missing rows in reports. Every example in this module runs on SQLite 3.53.

1.2 Pattern matching with LIKE

LIKE compares a text value against a pattern with two wildcards: % matches any sequence of zero or more characters, and _ matches exactly one character. name LIKE 'A%' finds names that start with A, name LIKE '%ee%' finds names containing ee, and name LIKE '_e%' finds names whose second character is e.

SQLite's LIKE is case-insensitive for ASCII letters by default, so 'aarav' LIKE 'A%' is TRUE. PostgreSQL's LIKE is case-sensitive and offers ILIKE for the other behaviour, which is why an interview answer should name the engine. A literal % or _ is escaped with ESCAPE, as in LIKE '50\%' ESCAPE '\'. GLOB is SQLite's case-sensitive alternative with * and ? wildcards.

A pattern that starts with % cannot use a normal index, because the engine does not know the prefix to seek. A pattern with a fixed prefix can. That single fact explains most "why is my search slow" questions about text filters.

1.3 String functions

SQLite ships a compact set of text functions. LENGTH counts characters for text and bytes for blobs. UPPER and LOWER change case for ASCII letters only; SQLite has no full Unicode case mapping without an extension. SUBSTR(value, start, length) counts from 1, and a negative start counts from the end. TRIM, LTRIM and RTRIM remove spaces from both sides or one side. REPLACE(value, old, new) swaps every occurrence, and INSTR(value, needle) returns the 1-based position or 0 when the needle is missing. The || operator concatenates text.

Most scalar functions return NULL when an input is NULL: UPPER(NULL) is NULL, LENGTH(NULL) is NULL, and 'Hi, ' || NULL is NULL rather than 'Hi, '. That rule is consistent, and it is why a missing middle name silently blanks a concatenated address. Use COALESCE(column, '') when an empty string is the right fallback.

SELECT name, LENGTH(name) AS chars, UPPER(city) AS city_upper,
       SUBSTR(email, 1, INSTR(email, '@') - 1) AS handle
FROM students
ORDER BY name
LIMIT 4;

name         | chars | city_upper | handle
-------------+-------+------------+-------------
Aarav Sharma | 12    | DELHI      | aarav.sharma
Dev Patel    | 9     | AHMEDABAD  | NULL
Ishita Bose  | 11    | KOLKATA    | ishita.bose
Kabir Nair   | 10    | KOCHI      | kabir.nair

1.4 Dates are text with a convention

SQLite has no dedicated date type. It stores dates and timestamps as TEXT, REAL or INTEGER, and the usable convention is ISO 8601 text such as '2026-03-18' or '2026-03-18 14:30:00'. The format is deliberately sortable: year, then month, then day, with fixed widths. That is why joined_on >= '2026-03-01' works as a date comparison even though it compares text.

The date functions read that text and return text, a number or a Julian day. date() returns the date part, datetime() returns date and time, strftime() formats a date with a pattern, and julianday() returns a day number suitable for subtraction. Modifiers shift the value: '+30 days', '-1 year', 'start of month', 'weekday 0'. DATE('now') uses UTC, and DATE('now', 'localtime') applies the machine's local zone.

PostgreSQL stores real date and timestamptz values and uses INTERVAL arithmetic. The portable habit is the same in both: store a canonical format, compare with canonical literals, and keep time zone conversion at the application edge. Text dates also have a trap: '2026-3-8' sorts differently from '2026-03-08', so pad to two digits everywhere.

SELECT name, joined_on, date(joined_on, '+30 days') AS follow_up
FROM students
ORDER BY joined_on DESC
LIMIT 4;

name        | joined_on  | follow_up
------------+------------+-----------
Dev Patel   | 2026-03-22 | 2026-04-21
Zoya Ali    | 2026-03-18 | 2026-04-17
Neel Joshi  | 2026-03-10 | 2026-04-09
Ishita Bose | 2026-03-01 | 2026-03-31

SELECT DISTINCT strftime('%Y-%m', joined_on) AS month
FROM students
ORDER BY month;

month
-------
2026-01
2026-02
2026-03

1.5 Numbers and CAST

ROUND(value, digits) rounds, ABS(value) removes the sign, and CAST(value AS type) converts a value from one storage class to another. SQLite also has type affinity: a column declared INTEGER prefers integers but can still store text. Casting makes intent explicit, and it is required when you want real division.

Integer division is the trap worth memorising. When both operands are integers, SQLite performs integer division, so fee / 1000 with fee 4999 gives 4, not 4.999. CAST(fee AS REAL) / 1000 gives the expected decimal. typeof() reveals the storage class of the result, so it is a handy diagnostic in a query that seems to lose precision. PostgreSQL behaves differently here: integer division is still integer division, but a numeric literal with a decimal point makes the whole expression numeric, as does a numeric column type.

SELECT title, fee,
       fee / 1000 AS int_share,
       CAST(fee AS REAL) / 1000 AS real_share,
       ROUND(fee / 3.0, 2) AS third
FROM courses
ORDER BY fee DESC
LIMIT 3;

title                   | fee  | int_share | real_share | third
------------------------+------+-----------+------------+--------
Machine Learning Basics | 8999 | 8         | 8.999      | 2999.67
Data Structures         | 5999 | 5         | 5.999      | 1999.67
Computer Networks       | 5499 | 5         | 5.499      | 1833

1.6 Aliases name the result

A column alias renames an output column: fee AS yearly_fee, or fee yearly_fee because AS is optional. A table alias renames a table inside the query: FROM students AS s. Table aliases keep multi-table queries readable and become necessary in self joins, where one table plays two roles and needs two names.

Alias legality follows the logical order from module one. ORDER BY may use a column alias because it runs after SELECT; WHERE may not in standard SQL because it runs before. SQLite allows the WHERE alias as an extension, and PostgreSQL rejects it, so do not ship it. A column alias can also end up quoting: fee AS "Course fee" needs double quotes because the label contains a space. Single quotes are for string values, double quotes for identifiers.

SELECT s.name AS student, s.city AS home_city
FROM students AS s
ORDER BY home_city ASC, student ASC
LIMIT 4;

student    | home_city
-----------+----------
Zoya Ali   | NULL
Dev Patel  | Ahmedabad
Neel Joshi | Bengaluru
Meera Iyer | Chennai

1.7 Filtering on computed values

Filters can test expressions as well as raw columns. WHERE LENGTH(name) > 10 keeps long names, and WHERE joined_on >= date('2026-03-01') compares computed dates. A CASE expression turns a condition into a label, and the label can be reused in ORDER BY. The cost is that wrapping a column in a function usually stops an index from helping, because the index stores the column, not the function's output. Store a generated column or a normalized column when a computed filter runs often.

CASE is the general conditional expression: CASE WHEN condition THEN value WHEN other THEN value ELSE fallback END. It evaluates conditions in order and stops at the first TRUE. If every WHEN is FALSE or UNKNOWN and there is no ELSE, the result is NULL.

SELECT name, LENGTH(name) AS chars
FROM students
WHERE LENGTH(name) > 10
ORDER BY chars DESC;

name         | chars
-------------+------
Aarav Sharma | 12
Rohan Verma  | 11
Ishita Bose  | 11

SELECT title,
       CASE WHEN fee < 4500 THEN 'budget'
            WHEN fee < 8000 THEN 'standard'
            ELSE 'premium' END AS price_band
FROM courses
ORDER BY fee;

title                   | price_band
------------------------+-----------
Python Basics           | budget
Web Development         | budget
SQL and Databases       | standard
Computer Networks       | standard
Data Structures         | standard
Machine Learning Basics | premium

1.8 Common mistakes

• Mixing AND and OR without parentheses, then reading the query under the wrong precedence.
• Using NOT IN or NOT (col = value) on a column that contains NULL and losing rows silently.
• Forgetting that LIKE '%text%' cannot use a prefix index.
• Comparing date strings in different formats, such as '2026-3-8' against '2026-03-08'.
• Assuming / returns a decimal in SQLite when both operands are integers.