Scribd
Upload
19 views1 page

Window Functions

The document contains SQL queries for various employee salary analyses. It includes fetching the maximum salary per department, selecting the first two employees from each department, retrieving the top three employees based on salary, and comparing each employee's salary to the previous one within their department. These queries utilize window functions such as ROW_NUMBER(), RANK(), and LAG() for data manipulation.

Uploaded by

Vijay Kumar
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
19 views1 page

Window Functions

The document contains SQL queries for various employee salary analyses. It includes fetching the maximum salary per department, selecting the first two employees from each department, retrieving the top three employees based on salary, and comparing each employee's salary to the previous one within their department. These queries utilize window functions such as ROW_NUMBER(), RANK(), and LAG() for data manipulation.

Uploaded by

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

Select e.

* , max(Salary) over(partition by dept_name) as max_salary from employee

--fetch first two employees joined in each department


select * from(
select e.*,
row_number() over(partition by dept_name order by emp_id) as rn from empmloyee e) x
where x.rn<3

-- fetch the top 3 employees in each department earning the max salry

Select * from ( select e.* from , rank() over (partition by dept_name order by
salary desc) as rnk from employee e) x where s.rnk<4

-- display if the salary of an employee is higher or lower or equal to previous


employee

select e.* , lag(salary) over (partition by dept_name order by emp_id) as


prev_emp_salary ,
case when e.salary>lag(salary) over (partition by dept_name order by emp_id) then
'Higher than previous employee'
when e.salary<lag(salary) over (partition by dept_name order by emp_id) then
'Higher than previous employee'
when e.salary=lag(salary) over (partition by dept_name order by emp_id) then 'Equal
to previous employee' end sal_range

from employee e

You might also like