MCQs on Advanced Query Techniques | SQL Server

Explore the intricacies of advanced SQL Server query techniques with this set of 30 multiple-choice questions. Covering subqueries, CTEs, UNIONs, and derived tables, this ensures comprehensive learning.

---

MCQs on Advanced Query Techniques in SQL Server

Subqueries and Correlated Subqueries

1. Which of the following best describes a subquery?
   • A) A query that calls a stored procedure
   • B) A query nested within another query
   • C) A type of join
   • D) A query used in the WHERE clause
2. Where can a subquery NOT be used?
   • A) SELECT clause
   • B) FROM clause
   • C) WHERE clause
   • D) ORDER BY clause
3. A correlated subquery:
   • A) Executes independently of the outer query
   • B) References columns from the outer query
   • C) Cannot use aggregate functions
   • D) Always returns a single value
4. In a correlated subquery, how often is the subquery executed?
   • A) Once for the entire query
   • B) Once for each row processed by the outer query
   • C) Depends on the query optimization level
   • D) Only when required by the optimizer
5. Which of the following queries uses a subquery correctly?
   • A) SELECT * FROM Customers WHERE CustomerID = ALL (SELECT MAX(OrderID) FROM Orders)
   • B) SELECT * FROM Customers INNER JOIN (SELECT * FROM Orders)
   • C) SELECT * FROM Customers WHERE EXISTS (CustomerName FROM Orders)
   • D) SELECT * FROM Customers GROUP BY (SELECT OrderID FROM Orders)

---

Common Table Expressions (CTEs)

6. What is the main advantage of using a Common Table Expression (CTE)?
   • A) Improved execution speed
   • B) Simplifies complex queries by breaking them into logical parts
   • C) Allows only one-time execution
   • D) Can be used across multiple databases
7. What keyword is used to define a CTE?
   • A) TEMP
   • B) WITH
   • C) DECLARE
   • D) CREATE
8. A CTE is valid until:
   • A) The database is restarted
   • B) The session ends
   • C) The end of the query in which it is defined
   • D) It is explicitly deleted
9. Which of the following can be achieved using a recursive CTE?
   • A) Complex joins
   • B) Hierarchical data traversal
   • C) Distributed queries
   • D) Table indexing
10. Which of the following statements uses a valid CTE?
    • A) WITH EmployeeData (SELECT * FROM Employees)
    • B) WITH EmployeeData AS (SELECT * FROM Employees) SELECT * FROM EmployeeData
    • C) CREATE TEMP EmployeeData AS (SELECT * FROM Employees)
    • D) WITH EmployeeData (EmployeeID INT) SELECT * FROM Employees

---

UNION, INTERSECT, and EXCEPT

11. What is the purpose of the UNION operator?
    • A) Combine rows from two queries without duplicates
    • B) Combine rows from two queries including duplicates
    • C) Combine columns from two queries
    • D) Perform joins between tables
12. The difference between UNION and UNION ALL is:
    • A) UNION removes duplicate rows, UNION ALL does not
    • B) UNION performs faster than UNION ALL
    • C) UNION supports only one column, UNION ALL supports multiple columns
    • D) UNION ALL removes duplicate rows
13. INTERSECT in SQL Server is used to:
    • A) Find rows present in both queries
    • B) Subtract rows of one query from another
    • C) Perform joins between queries
    • D) Combine data from multiple columns
14. EXCEPT in SQL Server:
    • A) Returns rows present in both queries
    • B) Returns rows present in the first query but not in the second
    • C) Returns rows unique to both queries
    • D) Combines data from two queries
15. Which of the following is valid syntax for UNION?
    • A) SELECT Col1 FROM Table1 INTERSECT SELECT Col1 FROM Table2
    • B) SELECT Col1 FROM Table1 UNION SELECT Col1 FROM Table2
    • C) SELECT Col1 FROM Table1 JOIN UNION SELECT Col1 FROM Table2
    • D) SELECT Col1 FROM Table1 EXCEPT SELECT Col1 FROM Table2

---

Working with Derived Tables

16. A derived table is:
    • A) A permanent table stored in the database
    • B) A table created as part of a subquery in the FROM clause
    • C) A temporary table created by using CTEs
    • D) Another name for a table-valued function
