w3resource

SQL Exercises: Display all salesmen and customer located in London

SQL UNION: Exercise-1 with Solution

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: 

SELECT salesman_id "ID", name, 'Salesman'
FROM salesman
WHERE city='London'
UNION
(SELECT customer_id "ID", cust_name, 'Customer'
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


Inventory database model

Query Visualization:

Duration:

Query visualization of Display all salesmen and customer located in London - Duration

Rows:

Query visualization of Display all salesmen and customer located in London - Rows

Cost:

Query visualization of Display all salesmen and customer located in London - Cost

Contribute your code and comments through Disqus.

Previous SQL Exercise: SQL UNION Exercises Home.
Next SQL Exercise: Display distinct salesman and their cities.

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.

SQL: Tips of the Day

Grouped LIMIT in PostgreSQL: Show the first N rows for each group?

db=# SELECT * FROM xxx;
 id | section_id | name
----+------------+------
  1 |          1 | A
  2 |          1 | B
  3 |          1 | C
  4 |          1 | D
  5 |          2 | E
  6 |          2 | F
  7 |          3 | G
  8 |          2 | H
(8 rows)

I need the first 2 rows (ordered by name) for each section_id, i.e. a result similar to:

id | section_id | name
----+------------+------
  1 |          1 | A
  2 |          1 | B
  5 |          2 | E
  6 |          2 | F
  7 |          3 | G
(5 rows)

PostgreSQL v9.3 you can do a lateral join

select distinct t_outer.section_id, t_top.id, t_top.name from t t_outer
join lateral (
    select * from t t_inner
    where t_inner.section_id = t_outer.section_id
    order by t_inner.name
    limit 2
) t_top on true
order by t_outer.section_id;

Database: PostgreSQL

Ref: https://bit.ly/3AfYwZI

 

We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook