Write a SQL Query to generate Cricket match fixtures for Asia Cup

 

Expected Output:

 

 

CREATE TABLE TEAMS(

   COUNTRY VARCHAR(50)

);

 

INSERT INTO TEAMS VALUES('India');

INSERT INTO TEAMS VALUES('Srilanka');

INSERT INTO TEAMS VALUES('Bangladesh');

INSERT INTO TEAMS VALUES('Pakistan');

 

SELECT

   1 AS DUMMY,

   CASE

      WHEN COUNTRY = 'India' THEN 1

      WHEN COUNTRY = 'Srilanka' THEN 2

      WHEN COUNTRY = 'Bangladesh' THEN 3

      WHEN COUNTRY = 'Pakistan' THEN 4

   ELSE 0 END AS ID,

   COUNTRY

 FROM TEAMS;

 

 

 WITH TEAM AS(

   SELECT 1 AS DUMMY,

   CASE

      WHEN COUNTRY = 'India' THEN 1

      WHEN COUNTRY = 'Srilanka' THEN 2

      WHEN COUNTRY = 'Bangladesh' THEN 3

      WHEN COUNTRY = 'Pakistan' THEN 4

   ELSE 0 END AS ID,

   COUNTRY

   FROM TEAMS

)

SELECT

   t1.COUNTRY "TEAM-A",

   t2.COUNTRY "TEAM-B"

FROM TEAM t1 JOIN TEAM t2

ON t1.DUMMY = t2.DUMMY

AND t1.ID < t2.ID;