Plan-of-SQLs Interface TP

Sample: 1837/1952
← Previous Next →

Task: Verify the Statement against the Table

Statement: two of the teams hold the first two positions with an equal number of points

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 'points' in descending order.

Step 2: Select the top two rows.

Step 3: Use a `CASE` statement to return TRUE if the number of rows is equal to 2 and the 'points' values are equal, otherwise return FALSE.

Step 1: Order the table by 'points' in descending order.

SQL command for the step:

SELECT * FROM table_sql ORDER BY points DESC;
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 the top two rows.

SQL command for the step:

SELECT * FROM table_sql LIMIT 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: Use a `CASE` statement to return TRUE if the number of rows is equal to 2 and the 'points' values are equal, otherwise return FALSE.

SQL command for the step:

SELECT CASE 
           WHEN COUNT(*) = 2 AND COUNT(DISTINCT points) = 1 THEN TRUE 
           ELSE FALSE 
       END AS verification 
FROM table_sql;
position team points played against difference
1 flamengo 13 8 12 8
2 fluminense 13 8 15 10

Verification:

The statement is TRUE