SQL Exercises: Find out if any of the customers are located in London.

SQL SUBQUERY: Exercise-15 with Solution

15. Write a query to extract all data from the customer table if and only if one or more of the customers in the customer table are located in London.

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 'customer_id', 'cust_name', and 'city' from the 'customer' table
SELECT customer_id, cust_name, city
-- Specifying the table to retrieve data from ('customer')
FROM customer
-- Filtering the results based on the existence of records in a subquery
WHERE EXISTS
   -- Subquery: Selecting any record from the 'customer' table where 'city' is 'London'
   (SELECT *
    FROM customer 
    WHERE city='London');

Output of the Query:

customer_id	cust_name		city
3002		Nick Rimando		New York
3007		Brad Davis		New York
3005		Graham Zusi		California
3008		Julian Green		London
3004		Fabian Johnson		Paris
3009		Geoff Cameron		Berlin
3003		Jozy Altidor		Moscow
3001		Brad Guzan		London

Explanation:

The said SQL query that selects the "customer_id", "cust_name", and "city" columns from the 'customer' table where there exists at least one record in the same 'customer' table where the "city" column is equal to 'London'.
The subquery in the EXISTS clause is returning all columns of the rows that match the condition 'city='London'', but the subquery only serves the purpose of checking if there is at least one row that matches the condition, it doesn't matter which columns or how many columns are selected.
The main query will return all the rows where there is a match in the subquery which means the customer's whose city is London.

Visual Explanation:

Practice Online

Sample Database: inventory

Query Visualization:

Duration:

Rows:

Cost:

Contribute your code and comments through Disqus.

Previous SQL Exercise: Sums of all order amounts, grouped by date.
Next SQL Exercise: Find the salesmen who have multiple customers.