Lecture 3 - Nested Queries

Question 1

What is a nested/inner query?

Answer 1

Nested query is a query within another (outer) query.

• SELECT-FROM-WHERE block within another outer WHERE clause.
• Nested query’s output is input to outer’s WHERE via: IN, ALL, EXISTS

Question 2

What are the two types of nested queries?

Answer 2

• nested uncorrelated query
• nested correlated query

Question 3

What is IN?

Answer 3

check if something is within a set

Question 4

What is ALL?

Answer 4

compares a value with all the values from the inner’s output set
using >, >=, <, <=, =, <>

Question 5

What is EXISTS?

Answer 5

Checks if resultant set of nested query is not empty.

Question 6

What is a nested uncorrelated query?

Answer 6

first execute the nested query, and then
execute the outer query using inner’s output.

Question 7

What is a nested correlated query?

Answer 7

for each tuple of the outer query, we execute the
nested query

Question 8

Within nested correlated queries we have ?

Answer 8

global and local scopes

Question 9

Problem with exists?

Answer 9

just focus on the count of the nested query or set, but doesn’t care about the content (can be all null)

Question 10

Example of a nested correlated query?

Answer 10

SELECT E.FNAME, E.LNAME
FROM EMPLOYEE AS E
WHERE E.SSN IN ( SELECT D.ESSN
FROM DEPENDENT AS D
WHERE E.FNAME = D.DEPENENT_NAME)

we are passing the employee to the inner query

Question 11

Example of a nested uncorrelated query?

Answer 11

SELECT FNAME
FROM EMPLOYEE
WHERE DNO IN ( SELECT DNUMBER
FROM DEPARTMENT
WHERE DNAME = ‘Research’);

Question 12

STUDENT (Name, StudentID, Class)
COURSE (Name, CourseID, Credits, School)
GRADES (StudentID, CourseID, Grade)
/Grade: {‘A’, ‘B’, ‘C’, ‘D’, ‘E’}/
Task: Retrieve the names of all students who have a Grade of ‘A’ in
all of their courses ( ‘distinction’ students)

Answer 12

SELECT S.Name
FROM STUDENT S
WHERE NOT EXISTS
(SELECT * FROM GRADES G
WHERE G.StudentID = S.StudentID
AND G.Grade <> ‘A’
)