Advanced SQL

Table of Contents

Advanced SQL

Prev: course-introduction-and-the-relational-model Next: database-storage-i

Relational Languages

SQL History

Comprised of 3 different classes of commands:

  1. Data Manipulation Language (DML): Select, Insert, Update, Delete
  2. Data Definition Language (DDL): Schema definition (Create table)
  3. Data Control Language (DCL): Security, Access Control

Aggregates

An aggregation function takes in a bag of tuples as its input and then produces a single scalar value as output.

Count will turn a scalar (the rows of the student table) into one number.

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

You can use multiple aggregates within a single SELECT statement:

SELECT AVG(gpa), COUNT(sid) FROM student WHERE login LIKE '%@cs';

Or distinct users (if you have duplicates).

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

If you want to output an aggregate, use a HAVING clause.

SELECT AVG(s.gpa) AS avg_gpa, e.cid
FROM enrolled AS e, student AS s
WHERE e.sid = s.sid
GROUP BY e.cid
HAVING avg_gpa > 3.9;

String Operations

The SQL standard says that strings are case sensitive and use single-quotes only.

Output Redirection

SELECT DISTINCT cid INTO CourseIds FROM enrolled;
INSERT INTO CourseIds (SELECT DISTINCT cid FROM enrolled);

Output Control

SELECT sid FROM enrolled WHERE cid = '15-721' ORDER BY grade DESC;

Multiple ORDER BYs can be used to break ties:

SELECT sid FROM enrolled WHERE cid = '15-721' ORDER BY grade DESC, sid ASC;

Any arbitrary expression may be used:

SELECT sid FROM enrolled WHERE cid = '15-721' ORDER BY UPPER(grade) DESC, sid + 1 ASC;
SELECT sid, name FROM student WHERE login LIKE '%@cs' LIMIT 10;
SELECT sid, name FROM student WHERE login LIKE '%@cs' LIMIT 10 OFFSET 20;

Nested Queries

SELECT name FROM student WHERE sid IN (SELECT sid FROM enrolled WHERE cid = '15-445');

Window Functions

  1. ROW_NUMBER: the number of the current row.
  2. RANK: the order position of the current row.
SELECT cid, sid, ROW_NUMBER() OVER (PARTITION BY cid) FROM enrolled ORDER BY cid;

You can also use an ORDER BY for deterministic sorting:

SELECT *, ROW_NUMBER() OVER (ORDER BY cid) FROM enrolled ORDER BY cid;

Common Table Expressions

WITH nums AS (SELECT 1)
SELECT * from nums;

You can bind output columns to names before the AS:

WITH nums (one, two) AS (SELECT 1, 2)
SELECT one + two FROM nums;

Use the RECURSIVE keyword after WITH to allow a CTE to reference itself.

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

Prev: course-introduction-and-the-relational-model Next: database-storage-i