AdventureWorks Database: Find the salespeople without a quota over $250,000
SQL Query - AdventureWorks: Exercise-152 with Solution
152. From the following tables write a query in SQL to find the salespersons who do not have a quota greater than $250,000. 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: 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 first name and last name from the Person table for salespersons whose sales quota is not greater than $250,000
SELECT
-- Selecting the FirstName column from the Person table
p.FirstName,
-- Selecting the LastName column from the Person table
p.LastName
-- Selecting data from the Person table with an alias 'p' and joining with the SalesPerson table using BusinessEntityID
FROM
Person.Person AS p
JOIN
Sales.SalesPerson AS sp
ON p.BusinessEntityID = sp.BusinessEntityID
-- Filtering records to include only those with BusinessEntityID not found in a subquery
WHERE
p.BusinessEntityID NOT IN
(
-- Subquery: Selecting BusinessEntityIDs from the SalesPerson table where SalesQuota is greater than $250,000
SELECT
BusinessEntityID
FROM
Sales.SalesPerson
WHERE
SalesQuota > 250000
);
Explanation:
- This SQL code retrieves first names and last names of salespersons whose sales quota is not greater than $250,000.
- The SELECT statement specifies the columns to be included in the result set.
- The WHERE clause filters records to include only those salespersons whose BusinessEntityID is not found in the subquery result.
- The subquery selects BusinessEntityIDs from the SalesPerson table where the SalesQuota is greater than $250,000.
- The outer query joins the Person table with the SalesPerson table based on BusinessEntityID to get the first name and last name of the salespersons not meeting the specified quota criteria.
- The result set will contain the first name and last name of salespersons whose sales quota is not greater than $250,000.
Sample Output:
firstname|lastname | ---------+-----------------+ Stephen |Jiang | Linda |Mitchell | Jillian |Carson | Garrett |Vargas | Pamela |Ansman-Wolfe | Shu |Ito | José |Saraiva | David |Campbell | Syed |Abbas | Lynn |Tsoflias | Amy |Alberts | Rachel |Valdez | Jae |Pak | Ranjit |Varkey Chudukatil|
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Identify all salespeople with a quota over $250,000.
Next: Find a match between salesorderheadersalesreason and SalesReason table.
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://www.w3resource.com/sql-exercises/adventureworks/sql-adventureworks-exercise-152.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics