Plan-of-SQLs Interface FN

Sample: 194/1952
← Previous Next →

Task: Verify the Statement against the Table

Statement: the average of all the home team scores is 15.67

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: Calculate the average of the 'home_team_score' column then add column 'avg_home_team_score' to the existing table.

Step 2: Select rows where 'avg_home_team_score' is equal to 15.67.

Step 3: Use a `CASE` statement to return TRUE if the number of rows is greater than or equal to 1, otherwise return FALSE.

Step 1: Calculate the average of the 'home_team_score' column then add column 'avg_home_team_score' to the existing table.

SQL command for the step:

ALTER TABLE table_sql ADD COLUMN avg_home_team_score FLOAT DEFAULT (SELECT AVG(home_team_score) FROM table_sql);
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 'avg_home_team_score' is equal to 15.67.

SQL command for the step:

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

Step 3: Use a `CASE` statement to return TRUE if the number of rows is greater than or 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 avg_home_team_score

Verification:

The statement is FALSE