Subqueries

Question 1

What type of query is used inside another query as if it were a table?

Answer 1

Subquery

Last Revised: 4/2/21, 3.3

Question 2

True or False: A subquery is always enclosed within parentheses, and it is usually executed prior to its outer query.

Answer 2

True

Last Revised: 4/2/21, 3.5

Question 3

Put parentheses around your query, give it an alias, and you’ve got a subquery that you can put into the ____________ clause.

Answer 3

FROM

Last Revised: 4/2/21, 3.6

Question 4

Inline views are another name for ________ ?

Answer 4

Subqueries in the FROM clause of the outer query

Last Revised: 4/2/21, 3.6

Question 5

What is a candidate key?

Answer 5

(Last Revised: 4/2/21, 3.7)

Question 6

What operator returns TRUE if the subquery yields any results and FALSE otherwise?

Answer 6

The EXISTS operator

Last Revised: 4/2/21, 3.8

Question 7

What follows the EXISTS operator?

Answer 7

A subquery

Last Revised: 4/2/21, 3.8

Question 8

Because the EXISTS operator returns TRUE or FASLE, it can be combined with other Boolean logic such as _____ .

Answer 8

NOT

Last Revised: 4/2/21, 3.8

Question 9

What clause is the EXISTS operator commonly used in?

Answer 9

The WHERE clause

Last Revised: 4/2/21, 3.8

Question 10

What is another way to think about latest encounters?

Answer 10

Think of it as “there are no later encounters.” In SQL, that translates to WHERE NOT EXISTS followed by a subquery that returns later encounters

(Last Revised: 4/2/21, 3.8)

Question 11

What is the extent to which a portion of a query can be referenced elsewhere?

Answer 11

Scope

Last Revised: 4/2/21, 3.10

Question 12

True or False: An outer query can only reference the results of a subquery, not the data or logic used to create the results.

Answer 12

True
• That means that the outer query can only reference the columns returned by the SELECT clause of the subquery.

(Last Revised: 4/2/21)

Question 13

What clause does the subquery use to pass columns to the outer query?

Answer 13

The SELECT clause

Last Revised: 4/2/21, 3.10

Question 14

Can a subquery reference the columns retuned by the FROM clause of the outer query?

Answer 14

Yes
• When a subquery is processed, SQL attempts to resolve references within the subquery; it that fails, then SQL attempts to resolve the reference with columns retuned by the FROM clause in the outer query.

(Last Revised: 4/2/21, 3.10)

Question 15

What is an Uncorrelated Subquery a result of?

Answer 15

If a subquery doesn’t reference columns from the outer query, it is known as an uncorrelated subquery.

(Last Revised: 4/2/21)

Question 16

What is a Correlated Subquery a result of?

Answer 16

If a subquery references columns from the outer query, it is known as a correlated subquery. Correlated subqueries are executed one for each row processed by the outer query, so they should generally be avoided for performance reasons. Queries that include correlated subqueries can often be rewritten to not use correlated subqueries.

(Last Revised: 4/2/21, 3.11)

Question 17

Are subqueries that appear in the FROM clause always correlated or uncorrelated?

Answer 17

They are always uncorrelated because the FROM clause hasn’t finished executing by the time the subquery is processed
• Subqueries elsewhere in the outer query may be correlated or uncorrelated.

(Last Revised: 4/2/21, 3.11)

Question 18

What operator should you use if you want your subquery to return only one column of data?

Answer 18

The IN operator
Write a query that has only one column in the SELECT clause. Then wrap it in parenthesis and it’s ready to be used as a subquery with the IN operator.

(Last Revised: 4/2/21, 3.13)

Question 19

What type of operator and in what clause should you use if you want your subquery to return a single value?

Answer 19

A comparison operator in the WHERE clause
Write a query that has only one column in the SELECT clause and returns only one row. Then wrap it in parentheses and it’s ready to be used as a subquery with a comparison operator.

(Last Revised: 4/2/21, 3.15)

Question 20

What do common table expressions (CTE) do?

Answer 20

They allow you to execute a subquery and save the result set for later reference by you main SELECT statement.

(Last Revised: 4/2/21, 3.16)

Question 21

What is a recursive CTE?

Answer 21

It is when a CTE references other CTEs.

Last Revised: 4/2/21, 3.16

Question 22

Where is the WITH clause positioned in your query?

Answer 22

Immediately before the main SELECT statement.

Last Revised: 4/2/21, 3.16

Question 23

Where are CTEs defined?

Answer 23

In the WITH clause.

Last Revised: 4/2/21, 3.16

Question 24

Can CTEs be referenced directly and be given aliases?

Answer 24

Yes, in the FROM clause.

Last Revised: 4/2/21, 3.17

Question 25

What two clauses can you use a subquery in?

Answer 25

1. The FROM clause
2. The WHERE clause

(Last Revised: 4/2/21, 3. )