Plan-of-SQLs Interface TP

Sample: 193/1952
← Previous Next →

Task: Verify the Statement against the Table

Statement: the collingwood vs melbourne game has the largest crowd size

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 row number 1.

Step 3: Select rows where 'home_team' is 'collingwood' and 'away_team' is 'melbourne'.

Step 4: 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 '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 row number 1.

SQL command for the step:

SELECT * FROM table_sql LIMIT 1;
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 'collingwood' and 'away_team' is 'melbourne'.

SQL command for the step:

SELECT * FROM table_sql WHERE home_team = 'collingwood' AND away_team = '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

Step 4: 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;
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

Verification:

The statement is TRUE