Workers With The Highest And Lowest Salaries

#10152

You have been asked to find the employees with the highest and lowest salary.

Your output should include the employee's ID, salary, and department, as well as a column salary_type that categorizes the output by:

  • 'Highest Salary' represents the highest salary
  • 'Lowest Salary' represents the lowest salary

table name: worker


table name: title


Solution:

with cte as(
select w.worker_id,w.salary,w.department ,
(case when w.salary = (select max(salary) from worker) then 'Highest Salary' 
  when w.salary = (select min(salary) from worker) then 'Lowest Salary'
  else null
  end) as salary_type
from worker as w
left join title as t on w.worker_id = t.worker_ref_id
)select * from cte where salary_type is not null

Output:


SQL Script:

USE [StrataScratch]
GO
/****** Object:  Table [dbo].[title]    Script Date: 14-01-2024 20:13:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[title](
	[worker_ref_id] [int] NULL,
	[worker_title] [varchar](50) NULL,
	[affected_from] [datetime] NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[worker]    Script Date: 14-01-2024 20:13:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[worker](
	[worker_id] [int] NULL,
	[first_name] [varchar](50) NULL,
	[last_name] [varchar](50) NULL,
	[salary] [int] NULL,
	[joining_date] [datetime] NULL,
	[department] [varchar](150) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[title] ([worker_ref_id], [worker_title], [affected_from]) VALUES (1, N'Manager', CAST(N'2016-02-20T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[title] ([worker_ref_id], [worker_title], [affected_from]) VALUES (2, N'Executive', CAST(N'2016-06-11T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[title] ([worker_ref_id], [worker_title], [affected_from]) VALUES (8, N'Executive', CAST(N'2016-06-11T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[title] ([worker_ref_id], [worker_title], [affected_from]) VALUES (5, N'Manager', CAST(N'2016-06-11T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[title] ([worker_ref_id], [worker_title], [affected_from]) VALUES (4, N'Asst. Manager', CAST(N'2016-06-11T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[title] ([worker_ref_id], [worker_title], [affected_from]) VALUES (7, N'Executive', CAST(N'2016-06-11T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[title] ([worker_ref_id], [worker_title], [affected_from]) VALUES (6, N'Lead', CAST(N'2016-06-11T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[title] ([worker_ref_id], [worker_title], [affected_from]) VALUES (3, N'Lead', CAST(N'2016-06-11T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[worker] ([worker_id], [first_name], [last_name], [salary], [joining_date], [department]) VALUES (1, N'Monika', N'Arora', 100000, CAST(N'2014-02-20T00:00:00.000' AS DateTime), N'HR')
GO
INSERT [dbo].[worker] ([worker_id], [first_name], [last_name], [salary], [joining_date], [department]) VALUES (2, N'Niharika', N'Verma', 80000, CAST(N'2014-06-11T00:00:00.000' AS DateTime), N'Admin')
GO
INSERT [dbo].[worker] ([worker_id], [first_name], [last_name], [salary], [joining_date], [department]) VALUES (3, N'Vishal', N'Singhal', 300000, CAST(N'2014-02-20T00:00:00.000' AS DateTime), N'HR')
GO
INSERT [dbo].[worker] ([worker_id], [first_name], [last_name], [salary], [joining_date], [department]) VALUES (4, N'Amitah', N'Singh', 500000, CAST(N'2014-02-20T00:00:00.000' AS DateTime), N'Admin')
GO
INSERT [dbo].[worker] ([worker_id], [first_name], [last_name], [salary], [joining_date], [department]) VALUES (5, N'Vivek', N'Bhati', 500000, CAST(N'2014-06-11T00:00:00.000' AS DateTime), N'Admin')
GO
INSERT [dbo].[worker] ([worker_id], [first_name], [last_name], [salary], [joining_date], [department]) VALUES (6, N'Vipul', N'Diwan', 200000, CAST(N'2014-06-11T00:00:00.000' AS DateTime), N'Account')
GO
INSERT [dbo].[worker] ([worker_id], [first_name], [last_name], [salary], [joining_date], [department]) VALUES (7, N'Satish', N'Kumar', 75000, CAST(N'2014-01-20T00:00:00.000' AS DateTime), N'Account')
GO
INSERT [dbo].[worker] ([worker_id], [first_name], [last_name], [salary], [joining_date], [department]) VALUES (8, N'Geetika', N'Chauhan', 90000, CAST(N'2014-04-11T00:00:00.000' AS DateTime), N'Admin')
GO
INSERT [dbo].[worker] ([worker_id], [first_name], [last_name], [salary], [joining_date], [department]) VALUES (9, N'Agepi', N'Argon', 90000, CAST(N'2015-04-10T00:00:00.000' AS DateTime), N'Admin')
GO
INSERT [dbo].[worker] ([worker_id], [first_name], [last_name], [salary], [joining_date], [department]) VALUES (10, N'Moe', N'Acharya', 65000, CAST(N'2015-04-11T00:00:00.000' AS DateTime), N'HR')
GO
INSERT [dbo].[worker] ([worker_id], [first_name], [last_name], [salary], [joining_date], [department]) VALUES (11, N'Nayah', N'Laghari', 75000, CAST(N'2014-03-20T00:00:00.000' AS DateTime), N'Account')
GO
INSERT [dbo].[worker] ([worker_id], [first_name], [last_name], [salary], [joining_date], [department]) VALUES (12, N'Jai', N'Patel', 85000, CAST(N'2014-03-21T00:00:00.000' AS DateTime), N'HR')
GO

Comments (0)
Questions List