Car Color

      

Write an SQL query to fetch all the names from the Persons table who did not order any car with the colour RED from the tables.

Input :-

Persons table :-
ID is the Primary Key column for the Persons table.

ID	Name	Car_taken_date
1	Vrushabh	2/3/2006
2	Vinay	5/1/2008
3	Viraj	10/4/2014
4	Pratheek	8/6/2012
5	Nikhil	5/7/2019
6	Mallikarjun	2/5/2022

Cars table :-

Car_ID is the Primary Key for the Cars table.

Car_ID	Colour	City
1	Red	Texas
2	Black	Chicago
3	Blue	New York
4	White	Oklahoma

Orders table :-

Order_ID is the Primary key for the Orders table.

Order_ID	ID	Car_ID	Price
1	4	3	100000
2	5	4	350000
3	1	1	500000
4	4	1	825000

Output :-

Name

Vinay

Viraj

Nikhil

Query :-

select name from Persons where name not in
(select p.name
 from Persons
 inner join Orders o on p.ID=o.ID
 inner join Cars c on c.Car_ID=o.Car_ID
 where c.colour='RED')

Explanation : - 

This query is selecting the names from the "Persons" table where the names are not present in the result set of the subquery.

The subquery is retrieving the names from the "Persons" table who have placed orders for cars that are red. It achieves this by joining the "Persons" table with the "Orders" table on the ID column, and then joining the "Orders" table with the "Cars" table on the Car_ID column. The condition c.color='RED' ensures that only orders for red cars are considered.

The main query uses the NOT IN operator to exclude the names that are returned by the subquery. It retrieves the names from the "Persons" table that are not found in the result set of the subquery.

In simpler terms, this query selects the names of persons who have not placed orders for red cars. It helps identify individuals who have not shown interest in purchasing cars of the colour red.