﻿ SQL Challenges-1: Find those salespersons whose commission is less than ten thousand - w3resource

# SQL Challenges-1: Find those salespersons whose commission is less than ten thousand

## SQL Challenges-1: Exercise-26 with Solution

From the following table, write a SQL query to find those salespersons whose commission is less than ten thousand. Return salesperson name, commission.

Input:

Table: salemast

Structure:

FieldTypeNullKeyDefaultExtra
salesman_idint(11)YES
salesman_namevarchar(255)YES
yearly_saleint(11)YES

Data:

salesman_idsalesman_nameyearly_sale
103Mark100000
104Liam200000
102Evan150000
105Blake275000
106Noah50000

Table: commision

Structure:

FieldTypeNullKeyDefaultExtra
salesman_idint(11)YES
commision_amtint(11)YES

Data:

salesman_idcommision_amt
10110000
1034000
1048000
1026000
10511000

Sample Solution:

SQL Code(MySQL):

``````CREATE TABLE salemast(salesman_id int, salesman_name varchar(255), yearly_sale int);
INSERT INTO salemast VALUES (101, 'Adam', 250000);
INSERT INTO salemast VALUES (103, 'Mark', 100000);
INSERT INTO salemast VALUES (104, 'Liam', 200000);
INSERT INTO salemast VALUES (102, 'Evan', 150000);
INSERT INTO salemast VALUES (105, 'Blake', 275000);
INSERT INTO salemast VALUES (106, 'Noah', 50000);
SELECT * FROM  salemast;
CREATE TABLE commision (salesman_id int, commision_amt int);
INSERT INTO commision VALUES (101, 10000);
INSERT INTO commision VALUES (103, 4000);
INSERT INTO commision VALUES (104, 8000);
INSERT INTO commision VALUES (102, 6000);
INSERT INTO commision VALUES (105, 11000);
SELECT * FROM  commision;

SELECT s.salesman_name,c.commision_amt
FROM salemast s LEFT JOIN
commision c
ON  s.salesman_id=c.salesman_id
WHERE c.commision_amt<10000;
```
```

Sample Output:

```salesman_name|commision_amt|
-------------|-------------|
Mark         |         4000|
Liam         |         8000|
Evan         |         6000|
```

SQL Code Editor: