w3resource

SQL Exercise: A salesperson who gets a commission of at least 12%

SQL JOINS: Exercise-4 with Solution

From the following tables write a SQL query to find salespeople who received commissions of more than 12 percent from the company. Return Customer Name, customer city, Salesman, commission.

Sample table: customer


Sample table: salesman


Sample Solution:

SELECT a.cust_name AS "Customer Name", 
a.city, b.name AS "Salesman", b.commission 
FROM customer a 
INNER JOIN salesman b 
ON a.salesman_id=b.salesman_id 
WHERE b.commission>.12;

Output of the Query:

Customer Name	city		Salesman	commission
Nick Rimando	New York	James Hoog	0.15
Brad Davis	New York	James Hoog	0.15
Graham Zusi	California	Nail Knite	0.13
Julian Green	London		Nail Knite	0.13
Fabian Johnson	Paris		Mc Lyon		0.14
Jozy Altidor	Moscow		Paul Adam	0.13

Explanation:

The said SQL query that is used to select specific columns from two tables, customer and salesman, and join them using the 'salesman_id' column.
The query selects the 'cust_name' column from the customer table as 'Customer Name', the 'city' column from the customer table, the 'name' column from the salesman table as 'Salesman', and the 'commission' column from the salesman table.
The query uses an inner join, which only returns rows where there is a match in both tables on the specified join column. Additionally, it has a WHERE clause that filters out the rows where the commission of the Salesman is less than or equal to 0.12. So, only the rows where commission of the Salesman is greater than 0.12 will be returned.

Visual Explanation:

Make a list of customers who appointed a salesman for their jobs who gets a commission is above 12%

Practice Online


Query Visualization:

Duration:

Query visualization of Make a list of customers who appointed a salesman for their jobs who gets a commission is above 12% - Duration

Rows:

Query visualization of Make a list of customers who appointed a salesman for their jobs who gets a commission is above 12% - Rows

Cost:

Query visualization of Make a list of customers who appointed a salesman for their jobs who gets a commission is above 12% - Cost

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: A list of which salesmen work for which customers.
Next SQL Exercise: Customers and salespeople live in different cities.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.

SQL: Tips of the Day

Grouped LIMIT in PostgreSQL: Show the first N rows for each group?

db=# SELECT * FROM xxx;
 id | section_id | name
----+------------+------
  1 |          1 | A
  2 |          1 | B
  3 |          1 | C
  4 |          1 | D
  5 |          2 | E
  6 |          2 | F
  7 |          3 | G
  8 |          2 | H
(8 rows)

I need the first 2 rows (ordered by name) for each section_id, i.e. a result similar to:

id | section_id | name
----+------------+------
  1 |          1 | A
  2 |          1 | B
  5 |          2 | E
  6 |          2 | F
  7 |          3 | G
(5 rows)

PostgreSQL v9.3 you can do a lateral join

select distinct t_outer.section_id, t_top.id, t_top.name from t t_outer
join lateral (
    select * from t t_inner
    where t_inner.section_id = t_outer.section_id
    order by t_inner.name
    limit 2
) t_top on true
order by t_outer.section_id;

Database: PostgreSQL

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

 

We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook