Salaries Differences

Write a query that calculates the difference between the highest salaries found in the marketing and engineering departments. Output just the absolute difference in salaries.

StrataScratch


table name: db_dept


table name: db_employee


Question Explanation: 

We have given two tables first  is department  and the second  is employee. Every employee will belong to a particular department. We need to find the employee who is earning highest in marketing and engineering departments then difference of salaries  between those 2 employees.

Solution Explanation:
Department id = 4 (marketing)
Department id = 1 (engineering)

max(salary) function can be used to get the maximum salary of employee in required departments.

We are going to calulate the highest salary of marketing and engineering departments using below query
  • select max(salary) as MaxEngSalry from db_employee where department_id=4
  • select max(salary) as MaxMarSalary from db_employee where department_id=1
Output:

Now, we need to find only the difference between these two max salaries. 
For that we are going to use one main query and these two above queries will be used as a subquery for that main query so that we can calculate the difference between these two.

Solution: 
select (e1.MaxEngSalry - e2.MaxMarSalary) as salaryDiff
from
(
	select max(salary) as MaxEngSalry from db_employee where department_id=4
) as e1,
( 
	select max(salary) as MaxMarSalary from db_employee where department_id=1
) as e2

Output:

SQL Script:
CREATE TABLE [dbo].[db_dept](
	[id] [int] NOT NULL,
	[department] [varchar](50) NULL,
)

CREATE TABLE [dbo].[db_employee](
	[id] [int] NOT NULL,
	[first_name] [varchar](50) NULL,
	[last_name] [varchar](50) NULL,
	[salary] [int] NULL,
	[department_id] [int] NULL,
 )
