2. Modern SQL

• Relational algebra is based on sets (unordered, no duplicates). SQL is based on bags (unordered, allows duplicates).

 

SQL History

• SQL is comprised of different classes of commands:
  • Data Manipulation Language (DML): SELECT, INSERT, UPDATE, and DELETE statements.
  • Data Definition Language (DDL): Schema definitions for tables, indexes, views, and other objects.
  • Data Control Language (DCL): Security, access controls.

 

Joins

• Combines columns from one or more tables and produces a new tables.

CREATE TABLE student (
  sid INT PRIMARY KEY,
  name VARCHAR(16),
  login VARCHAR(32) UNIQUE,
  age SMALLINT,
  gpa FLOAT
);

CREATE TABLE course (
  cid VARCHAR(32) PRIMARY KEY,
  name VARCHAR(32) NOT NULL
);

CREATE TABLE enrolled (
  sid INT REFERENCES student (sid),
  cid VARCHAR(32) REFERENCES course (cid),
  grade CHAR(1)
);

SELECT s.name
  FROM enrolled AS e, student AS s
 WHERE e.grade = 'A' AND e.cid = '15-721'
  AND e.sid = s.sid;

 

Aggregates

• An aggregation function takes in a bag of tuples as its input and then produces a single scalar value as its output. Almost only used in a SELECT output list.
  • AVG, MIN, MAX, COUNT
  • DISTINCT, GROUP BY, HAVING

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

• Pattern Matching:
  • "%" matches any substrings (including empty).
  • "-" matchs any one character.
• String Functions: SUBSTRING(S, B, E), UPPER(S)
• Concatenation: "||"

 

Output Redirection

• New Table: Store the output of  the query inton a new (permanent) table.

SELECT DISTINCT cid INTO CourseIds FROM enrolled;

• Existing Table: Store the output of the query into a table that already exists in the database. The target table must have the same  number of columns with the same types as the target table, but the names of the columns in the output query do not have to match.

 

Output Control

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 20 OFFSET 10;

 

Nested Queries

• Inner queries can appear in almost any part of a query:
• SELECT Output Targets:

SELECT (SELECT 1) AS one FROM student;

• From Clause:

SELECT name
  FROM student AS s, (SELECT sid FROM enrolled) AS e
  WHERE s.sid = e.sid;

• Where Clause:

SELECT name FROM student
  WHERE sid IN ( SELECT sid FROM enrolled );

• Netsted Query Results Expressions:
  • ALL: Must satisfy expression for all rows in sub-query.
  • ANY: Must satisfy expression for at least one row in sub-query.
  • IN: Equivalent to = ANY().
  • EXISTS: At least one row is returned.

 

Window Functions

• Functions: The window function can be any of the aggregation functions that we discussed above. There are also special window functions:
  • ROW_NUMBER: The number of the current row.
  • RANK: The order position of the current row.
• Grouping: The OVER clause specifies how to group together tuples when computing the window function. Use PARTITION BY to specify group.
• We can also put an ORDER BY within OVER to ensure a deterministic ordering of results even if database changes internally.

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

• The DBMS computes RANK after the window function sorting, whereas it computes ROW_NUMBER before the sorting.

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

 

Common Table Expressions (CTEs)

• The WITH clause binds the output of the inner query to a temporary result with that name.

WITH cteName AS (
	SELECT 1
)
SELECT * FROM cteName;

• We can bind output columns to names before the AS:

WITH cteName (col1, col2) AS (
	SELECT 1, 2
)
SELECT col1 + col2 FROM cteName;

• Adding the RECURSIVE keyword after WITH allows a CTE to reference itself.

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