Part 2: Introduction to SQL & ANSI Standards
SQL (Structured Query Language, pronounced either /ˈsiːkwəl/ or /ˌɛsˌkjuːˈɛl/) is the domain-specific language designed for managing and manipulating relational databases.
1. History of the Relational Model & SQL
- 1970: Dr. Edgar F. Codd (IBM) published the seminal mathematical paper "A Relational Model of Data for Large Shared Data Banks".
- 1974: Donald Chamberlin and Raymond Boyce developed SEQUEL (Structured English Query Language) at IBM.
- 1979: Relational Software, Inc. (now Oracle) released the first commercial implementation of SQL.
- 1986: ANSI (American National Standards Institute) officially standardized SQL (SQL-86).
2. The Major ANSI SQL Standards
SQL is an evolving international standard governed by ANSI and ISO:
- SQL-92 (Entry Level): The foundational baseline supported by virtually all databases (standard JOINs, subqueries).
- SQL:1999 (SQL3): Introduced Common Table Expressions (CTEs), Recursive Queries, and Triggers.
- SQL:2003 & SQL:2008: Introduced Window Functions (e.g., ROW_NUMBER(), RANK()), XML support.
- SQL:2016 & SQL:2023: Added native JSON querying, pattern matching (MATCH_RECOGNIZE), and graph query capabilities.
3. The Database Engine Dialects
While ANSI defines the core standard, every major RDBMS implements proprietary extensions and optimizations:
[ ANSI SQL Core Standard ]
│
┌──────────────┬─────────┴────────┬──────────────┐
▼ ▼ ▼ ▼
[PostgreSQL] [MySQL] [SQL Server] [Oracle]
(PL/pgSQL, JSONB) (InnoDB, Memory) (T-SQL) (PL/SQL)
In this masterclass, we teach standard ANSI SQL that works across engines, highlighting engine-specific capabilities when relevant.
Key Takeaways
- SQL is based on the relational algebra formalized by Edgar F. Codd.
- ANSI standards ensure that foundational SQL syntax is portable across RDBMS engines.
In Part 3, we will examine the Internal Architecture of an RDBMS: Storage Engines, Buffer Pools & Query Processors!