Subquery

Question 1

What is a self-contained subquery?

Answer 1

• A subquery that has no dependency on the outer query.
• You could run the content of the subquery by itself and it would work just fine.

SQL Server 70-461 04-02a

Question 2

What is a scalar subquery?

Answer 2

A query that returns a single value

SQL Server 70-461 04-02a

Question 3

Where can a self-contained scalar subquery be used?

Answer 3

• Anywhere in the out query where a single-valued expression can appear.
• Examples are the WHERE and SELECT clauses

SQL Server 70-461 04-02a

Question 4

What might a query using a scalar subquery look like?

Answer 4

SELECT orderid, orderdate, empid, custid
FROM sales.orders
WHERE orderid=(SELECT MAX(O.orderid) FROM sales.orders AS O)

In the code above, (SELECT MAX(O.orderid) FROM sales.orders AS O) is the scalar subquery and it returns a single value.

SQL Server 70-461 04-02a

Question 5

What happens if what is supposed to be a scalar query returns more than one value?

Answer 5

It fails at run time

SQL Server 70-461 04-02a

Question 6

What happens if a scalar query returns an empty set?

Answer 6

It is converted to a null

SQL Server 70-461 04-02a

Question 7

What is a multi-valued subquery?

Answer 7

• A subquery that returns multiple values in the form of a single column.
• This would be useful if you wanted to use WHERE IN and get the values from a different table, for example.

SQL Server 70-461 04-02a

Question 8

Where can a self-contained multi-valued subquery be used?

Answer 8

A good example is with IN or NOT IN. They are examples but they are rarely used.

SQL Server 70-461 04-02a

Question 9

What might a query using a multi-valued subquery look like?

Answer 9

SELECT orderid
FROM sales.orders
WHERE empid IN
(SELECT E.empid
FROM HR.Employees AS E
WHERE E.lastname LIKE N'D%')

In the code above, (SELECT E.empid FROM HR.Employees AS E WHERE E.lastname LIKE N’D%’) is the multi-valued subquery and it returns multiple values as a single column.

SQL Server 70-461 04-02a

Question 10

Where is EXISTS placed in the query?

Answer 10

Just after WHERE in the outer query

SQL Server 70-461 04-02a

Question 11

What is an example of what a query using EXISTS would look like?

Answer 11

SELECT custid, companyname
FROM sales.customers AS C
WHERE EXISTS
(SELECT * FROM sales.orders AS O
WHERE O.custid=C.custid
AND O.orderdate='20070212')

This query returns customers only if they have had an order.

SQL Server 70-461 04-02a

Question 12

Can you negate EXITSTS?

Answer 12

Yes. You can use NOT EXISTS

SQL Server 70-461 04-02a

Question 13

What might an example of a correlated subquery look like?

Answer 13

SELECT categoryid, productid, productname, unitprice
FROM production.products AS P1
WHERE unitprice=
--Correlated Subquery
(
SELECT MIN(unitprice)
FROM production.products AS P2
WHERE P2.categoryid=P1.categoryid
)

P1.categoryid in the correlated subquery correlates to the outer query

SQL Server 70-461 04-02a