AdventureWorks Database: Get employees with Johnson last names
SQL Query - AdventureWorks: Exercise-146 with Solution
146. From the following tables write a query in SQL to get employees with Johnson last names. Return first name and last name.
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|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 Solution:
-- Selecting FirstName and LastName columns from the Person table for persons whose last name is 'Johnson' and who also exist as employees
SELECT
-- Selecting the FirstName column from the Person table
a.FirstName,
-- Selecting the LastName column from the Person table
a.LastName
-- Selecting data from the Person table with an alias 'a'
FROM
Person.Person AS a
-- Checking for existence of records in a subquery
WHERE
EXISTS
(
-- Selecting all records from the Employee table with an alias 'b' where the last name is 'Johnson' and BusinessEntityID matches
SELECT *
FROM
HumanResources.Employee AS b
WHERE
a.BusinessEntityID = b.BusinessEntityID
AND
a.LastName = 'Johnson'
) ;
Explanation:
- This SQL code retrieves the first name and last name of persons whose last name is 'Johnson' and who also exist as employees.
- 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 Employee table where the BusinessEntityID matches and the last name is 'Johnson'.
- The subquery selects all records from the Employee table where the conditions specified are met.
- The outer query selects records from the Person table where the EXISTS condition is true, i.e., where the subquery returns at least one row.
- The result set will contain the first name and last name of persons whose last name is 'Johnson' and who also exist as employees.
OR
Sample Solution:
SELECT a.FirstName, a.LastName
-- Specifies the 'Person.Person' table as the source of data and aliases it as 'a'
FROM Person.Person AS a
-- Filters the result set to include only rows where the last name matches 'Johnson'
WHERE a.LastName IN
-- Subquery: Selects the last names of employees from the 'HumanResources.Employee' table
(SELECT a.LastName
-- Specifies the 'HumanResources.Employee' table as the source of data and aliases it as 'b'
FROM HumanResources.Employee AS b
-- Joins the 'Person.Person' and 'HumanResources.Employee' tables based on BusinessEntityID
WHERE a.BusinessEntityID = b.BusinessEntityID
-- Filters the result set to include only rows where the last name matches 'Johnson'
AND a.LastName = 'Johnson') ;
Explanation:
- The outer SELECT statement retrieves the first name and last name of persons from the 'Person.Person' table, which is aliased as 'a'.
- The FROM clause specifies the source of the data, which is the 'Person.Person' table, and it is aliased as 'a'.
- The WHERE clause filters the result set. It uses the IN operator to filter the rows where the last name is found in the subquery's result set.
- The subquery retrieves the last names of employees from the 'HumanResources.Employee' table, which is aliased as 'b'.
- The subquery's WHERE clause filters the result set to include only rows where the last name matches 'Johnson' and where the BusinessEntityID matches between the 'Person.Person' and 'HumanResources.Employee' tables.
Sample Output:
firstname|lastname| ---------+--------+ Barry |Johnson | David |Johnson | Willis |Johnson |
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Returns TRUE even if NULL is specified in the subquery.
Next: Find stores whose name is the same name as a vendor.
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