SQL Standard Fundamentals and Querying
SQL Overview and History
- Structured Query Language (SQL): Standard language implemented by Relational Database Management Systems (RDBMS) for schema definition, data manipulation, querying, transaction management, and programmatic access.
- Origin: Developed as SEQUEL at IBM San Jose Research Lab in the early 1970s as part of the System-R project.
- Evolution Milestones:
- SQL-86/89
- SQL-92 (SQL2)
- SQL-99/03 (SQL3, added object-relational capabilities)
SQL Standard Components
- Data Definition Language (DDL): Provides constructs for defining relation schemas, modifying schemas, declaring integrity constraints, and creating views.
- Data Manipulation Language (DML): Provides constructs to query, insert, update, and delete tuples within relation instances.
- Embedded and Dynamic SQL: Facilitates embedding SQL statements into high-level programming languages such as C, C++, and Java.
- Transaction Control: Manages transaction initiation, termination, and concurrent execution controls.
- Authorization: Restricts user privileges on data access and query execution types.
SQL-92 Domain Data Types
- Numeric Types:
INT, SMALLINT: Integer numbers of varying sizes.REAL, FLOAT, DOUBLE PRECISION: Floating-point and real numbers of varying precisions.DECIMAL(i, j) / NUMERIC(i, j): Formatted fixed-point numbers where i represents total precision (total digits) and j represents scale (digits after decimal).
- Character String Types:
CHAR(n): Fixed-length character string of length n.VARCHAR(n): Variable-length character string with a maximum capacity of n characters.
- Bit String Types:
BIT(n): Fixed-length bit string.BIT VARYING(n): Variable-length bit string.
- Temporal Types:
DATE: Calendar date formatted as YYYY-MM-DD (10 positions).TIME: Time of day formatted as HH:MM:SS (8 positions).
- Extended Types: Systems commonly support
BOOLEAN, TIMESTAMP, BLOB, CLOB, and JSON.
Integrity Constraints and Table Definition
- Table Syntax:
CREATE TABLE r (attribute_1 domain_1, ..., [table_constraints]); - Column Properties:
NOT NULL: Restricts attribute values from being null.DEFAULT v: Specifies a fallback value v during row insertion.
- Key Constraints:
PRIMARY KEY (A_1, ..., A_k): Uniquely identifies each tuple; automatically enforces NOT NULL and UNIQUE; restricted to one primary key per table.UNIQUE (B_1, ..., B_k): Enforces uniqueness for candidate key attribute sets; multiple unique constraints are permitted per table.CHECK (condition): Validates that attribute values satisfy a specified Boolean expression (e.g., CHECK (salary > 0)).
- Referential Integrity Constraints (RIC):
FOREIGN KEY (A_1) REFERENCES r_2(B_1): Requires that every non-null value of referencing column A1 exists within referenced column B1 of table r2.
- Referential Actions (
ON DELETE / ON UPDATE):CASCADE: Propagates deletions or updates from referenced rows directly to referencing rows.SET NULL: Sets the foreign key attributes of referencing tuples to null.SET DEFAULT: Sets the foreign key attributes of referencing tuples to their specified default values.RESTRICT: Disallows deletion or updating of referenced parent rows if referencing child rows exist.
Schema Modification
- Syntax:
ALTER TABLE modifies previously declared table definitions. - Adding Columns:
ALTER TABLE table_name ADD attribute_name domain_type; - Dropping Columns:
ALTER TABLE table_name DROP attribute_name option;RESTRICT: Drops the attribute only if no dependent constraints or views exist.CASCADE: Automatically drops the attribute alongside any dependent constraints or views.
Basic Query Semantics
- Clause Structure:
SELECT A_1, ..., A_m FROM R_1, ..., R_p WHERE \theta; - Relational Algebra Mapping: Evaluates conceptually to:
πA1,…,Am(σθ(R1×…×Rp))
- Duplicates: SQL outputs are multisets (bags) and may contain duplicates; duplicates are eliminated explicitly using
SELECT DISTINCT. - Disambiguation: Table aliases (e.g.,
student AS s) and attribute renaming (AS alias_name) resolve duplicate column names across joined relations or self-joins.
Subqueries and Set Comparisons
- Classification:
- Uncorrelated Subqueries: Independent of outer query rows; evaluated a single time.
- Correlated Subqueries: Reference attributes from the outer query; re-evaluated for every candidate tuple of the outer query.
- Set Comparison Quantifiers:
v op ANY S: Evaluates to true if the comparison v op x holds true for at least one element x∈S.v op ALL S: Evaluates to true if v op x holds true for every member x∈S.- Equivalence rules:
IN is equivalent to = ANY; NOT IN is equivalent to <> ALL.
- Existence Checks:
EXISTS (subquery): Returns true if the subquery returns at least one tuple; otherwise false.NOT EXISTS (subquery): Returns true if the subquery yields an empty set.
- Universal Quantification Emulation: SQL lacks a native universal quantifier; universal conditions (e.g., "all courses have 3 credits") are implemented by double negation via
NOT EXISTS or NOT IN (e.g., selecting entities where no course exists with credits =3).