This set of SQL Server Multiple Choice Questions & Answers (MCQs) focuses on “Ranking”.
1. Which of the following is not a ranking function?
a) RANK
b) NTILE
c) ROW_NUMBER
d) All of the mentioned
View Answer
Explanation: Ranking functions are a subset of the built in functions in SQL Server.
2. Point out the wrong statement.
a) The ROW_NUMBER function simply assigns sequential numbering to the records of a result-set or to the records within groups of a result-set
b) OVER clause is not required in all the ranking functions
c) SQL Server introduced four different ranking functions
d) All of the mentioned
View Answer
Explanation: OVER clause is required in all the ranking functions and with that you specify the partitioning and ordering of records before the ranking functions are evaluated.
3. Which of the following is the simplest ranking function?
a) RANK
b) NTILE
c) ROW_NUMBER
d) None of the mentioned
View Answer
Explanation: The ROW_NUMBER ranking function is the simplest of the ranking functions.Its purpose in life is to provide consecutive numbering of the rows in the result set by the order selected in the OVER clause for each partition specified in the OVER clause.
4. Which of the clause is not mandatory?
a) OVER clause
b) ORDER BY clause
c) PARTITION BY clause
d) All of the mentioned
View Answer
Explanation: PARTITION BY clause is not mandatory and if you don’t specify it all the records of the result-set will be considered as a part of single record group.
5. Point out the wrong statement.
a) RANK() returns the rank of each row in the result set of partitioned column
b) DENSE_RANK() is same as RANK() function. Only difference is returns rank without gaps
c) NTILE() distributes the columns in an ordered partition into a specified number of groups
d) ROW_NUMBER() returns the serial number of the row order by specified column
View Answer
Explanation: NTILE divides the partitioned result set into specified number of groups in an order.
6. Which of the function provides consecutive numbering except in the case of a tie?
a) RANK
b) NTILE
c) ROW_NUMBER
d) None of the mentioned
View Answer
Explanation: RANK function is used when you want all tied rows to have the same ranking.
7. Which of the following will not raise error if not used?
a) OVER clause
b) ORDER BY clause
c) PARTITION BY clause
d) All of the mentioned
View Answer
Explanation: PARTITION BY clause is not mandatory and when you specify a column/set of columns with PARTITION BY clause then it will divide the result-set into record groups/partitions.
8. Which of the following error message will be displayed if ORDER CLAUSE is not mentioned in ROW NUMBER function?
a) “The ranking function “ROW_NUMBER” must have an ORDER BY clause.”
b) “The ranking function “ROW_NUMBER” must have an OVER BY clause.”
c) “The ranking function “ROW_NUMBER” must have an PARTITION BY clause.”
d) All of the mentioned
View Answer
Explanation: ORDER BY CLAUSE is not optional clause in ROW NUMBER function.
9. Which of the following functions are similar?
a) RANK and NTILE
b) RANK and DENSE_RANK
c) DENSE_RANK and NTILE
d) None of the mentioned
View Answer
Explanation: DENSE_RANK() is same as RANK() function. Only difference is returns rank without gaps.
10. Which of the following function is used when you want all tied rows to have the same ranking?
a) RANK
b) NTILE
c) ROW_NUMBER
d) None of the mentioned
View Answer
Explanation: The numbers assigned by RANK are not necessarily consecutive.
Sanfoundry Global Education & Learning Series – SQL Server.
To practice all areas of SQL Server, here is complete set of 1000+ Multiple Choice Questions and Answers.
If you find a mistake in question / option / answer, kindly take a screenshot and email to [email protected]
- Practice Programming MCQs
- Check SQL Server Books
- Check Information Technology Books
- Apply for Programming Internship
