AdventureWorks Database: Retrieve the name of each employee whose bonus in the SalesPerson table is 5000
SQL Query - AdventureWorks: Exercise-197 with Solution
197. From the following tables write a query in SQL to retrieve the first and last name of each employee whose bonus in the SalesPerson table is 5000.
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: Sales.SalesPerson
businessentityid|territoryid|salesquota|bonus|commissionpct|salesytd |saleslastyear|rowguid |modifieddate |
----------------+-----------+----------+-----+-------------+------------+-------------+------------------------------------+-----------------------+
274| | | 0| 0| 559697.5639| 0|48754992-9ee0-4c0e-8c94-9451604e3e02|2010-12-28 00:00:00.000|
275| 2| 300000| 4100| 0.012|3763178.1787| 1750406.4785|1e0a7274-3064-4f58-88ee-4c6586c87169|2011-05-24 00:00:00.000|
276| 4| 250000| 2000| 0.015|4251368.5497| 1439156.0291|4dd9eee4-8e81-4f8c-af97-683394c1f7c0|2011-05-24 00:00:00.000|
277| 3| 250000| 2500| 0.015|3189418.3662| 1997186.2037|39012928-bfec-4242-874d-423162c3f567|2011-05-24 00:00:00.000|
278| 6| 250000| 500| 0.01|1453719.4653| 1620276.8966|7a0ae1ab-b283-40f9-91d1-167abf06d720|2011-05-24 00:00:00.000|
279| 5| 300000| 6700| 0.01| 2315185.611| 1849640.9418|52a5179d-3239-4157-ae29-17e868296dc0|2011-05-24 00:00:00.000|
280| 1| 250000| 5000| 0.01|1352577.1325| 1927059.178|be941a4a-fb50-4947-bda4-bb8972365b08|2011-05-24 00:00:00.000|
281| 4| 250000| 3550| 0.01|2458535.6169| 2073505.9999|35326ddb-7278-4fef-b3ba-ea137b69094e|2011-05-24 00:00:00.000|
282| 6| 250000| 5000| 0.015|2604540.7172| 2038234.6549|31fd7fc1-dc84-4f05-b9a0-762519eacacc|2011-05-24 00:00:00.000|
283| 1| 250000| 3500| 0.012|1573012.9383| 1371635.3158|6bac15b2-8ffb-45a9-b6d5-040e16c2073f|2011-05-24 00:00:00.000|
284| 1| 300000| 3900| 0.019|1576562.1966| 0|ac94ec04-a2dc-43e3-8654-dd0c546abc17|2012-09-23 00:00:00.000|
285| | | 0| 0| 172524.4512| 0|cfdbef27-b1f7-4a56-a878-0221c73bae67|2013-03-07 00:00:00.000|
286| 9| 250000| 5650| 0.018|1421810.9242| 2278548.9776|9b968777-75dc-45bd-a8df-9cdaa72839e1|2013-05-23 00:00:00.000|
287| | | 0| 0| 519905.932| 0|1dd1f689-df74-4149-8600-59555eef154b|2012-04-09 00:00:00.000|
288| 8| 250000| 75| 0.018|1827066.7118| 1307949.7917|224bb25a-62e3-493e-acaf-4f8f5c72396a|2013-05-23 00:00:00.000|
289| 10| 250000| 5150| 0.02|4116871.2277| 1635823.3967|25f6838d-9db4-4833-9ddc-7a24283af1ba|2012-05-23 00:00:00.000|
290| 7| 250000| 985| 0.016|3121616.3202| 2396539.7601|f509e3d4-76c8-42aa-b353-90b7b8db08de|2012-05-23 00:00:00.000|
Sample Solution:
-- Selecting distinct last names and first names
SELECT DISTINCT p.LastName, p.FirstName
-- From the Person.Person table aliased as 'p'
FROM Person.Person AS p
-- Joining with the Employee table aliased as 'e' based on BusinessEntityID
JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = p.BusinessEntityID
-- Filtering records based on a subquery result
WHERE 5000.00 IN
(SELECT Bonus
-- From the Sales.SalesPerson table aliased as 'sp'
FROM Sales.SalesPerson AS sp
-- Matching condition: BusinessEntityID
WHERE e.BusinessEntityID = sp.BusinessEntityID);
Explanation:
- This SQL query retrieves distinct last names and first names from the Person table for employees who have a bonus of 5000.00.
- Comments are added to explain each part of the query for better understanding and maintenance.
- Here's a breakdown of what the query does:
- The SELECT DISTINCT clause ensures that only unique combinations of last names and first names are returned in the result set.
- The FROM clause specifies the source table as Person.Person aliased as 'p'.
- The JOIN clause joins the Person table with the Employee table based on BusinessEntityID.
- The WHERE clause filters records based on the existence of a bonus of 5000.00 in a subquery result.
- The subquery selects the Bonus column from the Sales.SalesPerson table where the BusinessEntityID matches that of the Employee table.
Sample Output:
lastname |firstname| ------------+---------+ Ansman-Wolfe|Pamela | Saraiva |José |
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Find the product name that has a long sleeve logo jersey model.
Next: Models with a maximum list price more than twice the average.
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-197.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics