WorksheetsWindows Functions
Total questions: 13
Worksheet time: 39mins
Full name
Email ID
Student ID
Which all statements are NOT true about Windows Function?
They summarizes the values in the table
They perform calculation on set of rows somehow related to current row
They create different groups and performs aggregation
They are used to find out the Rank
With regards to Rank and Dense Rank, which statements are true?
RANK skips the number of positions after records with the same rank number.
RANK DENSE returns position numbers and it doesn’t skip records with the same rank number
RANK & DENSE RANK are same when there is no tie
All of above
To narrow a window function's application to a subgroup within a data set, use __________.
PARTITION
OVER
SUBGROUP
GROUP
Write a query to find the subject wise Rank.
SELECT subjects, s_name, mark,
rank() OVER ( partition by subjects order by mark desc ) AS 'Rnk' FROM result;
SELECT subjects, s_name, mark , rank() OVER (order by mark asc ) AS 'Rnk' FROM result;
SELECT subjects, s_name, mark, rank() OVER ( partition by subjects) AS 'Rnk' FROM result;
SELECT subjects, s_name, mark, rank() OVER ( partition by s_name order by mark ) AS 'Rnk' FROM result;
Which Query will give the desired result?
SELECT Country, Prod_name, Sales, SUM(Sales)
OVER(PARTITION BY Country) as grand_total
FROM table;
SELECT Country, Prod_name, Sales, SUM() OVER(PARTITION BY prod_name) as grand_total FROM table;
SELECT Country, Prod_name, Sales, grand_total OVER(PARTITION BY Prod_name) FROM Product_Sales;
SELECT Country, Prod_name, Sales, SUM(Sales) OVER(PARTITION BY Emp_Name) as grand_total FROM Product_Sales;
Which SQL query retrieves the details of the top 3 rank holders from the student table, considering that the ranks are determined based on the mtest column?
"select id, name, mtest,
rank() over (order by mtest desc) as Rnk
from student
where mtest is not null
having Rnk <= 3;"
"with c as (
select id, name, mtest,
rank() over (order by mtest desc) as Rnk
from student
where mtest is not null)
select *
from C
where Rnk <= 3;"
"with c as (
select id, name, mtest,
rank() over (order by mtest desc) as Rnk
from student
where mtest is not null)
select *
from C
having Rnk <= 3;"
"select id, name, mtest,
rank() over (order by mtest desc) as Rnk
from student
where mtest is not null
and Rnk <= 3;"
"7. What will be the output of the following query?
select name, mtest, class, ntile(4) over (order by name) as team
from student
where class is not null;"
It'll create a new column named 'team' and assign a number between 1-4 for each student, sorted by their names.
It'll create a new column named 'team' and assign values that represent the quartiles of the mtest, sorted by their names.
It'll create a new column named 'team' and assign a number between 1-4 for each student, sorted by their names descending order.
It'll create a new column named 'team' and assign values based on the mtest, sorted by their names ascending order.
8. Compare highest marks of mtest in each class with individual marks and show individual mtest in descending order?
"select Name, Class, Mtest,
high(mtest) over (partition by class order by mtest desc) as MxMtest
from student;"
"select Name, Class, Mtest,
max(mtest) over (partition by class order by mtest ) as MxMtest
from student;"
"select Name, Class, Mtest,
max(mtest) over (partition by class order by mtest desc) as MxMtest
from student;"
"select Name, Class, Mtest,
max(mtest) over (order by mtest desc) as MxMtest
from student;"
What is the primary purpose of using the LEAD() function in SQL?
It assigns a consecutive rank to each row within a partition of a result set.
It is useful in case, the current row values need to be compared with the data/value of the previous record.
It distributes rows of an ordered partition into a pre-defined number of roughly equal groups
It allows to access data of the following row, or the row after the subsequent row, and continue on.
What is the primary role of the DENSE_RANK() function in SQL?
It assigns a consecutive rank to each row within a partition of a result set.
It is useful in case, the current row values need to be compared with the data/value of the previous record.
It distributes rows of an ordered partition into a pre-defined number of roughly equal groups
It allows to access data of the following row, or the row after the subsequent row, and continue on.
