MCQs on Dynamic SQL and Advanced T-SQL | SQL Server

This collection of 30 multiple-choice questions (MCQs) focuses on advanced SQL Server topics such as Dynamic SQL, FOR XML/JSON queries, PIVOT/UNPIVOT operators, and advanced window functions. These MCQs are designed for SQL Server professionals aiming to enhance their query optimization skills and database management techniques.

---

Creating Dynamic SQL

1. Which of the following is used to execute a string of SQL code dynamically in SQL Server?
   • a) EXEC
   • b) EXECUTE
   • c) sp_executesql
   • d) All of the above
2. What is the main purpose of dynamic SQL in SQL Server?
   • a) To improve query execution time
   • b) To create and execute SQL code at runtime
   • c) To fetch multiple result sets
   • d) To optimize stored procedures
3. Which of the following is true about the sp_executesql stored procedure in dynamic SQL?
   • a) It only executes SELECT queries
   • b) It supports parameterized queries
   • c) It doesn’t allow variable substitution
   • d) It cannot return result sets
4. What should you use to prevent SQL injection in dynamic SQL queries?
   • a) SELECT statements
   • b) sp_executesql with parameters
   • c) Using EXEC without parameters
   • d) Dynamic SQL with open cursors
5. Which of the following SQL commands cannot be used with dynamic SQL?
   • a) INSERT
   • b) DELETE
   • c) CREATE
   • d) ALTER DATABASE

---

FOR XML and FOR JSON Queries

6. Which clause in SQL Server is used to convert query results into XML format?
   • a) FOR XML AUTO
   • b) FOR XML RAW
   • c) FOR XML PATH
   • d) All of the above
7. What is the default root element for a FOR XML PATH query?
   • a) root
   • b) xmlroot
   • c) data
   • d) None of the above
8. Which option would you use in FOR XML to generate XML with each row as a separate element?
   • a) FOR XML AUTO
   • b) FOR XML PATH
   • c) FOR XML RAW
   • d) FOR XML EXPLICIT
9. Which SQL clause converts query results into JSON format in SQL Server?
   • a) FOR JSON PATH
   • b) FOR JSON AUTO
   • c) JSON QUERY
   • d) Both a and b
10. What is the purpose of the INCLUDE_NULL_VALUES option in FOR JSON queries?
    • a) It eliminates NULL values from the JSON output.
    • b) It includes NULL values in the JSON output.
    • c) It formats JSON for compatibility.
    • d) None of the above

---

PIVOT and UNPIVOT Operators

11. What does the PIVOT operator do in SQL Server?
    • a) Converts rows to columns
    • b) Converts columns to rows
    • c) Aggregates data into groups
    • d) None of the above
12. Which function is often used in conjunction with PIVOT to aggregate data in SQL Server?
    • a) SUM
    • b) COUNT
    • c) AVG
    • d) All of the above
13. What is the purpose of the UNPIVOT operator in SQL Server?
    • a) Converts rows into columns
    • b) Converts columns into rows
    • c) Removes duplicates from the result set
    • d) Combines multiple rows into one
14. Which of the following is the correct syntax for using PIVOT in SQL Server?
    • a) SELECT * FROM table PIVOT (SUM(column) FOR column IN (val1, val2)) AS pivot_table
    • b) SELECT * FROM table PIVOT (SUM(column) FOR column BY (val1, val2)) AS pivot_table
    • c) SELECT * FROM table UNPIVOT (column FOR column IN (val1, val2)) AS unpivot_table
    • d) SELECT * FROM table PIVOT (AVG(column) FOR column IN (val1, val2)) AS pivot_table
15. Which of the following is true about the UNPIVOT operator?
    • a) It allows converting columns into rows.
    • b) It is used to summarize data.
    • c) It changes row values into column headers.
    • d) It removes NULL values from the output.

---

Advanced Window Functions (ROW_NUMBER, RANK, etc.)

16. Which window function is used to assign a unique sequential integer to rows within a partition of a result set?
    • a) ROW_NUMBER()
    • b) RANK()
    • c) DENSE_RANK()
    • d) NTILE()
17. What does the RANK() window function do when there are ties in the result set?
    • a) It skips the subsequent rank numbers after a tie.
    • b) It assigns the same rank to all tied rows.
    • c) It assigns a unique rank to each row, even with ties.
    • d) None of the above
18. Which window function can be used to rank rows within a partition and eliminate gaps between the ranks?
    • a) ROW_NUMBER()
    • b) RANK()
    • c) DENSE_RANK()
    • d) NTILE()
