Please note, this is a STATIC archive of website www.w3resource.com from 19 Jul 2022, cach3.com does not collect or store any user information, there is no "phishing" involved.
w3resource

SQL Exercises: Find customers details who were served by a salesman and commission within a range

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


Sample Solution:

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

Explanation:

Syntax to find customers details who served by a salesman and commission within a range

Pictorial presentation :

Result of customers details who served by a salesman and commission within a range

Practice Online


Query Visualization:

Duration:

Query visualization of Find customers details who served by a salesman and commission within a range - Duration

Rows:

Query visualization of Find customers details who served by a salesman and commission within a range - Rows

Cost:

Query visualization of Find customers details who served by a salesman and commission within a range - Cost

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

Previous: From the following tables, write a SQL query to find those customers where each customer has a grade and served by at least a salesperson who belongs to a city. Return cust_name as "Customer", grade as "Grade".
Next: From the following tables, write a SQL query to find those orders executed by the salesperson, ordered by the customer whose grade is greater than or equal to 200. Compute purch_amt*commission as "Commission". Return customer name, commission as "Commission%" and Commission.

Test your Programming skills with w3resource's quiz.

What is the difficulty level of this exercise?



SQL: Tips of the Day

SQL Server SELECT into existing table. 

INSERT INTO dbo.TABLETWO
SELECT col1, col2
  FROM dbo.TABLEONE
 WHERE col3 LIKE @search_key

This assumes there's only two columns in dbo.TABLETWO - you need to specify the columns otherwise:

INSERT INTO dbo.TABLETWO
  (col1, col2)
SELECT col1, col2
  FROM dbo.TABLEONE
 WHERE col3 LIKE @search_key

Database: SQL Server

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