Plan-of-SQLs Interface TN

Sample: 1842/1952
← Previous Next →

Task: Verify the Statement against the Table

Statement: the teams in the first 2 positions held a different amount of points with the 2nd falling behind by 2

Table: 1940 in brazilian football

position team points played against difference
1 flamengo 13 8 12 8
2 fluminense 13 8 15 10
3 corinthians 9 8 15 4
4 palestra itália 8 8 19 3
5 portuguesa 7 8 23 - 10
6 botafogo 6 8 25 0
7 vasco da gama 6 8 19 - 2
8 américa 6 8 25 - 10
9 são paulo 4 8 24 - 13
Generating plan to answer the query...

Generated steps

Step 1: Order the table by 'position' in ascending order.

Step 2: Select rows where 'position' is 1 or 2.

Step 3: Extract the 'points' from the selected rows and add column 'points_1_2' to the existing table.

Step 4: Select rows where 'position' is 2.

Step 5: Extract the 'points' from the selected rows and add column 'points_2' to the existing table.

Step 6: Use a `CASE` statement to return TRUE if the difference between 'points_1_2' and 'points_2' is 2, otherwise return FALSE.

Step 1: Order the table by 'position' in ascending order.

SQL command for the step:

SELECT * FROM table_sql ORDER BY position ASC;
position team points played against difference
1 flamengo 13 8 12 8
2 fluminense 13 8 15 10
3 corinthians 9 8 15 4
4 palestra itália 8 8 19 3
5 portuguesa 7 8 23 - 10
6 botafogo 6 8 25 0
7 vasco da gama 6 8 19 - 2
8 américa 6 8 25 - 10
9 são paulo 4 8 24 - 13

Step 2: Select rows where 'position' is 1 or 2.

SQL command for the step:

SELECT * FROM table_sql WHERE position IN (1, 2);
position team points played against difference
1 flamengo 13 8 12 8
2 fluminense 13 8 15 10
3 corinthians 9 8 15 4
4 palestra itália 8 8 19 3
5 portuguesa 7 8 23 - 10
6 botafogo 6 8 25 0
7 vasco da gama 6 8 19 - 2
8 américa 6 8 25 - 10
9 são paulo 4 8 24 - 13

Step 3: Extract the 'points' from the selected rows and add column 'points_1_2' to the existing table.

SQL command for the step:

ALTER TABLE table_sql ADD COLUMN points_1_2 INT;
UPDATE table_sql SET points_1_2 = points WHERE <condition>;
position team points played against difference
1 flamengo 13 8 12 8
2 fluminense 13 8 15 10

Step 4: Select rows where 'position' is 2.

SQL command for the step:

SELECT * FROM table_sql WHERE position = 2;
position team points played against difference points_1_2
1 flamengo 13 8 12 8 13
2 fluminense 13 8 15 10 13

Step 5: Extract the 'points' from the selected rows and add column 'points_2' to the existing table.

SQL command for the step:

ALTER TABLE table_sql ADD COLUMN points_2 INT;
UPDATE table_sql SET points_2 = points WHERE <condition>;
position team points played against difference points_1_2
2 fluminense 13 8 15 10 13

Step 6: Use a `CASE` statement to return TRUE if the difference between 'points_1_2' and 'points_2' is 2, otherwise return FALSE.

SQL command for the step:

SELECT CASE WHEN (points_1_2 - points_2) = 2 THEN TRUE ELSE FALSE END AS verification FROM table_sql;
position team points played against difference points_1_2 points_2
2 fluminense 13 8 15 10 13 13

Verification:

The statement is FALSE