Derive Points table for ICC tournament.-SQL IntreviewQuestion-1

 Please find the SQL Question & Answer.

Day-1:

Date-5th Nov 2022
Question-1:
Derive Points table for ICC tournament.

select ICC1.Team_1 "Team Name" FROM  icc_world_cup ICC1

UNION 

select ICC2.Team_2 FROM  icc_world_cup ICC2

INSERT INTO icc_world_cup values('Aus','India','India','NZ');

INSERT INTO icc_world_cup values('Aus','India','India','Aus');

INSERT INTO icc_world_cup values('Aus','India','India','SA');

INSERT INTO icc_world_cup values('Aus','India','India','SL');

ALTER TABLE icc_world_cup ADD DRAW varchar(100);

select * from  icc_world_cup ICC1 

select A.[Team Name],Count(1) as "No of Match Played",SUM (A.[Win])as "Winner",Count(1)-SUM (A.[Win]) as "Loss" from

(

select ICC1.Team_1 as "Team Name" ,CASE WHEN ICC1.Team_1=ICC1.Winner THEN 1 else 0 END as "Win"  from icc_world_cup ICC1

union all

select ICC1.Team_2,CASE WHEN ICC1.Team_2=ICC1.Winner THEN 1 else 0 END as "Win"  from icc_world_cup ICC1

)A

GROUP BY A.[Team Name]