RSS Feed

Multiple Choice Questions - SQL Server Window Functions

Multiple Choice Questions - SQL Server Window Functions

1.
Mobiles table
MobileID MobileName Company
1 Nokia 100 MS
2 Nokia 120 MS
3 Nokia 130 MS
4 N72 Nokia
5 P21 Panaonic
6 M69 Micromax


What is the result of below query? 
SELECT ROW_NUMBER() OVER (ORDER BY MobileID), *
FROM Mobiles;
A)
No Column Name MobileID MobileName Company
1 1 Nokia 100 MS
2 2 Nokia 120 MS
3 3 Nokia 130 MS
4 4 N72 Nokia
5 5 P21 Panaonic
6 6 M69 Micromax

B)
No Column Name MobileID MobileName Company
1 6 M69 Micromax
2 5 P21 Panaonic
3 4 N72 Nokia
4 3 Nokia 130 MS
5 2 Nokia 120 MS
6 1 Nokia 100 MS


2.
The rows returned by a query using ROW_NUMBER() will be ordered exactly the same with each execution.

A) True
B) False

3.
Salary table
name emp_sal
a 100
b 70
b 200
d 300
e 140
f NULL


What is the result of below query? 
select NTILE(2) over(order by emp_sal),*
from salary
order by emp_sal
A)
No Column Name name emp_sal
1 f NULL
1 b 70
1 a 100
1 e 140
1 b 200
1 d 300

B)
No Column Name name emp_sal
2 f NULL
1 b 70
1 a 100
2 e 140
1 b 200
2 d 300

C)
No Column Name name emp_sal
1 a 100
1 b 70
1 b 200
2 d 300
2 e 140
2 f NULL

D)
No Column Name name emp_sal
1 f NULL
1 b 70
1 a 100
2 e 140
2 b 200
2 d 300


4.
In case of NTILE() if the total number of rows is 53 and the number of groups is five, the first three groups will have . . . rows and the two remaining groups will have . . . rows each.

A) 10, 10
B) 10, 11
C) 11, 10
D) 11, 11

5.
In case of PERCENT_RANK, NULL values are included by default and are treated as the lowest possible values.

A) True
B) False

6.
The numbers returned by the DENSE_RANK function may have gaps and does not always have consecutive ranks.

A) True
B) False

7.
Salary table
name emp_sal
a 100
b 70
b 200
d 300
e 140
f NULL

What is the result of below query? 
SELECT
  emp_sal,
  DENSE_RANK() OVER (ORDER BY emp_sal) dense_rank
FROM salary
ORDER BY emp_sal;
A)
emp_sal dense_rank
NULL 0
70 1
100 2
140 3
200 4
300 5

B)
emp_sal dense_rank
70 1
100 2
140 3
200 4
300 5

C)
emp_sal dense_rank
NULL 1
70 2
100 3
140 4
200 5
300 6

D)
None of above

8.
In case of RANK, the sort order that is used for the whole query determines the order in which the rows appear in a result set.

A) True
B) False

9.
Salary table
name emp_sal
a 100
b 70
b 200
d 300
e 140
f NULL
g 70

What is the result of below query? 
SELECT
  emp_sal,
  RANK() OVER (ORDER BY emp_sal) dense_rank
FROM salary
ORDER BY emp_sal;
A)
emp_sal dense_rank
NULL 1
70 2
70 2
100 3
140 4
200 5
300 6

B)
emp_sal dense_rank
NULL 1
70 2
70 2
100 4
140 5
200 6
300 7

C)
emp_sal dense_rank
NULL 0
70 2
70 2
100 4
140 5
200 6
300 7

D)
emp_sal dense_rank
NULL 0
70 1
70 1
100 2
140 3
200 4
300 5


10.
PERCENT_RANK is similar to the ...... function.

A) CUST_DIST
B) CUNE_DIST
C) CUMU_DIST
D) CUME_DIST

Answers