SQL Exercises: The customer whose ID is 2001 is below Mc Lyons

SQL SUBQUERY: Exercise-7 with Solution

7. Write a query to display all the customers whose ID is 2001 below the salesperson ID of Mc Lyon.

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

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 all columns from the 'customer' table
SELECT *
-- Specifying the table to retrieve data from ('customer')
FROM customer
-- Filtering the results based on the condition that 'customer_id' is equal to the result of a subquery
WHERE customer_id =
    -- Subquery: Selecting 'salesman_id - 2001' from the 'salesman' table where 'name' is 'Mc Lyon'
    (SELECT salesman_id - 2001
     FROM salesman
     WHERE name = 'Mc Lyon');

Output of the Query:

customer_id	cust_name	city		grade	salesman_id
3005		Graham Zusi	California	200		5002

Explanation:

The said SQL query selects all columns (*) from the customer table where the 'customer_id' is equal to the 'salesman_id' minus 2001 from the salesman table where the 'name' is equal to 'Mc Lyon'. In other words, it is selecting the customer with the ID that is the salesman_id of the salesman 'Mc Lyon' -2001.

Visual Explanation:

Practice Online

Sample Database: inventory

Query Visualization:

Duration:

Rows:

Cost:

Contribute your code and comments through Disqus.

Previous SQL Exercise: Display the commissions of all salespeople in Paris.
Next SQL Exercise: Counts the customers with grades over New York average.