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 ii represents total precision (total digits) and jj represents scale (digits after decimal).
  • Character String Types:
    • CHAR(n): Fixed-length character string of length nn.
    • VARCHAR(n): Variable-length character string with a maximum capacity of nn 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 vv 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 A1A_1 exists within referenced column B1B_1 of table r2r_2.
  • 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))\pi_{A_1, \ldots, A_m}(\sigma_{\theta}(R_1 \times \ldots \times R_p))
  • 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 xv \text{ op } x holds true for at least one element x∈Sx \in S.
    • v op ALL S: Evaluates to true if v op xv \text{ op } x holds true for every member x∈Sx \in 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\neq 3).