AdventureWorks Database: Employees who aren't in departments whose names begin with P
SQL Query - AdventureWorks: Exercise-149 with Solution
149. From the following tables write a query in SQL to find all employees that do not belong to departments whose names begin with P.
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 not 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 absence of records in a subquery
WHERE
NOT 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%'
)
-- Ordering the result set by last name and then by first name
ORDER BY
LastName,
FirstName ;
Explanation:
- This SQL code retrieves first names, last names, and job titles of employees who have not 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 NOT EXISTS to check for the absence 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 NOT EXISTS condition is true, i.e., where the subquery returns no rows.
Sample Output:
firstname|lastname |jobtitle | ---------+-----------------+----------------------------------------+ Syed |Abbas |Pacific Sales Manager | Hazem |Abolrous |Quality Assurance Manager | Pilar |Ackerman |Shipping and Receiving Supervisor | François |Ajenstat |Database Administrator | Amy |Alberts |European Sales Manager | Sean |Alexander |Quality Assurance Technician | Gary |Altman |Facilities Manager | Pamela |Ansman-Wolfe |Sales Representative | Zainal |Arifin |Document Control Manager | Dan |Bacon |Application Specialist | David |Barber |Assistant to the Chief Financial Officer| Paula |Barreto de Mattos|Human Resources Manager | ...
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Find employees of departments that start with P.
Next: Names of design engineers, tool designers, and marketing assistants.
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