RSS Feed

Multiple Choice Questions - SQL Subqueries - Set 2

Multiple Choice Questions - SQL Subqueries - Set 2

1. Which of the following are not valid subquery type:

A) Single row subquery
B) Multiple row subquery
C) Multiple column subquery
D) Correlated subqueries
E) Nested subqueries
F) All are valid

2. SQL Server attempts to flatten some subqueries into joins when possible, to allow the Query Optimizer to select the optimal . . . . . order rather than be forced to process the query inside-out.

A) descending
B) increasing
C) sort
D) join

3. A noncorrelated (independent) subquery can be independently evaluated and relies only on its own . . . . . . clause for instructions.

A) SELECT
B) DELETE
C) INSERT
D) UPDATE

4. Shop_Info Table
Shop_Name Sales Txn_Date
New Delhi 1500 2015-05-01
Mumbai 250 2015-07-01
New Delhi 250 2015-08-01
Boston 700 2015-08-01

Location Table:
Region_Name Shop_Name
East Madras
East Calcutta
West New Delhi
West Mumbai

Which of the below subqueries is correct if we want to find the sales of all shops in the West region

A) 
SELECT SUM(Sales) FROM Shop_Info
WHERE EXISTS
(SELECT * FROM Location
WHERE Region_Name = 'East');
B) 
SELECT SUM(Sales) FROM Shop_Info
WHERE IN
(SELECT * FROM Location
WHERE Region_Name = 'West');
C) 
SELECT SUM(Sales) FROM Shop_Info
WHERE EXISTS
(SELECT * FROM Location
WHERE Region_Name = 'West');
D) 
SELECT SUM(Sales) FROM Shop_Info
WHERE NOT IN
(SELECT * FROM Location
WHERE Region_Name = 'West');

5. Often, a correlated subquery looks like a nested loop join.

A) True
B) False

6. SQL Server frequently uses . . . . . . . to evaluate EXISTS subqueries because the . . . . . . merely checks whether a row from one input joins with or matches any row from the other input.

A) hash-joins, semi-joins
B) semi-joins, hash-joins
C) semi-joins, semi-joins
D) hash-joins, hash-joins

7. Subqueries are disadvantageous over joins when you have to calculate an aggregate.

A) True
B) False

8. A subquery is evaluated . . . . . . for the entire parent statement.

A) twice
B) thrice
C) once
D) none of above

9. A . . . . . . . subquery is one that depends on a value in the outer query.

A) nested
B) correlated
C) materialized
D) both b & c

10. A subquery can itself include one or more subqueries. The subqueries that can be nested in a statement can be . . . . .

A) Limited
B) 256
C) 1024
D) Any

Answers