AdventureWorks Database: Retrieve employees whose job titles begin with 'Sales'
SQL Query - AdventureWorks: Exercise-77 with Solution
77. From the following table write a SQL query to retrieve all the employees whose job titles begin with "Sales". Return firstname, middlename, lastname and jobtitle column.
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 columns firstname, middlename, lastname from the 'person' table aliased as 'pepe'
SELECT pepe.firstname, pepe.middlename, pepe.lastname, huem.jobtitle
-- From the 'person' table in the 'person' schema, aliased as 'pepe'
FROM person.person pepe
-- Performing an inner join with the 'employee' table in the 'humanresources' schema, aliased as 'huem'
INNER JOIN humanresources.employee huem
-- Joining the tables on the 'businessentityid' column
ON pepe.businessentityid = huem.businessentityid
-- Filtering the result set to include only records where the first 5 characters of 'jobtitle' are 'Sales'
WHERE SUBSTRING(huem.jobtitle, 1, 5) = 'Sales';
Explanation:
- SELECT pepe.firstname, pepe.middlename, pepe.lastname, huem.jobtitle: This line selects columns 'firstname', 'middlename', 'lastname' from the 'person' table, aliased as 'pepe', and 'jobtitle' from the 'employee' table in the 'humanresources' schema, aliased as 'huem'.
- FROM person.person pepe: This line specifies the source of the data as the 'person' table in the 'person' schema, aliased as 'pepe'.
- INNER JOIN humanresources.employee huem ON pepe.businessentityid = huem.businessentityid: This line performs an inner join with the 'employee' table in the 'humanresources' schema, aliased as 'huem'. It joins the 'pepe' and 'huem' tables based on the 'businessentityid' column from both tables.
- WHERE SUBSTRING(huem.jobtitle, 1, 5) = 'Sales': This line filters the result set to include only records where the first 5 characters of the 'jobtitle' column are 'Sales'. The SUBSTRING() function is used to extract a substring from the 'jobtitle' column, starting from the first character (index 1) and including 5 characters. If the extracted substring matches 'Sales', the row is included in the result set.
Sample Output:
firstname|middlename|lastname |jobtitle | ---------+----------+-----------------+--------------------+ Michael |G |Blythe |Sales Representative| Linda |C |Mitchell |Sales Representative| Jillian | |Carson |Sales Representative| Garrett |R |Vargas |Sales Representative| Tsvi |Michael |Reiter |Sales Representative| Pamela |O |Ansman-Wolfe |Sales Representative| Shu |K |Ito |Sales Representative| José |Edvaldo |Saraiva |Sales Representative| David |R |Campbell |Sales Representative| Tete |A |Mensa-Annan |Sales Representative| Lynn |N |Tsoflias |Sales Representative| Rachel |B |Valdez |Sales Representative| Jae |B |Pak |Sales Representative| Ranjit |R |Varkey Chudukatil|Sales Representative|
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Replace string 'Supervisor' to 'Assistant' in jobtitle.
Next: Return the lastname in uppercase, trimmed with first name.
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