Given a list of a company's employees, find the name of the manager from the largest department. Manager is each employee that contains word "manager" under their position. Output their first and last name.
table name: az_employees
Solution:
select first_name,last_name from az_employees
where
position like '%Manager%' and
department_id in(
select top 1 with ties department_id from az_employees
group by department_id,department_name
order by count(*) desc)
Output:
SQL Script:
USE [StrataScratch]
GO
CREATE TABLE [dbo].[az_employees](
[id] [int] NULL,
[first_name] [varchar](50) NULL,
[last_name] [varchar](50) NULL,
[department_id] [int] NULL,
[department_name] [varchar](50) NULL,
[position] [varchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (9, N'Christy', N'Mitchell', 1001, N'Marketing', N'Senior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (13, N'Julie', N'Sanchez', 1001, N'Marketing', N'Intern')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (14, N'John', N'Coleman', 1001, N'Marketing', N'Senior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (15, N'Anthony', N'Valdez', 1001, N'Marketing', N'Junior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (26, N'Allison', N'Johnson', 1001, N'Marketing', N'Senior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (30, N'Stephen', N'Smith', 1001, N'Marketing', N'Intern')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (38, N'Nicole', N'Lewis', 1001, N'Marketing', N'Manager')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (41, N'John', N'George', 1005, N'Sales', N'Junior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (44, N'Trevor', N'Carter', 1001, N'Marketing', N'Senior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (53, N'Teresa', N'Cohen', 1001, N'Marketing', N'Contractor')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (63, N'Richard', N'Sanford', 1001, N'Marketing', N'Senior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (85, N'Meagan', N'Bullock', 1001, N'Marketing', N'Senior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (89, N'Jason', N'Taylor', 1001, N'Marketing', N'Junior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (97, N'Kelli', N'Moss', 1001, N'Marketing', N'Contractor')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (3, N'Kelly', N'Rosario', 1002, N'Human Resources', N'Senior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (5, N'Sherry', N'Golden', 1002, N'Human Resources', N'Junior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (7, N'Diane', N'Gordon', 1002, N'Human Resources', N'Contractor')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (11, N'Kevin', N'Townsend', 1002, N'Human Resources', N'Senior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (18, N'Jeffrey', N'Harris', 1002, N'Human Resources', N'Intern')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (21, N'Stephen', N'Berry', 1002, N'Human Resources', N'Senior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (22, N'Brittany', N'Scott', 1002, N'Human Resources', N'Junior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (33, N'Peter', N'Holt', 1002, N'Human Resources', N'Junior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (39, N'Linda', N'Clark', 1002, N'Human Resources', N'Intern')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (49, N'Amber', N'Harding', 1002, N'Human Resources', N'Intern')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (50, N'Victoria', N'Wilson', 1002, N'Human Resources', N'Interim manager')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (51, N'Theresa', N'Everett', 1002, N'Human Resources', N'Junior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (56, N'Rachael', N'Williams', 1005, N'Sales', N'Senior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (67, N'Tyler', N'Green', 1002, N'Human Resources', N'Senior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (68, N'Antonio', N'Carpenter', 1002, N'Human Resources', N'Senior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (81, N'Leah', N'Contreras', 1002, N'Human Resources', N'Junior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (91, N'Jason', N'Burch', 1002, N'Human Resources', N'Intern')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (92, N'Frances', N'Jackson', 1002, N'Human Resources', N'Intern')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (95, N'Jillian', N'Potter', 1002, N'Human Resources', N'Senior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (96, N'Sharon', N'Hunter', 1002, N'Human Resources', N'Junior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (98, N'Jonathan', N'Jarvis', 1002, N'Human Resources', N'Junior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (99, N'Kendra', N'Lynch', 1002, N'Human Resources', N'Junior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (100, N'Amber', N'Miles', 1002, N'Human Resources', N'Senior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (19, N'Michael', N'Ramsey', 1003, N'Operations', N'Senior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (24, N'William', N'Flores', 1003, N'Operations', N'Manager')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (27, N'Anthony', N'Ball', 1003, N'Operations', N'Contractor')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (31, N'Kimberly', N'Brooks', 1003, N'Operations', N'Senior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (34, N'Justin', N'Dunn', 1003, N'Operations', N'Contractor')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (42, N'Traci', N'Williams', 1003, N'Operations', N'Senior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (45, N'Kevin', N'Duncan', 1003, N'Operations', N'Intern')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (46, N'Joshua', N'Ewing', 1003, N'Operations', N'Senior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (47, N'Kimberly', N'Dean', 1003, N'Operations', N'Contractor')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (61, N'Ryan', N'Brown', 1003, N'Operations', N'Senior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (70, N'Karen', N'Fernandez', 1003, N'Operations', N'Senior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (71, N'Kristine', N'Casey', 1003, N'Operations', N'Intern')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (73, N'William', N'Preston', 1003, N'Operations', N'Intern')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (74, N'Richard', N'Cole', 1003, N'Operations', N'Junior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (77, N'Brittany', N'Harrison', 1003, N'Operations', N'Junior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (83, N'Cameron', N'Webb', 1003, N'Operations', N'Junior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (84, N'Jennifer', N'Mcneil', 1003, N'Operations', N'Junior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (86, N'Jessica', N'Adams', 1003, N'Operations', N'Senior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (4, N'Patricia', N'Powell', 1004, N'Finance', N'Senior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (12, N'Joshua', N'Johnson', 1004, N'Finance', N'Junior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (20, N'Cody', N'Gonzalez', 1004, N'Finance', N'Junior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (23, N'Angela', N'Williams', 1004, N'Finance', N'Senior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (35, N'John', N'Ball', 1004, N'Finance', N'Junior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (40, N'Colleen', N'Carrillo', 1004, N'Finance', N'Junior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (48, N'Robert', N'Lynch', 1004, N'Finance', N'Manager')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (52, N'Kara', N'Smith', 1004, N'Finance', N'Contractor')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (60, N'Charles', N'Pearson', 1004, N'Finance', N'Intern')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (65, N'Deborah', N'Martin', 1004, N'Finance', N'Intern')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (66, N'Dustin', N'Bush', 1004, N'Finance', N'Intern')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (72, N'Christine', N'Frye', 1004, N'Finance', N'Senior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (79, N'Katherine', N'Williams', 1004, N'Finance', N'Intern')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (87, N'Gary', N'Martin', 1004, N'Finance', N'Intern')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (88, N'Robert', N'Ortega', 1004, N'Finance', N'Contractor')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (94, N'Vanessa', N'Lee', 1004, N'Finance', N'Senior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (2, N'Justin', N'Simon', 1005, N'Sales', N'Senior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (6, N'Natasha', N'Swanson', 1005, N'Sales', N'Junior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (8, N'Mercedes', N'Rodriguez', 1005, N'Sales', N'Junior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (16, N'Briana', N'Rivas', 1005, N'Sales', N'Senior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (25, N'Pamela', N'Matthews', 1005, N'Sales', N'Junior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (28, N'Alexis', N'Beck', 1005, N'Sales', N'Senior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (36, N'Jesus', N'Ward', 1005, N'Sales', N'Junior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (43, N'Joseph', N'Rogers', 1005, N'Sales', N'Junior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (54, N'Wesley', N'Tucker', 1005, N'Sales', N'Intern')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (55, N'Michael', N'Morris', 1005, N'Sales', N'Senior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (57, N'Patricia', N'Harmon', 1005, N'Sales', N'Senior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (58, N'Edward', N'Sharp', 1005, N'Sales', N'Senior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (59, N'Kevin', N'Robinson', 1005, N'Sales', N'Senior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (62, N'Dale', N'Hayes', 1005, N'Sales', N'Senior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (69, N'Ernest', N'Peterson', 1005, N'Sales', N'Intern')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (76, N'Melissa', N'Lee', 1005, N'Sales', N'Junior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (78, N'Virginia', N'Mann', 1005, N'Sales', N'Intern')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (80, N'Kimberly', N'Hawkins', 1005, N'Sales', N'Intern')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (82, N'Melissa', N'Byrd', 1005, N'Sales', N'Junior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (90, N'Richard', N'Garcia', 1005, N'Sales', N'Manager')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (1, N'Todd', N'Wilson', 1006, N'Engineering', N'Senior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (10, N'Sean', N'Crawford', 1006, N'Engineering', N'Senior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (17, N'Jason', N'Burnett', 1006, N'Engineering', N'Senior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (29, N'Jason', N'Olsen', 1006, N'Engineering', N'Manager')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (32, N'Eric', N'Zimmerman', 1006, N'Engineering', N'Junior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (37, N'Philip', N'Gillespie', 1006, N'Engineering', N'Senior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (64, N'Danielle', N'Williams', 1006, N'Engineering', N'Junior specialist')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (75, N'Julia', N'Ramos', 1006, N'Engineering', N'Contractor')
GO
INSERT [dbo].[az_employees] ([id], [first_name], [last_name], [department_id], [department_name], [position]) VALUES (93, N'Frank', N'Newton', 1006, N'Engineering', N'Contractor')
GO