w3resource

SQL Exercises: Display the commissions of all salespeople in Paris

SQL SUBQUERY : Exercise-6 with Solution

6. From the following tables write a SQL query to determine the commission of the salespeople in Paris. Return commission.

Sample table: Salesman
salesman_id  name        city        commission
-----------  ----------  ----------  ----------
5001         James Hoog  New York    0.15
5002         Nail Knite  Paris       0.13
5005         Pit Alex    London      0.11
5006         Mc Lyon     Paris       0.14
5003         Lauson Hen  San Jose    0.12
5007         Paul Adam   Rome        0.13
Sample table : Customer
customer_id  cust_name     city        grade       salesman_id
-----------  ------------  ----------  ----------  -----------
3002         Nick Rimando  New York    100         5001
3005         Graham Zusi   California  200         5002
3001         Brad Guzan    London      100         5005
3004         Fabian Johns  Paris       300         5006
3007         Brad Davis    New York    200         5001
3009         Geoff Camero  Berlin      100         5003
3008         Julian Green  London      300         5002
3003         Jozy Altidor  Moncow      200         5007

Sample Solution:

-- Selecting the 'commission' column from the 'salesman' table
SELECT commission
-- Specifying the table to retrieve data from ('salesman')
FROM salesman
-- Filtering the results based on the condition that 'salesman_id' is in the result of a subquery
WHERE salesman_id IN
    -- Subquery: Selecting 'salesman_id' from the 'customer' table where 'city' is 'Paris'
    (SELECT salesman_id 
     FROM customer
     WHERE city = 'Paris');

Output of the Query:

commission
0.14

Explanation:

The said SQL query selects the 'commission' column from the salesman table where the 'salesman_id' is in the set of 'salesman_id' from the customer table where the 'city' is equal to 'Paris'. In other words, it is selecting the commission of the salesman who serviced a customer from the city of Paris.

Visual Explanation:

SQL Subqueries: Display the commission of all the salesmen servicing customers in Paris.

Practice Online


Sample Database: inventory

Inventory database model

Query Visualization:

Duration:

Query visualization of Display the commission of all the salesmen servicing customers in Paris - Duration

Rows:

Query visualization of Display the commission of all the salesmen servicing customers in Paris - Rows

Cost:

Query visualization of Display the commission of all the salesmen servicing customers in Paris - Cost

Contribute your code and comments through Disqus.

Previous SQL Exercise: Find all orders attributed to salesman in New York.
Next SQL Exercise: The customer whose ID is 2001 is below Mc Lyons.

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.