17. Which of the following is an advantage of derived tables?
    • A) Increased execution speed
    • B) Simplified query logic for complex datasets
    • C) Ability to persist data across queries
    • D) Support for recursive operations
18. Which of the following demonstrates the correct use of a derived table?
    • A) SELECT * FROM (SELECT * FROM Orders) AS OrderData WHERE OrderID > 100
    • B) SELECT * FROM Orders WHERE EXISTS (SELECT * FROM Customers)
    • C) SELECT (SELECT * FROM Orders) AS OrderData
    • D) SELECT * FROM Orders JOIN (Customers)
19. How is a derived table named?
    • A) By using an alias in the SELECT clause
    • B) By using an alias in the FROM clause
    • C) By assigning it a table type
    • D) By defining a PRIMARY KEY constraint
20. Derived tables can be used in:
    • A) GROUP BY clauses
    • B) FROM clauses
    • C) WHERE clauses
    • D) ORDER BY clauses

---

Mixed Concepts

21. Which type of query often uses subqueries?
    • A) INSERT
    • B) SELECT
    • C) DROP
    • D) CREATE
22. Recursive CTEs must include:
    • A) A UNION ALL clause
    • B) An ORDER BY clause
    • C) An INDEX clause
    • D) A PRIMARY KEY
23. UNION ALL is preferred when:
    • A) Duplicate rows are needed in the output
    • B) Data must be merged with constraints
    • C) A single row needs to be retrieved
    • D) Data needs to be intersected
24. EXCEPT is NOT supported in:
    • A) PostgreSQL
    • B) MySQL
    • C) Oracle
    • D) SQL Server
25. Common Table Expressions improve readability by:
    • A) Creating temporary databases
    • B) Breaking down complex queries into smaller parts
    • C) Modifying existing stored procedures
    • D) Enhancing security features
26. Correlated subqueries are often inefficient because:
    • A) They run multiple times for each row in the outer query
    • B) They lack indexing support
    • C) They cannot use joins
    • D) They do not support aggregation
27. UNION operators work best when:
    • A) All columns in the queries have the same data type and number of columns
    • B) Different column numbers exist between queries
    • C) Subqueries are needed
    • D) Duplicate elimination is critical
28. What is the limitation of a derived table?
    • A) It cannot have an alias
    • B) It cannot be reused in another query
    • C) It is faster than CTEs
    • D) It must have an index
29. Which is NOT a valid use case for a CTE?
    • A) Simplifying recursive queries
    • B) Structuring complex joins
    • C) Modifying physical tables
    • D) Improving readability
30. INTERSECT returns:
    • A) A Cartesian product of rows
    • B) Common rows between two queries
    • C) Unique rows in both queries
    • D) Rows excluded by EXCEPT

---

Answers Table

Qno	Answer
1	B) A query nested within another query
2	D) ORDER BY clause
3	B) References columns from the outer query
4	B) Once for each row processed by the outer query
5	A) SELECT * FROM Customers WHERE CustomerID = ALL (SELECT MAX(OrderID) FROM Orders)
6	B) Simplifies complex queries by breaking them into logical parts
7	B) WITH
8	C) The end of the query in which it is defined
9	B) Hierarchical data traversal
10	B) WITH EmployeeData AS (SELECT * FROM Employees) SELECT * FROM EmployeeData
11	A) Combine rows from two queries without duplicates
12	A) UNION removes duplicate rows, UNION ALL does not
13	A) Find rows present in both queries
14	B) Returns rows present in the first query but not in the second
15	B) SELECT Col1 FROM Table1 UNION SELECT Col1 FROM Table2
16	B) A table created as part of a subquery in the FROM clause
17	B) Simplified query logic for complex datasets
18	A) SELECT * FROM (SELECT * FROM Orders) AS OrderData WHERE OrderID > 100
19	B) By using an alias in the FROM clause
20	B) FROM clauses
21	B) SELECT
22	A) A UNION ALL clause
23	A) Duplicate rows are needed in the output
24	B) MySQL
25	B) Breaking down complex queries into smaller parts
26	A) They run multiple times for each row in the outer query
27	A) All columns in the queries have the same data type and number of columns
28	B) It cannot be reused in another query
29	C) Modifying physical tables
30	B) Common rows between two queries