Subqueries

Question 1

Subquery

Answer 1

A subquery is a SQL query nested inside a larger query

Question 2

SELECT lastName, firstName
FROM employees
WHERE officeCode IN
(SELECT officeCode
FROM offices
WHERE country = ‘USA’);

Is the “SELECT lastName, firstName” query the outer or inner query?

Answer 2

The outer query

Question 3

SELECT lastName, firstName
FROM employees
WHERE officeCode IN
(SELECT officeCode
FROM offices
WHERE country = ‘USA’);

Is the “SELECT officeCode” query within the parenthesis the outer or inner query?

Answer 3

The inner query

Question 4

What do we need to add to the subquery of a FROM clause in order for it to work?

Answer 4

An AS keyword

This will give the temporary table created by the subquery a name that the outer query can use to pull the information from.

Question 5

When you have a query inside of another query, the term “subquery” refers to which query?

Answer 5

The inner query

Question 6

Correlated Subquery

Answer 6

Is a subquery that contains a reference to a table that also appears in the outer query.

The correlation comes from the fact that the subquery uses information from the outer query and the subquery executes once for every row in the outer query.