AdventureWorks Database: Find employees of departments that start with P
SQL Query - AdventureWorks: Exercise-148 with Solution
148. From the following tables write a query in SQL to find employees of departments that start with P. Return first name, last name, job title.
Sample table: Person.Person
businessentityid|persontype|namestyle|title|firstname |middlename |lastname |suffix|emailpromotion|additionalcontactinfo|demographics|rowguid |modifieddate |
----------------+----------+---------+-----+------------------------+----------------+----------------------+------+--------------+---------------------+------------+------------------------------------+-----------------------+
1|EM |false | |Ken |J |Sánchez | | 0| |[XML] |92c4279f-1207-48a3-8448-4636514eb7e2|2009-01-07 00:00:00.000|
2|EM |false | |Terri |Lee |Duffy | | 1| |[XML] |d8763459-8aa8-47cc-aff7-c9079af79033|2008-01-24 00:00:00.000|
3|EM |false | |Roberto | |Tamburello | | 0| |[XML] |e1a2555e-0828-434b-a33b-6f38136a37de|2007-11-04 00:00:00.000|
4|EM |false | |Rob | |Walters | | 0| |[XML] |f2d7ce06-38b3-4357-805b-f4b6b71c01ff|2007-11-28 00:00:00.000|
5|EM |false |Ms. |Gail |A |Erickson | | 0| |[XML] |f3a3f6b4-ae3b-430c-a754-9f2231ba6fef|2007-12-30 00:00:00.000|
6|EM |false |Mr. |Jossef |H |Goldberg | | 0| |[XML] |0dea28fd-effe-482a-afd3-b7e8f199d56f|2013-12-16 00:00:00.000|
7|EM |false | |Dylan |A |Miller | | 2| |[XML] |c45e8ab8-01be-4b76-b215-820c8368181a|2009-02-01 00:00:00.000|
8|EM |false | |Diane |L |Margheim | | 0| |[XML] |a948e590-4a56-45a9-bc9a-160a1cc9d990|2008-12-22 00:00:00.000|
9|EM |false | |Gigi |N |Matthew | | 0| |[XML] |5fc28c0e-6d36-4252-9846-05caa0b1f6c5|2009-01-09 00:00:00.000|
-- more --
Sample table: HumanResources.Employee
businessentityid|nationalidnumber|loginid |jobtitle |birthdate |maritalstatus|gender|hiredate |salariedflag|vacationhours|sickleavehours|currentflag|rowguid |modifieddate |organizationnode|
----------------+----------------+----------------------------+----------------------------------------+----------+-------------+------+----------+------------+-------------+--------------+-----------+------------------------------------+-----------------------+----------------+
1|295847284 |adventure-works\ken0 |Chief Executive Officer |1969-01-29|S |M |2009-01-14|true | 99| 69|true |f01251e5-96a3-448d-981e-0f99d789110d|2014-06-30 00:00:00.000|/ |
2|245797967 |adventure-works\terri0 |Vice President of Engineering |1971-08-01|S |F |2008-01-31|true | 1| 20|true |45e8f437-670d-4409-93cb-f9424a40d6ee|2014-06-30 00:00:00.000|/1/ |
3|509647174 |adventure-works\roberto0 |Engineering Manager |1974-11-12|M |M |2007-11-11|true | 2| 21|true |9bbbfb2c-efbb-4217-9ab7-f97689328841|2014-06-30 00:00:00.000|/1/1/ |
4|112457891 |adventure-works\rob0 |Senior Tool Designer |1974-12-23|S |M |2007-12-05|false | 48| 80|true |59747955-87b8-443f-8ed4-f8ad3afdf3a9|2014-06-30 00:00:00.000|/1/1/1/ |
5|695256908 |adventure-works\gail0 |Design Engineer |1952-09-27|M |F |2008-01-06|true | 5| 22|true |ec84ae09-f9b8-4a15-b4a9-6ccbab919b08|2014-06-30 00:00:00.000|/1/1/2/ |
6|998320692 |adventure-works\jossef0 |Design Engineer |1959-03-11|M |M |2008-01-24|true | 6| 23|true |e39056f1-9cd5-478d-8945-14aca7fbdcdd|2014-06-30 00:00:00.000|/1/1/3/ |
7|134969118 |adventure-works\dylan0 |Research and Development Manager |1987-02-24|M |M |2009-02-08|true | 61| 50|true |4f46deca-ef01-41fd-9829-0adab368e431|2014-06-30 00:00:00.000|/1/1/4/ |
8|811994146 |adventure-works\diane1 |Research and Development Engineer |1986-06-05|S |F |2008-12-29|true | 62| 51|true |31112635-663b-4018-b4a2-a685c0bf48a4|2014-06-30 00:00:00.000|/1/1/4/1/ |
9|658797903 |adventure-works\gigi0 |Research and Development Engineer |1979-01-21|M |F |2009-01-16|true | 63| 51|true |50b6cdc6-7570-47ef-9570-48a64b5f2ecf|2014-06-30 00:00:00.000|/1/1/4/2/ |
10|879342154 |adventure-works\michael6 |Research and Development Manager |1984-11-30|M |M |2009-05-03|true | 16| 64|true |eaa43680-5571-40cb-ab1a-3bf68f04459e|2014-06-30 00:00:00.000|/1/1/4/3/ |
-- more --
Sample table: HumanResources.Department
departmentid|name |groupname |modifieddate |
------------+--------------------------+------------------------------------+-----------------------+
1|Engineering |Research and Development |2008-04-30 00:00:00.000|
2|Tool Design |Research and Development |2008-04-30 00:00:00.000|
3|Sales |Sales and Marketing |2008-04-30 00:00:00.000|
4|Marketing |Sales and Marketing |2008-04-30 00:00:00.000|
5|Purchasing |Inventory Management |2008-04-30 00:00:00.000|
6|Research and Development |Research and Development |2008-04-30 00:00:00.000|
7|Production |Manufacturing |2008-04-30 00:00:00.000|
8|Production Control |Manufacturing |2008-04-30 00:00:00.000|
9|Human Resources |Executive General and Administration|2008-04-30 00:00:00.000|
10|Finance |Executive General and Administration|2008-04-30 00:00:00.000|
11|Information Services |Executive General and Administration|2008-04-30 00:00:00.000|
12|Document Control |Quality Assurance |2008-04-30 00:00:00.000|
13|Quality Assurance |Quality Assurance |2008-04-30 00:00:00.000|
14|Facilities and Maintenance|Executive General and Administration|2008-04-30 00:00:00.000|
15|Shipping and Receiving |Inventory Management |2008-04-30 00:00:00.000|
16|Executive |Executive General and Administration|2008-04-30 00:00:00.000|
Sample table: HumanResources.EmployeeDepartmentHistory
businessentityid|departmentid|shiftid|startdate |enddate |modifieddate |
----------------+------------+-------+----------+----------+-----------------------+
1| 16| 1|2009-01-14| |2009-01-13 00:00:00.000|
2| 1| 1|2008-01-31| |2008-01-30 00:00:00.000|
3| 1| 1|2007-11-11| |2007-11-10 00:00:00.000|
4| 1| 1|2007-12-05|2010-05-30|2010-05-28 00:00:00.000|
4| 2| 1|2010-05-31| |2010-05-30 00:00:00.000|
5| 1| 1|2008-01-06| |2008-01-05 00:00:00.000|
6| 1| 1|2008-01-24| |2008-01-23 00:00:00.000|
-- more --
Sample Solution:
-- Selecting first name, last name, and job title from the Person table for employees who have worked in departments starting with 'P'
SELECT
-- Selecting the FirstName column from the Person table
p.FirstName,
-- Selecting the LastName column from the Person table
p.LastName,
-- Selecting the JobTitle column from the Employee table
e.JobTitle
-- Selecting data from the Person table with an alias 'p' and joining with the Employee table using BusinessEntityID
FROM
Person.Person AS p
JOIN
HumanResources.Employee AS e
ON e.BusinessEntityID = p.BusinessEntityID
-- Checking for existence of records in a subquery
WHERE
EXISTS
(
-- Selecting all records from the Department and EmployeeDepartmentHistory tables where the department name starts with 'P' and matches with the employee's department history
SELECT *
FROM
HumanResources.Department AS d
JOIN
HumanResources.EmployeeDepartmentHistory AS edh
ON d.DepartmentID = edh.DepartmentID
WHERE
e.BusinessEntityID = edh.BusinessEntityID
AND
d.Name LIKE 'P%'
) ;
Explanation:
- This SQL code retrieves first names, last names, and job titles of employees who have worked in departments whose names start with 'P'.
- The SELECT statement specifies the columns to be included in the result set.
- The WHERE clause includes a subquery using EXISTS to check for the existence of records in the Department and EmployeeDepartmentHistory tables where the department name starts with 'P' and matches with the employee's department history.
- The subquery selects all records from the Department and EmployeeDepartmentHistory tables where the conditions specified are met.
- The outer query selects first names, last names, and job titles of employees where the EXISTS condition is true, i.e., where the subquery returns at least one row.
OR
Sample Solution:
-- Selects the first name, last name, and job title of persons
SELECT p.FirstName, p.LastName, e.JobTitle
-- Specifies the 'Person.Person' table as the source of data and aliases it as 'p'
FROM Person.Person AS p
-- Joins the 'Person.Person' and 'HumanResources.Employee' tables based on BusinessEntityID
JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = p.BusinessEntityID
-- Joins the 'HumanResources.Employee' and 'HumanResources.EmployeeDepartmentHistory' tables based on BusinessEntityID
JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.BusinessEntityID = edh.BusinessEntityID
-- Filters the result set to include only rows where the department ID is found in the subquery's result set
WHERE edh.DepartmentID IN
-- Subquery: Selects DepartmentID from the 'HumanResources.Department' table
(SELECT DepartmentID
-- Specifies the 'HumanResources.Department' table as the source of data
FROM HumanResources.Department
-- Filters the result set to include only rows where the department name starts with 'P'
WHERE Name LIKE 'P%') ;
Explanation:
- The outer SELECT statement retrieves the first name, last name, and job title of persons.
- The FROM clause specifies the source of the data, which is the 'Person.Person' table, and it is aliased as 'p'.
- The JOIN clause joins the 'Person.Person' and 'HumanResources.Employee' tables based on the BusinessEntityID column.
- Another JOIN clause joins the 'HumanResources.Employee' and 'HumanResources.EmployeeDepartmentHistory' tables based on the BusinessEntityID column.
- The WHERE clause filters the result set. It uses the IN operator to filter the rows where the department ID is found in the subquery's result set.
- The subquery retrieves DepartmentID from the 'HumanResources.Department' table, filtering the result set to include only rows where the department name starts with 'P'.
Sample Output:
firstname |lastname |jobtitle | -----------+---------------+----------------------------+ David |Bradley |Marketing Manager | James |Hamilton |Vice President of Production| Peter |Krebs |Production Control Manager | Jo |Brown |Production Supervisor - WC60| Guy |Gilbert |Production Technician - WC60| Mark |McArthur |Production Technician - WC60| Britta |Simon |Production Technician - WC60| Margie |Shoop |Production Technician - WC60| Rebecca |Laszlo |Production Technician - WC60| Annik |Stahl |Production Technician - WC60| Suchitra |Mohan |Production Technician - WC60| Brandon |Heidepriem |Production Technician - WC60| ....
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Find stores whose name is the same name as a vendor.
Next: Employees who aren't in departments whose names begin with P.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics