Subquery Flashcards

1
Q

What is a self-contained subquery?

A
  • 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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What is a scalar subquery?

A

A query that returns a single value

SQL Server 70-461 04-02a

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Where can a self-contained scalar subquery be used?

A
  • 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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What might a query using a scalar subquery look like?

A
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

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

A

It fails at run time

SQL Server 70-461 04-02a

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

What happens if a scalar query returns an empty set?

A

It is converted to a null

SQL Server 70-461 04-02a

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

What is a multi-valued subquery?

A
  • 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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

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

A

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

SQL Server 70-461 04-02a

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

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

A
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

Where is EXISTS placed in the query?

A

Just after WHERE in the outer query

SQL Server 70-461 04-02a

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

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

A
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Can you negate EXITSTS?

A

Yes. You can use NOT EXISTS

SQL Server 70-461 04-02a

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

What might an example of a correlated subquery look like?

A
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly