CS145 Lecture Notes (4) -- SQL: Queries and Updates

SQL ("S.Q.L." or "sequel") - Structured Query Language
Query language of all commercial relational DBMS's

(Diagram of how the SQL standard evolves over time)

We'll cover primarily SQL2 (SQL92), some features of SQL-99 (SQL3). Class demos and all programming assignments use MySQL, which adheres fairly closely to the standard.
Class demos use "interactive" SQL -- commands typed to a prompt or entered in a GUI; answers printed as tables.
"Embedded" and "dynamic" SQL with cursor-based answers is covered in required reading and a later optional help session.

SQL is a big language, not just queries and updates.
Components of the language:

Schema definition
Data retrieval
Data modification
Indexes
Constraints
Views
Triggers
Transactions
Authorization
etc.

DDL = Data Definition Language
DML = Data Manipulation Language

Basic DDL commands

   CREATE TABLE name (att1 type1, att2 type2, ..., attN typeN)

   DROP TABLE name

See textbook for details of types.

DML commands

   SELECT, INSERT, DELETE, UPDATE

SELECT Statement

   SELECT A1, A2, ..., An
   FROM   R1, R2, ..., Rm
   WHERE  condition

Equivalent to relational algebra query:

   PROJECT_{A1, A2, ..., An} SELECT_{condition} (R1 X R2 X ... X Rm)

Returns unnamed relation whose schema is (A1, A2, ..., An)

[[On-Line SQL Demo]]

Schema for examples:

   Student(ID, name, GPA, sizeHS)       // ID is key
   Campus(location, enrollment, rank)   // location is key
   Apply(ID, location, major, decision) // (ID,location,major) is key

Constructs covered:

Basic one-table queries
Basic multi-table queries
Removing duplicates
Sorted results
LIKE predicate
SELECT *
SELECT with arithmetic
Result attribute renaming
Table variables
UNION, INTERSECT, EXCEPT
Subqueries with IN, NOT IN, EXISTS, NOT EXISTS, ALL, ANY
Subqueries in FROM and SELECT
Basic aggregation
Aggregation over join
COUNT DISTINCT
GROUP BY and HAVING
Null values
INSERT, DELETE, UPDATE

Summary of SQL SELECT Statement

   SELECT    attributes, aggregates
   FROM      relations
   WHERE     condition
   GROUP BY  grouping attributes
   HAVING    conditions on aggregates
   ORDER BY  attributes, aggregates

=> All optional except SELECT and FROM

("Flowchart" of clause evaluation)