Plan-of-SQLs Interface TP

Sample: 195/1952
← Previous Next →

Task: Verify the Statement against the Table

Statement: 1 / 3 of the away teams had a score greater than 10

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: Select rows where the away_team_score is greater than 10.

Step 2: Count the number of rows selected in step 1 and divide it by the total number of rows in the table.

Step 3: Use a `CASE` statement to return TRUE if the result of step 2 is equal to 1/3, otherwise return FALSE.

Step 1: Select rows where the away_team_score is greater than 10.

SQL command for the step:

SELECT * FROM table_sql WHERE away_team_score > 10;
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: Count the number of rows selected in step 1 and divide it by the total number of rows in the table.

SQL command for the step:

SET @count_selected = (SELECT COUNT(*) FROM table_sql WHERE <condition_from_step_1>);
SET @total_rows = (SELECT COUNT(*) FROM table_sql);
SET verification = (@count_selected / @total_rows);
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 3: Use a `CASE` statement to return TRUE if the result of step 2 is equal to 1/3, otherwise return FALSE.

SQL command for the step:

SELECT CASE WHEN result_column = 1/3 THEN TRUE ELSE FALSE END AS verification FROM table_sql;
ratio
1

Verification:

The statement is TRUE