﻿ SQL - Customers along with the salesmen who works for them

# SQL Exercises: Customers along with the salesmen who works for them

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

From the following tables, write a SQL query to locate all the customers and the salesperson who works for them. Return customer name, and salesperson name.

Sample table: customer

``` customer_id |   cust_name    |    city    | grade | salesman_id
-------------+----------------+------------+-------+-------------
3002 | Nick Rimando   | New York   |   100 |        5001
3007 | Brad Davis     | New York   |   200 |        5001
3005 | Graham Zusi    | California |   200 |        5002
3008 | Julian Green   | London     |   300 |        5002
3004 | Fabian Johnson | Paris      |   300 |        5006
3009 | Geoff Cameron  | Berlin     |   100 |        5003
3003 | Jozy Altidor   | Moscow     |   200 |        5007
3001 | Brad Guzan     | London     |       |        5005
```

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
5007 | Paul Adam  | Rome     |       0.13
5003 | Lauson Hen | San Jose |       0.12
```

Sample Solution:

``````-- This query selects specific columns ('customer.cust_name' and 'salesman.name') from the 'customer' and 'salesman' tables.
-- It retrieves data where the 'salesman_id' column in the 'salesman' table matches the 'salesman_id' column in the 'customer' table.
SELECT customer.cust_name, salesman.name
-- Specifies the tables from which to retrieve the data (in this case, 'customer' and 'salesman').
FROM customer, salesman
-- Specifies the condition for joining the 'customer' and 'salesman' tables based on the equality of the 'salesman_id' columns.
WHERE salesman.salesman_id = customer.salesman_id;
``````

Output of the query:

```cust_name	name
Nick Rimando	James Hoog
Graham Zusi	Nail Knite
Julian Green	Nail Knite
Fabian Johnson	Mc Lyon
Geoff Cameron	Lauson Hen
```

Code Explanation:

The given query in SQL that joins the 'customer' and 'salesman' tables based on the "salesman_id" column. The result set includes the customer name (cust_name) and salesman name (name). The WHERE clause specifies the join condition between the two tables, which is that the "salesman_id" column must be equal in both tables.

Relational Algebra Expression:

Relational Algebra Tree:

Explanation:

Visual presentation:

## Query Visualization:

Duration:

Rows:

Cost:

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

Previous SQL Exercise: Customer and salesmen who lives in the same city.
Next SQL Exercise: Customer lives in a city other than the salesman's.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