SQLite - Syntax

SQLite - Modern SQL Syntax Reference

This chapter provides a reference to the syntax of common and modern SQLite statements. A solid grasp of this syntax is essential for effective database management.

General Rules

Case Sensitivity: SQL keywords (like SELECT, FROM, WHERE) are case-insensitive. However, table and column names can be case-sensitive depending on the collation. It’s best practice to be consistent (e.g., lowercase keywords, snake_case identifiers).

Comments: Use comments to document your SQL code. Single-line comments start with --. Multi-line comments are enclosed in /* ... */.

-- This is a single-line comment.
SELECT * FROM employees;

/*
  This is a multi-line comment
  used to explain a complex query.
*/
SELECT name, salary -- you can also have comments at the end of a line
FROM employees;

Semicolons: Each SQL statement should end with a semicolon (;). While some clients are lenient, it is required for executing multiple statements.

Data Definition Language (DDL)

CREATE TABLE

CREATE TABLE [IF NOT EXISTS] table_name (
    column1_name data_type [CONSTRAINTS],
    column2_name data_type [CONSTRAINTS],
    ...
);

ALTER TABLE (Add Column)

ALTER TABLE table_name ADD COLUMN column_definition;

ALTER TABLE (Rename Table)

ALTER TABLE table_name RENAME TO new_table_name;

DROP TABLE

DROP TABLE [IF EXISTS] table_name;

CREATE INDEX

CREATE [UNIQUE] INDEX index_name ON table_name (column1, column2...);

Data Manipulation Language (DML)

SELECT

SELECT [DISTINCT] column_list
FROM table_name
[WHERE search_condition]
[GROUP BY column_list]
[HAVING search_condition]
[ORDER BY column_list ASC|DESC]
[LIMIT count [OFFSET position]];

INSERT INTO

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

UPSERT (INSERT ON CONFLICT)

-- If a row with the same unique key exists, update it. Otherwise, insert.
INSERT INTO table_name (id, name, value)
VALUES (?, ?, ?)
ON CONFLICT(id) DO UPDATE SET
  name = excluded.name,
  value = excluded.value;

UPDATE

UPDATE table_name
SET column1 = value1, column2 = value2, ...
[WHERE search_condition];

DELETE

DELETE FROM table_name [WHERE search_condition];

Advanced SQL Syntax

Window Functions

Window functions perform a calculation across a set of table rows that are somehow related to the current row. This is a powerful feature for analytics.

-- Example: Get each employee's salary and the average salary of their department.
SELECT
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) as avg_dept_salary
FROM employees;

Common Table Expressions (CTE) with WITH Clause

CTEs help break down complex queries into simple, readable logical blocks.

WITH sales_employees AS (
  SELECT * FROM employees WHERE department = 'Sales'
)
SELECT name, salary FROM sales_employees WHERE salary > 50000;

JSON Functions

Modern SQLite has excellent support for JSON data.

-- Assume a 'products' table with a 'details' JSON column
-- json_extract(json_column, path)
SELECT name, json_extract(details, '$.color') as color
FROM products;

Transaction and System Commands

Transaction Control

BEGIN TRANSACTION;
-- Your SQL statements here...
COMMIT; -- or ROLLBACK;

PRAGMA

The PRAGMA command is used to query or modify the internal settings of the SQLite library.

-- Best practice: Enforce foreign key constraints for each connection.
PRAGMA foreign_keys = ON;

-- Recommended for better concurrency and performance.
PRAGMA journal_mode = WAL;

-- Get information about a table's columns.
PRAGMA table_info(table_name);

EXPLAIN QUERY PLAN

-- Shows the query plan chosen by the optimizer. Essential for debugging performance.
EXPLAIN QUERY PLAN SELECT * FROM employees WHERE age > 30;