Which finds employees with no supervisor?
Advanced SQL
NULLs and three-valued logic, nested and correlated subqueries (IN, ANY/ALL, EXISTS, UNIQUE), joins (inner, natural, outer, multiway), aggregation, GROUP BY and HAVING, views, schema changes with DROP and ALTER, and running queries from Java with JDBC.
How to read this page
Every original slide is shown unchanged. Yellow marks on a slide point to a numbered note: hover or tap a mark to jump to its note, and hover a note to see its marks. Questions tagged Past exam appeared on a real paper; Prediction questions are likely angles, not confirmed. Pick an answer to each question: a wrong pick tells you why it is wrong, so you can try again.
All 64 slides
- 1 Advanced SQL
- 2 LEARNING GOALS
- 3 SCHEMA REMINDER: EXAMPLES
- 4 NULL VALUES
- 5 Slide 5
- 6 3 VALUED LOGIC
- 7 NULL IN SQL QUERIES
- 8 select presenter
- 9 Workshop
- 10 Workshop
- 11 Outer query SELECT *
- 12 NESTED QUERIES
- 13 COMPARISON OPERATORS FOR NESTED QUERIES
- 14 NESTED QUERY EXERCISES
- 15 Austin
- 16 NESTED QUERY EXERCISES
- 17 SQL EXPLICIT SETS
- 18 CORRELATED NESTED QUERIES
- 19 CORRELATED NESTED QUERIES
- 20 CORRELATED NESTED QUERIES
- 21 CORRELATED NESTED QUERY EVALUATION
- 22 CORRELATED NESTED QUERIES
- 23 EXISTS AND UNIQUE
- 24 NOT EXISTS
- 25 EXERCISES
- 26 MORE JOINS
- 27 Course Faculty id name Lecturer_id id First_name Last_name 1 Introduction to Database 1 1 Maram Alajlan 2 Operating systems 2 2 Roohi Jan 3 Programming 1 2 3 Khwlah Alrajhi 4 Data structure Null
- 28 Course Faculty id name Lecturer_id id First_name Last_name 1 Introduction to Database 1 1 Maram Alajlan 2 Operating systems 2 2 Roohi Jan 3 Programming 1 2 3 Khwlah Alrajhi 4 Data structure Null
- 29 Course Faculty id name Lecturer_id id First_name Last_name 1 Introduction to Database 1 1 Maram Alajlan 2 Operating systems 2 2 Roohi Jan 3 Programming 1 2 3 Khwlah Alrajhi 4 Data structure Null
- 30 Course Faculty id name Lecturer_id id First_name Last_name 1 Introduction to Database 1 1 Maram Alajlan 2 Operating systems 2 2 Roohi Jan 3 Programming 1 2 3 Khwlah Alrajhi 4 Data structure Null
- 31 Course Faculty id name Lecturer_id id First_name Last_name 1 Introduction to Database 1 1 Maram Alajlan 2 Operating systems 2 2 Roohi Jan 3 Programming 1 2 3 Khwlah Alrajhi 4 Data structure Null
- 32 NATURAL JOIN
- 33 INNER JOIN
- 34 OUTER JOIN
- 35 LIST OF IDENTICAL JOINS WITH DIFFERENT NAMES
- 36 OTHER TYPES OF JOIN
- 37 MULTIWAY JOIN
- 38 SUMMARY
- 39 JOIN EXERCISES
- 40 AGGREGATION
- 41 Student
- 42 Student
- 43 Student
- 44 Student
- 45 AGGREGATION
- 46 d
- 47 AGGREGATES IN NESTED QUERIES
- 48 EXERCISES
- 49 GROUP BY CLAUSE
- 50 Student
- 51 GROUP BY CLAUSE
- 52 NULLS IN GROUPS
- 53 old
- 54 SUMMARY OF SQL (NOW THAT WE KNOW MORE)
- 55 VIEWS
- 56 Employees(EmployeeID, Name, Position, salary, hiring date)
- 57 MATERIALIZED VS NON-MATERIALIZED VIEWS
- 58 VIEW FUNCTIONS
- 59 SCHEMA CHANGES (DROP AND ALTER)
- 60 SCHEMA CHANGES (DROP AND ALTER)
- 61 EXECUTING QUERIES IN CODE
- 62 EXAMPLE (JAVA)
- 63 PROJECT CODE
- 64 SUMMARY
Advanced SQL
LEARNING GOALS
SCHEMA REMINDER: EXAMPLES
NULL VALUES
Notes
Trap WHERE x = NULL is never true; write WHERE x IS NULL.
Exam angle
Slide 5
3 VALUED LOGIC
Notes
Three-valued logic: TRUE, FALSE, UNKNOWN. Any comparison with NULL is UNKNOWN; WHERE keeps only TRUE rows.
NULL IN SQL QUERIES
select presenter
Workshop
Workshop
Outer query SELECT *
NESTED QUERIES
COMPARISON OPERATORS FOR NESTED QUERIES
Notes
= ANY (SOME) is like IN; > ALL means greater than every value returned.
Exam angle
Salary > ALL (SELECT Salary FROM EMPLOYEE WHERE Dno = 5) finds employees who earn:
NESTED QUERY EXERCISES
Austin
NESTED QUERY EXERCISES
SQL EXPLICIT SETS
CORRELATED NESTED QUERIES
Notes
Correlated subquery: refers to the outer query’s row, so it is re-evaluated for each outer row.
CORRELATED NESTED QUERIES
CORRELATED NESTED QUERIES
CORRELATED NESTED QUERY EVALUATION
CORRELATED NESTED QUERIES
EXISTS AND UNIQUE
Notes
EXISTS (subquery) is TRUE if it returns at least one row; NOT EXISTS if it returns none.
Exam angle
borrowers who never borrowed / orders without a Mouse
Find employees who have no dependents.
Compare with the model answer
SELECT Fname, Lname FROM EMPLOYEE E WHERE NOT EXISTS (SELECT * FROM DEPENDENT D WHERE D.Essn = E.Ssn);
NOT EXISTS
EXERCISES
MORE JOINS
Course Faculty id name Lecturer_id id First_name Last_name 1 Introduction to Database 1 1 Maram Alajlan 2 Operating systems 2 2 Roohi Jan 3 Programming 1 2 3 Khwlah Alrajhi 4 Data structure Null
Course Faculty id name Lecturer_id id First_name Last_name 1 Introduction to Database 1 1 Maram Alajlan 2 Operating systems 2 2 Roohi Jan 3 Programming 1 2 3 Khwlah Alrajhi 4 Data structure Null
Course Faculty id name Lecturer_id id First_name Last_name 1 Introduction to Database 1 1 Maram Alajlan 2 Operating systems 2 2 Roohi Jan 3 Programming 1 2 3 Khwlah Alrajhi 4 Data structure Null
Course Faculty id name Lecturer_id id First_name Last_name 1 Introduction to Database 1 1 Maram Alajlan 2 Operating systems 2 2 Roohi Jan 3 Programming 1 2 3 Khwlah Alrajhi 4 Data structure Null
Course Faculty id name Lecturer_id id First_name Last_name 1 Introduction to Database 1 1 Maram Alajlan 2 Operating systems 2 2 Roohi Jan 3 Programming 1 2 3 Khwlah Alrajhi 4 Data structure Null
NATURAL JOIN
Notes
NATURAL JOIN joins on all same-named columns and keeps one copy of each.
INNER JOIN
OUTER JOIN
Notes
LEFT/RIGHT/FULL OUTER JOIN also keep unmatched rows from the left/right/both side, padded with NULLs.
Exam angle
List all employees, with their department name if they have one:
LIST OF IDENTICAL JOINS WITH DIFFERENT NAMES
OTHER TYPES OF JOIN
MULTIWAY JOIN
SUMMARY
JOIN EXERCISES
AGGREGATION
Notes
Aggregates: COUNT, SUM, MAX, MIN, AVG. COUNT(*) counts rows; COUNT(col) ignores NULLs.
Exam angle
COUNT(Super_ssn) on a table where 2 of 8 rows have NULL Super_ssn returns:
Student
Student
Student
Student
AGGREGATION
d
AGGREGATES IN NESTED QUERIES
EXERCISES
GROUP BY CLAUSE
Notes
GROUP BY splits rows into groups; aggregates are computed per group. Every non-aggregated SELECT column must be in GROUP BY.
Student
GROUP BY CLAUSE
NULLS IN GROUPS
old
Notes
HAVING filters GROUPS (after grouping); WHERE filters ROWS (before grouping).
Memory trick Condition on an aggregate (SUM, COUNT…) → HAVING.
Exam angle
Q15: customers whose orders total over $500
Departments with more than 5 employees:
SUMMARY OF SQL (NOW THAT WE KNOW MORE)
VIEWS
Notes
View: a virtual table defined by a query (CREATE VIEW v AS SELECT …).
Exam angle
create view AVAILABLE_BOOKS
Employees(EmployeeID, Name, Position, salary, hiring date)
MATERIALIZED VS NON-MATERIALIZED VIEWS
VIEW FUNCTIONS
SCHEMA CHANGES (DROP AND ALTER)
Notes
DROP TABLE removes a table (CASCADE / RESTRICT). ALTER TABLE adds/drops columns or constraints.
Exam angle
drop a constraint, rename and resize columns
Remove the foreign-key constraint fk_publisher from BOOK:
SCHEMA CHANGES (DROP AND ALTER)
EXECUTING QUERIES IN CODE
Notes
From code: connect (JDBC), prepare a statement, execute, read the ResultSet.
EXAMPLE (JAVA)
PROJECT CODE
SUMMARY
