Plan-of-SQLs Interface FP

Sample: 198/1952
← Previous Next →

Task: Verify the Statement against the Table

Statement: more attended the fitzroy vs north melbourne game than the essendon vs richmond game

Table: 1972 vfl season

home_team home_team_score away_team away_team_score venue crowd date
footscray 14.7 (91) st kilda 9.11 (65) western oval 18655 1972-07-15
fitzroy 16.14 (110) north melbourne 9.12 (66) junction oval 7007 1972-07-15
essendon 13.12 (90) richmond 17.9 (111) windy hill 22251 1972-07-15
carlton 20.8 (128) south melbourne 8.15 (63) princes park 14465 1972-07-15
hawthorn 19.14 (128) geelong 15.8 (98) glenferrie oval 12425 1972-07-15
collingwood 10.13 (73) melbourne 8.10 (58) vfl park 30883 1972-07-15
Generating plan to answer the query...

Generated steps

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

Step 2: Select rows where 'home_team' is 'fitzroy' and 'away_team' is 'north melbourne'.

Step 3: Select rows where 'home_team' is 'essendon' and 'away_team' is 'richmond'.

Step 4: Use a `CASE` statement to return TRUE if the number of rows for the fitzroy vs north melbourne game is greater than the number of rows for the essendon vs richmond game, otherwise return FALSE.

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

SQL command for the step:

SELECT * FROM table_sql ORDER BY crowd DESC;
home_team home_team_score away_team away_team_score venue crowd date
footscray 14.7 (91) st kilda 9.11 (65) western oval 18655 1972-07-15
fitzroy 16.14 (110) north melbourne 9.12 (66) junction oval 7007 1972-07-15
essendon 13.12 (90) richmond 17.9 (111) windy hill 22251 1972-07-15
carlton 20.8 (128) south melbourne 8.15 (63) princes park 14465 1972-07-15
hawthorn 19.14 (128) geelong 15.8 (98) glenferrie oval 12425 1972-07-15
collingwood 10.13 (73) melbourne 8.10 (58) vfl park 30883 1972-07-15

Step 2: Select rows where 'home_team' is 'fitzroy' and 'away_team' is 'north melbourne'.

SQL command for the step:

SELECT * FROM table_sql WHERE home_team = 'fitzroy' AND away_team = 'north melbourne';
home_team home_team_score away_team away_team_score venue crowd date
collingwood 10.13 (73) melbourne 8.10 (58) vfl park 30883 1972-07-15
essendon 13.12 (90) richmond 17.9 (111) windy hill 22251 1972-07-15
footscray 14.7 (91) st kilda 9.11 (65) western oval 18655 1972-07-15
carlton 20.8 (128) south melbourne 8.15 (63) princes park 14465 1972-07-15
hawthorn 19.14 (128) geelong 15.8 (98) glenferrie oval 12425 1972-07-15
fitzroy 16.14 (110) north melbourne 9.12 (66) junction oval 7007 1972-07-15

Step 3: Select rows where 'home_team' is 'essendon' and 'away_team' is 'richmond'.

SQL command for the step:

SELECT * FROM table_sql WHERE home_team = 'essendon' AND away_team = 'richmond';
home_team home_team_score away_team away_team_score venue crowd date
fitzroy 16.14 (110) north melbourne 9.12 (66) junction oval 7007 1972-07-15

Step 4: Use a `CASE` statement to return TRUE if the number of rows for the fitzroy vs north melbourne game is greater than the number of rows for the essendon vs richmond game, otherwise return FALSE.

SQL command for the step:

SELECT CASE 
           WHEN (SELECT COUNT(*) FROM table_sql WHERE game = 'fitzroy vs north melbourne') > 
                (SELECT COUNT(*) FROM table_sql WHERE game = 'essendon vs richmond') 
           THEN TRUE 
           ELSE FALSE 
       END AS verification;
home_team home_team_score away_team away_team_score venue crowd date

Verification:

The statement is TRUE