Plan-of-SQLs Interface TN

Sample: 1843/1952
← Previous Next →

Task: Verify the Statement against the Table

Statement: the second team had less points than the first and are separated by a two goal difference

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 the 'position' is 1 and 2.

Step 3: Select rows where the 'points' of the second team is less than the first team.

Step 4: Select rows where the 'difference' of the first team minus the second team is equal to 2.

Step 5: Use a `CASE` statement to return TRUE if the number of rows is equal to 1, 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 the 'position' is 1 and 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: Select rows where the 'points' of the second team is less than the first team.

SQL command for the step:

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

Step 4: Select rows where the 'difference' of the first team minus the second team is equal to 2.

SQL command for the step:

SELECT * FROM table_sql WHERE (first_team - second_team) = 2;
position team points played against difference

Step 5: Use a `CASE` statement to return TRUE if the number of rows is equal to 1, otherwise return FALSE.

SQL command for the step:

SELECT CASE WHEN COUNT(*) = 1 THEN TRUE ELSE FALSE END AS verification FROM table_sql;
position team points played against difference diff

Verification:

The statement is FALSE