19. What is the purpose of the NTILE() window function in SQL Server?
    • a) It divides the result set into a specified number of approximately equal parts.
    • b) It calculates the percentile rank for rows in a partition.
    • c) It generates a unique sequence number for rows.
    • d) It ranks rows based on a specific condition.
20. In which scenarios would you typically use the PARTITION BY clause with window functions?
    • a) To group rows and reset the calculation for each partition.
    • b) To sort rows in ascending order.
    • c) To aggregate data across the entire result set.
    • d) None of the above
21. What does the DENSE_RANK() function return when there are multiple rows with the same value in a partition?
    • a) A unique sequential integer with gaps for ties.
    • b) The same rank for tied rows without gaps.
    • c) A random ranking for tied rows.
    • d) An error message.
22. How can the ROW_NUMBER() function be used in conjunction with a WHERE clause?
    • a) To limit the result set to a specific number of rows.
    • b) To group rows before applying the WHERE condition.
    • c) To filter rows before assigning row numbers.
    • d) It cannot be used with a WHERE clause.
23. Which of the following is an advantage of using window functions over traditional aggregation methods?
    • a) They allow for calculations on individual rows while preserving the result set.
    • b) They speed up query execution by eliminating subqueries.
    • c) They require fewer resources compared to regular queries.
    • d) They provide more flexibility in result formatting.
24. Which of the following window functions does not require an ORDER BY clause?
    • a) ROW_NUMBER()
    • b) RANK()
    • c) NTILE()
    • d) All window functions require an ORDER BY clause.
25. What does the following query return?
    SELECT ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) FROM Employees;
    • a) The rank of employees within each department based on salary.
    • b) The unique row number for employees, ordered by salary in descending order.
    • c) The total number of employees in each department.
    • d) An error because ROW_NUMBER() requires a PARTITION clause.
26. Which of the following is a valid example of using window functions to calculate a running total?
    • a) SELECT SUM(Sales) OVER (ORDER BY Date) FROM Orders;
    • b) SELECT SUM(Sales) FROM Orders WHERE Date > ‘2023-01-01’;
    • c) SELECT COUNT(Sales) OVER (PARTITION BY Category) FROM Orders;
    • d) SELECT AVG(Sales) OVER (ORDER BY Date) FROM Orders;
27. What does the ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW clause in a window function define?
    • a) It calculates the value from the beginning of the partition to the current row.
    • b) It includes only the current row in the calculation.
    • c) It ignores previous rows in the partition.
    • d) It resets the calculation for each new row.
28. Which of the following window functions would you use to calculate the percentage rank of a value in a result set?
    • a) NTILE()
    • b) PERCENT_RANK()
    • c) DENSE_RANK()
    • d) ROW_NUMBER()
29. Which SQL Server version first introduced support for window functions?
    • a) SQL Server 2000
    • b) SQL Server 2005
    • c) SQL Server 2008
    • d) SQL Server 2012
30. Which clause would you use to reset the window function calculation for each department in the query?
    • a) ORDER BY Department
    • b) GROUP BY Department
    • c) PARTITION BY Department
    • d) RESET BY Department

---

Answer Key

Qno	Answer (Option with Text)
1	d) All of the above
2	b) To create and execute SQL code at runtime
3	b) It supports parameterized queries
4	b) sp_executesql with parameters
5	d) ALTER DATABASE
6	d) All of the above
7	a) root
8	b) FOR XML PATH
9	d) Both a and b
10	b) It includes NULL values in the JSON output
11	a) Converts rows to columns
12	d) All of the above
13	b) Converts columns into rows
14	a) SELECT * FROM table PIVOT (SUM(column) FOR column IN (val1, val2)) AS pivot_table
15	a) It allows converting columns into rows.
16	a) ROW_NUMBER()
17	a) It skips the subsequent rank numbers after a tie.
18	c) DENSE_RANK()
19	a) It divides the result set into a specified number of approximately equal parts.
20	a) To group rows and reset the calculation for each partition.
21	b) The same rank for tied rows without gaps.
22	a) To limit the result set to a specific number of rows.
23	a) They allow for calculations on individual rows while preserving the result set.
24	d) All window functions require an ORDER BY clause.
25	b) The unique row number for employees, ordered by salary in descending order.
26	a) SELECT SUM(Sales) OVER (ORDER BY Date) FROM Orders;
27	a) It calculates the value from the beginning of the partition to the current row.
28	b) PERCENT_RANK()
29	b) SQL Server 2005
30	c) PARTITION BY Department