SQL Exercises: Display all salesmen and customer located in London

Last update on December 20 2024 11:46:05 (UTC/GMT +8 hours)

1. From the following tables, write a SQL query to find all salespeople and customers located in the city of London.

Sample table: Salesman

Sample table: Customer

Sample Solution:

-- Selecting specific columns (salesman_id "ID", name, 'Salesman') from the 'salesman' table
SELECT salesman_id "ID", name, 'Salesman'

-- Filtering rows from the 'salesman' table based on the condition that 'city' is equal to 'London'
FROM salesman
WHERE city='London'

-- Performing a UNION operation with the result set of a subquery that selects specific columns (customer_id "ID", cust_name, 'Customer') from the 'customer' table
UNION

(SELECT customer_id "ID", cust_name, 'Customer'

-- Filtering rows from the 'customer' table based on the condition that 'city' is equal to 'London'
FROM customer
WHERE city='London')

Sample Output:

ID	name	?column?
3001	Brad Guzan	Customer
3008	Julian Green	Customer
5005	Pit Alex	Salesman

Code Explanation:

The said query in SQL that retrieves the ID, name, and "Salesman" or "Customer" label for all salespeople and customers located in London. The results are combined using the UNION operator to create a single set of results.
The WHERE clause filters the results to only include rows where the city column equals 'London' for the salesman table.
The UNION operator combines the results of the first query with the results of a second query, which is enclosed in parentheses.
The WHERE clause in the second query filters the results to only include rows where the city column equals 'London' for the customer table.
The overall result set includes all rows from both queries with duplicates removed.

Practice Online