Overview

Structured Query Language (SQL) is an evolving spec. The minimum standard a system needs to support to claim it is a SQL database is SQL-92.

SQL is based on bags (unordered set with duplicates) instead of sets (unordered set).

SQL essentially provides:

• Data Manipulation Language
• Data Definition Language
• Data Control Language

(Modern) SQL features

The example DB uses have 3 relations:

• student(sid, name, login, age, gpa)
• course(cid, name)
• enrolled(sid, cid, grade)

Aggregates

functions that return a single value from a bag of tuples:

• Example: AVG(col), MIN, MAX, SUM, COUNT
• can only be used in SELECT output list
  • if there are other non-aggregate outputs in the output list, the non-agg outputs are undefined
    • ex: SELECT AVG(gpa), cid FROM STUDENT; ⇒ cid output is undefined!

Group by

Project tuples into subsets and calculate aggregates against each subset

Example: