All databases support standard SQL (SELECT, INSERT, UPDATE, DELETE, JOIN, GROUP BY, etc.). Differences are in extensions and system tables. PostgreSQL: pg_stat_activity (active queries), pg_stat_user_tables (table statistics). MySQL: SHOW PROCESSLIST (active queries), SHOW TABLE STATUS (table statistics). SQL Server: sys.dm_exec_requests (active queries), sys.dm_db_index_usage_stats (index usage). Oracle: V$SESSION (active sessions), DBA_TABLES (table statistics). Learn standard SQL first (portable across databases), then database-specific features (performance tuning, monitoring).

All databases support standard SQL: SELECT, INSERT, UPDATE, DELETE, JOIN, GROUP BY, HAVING, ORDER BY, LIMIT

System tables differ: PostgreSQL (pg_stat_activity, pg_stat_user_tables), MySQL (INFORMATION_SCHEMA, SHOW commands), SQL Server (sys.dm_exec_requests, sys.dm_db_index_usage_stats)

Transaction syntax: BEGIN/START TRANSACTION, COMMIT, ROLLBACK (standard), SAVEPOINT (partial rollback)

Isolation levels: SET TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

Index syntax: CREATE INDEX idx_name ON table(column) - standard, CREATE INDEX CONCURRENTLY (PostgreSQL), ONLINE (MySQL 5.6+, SQL Server)

Monitoring queries: PostgreSQL (SELECT * FROM pg_stat_activity WHERE state = 'active'), MySQL (SHOW PROCESSLIST), SQL Server (SELECT * FROM sys.dm_exec_requests)

Database size: PostgreSQL (SELECT pg_size_pretty(pg_database_size('dbname'))), MySQL (SELECT SUM(data_length + index_length) FROM information_schema.tables)

MVCC (non-blocking reads), heap storage (fast secondary keys), advanced features (JSON, arrays, full-text search), best for high read concurrency

InnoDB clustered index (fast PK lookups), locking (simpler than MVCC), popular (large ecosystem), best for PK-heavy queries

Enterprise features (Always On, columnstore), T-SQL (procedural language), Windows integration, best for Microsoft ecosystem

Enterprise features (RAC, Data Guard), PL/SQL (mature procedural language), expensive licensing, best for large corporations with budget

Core references in this topic include WHERE, =, <, >, <=, >=. Learn what each one does, when to use it, and the execution or engine rules that matter.

WHERE

Filters rows before projection and sorting. It decides which rows continue through the query pipeline.

SELECT ... FROM table WHERE condition;

Most performance issues start with a weak WHERE clause or a missing supporting index.

=

Returns rows where the left and right values are exactly equal.

column = value

Use with exact matches. Do not use = NULL.

<, >, <=, >=

Range comparison operators for less-than, greater-than, and inclusive boundary checks.

salary >= 80000

BETWEEN

Checks whether a value falls inside an inclusive lower/upper range.

order_total BETWEEN 100 AND 500

LIKE

Pattern-matching operator for wildcard string searches.

name LIKE 'Joh%'

ANY / ALL

Compares one value against every or at least one value from a subquery result.

salary > ALL (SELECT salary FROM interns)

TIME

Stores a time-of-day value without any date component.

TIME '14:30:00'

TIMESTAMP

Stores date and time together, typically without timezone context.

TIMESTAMP '2026-04-18 14:30:00'

PRIMARY KEY