Given a table of candidates and their skills, you
are tasked with finding the candidates best suited for an open Data Science
job. You want to find candidates who are proficient in Python, Tableau, and
PostgreSQL.
Write a query to list the candidates who possess
all of the required skills for the job. Sort the output by candidate ID in
ascending order.
Assumption: There are no duplicates in the
candidates table.
table name: candidates
Solution:
select candidate_id from candidates group by candidate_id
having STRING_AGG(skill,' ') like '%Python%' and STRING_AGG(skill,' ') like '%Tableau%' and STRING_AGG(skill,' ') like '%PostgreSQL%'
order by candidate_id
SELECT candidate_id
FROM candidates
WHERE skill IN ('Python', 'Tableau', 'PostgreSQL')
GROUP BY candidate_id
HAVING COUNT(skill) = 3
ORDER BY candidate_id;
Output:
Execution Plan:
SQL Script:
CREATE TABLE [dbo].[candidates](
[candidate_id]
[int] NULL,
[skill]
[varchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[candidates] ([candidate_id], [skill]) VALUES (123, N'Python')
GO
INSERT [dbo].[candidates] ([candidate_id], [skill]) VALUES (123, N'Tableau')
GO
INSERT [dbo].[candidates] ([candidate_id], [skill]) VALUES (123, N'PostgreSQL')
GO
INSERT [dbo].[candidates] ([candidate_id], [skill]) VALUES (234, N'R')
GO
INSERT [dbo].[candidates] ([candidate_id], [skill]) VALUES (234, N'PowerBI')
GO
INSERT [dbo].[candidates] ([candidate_id], [skill]) VALUES (234, N'SQL Server')
GO
INSERT [dbo].[candidates] ([candidate_id], [skill]) VALUES (34, N'Python')
GO
INSERT [dbo].[candidates] ([candidate_id], [skill]) VALUES (345, N'Tableau')
GO