CS-10337 – Applied Database Technologies� Lecture 8
By Prof. Rafael Orta
Wayground
Last class we covered
Agenda
Subqueries – Single row subquery
A subquery (also called an inner query or nested query) is a query inside another query.
It’s like asking a question within a question — first you find one answer, and then you use that answer to solve the bigger question.
Example:
“Who earns more than the average salary?”
To answer that, you must first find the average salary, then use it in your outer query.
That’s exactly what a subquery does.
Subqueries – Single row subquery
Subqueries – Single row subquery
Types of Subqueries
Multi-row subquery
Multi-row Subquery
Correlated Subquery
The inner query uses e.department_id from the outer query.
It calculates the average salary for each department and compares each employee’s salary to their department’s average.
This runs once per row, not once for the whole table.
Correlated Subquery
Subquery in the from clause (inline view)
Subquery in the from clause (inline view)
Subquery in the select clause
Attendance
DEMO TIME
DEMO TIME
DEMO TIME
Supplemental Video(s)
Non-graded hands-on practice
Exercise 1 — Single-row subquery
Prompt: List course_code and title for all courses in the same department as “Dr. Elena Ruiz.”
Hint: First find Dr. Ruiz’s dept_id, then use it in the outer query.
Exercise 2 — Multi-row subquery (simpler version)
Prompt: Find the full_name of all students whose major department is one of the departments that offer courses worth more than 3 credits.
Hint: The inner query should find all departments that have any 4-credit course, then the outer query returns students majoring in those departments.
Non-graded hands-on practice
Exercise 1 — Single-row subquery
Prompt: List course_code and title for all courses in the same department as “Dr. Elena Ruiz.”
Hint: First find Dr. Ruiz’s dept_id, then use it in the outer query.
Exercise 2 — Multi-row subquery (simpler version)
Prompt: Find the full_name of all students whose major department is one of the departments that offer courses worth more than 3 credits.
Hint: The inner query should find all departments that have any 4-credit course, then the outer query returns students majoring in those departments.
Supplemental Reading
Knowledge Check
Which of the following statements about subqueries are true?�(Select all that apply)
�A. A subquery is a query inside another query.�B. Subqueries can return one or multiple results.�C. Subqueries cannot use aggregate functions.�D. Subqueries can be used in SELECT, WHERE, or FROM clauses.
Correct Answers: A, B, D
Reference Material used in this presentation