AdventureWorks Database: Names of design engineers, tool designers, and marketing assistants
SQL Query - AdventureWorks: Exercise-150 with Solution
150. From the following table write a query in SQL to select employees who work as design engineers, tool designers, or marketing assistants.
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 Solution:
-- Selecting first name, last name, and job title from the Person table for employees with specific job titles
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 p.BusinessEntityID = e.BusinessEntityID
-- Filtering records to include only those with specific job titles
WHERE
e.JobTitle IN ('Design Engineer', 'Tool Designer', 'Marketing Assistant');
Explanation:
- This SQL code retrieves first names, last names, and job titles of employees who have specific job titles.
- The SELECT statement specifies the columns to be included in the result set.
- The WHERE clause filters records to include only those employees whose job titles match any of the specified titles: 'Design Engineer', 'Tool Designer', or 'Marketing Assistant'.
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
-- Specifies the 'HumanResources.Employee' table as the source of data and aliases it as 'e'
JOIN HumanResources.Employee AS e
-- Joins the 'Person.Person' and 'HumanResources.Employee' tables based on BusinessEntityID
ON p.BusinessEntityID = e.BusinessEntityID
-- Filters the result set to include only rows where the job title matches one of the specified titles
WHERE e.JobTitle = 'Design Engineer'
OR e.JobTitle = 'Tool Designer'
OR e.JobTitle = 'Marketing Assistant';
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. It includes the 'Person.Person' table as 'p' and the 'HumanResources.Employee' table as 'e'.
- The JOIN clause joins the 'Person.Person' and 'HumanResources.Employee' tables based on the BusinessEntityID column.
- The WHERE clause filters the result set to include only rows where the job title matches one of the specified titles: 'Design Engineer', 'Tool Designer', or 'Marketing Assistant'.
Sample Output:
firstname|lastname |jobtitle | ---------+---------+-------------------+ Gail |Erickson |Design Engineer | Jossef |Goldberg |Design Engineer | Thierry |D'Hers |Tool Designer | Janice |Galvin |Tool Designer | Sharon |Salavaria|Design Engineer | Kevin |Brown |Marketing Assistant| Mary |Dempsey |Marketing Assistant| Wanida |Benshoof |Marketing Assistant|
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Employees who aren't in departments whose names begin with P.
Next: Identify all salespeople with a quota over $250,000.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://w3resource.com/sql-exercises/adventureworks/sql-adventureworks-exercise-150.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics