SQL Exercises: Customers served by a salesman and commission
SQL Query on Multiple Tables: Exercise-6 with Solution
From the following table, write a SQL query to find those customers who are served by a salesperson and the salesperson earns commission in the range of 12% to 14% (Begin and end values are included.). Return cust_name AS "Customer", city AS "City".
Sample table: salesman
Sample table: customer
SELECT customer.cust_name AS "Customer", customer.city AS "City", salesman.name AS "Salesman", salesman.commission FROM customer,salesman WHERE customer.salesman_id = salesman.salesman_id AND salesman.commission BETWEEN .12 AND .14;
Output of the query:
Customer City Salesman commission Graham Zusi California Nail Knite 0.13 Julian Green London Nail Knite 0.13 Fabian Johnson Paris Mc Lyon 0.14 Geoff Cameron Berlin Lauson Hen 0.12 Jozy Altidor Moscow Paul Adam 0.13
The said query in SQL that joins the 'customer' and 'salesman' tables based on the "salesman_id" column. The result set includes the customer name with an alias "Customer", customer city with an alias "City", salesman name with an alias "Salesman", and commission from the salesman table.
The WHERE clause specifies multiple conditions for the join. The first condition is that the salesman_id column of the customer table must be equal to the salesman_id column of the salesman table. The second condition specifies that a particular row of the salesman table must contain a value between 0.12 and 0.14, inclusive, for the commission column.
Visual presentation :
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous SQL Exercise: Sort out the customer who made at least an order.
Next SQL Exercise: Find salesman commission details of given customer.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
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;
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
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