Lecture 02: Modern SQL 📚¶

15-445/645 Database Systems (Spring 2025)
Carnegie Mellon University | Prof. Jignesh Patel

1. SQL History 📜¶

• Origins:
• Developed in the 1970s as part of IBM’s System R project.
• Originally called SEQUEL (Structured English Query Language).

• Renamed SQL (Structured Query Language) in the 1980s.

• Standards:

• SQL-92: Minimum standard for claiming SQL support.
• Major updates:
  | Version | Key Features |
  |---------|--------------|
  | SQL:1999 | Regular Expressions, Triggers |
  | SQL:2003 | XML, Windows, Sequences |
  | SQL:2008 | Truncation, Fancy Sorting |
  | SQL:2011 | Temporal DBs, Pipelined DML |
  | SQL:2016 | JSON, Polymorphic Tables |
  | SQL:2023 | Property Graph Queries, Multi-Dim. Arrays |

2. Relational Languages 🛠️¶

• Categories:
• DML (Data Manipulation): SELECT, INSERT, UPDATE, DELETE.
• DDL (Data Definition): Schema definitions (tables, indexes).
• DCL (Data Control): Security, access controls.

• Others: Views, constraints, transactions.

• Key Difference:

• Relational Algebra uses sets (no duplicates).
• SQL uses bags (allows duplicates).

3. Example Database 🗃️¶

Tables:

student¶

course¶

enrolled¶

4. Aggregates 🧮¶

Functions: AVG, MIN, MAX, SUM, COUNT.

Example 1: Count Students with "@cs" Login¶

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

Example 2: Multiple Aggregates¶

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

Example 3: Group By + HAVING¶

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

5. String Operations 📝¶

• Case Sensitivity: SQL-92 is case-sensitive.
• Pattern Matching: Use LIKE, % (any substring), _ (single character).

Example:¶

SELECT *  
FROM student  
WHERE UPPER(name) = UPPER('TuPaC');  

6. Output Control 🎛️¶

• Sorting:
  
  SQL

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

• Limits:
  
  SQL

  SELECT sid, name  
  FROM student  
  LIMIT 2;  
  

7. Window Functions 🪟¶

Concept: Compute values over a "window" of rows.

Example: Rank Students by Grade¶

SELECT *, RANK() OVER (PARTITION BY cid ORDER BY grade)  
FROM enrolled;  

8. Nested Queries 🔄¶

Example: Find Students Enrolled in 15-445¶

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

9. Lateral Joins 🔗¶

Use Case: Reference outer query columns in subqueries.

Example: Course Stats¶

SELECT c.cid, t1.cnt, t2.avg_gpa  
FROM course AS c  
LATERAL (SELECT COUNT(*) AS cnt FROM enrolled WHERE cid = c.cid) AS t1  
LATERAL (SELECT AVG(gpa) AS avg_gpa FROM student s  
         JOIN enrolled e ON s.sid = e.sid WHERE e.cid = c.cid) AS t2;  

10. Common Table Expressions (CTEs) 📦¶

Example: Recursive Counter¶

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

Key Takeaways 🚀
- SQL evolves with new standards (e.g., SQL:2023).
- Master aggregates, window functions, and CTEs for advanced queries.
- Use LATERAL and nested queries for complex joins.