NTH_VALUE function in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select the 3rd salary value in the ordered salary list for each department.

SQL

SELECT department, salary, NTH_VALUE(salary, [1]) OVER (PARTITION BY department ORDER BY salary) AS third_salary FROM employees;

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

A2

B1

C3

D4

Attempts:

3 left

💡 Hint

Common Mistakes

Using 2 or 4 instead of 3 for the position.

Confusing the order of arguments in NTH_VALUE.

The NTH_VALUE function returns the value of the salary at the specified position in the ordered set. Here, 3 means the 3rd salary.

2fill in blank

medium

Complete the code to get the 1st salary value in the ordered salary list for each department.

SQL

SELECT department, salary, NTH_VALUE(salary, [1]) OVER (PARTITION BY department ORDER BY salary DESC) AS highest_salary FROM employees;

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

A1

B2

C3

D4

Attempts:

3 left

💡 Hint

Common Mistakes

Using 0 or numbers greater than 1 when wanting the first value.

Not ordering DESC to get the highest salary first.

Using 1 as the second argument returns the first value in the ordered list, which is the highest salary because of DESC order.

3fill in blank

hard

Fix the error in the code to correctly get the 2nd salary value per department.

SQL

SELECT department, salary, NTH_VALUE(salary, [1]) OVER (PARTITION BY department ORDER BY salary) AS second_salary FROM employees;

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

A1

B0

C3

D2

Attempts:

3 left

💡 Hint

Common Mistakes

Using 0 which is invalid.

Using 1 which returns the first value, not the second.

The second argument must be 2 to get the 2nd value. Using 0 or 1 is incorrect.

4fill in blank

hard

Fill both blanks to get the 4th highest salary per department.

SQL

SELECT department, salary, NTH_VALUE(salary, [1]) OVER (PARTITION BY department ORDER BY salary [2]) AS fourth_highest FROM employees;

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

A4

BDESC

CASC

D3

Attempts:

3 left

💡 Hint

Common Mistakes

Ordering ASC when wanting highest values.

Using position 3 or other numbers instead of 4.

To get the 4th highest salary, use 4 as the position and order salaries descending (DESC).

5fill in blank

hard

Fill all three blanks to get the 2nd lowest salary per department and alias it as second_lowest_salary.

SQL

SELECT department, salary, NTH_VALUE(salary, [1]) OVER (PARTITION BY department ORDER BY salary [2] ROWS BETWEEN UNBOUNDED PRECEDING AND [3]) AS second_lowest_salary FROM employees;

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

A2

BASC

CCURRENT ROW

DDESC

Attempts:

3 left

💡 Hint

Common Mistakes

Ordering DESC when wanting lowest values.

Using wrong frame clause causing unexpected results.

Using position 1 instead of 2.

To get the 2nd lowest salary, use position 2, order ascending (ASC), and frame rows between unbounded preceding and current row.