Relational Language

Edgar Codd's seminal work in the early 1970s laid the foundation for relational models through the introduction of relational algebra—a mathematical framework defining operations such as selection, projection, join, Cartesian product, intersection, union, and difference.

Users express their data needs using declarative languages like SQL, allowing the database management system (DBMS) to determine optimal execution strategies.

Relational algebra operates on sets (unordered, distinct elements), whereas SQL works with bags (unordered, potentially duplicate elements). SQL does not inherently sort or deduplicate unless explicitly requested.

Historical Overview of SQL

Structured Query Language (SQL) originated from "SEQUEL," developed by IBM. It encompasses several command types:

• Data Manipulation Language (DML): SELECT, INSERT, UPDATE, DELETE
• Data Definition Language (DDL): CREATE, ALTER, DROP
• Data Control Language (DCL): Permissions and access control
• Additional features: Views, constraints, transactions

SQL evolves continuously with new standards released periodically. SQL-92 established a baseline for compliance, though vendors implement varying degrees of support. No current DBMS fully conforms to the SQL 2016 standard.

Aggregate Functions

Aggregate functions process groups of rows and return single scalar values. They are exclusive to the SELECT clause.

Standard functions include:

• AVG(col) – Average value
• MIN(col) – Minimum value
• MAX(col) – Maximum value
• SUM(col) – Total sum
• COUNT(col) – Row count

Example:

SELECT COUNT(*) FROM student WHERE login LIKE '%@cs';

Using DISTINCT removes duplicates:

SELECT COUNT(DISTINCT login) FROM student WHERE login LIKE '%@cs';

Multiple aggregates can be used in one query:

SELECT COUNT(*) AS student_count, AVG(gpa) AS avg_gpa FROM student WHERE login LIKE '%@cs';

The GROUP BY clause partitions rows into groups based on specified attributes. The HAVING clause filters these groups:

SELECT cid, AVG(gpa) FROM enrolled GROUP BY cid;

Without GROUP BY, all rows form a single group, leading to incorrect results when selecting non-aggregated columns.

String Operations

Database systems vary in case sensitivity and quote handling. Standard SQL treats strings as case-sensitive and uses single quotes.

Common string functions:

• UPPER(s) / LOWER(s) – Case conversion
• TRIM(s) – Removes trailing spaces
• SUBSTRING(s, start, length) – Extracts substring

Pattern matching with LIKE:

• % matches any sequence
• _ matches any character

String concatenation varies across platforms:

• Standard SQL uses ||
• MySQL requires CONCAT()
• SQL Server uses +

Date and Time Handling

Timestamps represent time values rather than formatted dates.

Function	PostgreSQL	MySQL	SQLite
NOW()	✓	✓	✗
CURRENT_TIMESTAMP()	✗	✓	✗
CURRENT_TIMESTAMP	✓	✓	✓

Examples:

SELECT EXTRACT(DAY FROM DATE('2021-08-19')) AS day;
SELECT DATE('2021-08-19') - DATE('2021-01-01') AS days;
SELECT DATEDIFF(DATE('2021-08-19'), DATE('2021-01-01')) AS days;
SELECT CAST((julianday(CURRENT_TIMESTAMP) - julianday('2021-01-01')) AS INT) AS days;

Output Redirection

Results can be stored in new or existing tables instead of being returned to the client.

• New table: Use INTO keyword to create a new permanent table
• Existing table: Match column types and names appropriately

Output Control

SQL returns data as bags, allowing duplicates.

• ORDER BY <column> [ASC|DESC]: Sort output
• LIMIT <count> [OFFSET]: Restrict result size

Note: Without ORDER BY, row order may vary between executions.

Nested Queries

Subqueries allow complex logic within a single query. Inner queries can reference outer query attributes.

Opertaors include:

• ALL – Must satisfy all subquery rows
• ANY – At least one row must match
• IN – Equivalent to = ANY()
• EXISTS – Checks if subquery returns any rows

Window Functions

Window functions perforrm calculations over sets of rows related to the current row.

Syntax:

<function> OVER (PARTITION BY <expression> ORDER BY <expression>)

Functions include:

• Aggregates: SUM(), AVG(), COUNT(), MIN(), MAX()
• Special: ROW_NUMBER(), RANK()

Example:

SELECT *, RANK() OVER (PARTITION BY cid ORDER BY gpa DESC) AS rank FROM enrolled;

Window functions operate after full query execution, returning original tuples augmented with computed values.

Common Table Expressions (CTE)

CTEs act as temporary tables within a query scope, introduced via the WITH clause.

Syntax:

WITH cte_name AS (SELECT ...) SELECT * FROM cte_name;

Multiple CTEs supported:

WITH cte1 AS (SELECT ...), cte2 AS (SELECT ...) SELECT * FROM cte1 JOIN cte2;

Recursive CTEs use the RECURSIVE keyword:

WITH RECURSIVE counter AS (SELECT 1 AS n UNION ALL SELECT n+1 FROM counter WHERE n < 10) SELECT * FROM counter;

CTEs provide an alternative to nested queries, supporting recursion where nested queries do not.