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
101Adam250000
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:


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.



SQL: Tips of the Day

How to select the nth row in a SQL database table?

Basically, PostgreSQL and MySQL supports the non-standard:

SELECT...
LIMIT y OFFSET x 

Oracle, DB2 and MSSQL supports the standard windowing functions:

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
) AS foo
WHERE rownumber <= n

Database: SQL

Ref: https://bit.ly/3zPxcD8