MS SQL Server - 90 - Standard and Nonstandard Functions MCQ

90 – Standard and Nonstandard Functions MCQ

1 / 25

1. What does the TOP clause in SQL Server allow you to do?

B. Limit the number of rows returned by a query

A. Specify the number of columns to retrieve

D. Filter rows based on a condition

C. Order the result set in ascending order

2 / 25

2. In SQL Server, how do you use the OFFSET/FETCH clause to skip the first 5 rows and retrieve the next 10 rows?

D. FETCH 10 ROWS AFTER 5 ROWS

C. SKIP 5 ROWS FETCH 10 ROWS ONLY

B. OFFSET 10 ROWS FETCH 5 ROWS ONLY

A. OFFSET 5 ROWS FETCH 10 ROWS ONLY

3 / 25

3. What is the primary purpose of the NTILE() function in SQL Server?

A. Calculating the average of values

C. Dividing the result set into roughly equal parts or percentiles

D. Aggregating values within groups

B. Assigning a unique number to each row

4 / 25

4. In SQL Server, what is the maximum number of parts or percentiles into which the NTILE() function can divide a result set?

A. 5

C. 100

B. 10

D. There is no maximum limit

5 / 25

5. Which SQL Server clause allows you to rotate rows into columns, creating a cross-tabulation or pivot table?

D. UNPIVOT

A. GROUP BY

B. HAVING

C. PIVOT

6 / 25

6. What does the STRING_AGG() function do in SQL Server?

A. Aggregates numeric values

C. Calculates the average of values

D. Retrieves the number of rows in a result set

B. Concatenates values into a single string

7 / 25

7. In SQL Server, what is the primary purpose of the PIVOT clause?

C. Filtering rows based on a condition

A. Aggregating numeric values

B. Transforming rows into columns

D. Calculating the sum of values

8 / 25

8. What is the primary benefit of using the OFFSET/FETCH clause in SQL Server?

C. Enhancing data encryption

B. Improving query performance

A. Simplifying query writing

D. Reducing storage space

9 / 25

9. In SQL Server, how do you use the OFFSET/FETCH clause to retrieve the first 10 rows of a result set?

D. SKIP 10 ROWS FETCH 10 ROWS ONLY

A. OFFSET 10 ROWS FETCH 10 ROWS ONLY

B. FETCH 10 ROWS ONLY

C. OFFSET 0 ROWS FETCH 10 ROWS ONLY

10 / 25

10. Which SQL Server function returns an approximate count of distinct values in a column?

A. COUNT()

C. AVG()

B. SUM()

D. APPROX_COUNT_DISTINCT()

11 / 25

11. What is the primary purpose of the UNPIVOT clause in SQL Server?

B. Transforming columns into rows

D. Calculating the median of values

C. Filtering rows based on a condition

A. Aggregating numeric values

12 / 25

12. In SQL Server, how do you use the NTILE() function to divide a result set into 4 equal parts or quartiles?

D. NTILE(10)

A. NTILE(4)

B. NTILE(25)

C. NTILE(100)

13 / 25

13. Which SQL Server function returns the number of rows in a result set?

B. SUM()

C. AVG()

D. ROWCOUNT()

A. COUNT()

14 / 25

14. In SQL Server, what is the purpose of the ROW_NUMBER() function?

D. Filtering rows based on a condition

A. Aggregating numeric values

C. Dividing the result set into percentiles

B. Assigning a rank to each row within a result set

15 / 25

15. What is the main difference between the OFFSET/FETCH and TOP clauses in SQL Server?

A. OFFSET/FETCH can be used to skip rows, while TOP is used for limiting rows.

C. OFFSET/FETCH can be used for sorting rows, while TOP is used for aggregating values.

B. TOP can be used to skip rows, while OFFSET/FETCH is used for limiting rows.

D. TOP can be used for filtering rows, while OFFSET/FETCH is used for sorting rows.

16 / 25

16. In SQL Server, what is the purpose of the PIVOT function?

D. Filtering rows based on a condition

B. Calculating the average of values

C. Aggregating numeric values

A. Transforming rows into columns

17 / 25

17. Which SQL Server function returns the total sum of values in a column?

D. TOTAL()

C. AVG()

B. SUM()

A. COUNT()

18 / 25

18. In SQL Server, how do you use the OFFSET/FETCH clause to skip the first 5 percent of rows and retrieve the next 10 percent?

A. OFFSET 5 PERCENT FETCH 10 PERCENT ONLY

D. OFFSET 10 ROWS FETCH 5 ROWS ONLY

B. OFFSET 5 ROWS FETCH 10 ROWS ONLY

C. OFFSET 5 ROWS FETCH 10 PERCENT ONLY

19 / 25

19. What is the primary purpose of the STRING_AGG() function in SQL Server?

B. Concatenating values into a single string

C. Calculating the median of values

A. Aggregating numeric values

D. Retrieving the number of rows in a result set

20 / 25

20. In SQL Server, what is the purpose of the LEAD() function?

B. Calculating the sum of values

D. Assigning ranks to rows

A. Aggregating numeric values

C. Retrieving the next value in a result set

21 / 25

21. What does the APPROX_COUNT_DISTINCT() function provide in SQL Server compared to the COUNT(DISTINCT) function?

A. It provides an exact count of distinct values.

B. It provides a faster but approximate count of distinct values.

D. It counts the total number of NULL values.

C. It counts the total number of rows.

22 / 25

22. In SQL Server, what is the purpose of the STDEV() function?

B. Aggregating numeric values

C. Finding the standard deviation of values

D. Assigning ranks to rows

A. Calculating the average of values

23 / 25

23. Which SQL Server clause allows you to specify the number of rows or percentage of rows to retrieve from a result set?

B. HAVING

A. GROUP BY

C. OFFSET/FETCH

D. UNION

24 / 25

24. What is the primary benefit of using the APPROX_COUNT_DISTINCT() function in SQL Server?

A. It provides an exact count of distinct values.

D. It performs faster than COUNT(DISTINCT).

C. It counts the total number of rows.

B. It is more memory-efficient.

25 / 25

25. In SQL Server, what is the primary purpose of the MEDIAN() function?

D. Retrieving the most frequently occurring value in a column

C. Aggregating numeric values

A. Calculating the sum of values

B. Finding the middle value of a numeric column's values

Your score is

MS SQL Server – 90 – Standard and Nonstandard Functions MCQ