Plan-of-SQLs Interface FP

Sample: 200/1952
← Previous Next →

Task: Verify the Statement against the Table

Statement: more than half 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 add a column 'away_score_gt_10_cnt' to the existing table.

Step 3: Calculate the total number of rows in the table and add a column 'total_rows' to the existing table.

Step 4: Use a `CASE` statement to return TRUE if the value of 'away_score_gt_10_cnt' is greater than half of the value of 'total_rows', 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 add a column 'away_score_gt_10_cnt' to the existing table.

SQL command for the step:

ALTER TABLE table_sql ADD COLUMN away_score_gt_10_cnt INT;
UPDATE table_sql SET away_score_gt_10_cnt = (SELECT COUNT(*) FROM table_sql WHERE away_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 3: Calculate the total number of rows in the table and add a column 'total_rows' to the existing table.

SQL command for the step:

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

Step 4: Use a `CASE` statement to return TRUE if the value of 'away_score_gt_10_cnt' is greater than half of the value of 'total_rows', otherwise return FALSE.

SQL command for the step:

SELECT CASE WHEN away_score_gt_10_cnt > (total_rows / 2) THEN TRUE ELSE FALSE END AS verification FROM table_sql;
home_team home_team_score away_team away_team_score venue crowd date away_score_gt_10_cnt total_rows
footscray 14.7 (91) st kilda 9.11 (65) western oval 18655 1972-07-15 6 6
fitzroy 16.14 (110) north melbourne 9.12 (66) junction oval 7007 1972-07-15 6 6
essendon 13.12 (90) richmond 17.9 (111) windy hill 22251 1972-07-15 6 6
carlton 20.8 (128) south melbourne 8.15 (63) princes park 14465 1972-07-15 6 6
hawthorn 19.14 (128) geelong 15.8 (98) glenferrie oval 12425 1972-07-15 6 6
collingwood 10.13 (73) melbourne 8.10 (58) vfl park 30883 1972-07-15 6 6

Verification:

The statement is TRUE