FIRST_VALUE & LAST_VALUE — Concept Check
Questions on FIRST_VALUE & LAST_VALUE. FIRST_VALUE returns the first value in the window frame; LAST_VALUE requires an explicit extended frame clause or it will only ever return the current row's own value.
Questions in this quiz
8 questions on FIRST_VALUE & LAST_VALUE — Concept Check. Work through them here, then start the quiz to answer them and see your score.
1.In a SQL query, you use LAST_VALUE on a salary column without extending the frame. What value does it return for each row?
- AThe first row's salary
- BThe lowest salary in the partition
- CThe highest salary in the partition
- DThe current row's salary
2.You want to find the highest salary in each department using FIRST_VALUE. Which clause should you use?
- AORDER BY salary ASC
- BORDER BY department_id
- CORDER BY name
- DORDER BY salary DESC
3.A developer uses LAST_VALUE to get the last salary in a department but gets unexpected results. What might be wrong?
- AThe function should be FIRST_VALUE
- BThe PARTITION BY clause is incorrect
- CThe ORDER BY clause is missing
- DThe frame is not extended to UNBOUNDED FOLLOWING
4.Which SQL function can be used to get the second-highest salary in a department?
- ANTH_VALUE with n=2
- BFIRST_VALUE with n=2
- CMAX_VALUE with n=2
- DLAST_VALUE with n=2
5.A developer mistakenly believes LAST_VALUE returns the last row of the partition by default. Why is this incorrect?
- AIt requires ORDER BY to function
- BIt requires a WHERE clause
- CIt defaults to the first row's value
- DIt defaults to the current row's value
6.In the query using FIRST_VALUE to get top earners, what does the default frame range cover?
- AFrom the current row to the end
- BThe entire partition
- CFrom the start to the current row
- DOnly the current row
7.A query uses NTH_VALUE to get the third salary in a department but returns NULL. What could be the reason?
- AThe frame isn't extended
- BThe PARTITION BY clause is incorrect
- CThe ORDER BY clause is missing
- DThere are fewer than three rows in the partition
8.What is a common mistake when using LAST_VALUE to determine the lowest salary in a department?
- AIncorrect PARTITION BY clause
- BNot extending the frame to UNBOUNDED FOLLOWING
- CUsing FIRST_VALUE instead
- DMissing SELECT DISTINCT