GO
INSERT [dbo].[db_dept] ([id], [department]) VALUES (1, N'engineering')
GO
INSERT [dbo].[db_dept] ([id], [department]) VALUES (2, N'human resource')
GO
INSERT [dbo].[db_dept] ([id], [department]) VALUES (3, N'operation')
GO
INSERT [dbo].[db_dept] ([id], [department]) VALUES (4, N'marketing')
GO
INSERT [dbo].[db_dept] ([id], [department]) VALUES (5, N'sales')
GO
INSERT [dbo].[db_dept] ([id], [department]) VALUES (6, N'customer care')
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10301, N'Keith', N'Morgan', 27056, 2)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10302, N'Tyler', N'Booth', 32199, 3)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10303, N'Clifford', N'Nguyen', 32165, 2)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10304, N'Mary', N'Jones', 49488, 3)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10305, N'Melissa', N'Lucero', 27024, 3)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10306, N'Ashley', N'Li', 28516, 4)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10307, N'Joseph', N'Solomon', 19945, 1)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10308, N'Anthony', N'Sanchez', 43801, 3)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10309, N'Katherine', N'Huffman', 12984, 4)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10310, N'Dawn', N'Foley', 28902, 2)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10311, N'Melissa', N'Holmes', 33575, 1)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10312, N'Kathleen', N'Davis', 44579, 2)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10313, N'Adam', N'Simmons', 15442, 6)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10314, N'Desiree', N'Brewer', 37494, 6)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10315, N'Sierra', N'Anderson', 20592, 6)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10316, N'Beth', N'Torres', 34902, 1)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10317, N'Pamela', N'Rodriguez', 48187, 4)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10318, N'Jessica', N'Austin', 43154, 2)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10319, N'Stephanie', N'Gamble', 31160, 2)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10320, N'Gregory', N'Cook', 22681, 4)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10321, N'Debra', N'Knapp', 17844, 5)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10322, N'Paul', N'Horton', 37431, 3)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10323, N'Henry', N'Solis', 45269, 4)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10324, N'William', N'Brewer', 15947, 1)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10325, N'Brian', N'Nelson', 27006, 1)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10326, N'Randall', N'Kramer', 20695, 2)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10327, N'Mary', N'Baker', 46654, 5)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10328, N'Gene', N'Shepard', 10143, 3)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10329, N'Christopher', N'Ramos', 37710, 4)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10330, N'Robin', N'Horne', 34679, 3)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10331, N'Alan', N'Wilson', 43730, 5)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10332, N'Jessica', N'Mccall', 40882, 5)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10333, N'Jennifer', N'Blankenship', 13433, 4)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10334, N'Michael', N'Mitchell', 19044, 2)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10335, N'Johnny', N'Fritz', 40638, 6)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10336, N'Gloria', N'Krause', 21670, 3)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10337, N'Jonathan', N'Holt', 30267, 2)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10338, N'Anthony', N'Bullock', 39362, 6)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10339, N'Robert', N'Mills', 13188, 1)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10340, N'Joshua', N'Stephens', 29339, 3)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10341, N'Alison', N'Hernandez', 45144, 3)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10342, N'Derek', N'Morgan', 11286, 2)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10343, N'Anthony', N'Morgan', 15145, 5)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10344, N'Jenny', N'Owens', 40606, 4)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10345, N'Elizabeth', N'Raymond', 33050, 5)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10346, N'Philip', N'Jones', 46356, 2)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10347, N'Seth', N'Gross', 25681, 5)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10348, N'Edwin', N'Johnson', 35144, 5)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10349, N'Ryan', N'Barnes', 25085, 1)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10350, N'Patrick', N'Booth', 39940, 1)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10351, N'Andrew', N'Lopez', 18997, 3)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10352, N'Sarah', N'Mcknight', 31082, 5)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10353, N'Patricia', N'Lawson', 15808, 1)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10354, N'Taylor', N'Tucker', 20225, 3)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10355, N'Aaron', N'Fischer', 45787, 1)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10356, N'Wendy', N'Brown', 30432, 4)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10357, N'Michael', N'Robertson', 47657, 5)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10358, N'Edward', N'Wolf', 45649, 1)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10359, N'Jacob', N'Brown', 28782, 6)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10360, N'Randy', N'Lee', 45121, 4)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10361, N'Xavier', N'Pace', 18842, 3)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10362, N'Anita', N'Chen', 19669, 6)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10363, N'David', N'Chapman', 15426, 4)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10364, N'Elijah', N'Potter', 44472, 2)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10365, N'Craig', N'West', 37278, 1)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10366, N'Heather', N'Schroeder', 40546, 3)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10367, N'Jeremy', N'Phillips', 36605, 4)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10368, N'William', N'Hall', 20534, 4)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10369, N'Kimberly', N'White', 31456, 3)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10370, N'Susan', N'Fisher', 32308, 1)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10371, N'Deborah', N'Duncan', 18517, 6)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10372, N'Lawrence', N'Smith', 11636, 3)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10373, N'Meghan', N'Martinez', 12993, 2)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10374, N'Paige', N'Mills', 19448, 6)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10375, N'Michael', N'Johnson', 34888, 6)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10376, N'Lori', N'Burns', 14603, 3)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10377, N'Stephanie', N'Chavez', 30196, 4)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10378, N'Luis', N'Sheppard', 23532, 6)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10379, N'Rebecca', N'Turner', 28312, 2)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10380, N'Justin', N'Jones', 31170, 6)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10381, N'Stephanie', N'Mejia', 11913, 3)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10382, N'Sharon', N'Liu', 34620, 5)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10383, N'Jonathan', N'Lester', 30517, 2)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10384, N'Olivia', N'Hoffman', 44696, 3)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id])
VALUES (10385, N'Michelle', N'Miller', 36911, 2)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10386, N'Jacob', N'Carr', 20676, 2)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id])
VALUES (10387, N'Ashley', N'Todd', 49926, 6)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10388, N'Michael', N'Harvey', 18280, 3)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10389, N'Anthony', N'Molina', 26304, 3)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10390, N'Maria', N'Warren', 20599, 5)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10391, N'Kenneth', N'Smith', 28762, 6)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10392, N'Melissa', N'Nichols', 35266, 4)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10393, N'Lori', N'Page', 24569, 3)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10394, N'Jacqueline', N'Stewart', 41454, 4)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10395, N'Victor', N'Robinson', 28034, 6)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10396, N'Meghan', N'Henderson', 28733, 2)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10397, N'Heather', N'Kelly', 42487, 6)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10398, N'Alexander', N'Abbott', 15334, 2)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10399, N'Christopher', N'Ryan', 33224, 3)
GO
INSERT [dbo].[db_employee] ([id], [first_name], [last_name], [salary], [department_id]) 
VALUES (10400, N'Chelsea', N'Gonzalez', 20785, 1)
GO

Comments (0)
Questions List