FIRST_VALUE and LAST_VALUE in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select the first salary in each department using FIRST_VALUE.

SQL

SELECT department_id, FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary DESC) AS first_salary FROM employees WHERE salary > [1];

Drag options to blanks, or click blank then click option'

A3000

B2000

C10000

D5000

Attempts:

3 left

💡 Hint

Common Mistakes

Using a salary threshold that excludes all rows.

Not using the PARTITION BY clause.

The query filters employees with salary greater than 3000 and uses FIRST_VALUE to get the highest salary per department.

2fill in blank

medium

Complete the code to select the last hire date in each department using LAST_VALUE.

SQL

SELECT department_id, LAST_VALUE(hire_date) OVER (PARTITION BY department_id ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND [1]) AS last_hire FROM employees;

Drag options to blanks, or click blank then click option'

ACURRENT ROW

B1 PRECEDING

CUNBOUNDED FOLLOWING

D1 FOLLOWING

Attempts:

3 left

💡 Hint

Common Mistakes

Omitting the window frame clause causes unexpected results.

Using CURRENT ROW causes LAST_VALUE to return the current row's value.

LAST_VALUE requires the window frame to extend to UNBOUNDED FOLLOWING to return the last value in the partition correctly.

3fill in blank

hard

Fix the error in the query to correctly get the first salary per department.

SQL

SELECT department_id, FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY [1]) AS first_salary FROM employees;

Drag options to blanks, or click blank then click option'

Ahire_date ASC

Bsalary DESC

Cdepartment_id DESC

Dsalary ASC

Attempts:

3 left

💡 Hint

Common Mistakes

Ordering descending returns the highest salary, not the first.

Ordering by department_id does not affect salary order.

Ordering by salary ASC returns the smallest salary as the first value, which is the usual intent for FIRST_VALUE.

4fill in blank

hard

Fill both blanks to get the last hire date per department correctly.

SQL

SELECT department_id, LAST_VALUE(hire_date) OVER (PARTITION BY department_id ORDER BY hire_date ROWS BETWEEN [1] AND [2]) AS last_hire FROM employees;

Drag options to blanks, or click blank then click option'

AUNBOUNDED PRECEDING

BCURRENT ROW

CUNBOUNDED FOLLOWING

D1 PRECEDING

Attempts:

3 left

💡 Hint

Common Mistakes

Using CURRENT ROW as the end frame causes incorrect results.

Omitting the frame clause leads to unexpected last values.

The window frame must start from UNBOUNDED PRECEDING and end at UNBOUNDED FOLLOWING for LAST_VALUE to work as expected.

5fill in blank

hard

Fill all three blanks to select the first and last salary per department and filter salaries above 4000.

SQL

SELECT department_id, FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary [1]) AS first_salary, LAST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary ROWS BETWEEN [2] AND [3]) AS last_salary FROM employees WHERE salary > 4000;

Drag options to blanks, or click blank then click option'

AASC

BUNBOUNDED PRECEDING

CUNBOUNDED FOLLOWING

DDESC

Attempts:

3 left

💡 Hint

Common Mistakes

Using DESC ordering for FIRST_VALUE returns the highest salary first.

Omitting the window frame for LAST_VALUE causes wrong results.

Ordering salary ascending gets the lowest salary first. The window frame from UNBOUNDED PRECEDING to UNBOUNDED FOLLOWING ensures LAST_VALUE returns the correct last salary.