Structured Query Language (SQL)
SQL is the standard language for relational databases. Originally developed at IBM in the 1970s, SQL is now supported by all major RDBMSs—Oracle, MySQL, PostgreSQL, SQL Server, SQLite—with minor dialectal variations. SQL is both a query language and a complete data-definition and data-manipulation tool.
Data Definition Language (DDL)
DDL statements define and modify database structure. CREATE TABLE defines a new relation with attributes, data types, and constraints. ALTER TABLE adds, removes, or modifies columns and constraints. DROP TABLE removes a table. SQL supports data types including INTEGER, DECIMAL, VARCHAR, DATE, TIMESTAMP, and BOOLEAN, with database-specific extensions.
Data Manipulation Language (DML)
The four core DML statements are SELECT, INSERT, UPDATE, and DELETE. INSERT adds new rows, UPDATE modifies existing rows subject to a WHERE clause, and DELETE removes rows. SELECT is the most versatile, supporting projection, selection, joining, grouping, ordering, and combining results from multiple tables.
SELECT Query Structure
The canonical SELECT statement is SELECT ‹columns› FROM ‹tables› WHERE ‹condition› GROUP BY ‹columns› HAVING ‹condition› ORDER BY ‹columns›. Logically, clauses are evaluated roughly in this order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY. Understanding this order helps write correct queries.
Joins in SQL
Joins combine rows from multiple tables. INNER JOIN returns matching rows; LEFT OUTER JOIN preserves unmatched rows from the left table (with NULLs for missing right-side values); RIGHT OUTER JOIN mirrors this; FULL OUTER JOIN preserves both. CROSS JOIN is the Cartesian product. Self-joins join a table to itself and require aliases.
Aggregate Functions and Grouping
Aggregates—COUNT, SUM, AVG, MIN, MAX—summarize multiple rows into single values. GROUP BY partitions data; HAVING filters groups. Aggregates are often paired with joins to answer business questions like "total sales per region" or "average grade per course".
Subqueries
A subquery is a SELECT nested inside another query. Subqueries appear in WHERE (with operators like IN, EXISTS, ANY, ALL), SELECT (scalar subqueries), and FROM (derived tables). Correlated subqueries reference the outer query's rows and are evaluated per outer row.
Views
A view is a named query; users can query a view as if it were a table. Views simplify complex queries, enforce security by restricting access to specific columns or rows, and provide logical independence from underlying table changes. Materialized views cache their results for performance.
Integrity and Constraints
SQL enforces constraints declaratively: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY (with ON UPDATE and ON DELETE actions like CASCADE), CHECK constraints, and DEFAULT values. These ensure data correctness without requiring application-level validation.
Triggers and Stored Procedures
Triggers are procedural code that executes automatically in response to events (INSERT, UPDATE, DELETE). Stored procedures and functions are user-defined routines stored in the database. They encapsulate business logic close to the data and reduce network traffic.
Transactions in SQL
SQL provides BEGIN (or START TRANSACTION), COMMIT, and ROLLBACK for transaction control. Isolation levels—READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE—trade consistency against concurrency.
Summary
SQL is a rich, declarative language that covers nearly all database interaction. Its core is straightforward, but mastering advanced features such as window functions, CTEs, and query optimization is a career-long journey for database practitioners.