Oct 27: Subqueries

Learning Objectives

After today's class, you should be able to:

• Write subqueries in the FROM clause and the WHERE clause.
• Rewrite a subquery as a join, and explain when each approach is clearer.

Lesson Outline

Announcements [5 min]

• PE1 is due tonight
• GP3 reviews: leave your comments before Thursday's class
• HW5 is out; Queries 1–6 use only what we cover through today

Subqueries [20 min]

• Subqueries
• coalesce, and subqueries in WHERE, SELECT, and FROM

Lab [20 min]

• Subqueries on TPC-H
  • Problems 1–4 in class; Problem 5 if you have time

Exam 1 Retake [30 min]

• 1:30 to 2:00, five questions
• A retake score replaces the original only if it is higher

Your To-Do List

• PE1 ← due tonight
• Week 10 reading ← due Wednesday
• GP3 review comments ← before Thursday's class
• HW5 ← due Monday