Lecture 09a: Derived Tables, 3VL Truth Tables & NULL Functions

1. Derived Tables (Subqueries in FROM)

A subquery in the FROM clause functions as a temporary inline relation that exists only for that specific statement.

-- Find the maximum department average salary across all departments
SELECT MAX(dept_avg) AS highest_dept_avg
FROM (
    SELECT dept_name, AVG(salary) AS dept_avg
    FROM instructor
    GROUP BY dept_name
) AS dept_summaries;

Rule: ANSI SQL mandates that every derived table in the FROM clause must be given a table alias (AS dept_summaries), even if you do not explicitly refer to it elsewhere in the query.

2. Three-Valued Logic (3VL) Truth Tables

Under Codd's Three-Valued Logic, boolean expressions evaluate to TRUE (T), FALSE (F), or UNKNOWN (U).

AND Truth Table

OR Truth Table

NOT Truth Table

3. Essential NULL Functions

1. COALESCE(e1, e2, ..., en)

Returns the first non-null expression in the argument list from left to right.

-- If phone is null, use email; if email is null, use 'No Contact'
SELECT name, COALESCE(phone, email, 'No Contact') AS contact_info
FROM student;

2. NULLIF(e1, e2)

Returns NULL if e_1; otherwise returns e_1.
Primary Use Case: Preventing fatal division-by-zero errors in analytical calculations:

-- If total_attempts is 0, NULLIF turns it into NULL. 
-- Any number divided by NULL safely evaluates to NULL instead of crashing!
SELECT student_id, (passed_credits * 1.0) / NULLIF(total_attempts, 0) AS pass_rate
FROM student_stats;

4. Introduction to Views

A view is a stored, named SELECT query in the database dictionary. It acts as a virtual table; the database stores the query text, not the resulting data rows.

CREATE VIEW faculty_public AS
SELECT id, name, dept_name
FROM instructor;

-- Querying the view
SELECT * FROM faculty_public WHERE dept_name = 'Physics';

When you query faculty_public, the DBMS query rewriter automatically merges the view's definition with your query:

SELECT id, name, dept_name FROM instructor WHERE dept_name = 'Physics';

5. Topic Practice Questions

SELECT 
    name,
    COALESCE(salary * 0.10, 500.00) AS bonus
FROM instructor;