Plan-of-SQLs Interface TN

Sample: 199/1952
← Previous Next →

Task: Verify the Statement against the Table

Statement: the average of all the home team scores is less than 12

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: Extract the numerical home team score from the 'home_team_score' column then add column 'num_home_score' to existing table.

Step 2: Calculate the average of the 'num_home_score' column then add column 'avg_home_score' to existing table.

Step 3: Select rows where 'avg_home_score' is less than 12.

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

Step 1: Extract the numerical home team score from the 'home_team_score' column then add column 'num_home_score' to existing table.

SQL command for the step:

ALTER TABLE table_sql ADD COLUMN num_home_score INT; UPDATE table_sql SET num_home_score = home_team_score;
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: Calculate the average of the 'num_home_score' column then add column 'avg_home_score' to existing table.

SQL command for the step:

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

Step 3: Select rows where 'avg_home_score' is less than 12.

SQL command for the step:

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

Step 4: 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 num_home_score avg_home_score

Verification:

The statement is FALSE