Consider the following tables and assume they have been crea

Consider the following tables and assume they have been created:

Use SQL to manipulate the data and extract the following information (provide SQL query):

Which salespersons with salesperson numbers greater than 200 have the lowest commission percentage of any such salesperson?

SALESPERON table SPNum 137 186 204 361 issionPecentage 10 15 10 20 CommissionPercentage SPName Baker Adams Dickens Carlyle CUSTOMER table CustNum CustName 0121 0839 0933 1047 1525 1700 1826 2198 2267 Main St. Hardware Janes\'s Stores ABC Home Stores Acme Hardware Store Fred\'s Tool Stores XYZ Stores City Hardware Western Hardware Central Stores SPNum 137 186 137 137 361 361 137 204 186

Solution

Please find the required query :

SELECT SPNum
   ,SPName
FROM SALESPERSON
WHERE CommissionPercentage = (
       SELECT Min(CommissionPercentage)
       FROM SALESPERSON
       WHERE SPNum > 200
       );

Here the sub query : SELECT Min(CommissionPercentage) FROM SALESPERSON SPNum > 200, will determine the minimum commissionPercentage of salespersons whose SPNum is greater than 200. Thus finally we have to return those salesperson details whose commissionPercentage is equal to the returned value from sub-query.