Winners Prize Amount

 

Write an SQL query to fetch all the names whose prize amount is less than 1000 and those who haven't received any prize from the table.

Input :-

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

ID	Name
3	Raksha
1	Ranjitha
2	Manu
4	Gaman

Winners Table :-

ID is the Primary Key column for the Winners table.

ID	Prize
2	800
4	1500

Output :-

Name	Prize
Raksha	null
Ranjitha	null
Manu	800

 

Query :-

select p.name,w.prize
from Participants p
left join Winners w on p.ID=w.ID
where Prize<1000 or Prize is null;

Explanation : - 

This query retrieves the names and prize amount of the participants. 'Participants' table performs a left join with the 'Winners' table on the ID column, linking participants to their respective prizes.

The WHERE clause specifies two conditions:

• Prize<1000: This condition filters the results to include only participants whose prize is less than 1000. 
• Prize is null: This condition includes participants who do not have a prize assigned or whose prize value is null.

By combining these conditions with the OR operator, the query will retrieve participants who either have a prize less than 1000 or have no prize assigned at all.

In summary, the query aims to fetch the names and prizes of participants who have a prize less than 1000 or no prize assigned.