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

Last update on December 20 2024 11:58:47 (UTC/GMT +8 hours)

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:

Data:

Table: commision

Structure:

Data:

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:

Contribute your code and comments through Disqus.

Previous: Find the first login date for each customer.
Next: Distributor who purchased all types of item from the company.