Windows Functions

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.