w3resource

SQL Exercises: Find salespeople in the city where the customer lives

SQL SUBQUERY : Exercise-19 with Solution

19. From the following tables write a SQL query to find the salespeople who deal with those customers who live in the same city. Return salesman_id, name, city and 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 all columns from the 'salesman' table
SELECT *
-- Specifying the table to retrieve data from ('salesman')
FROM salesman 
-- Filtering the results based on the condition that 'city' is equal to any value in the result of a subquery
WHERE city = ANY
    -- Subquery: Selecting 'city' values from the 'customer' table
    (SELECT city
     FROM customer);

Output of the Query:

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

Explanation:

The said SQL query that selects all columns from the 'salesman' table where the "city" value of the salesman table is equal to any of the values in the "city" column from the 'customer' table. The subquery retrieves all the cities from the customer table, and the main query retrieves the rows from the salesman table where the city of the salesman is present in the list of cities returned by the subquery. The ANY keyword is used to check if the value in the "city" column of the 'salesman' table matches any of the values in the subquery. It is similar to using the IN clause.

Visual Explanation:

SQL Subqueries:  Find salesmen with all information who lives in the city where any of the customers lives.

Practice Online


Sample Database: inventory

Inventory database model

Query Visualization:

Duration:

Query visualization of Find salesmen with all information who lives in the city where any of the customers lives - Duration

Rows:

Query visualization of Find salesmen with all information who lives in the city where any of the customers lives - Rows

Cost:

Query visualization of Find salesmen with all information who lives in the city where any of the customers lives - Cost

Contribute your code and comments through Disqus.

Previous SQL Exercise: Show all salesmen with more than one order.
Next SQL Exercise: Display the salesmen with customers following them.

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.