Scribd
Upload
7 views2 pages

Advanced SQL CTE Questions Partial

The document contains advanced SQL questions focused on using Common Table Expressions (CTEs) for various scenarios. It includes examples such as finding the top 3 highest paid employees per department, generating a list of numbers from 1 to 10, and calculating cumulative salary per department. Each question is accompanied by a SQL query and its expected output.

Uploaded by

jawarimandiofficial
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
7 views2 pages

Advanced SQL CTE Questions Partial

The document contains advanced SQL questions focused on using Common Table Expressions (CTEs) for various scenarios. It includes examples such as finding the top 3 highest paid employees per department, generating a list of numbers from 1 to 10, and calculating cumulative salary per department. Each question is accompanied by a SQL query and its expected output.

Uploaded by

jawarimandiofficial
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 2

100 Advanced SQL Questions with CTEs - Scenario-Based

Q1. Find top 3 highest paid employees per department using CTEs
Table: employees(emp_id, name, salary, department_id)

Query:

WITH RankedSalaries AS (
SELECT *,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
FROM employees
)
SELECT emp_id, name, salary, department_id
FROM RankedSalaries
WHERE rnk <= 3;

Output:
Displays top 3 earners from each department.

Q2. Generate a list of numbers from 1 to 10 using Recursive CTE


No table required

Query:

WITH RECURSIVE Numbers AS (


SELECT 1 AS num
UNION ALL
SELECT num + 1 FROM Numbers WHERE num < 10
)
SELECT * FROM Numbers;

Output:
Returns numbers from 1 to 10 in rows.

Q3. Show the cumulative salary per department using CTE and Window Function
Table: employees(emp_id, name, salary, department_id)

Query:
100 Advanced SQL Questions with CTEs - Scenario-Based

WITH DepartmentSalaries AS (
SELECT department_id, salary
FROM employees
)
SELECT department_id,
salary,
SUM(salary) OVER (PARTITION BY department_id ORDER BY salary) AS cumulative_salary
FROM DepartmentSalaries;

Output:
Displays running total of salary by department.

You might also like