Oct 22: Outer Joins, Set Operations

Learning Objectives

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

• Read and write queries that include inner, outer, and self joins.
• Connect two queries with UNION, INTERSECT, and EXCEPT.

Lesson Outline

Announcements [10 min]

• Exam 1 Retake, next Tuesday (Oct 27), last 30 minutes of class
  • New versions of five questions that most students missed
  • A retake score replaces the original only if it is higher
• PE1 – evaluate your team's performance on GP1 through GP3
  • Your answers are private, and only the instructor sees them

Joins [20 min]

• Outer Joins, Set Operations
• New database: tpch on data.cs.jmu.edu

Lab [30 min]

• Joins on TPC-H
  • Outer joins, self joins, and set operations in pgAdmin
  • Steps 1–5 in class; Step 6 if you have time

Team time [15 min]

• Run python scripts/load.py --scratch before you push

Your To-Do List

• GP3 ← due Monday, Oct 26
• PE1 ← due Tuesday, Oct 27
• Study for Exam 1 Retake

Retake Outline

See the Exam 1 Study Guide for details about each of these questions.

Retake	Topic	Exam 1	Points
1	Aggregation	Question 2	15
2	Insert and Constraints	Question 3A	7
3	SQL in Python	Question 4A	3
4	EER Diagrams	Question 5D	3
5	Relational Mapping	Question 6	20