﻿ SQL Query on Multiple Tables: Find salesperson commission details where customer grade is 200 or higher - w3resource

# SQL Exercises: Find salesperson commission details where customer grade is 200 or higher

## SQL Query on Multiple Tables: Exercise-7 with Solution

From the following tables, write a SQL query to find all orders executed by the salesperson and 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.

Sample table: salesman

Sample table: customer

Sample table: orders

Sample Solution:

``````SELECT ord_no, cust_name, commission AS "Commission%",
purch_amt*commission AS "Commission"
FROM salesman,orders,customer
WHERE orders.customer_id = customer.customer_id
AND orders.salesman_id = salesman.salesman_id

Output of the query:

```ord_no	cust_name	Commission%	Commission
70010	Fabian Johnson	0.14		277.6802
70011	Jozy Altidor	0.13		9.7877
70001	Graham Zusi	0.13		19.5650
70007	Graham Zusi	0.13		123.3050
70012	Julian Green	0.13		32.5585
```

Explanation:

Pictorial presentation:

## Query Visualization:

Duration:

Rows:

Cost:

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

Test your Programming skills with w3resource's quiz.

What is the difficulty level of this exercise?

﻿

## SQL: Tips of the Day

Delete all Duplicate Rows except for One in MySQL?

```SELECT * FROM names;

+----+--------+
| id | name   |
+----+--------+
| 2  | yahoo  |
| 3  | msn    |
| 6  | yahoo  |
+----+--------+
```

1. If you want to keep the row with the lowest id value:

```DELETE n1 FROM names n1, names n2 WHERE n1.id > n2.id AND n1.name = n2.name
```

2. If you want to keep the row with the highest id value:

```DELETE n1 FROM names n1, names n2 WHERE n1.id < n2.id AND n1.name = n2.name
```

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