Plan-of-SQLs Interface TN

Sample: 1942/1952
← Previous Next →

Task: Verify the Statement against the Table

Statement: collingwood had a home team score 3.00 points higher than that of carlton

Table: 1954 vfl season

home_team home_team_score away_team away_team_score venue crowd date
geelong 13.12 (90) hawthorn 7.6 (48) kardinia park 16870 1954-08-14
collingwood 12.16 (88) south melbourne 13.12 (90) victoria park 18556 1954-08-14
carlton 10.16 (76) essendon 11.14 (80) princes park 29744 1954-08-14
richmond 10.18 (78) melbourne 15.4 (94) punt road oval 24000 1954-08-14
north melbourne 9.14 (68) footscray 9.14 (68) arden street oval 22000 1954-08-14
st kilda 13.14 (92) fitzroy 9.15 (69) junction oval 11500 1954-08-14
Generating plan to answer the query...

Generated steps

Step 1: Select rows where 'home_team' is 'collingwood'.

Step 2: Select rows where 'home_team' is 'carlton'.

Step 3: Extract the numerical home team score from the 'home_team_score' column for collingwood then add column 'collingwood_score' to existing table.

Step 4: Extract the numerical home team score from the 'home_team_score' column for carlton then add column 'carlton_score' to existing table.

Step 5: Select rows where 'collingwood_score' is 3.00 points higher than 'carlton_score'.

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

Step 1: Select rows where 'home_team' is 'collingwood'.

SQL command for the step:

SELECT * FROM table_sql WHERE home_team = 'collingwood';
home_team home_team_score away_team away_team_score venue crowd date
geelong 13.12 (90) hawthorn 7.6 (48) kardinia park 16870 1954-08-14
collingwood 12.16 (88) south melbourne 13.12 (90) victoria park 18556 1954-08-14
carlton 10.16 (76) essendon 11.14 (80) princes park 29744 1954-08-14
richmond 10.18 (78) melbourne 15.4 (94) punt road oval 24000 1954-08-14
north melbourne 9.14 (68) footscray 9.14 (68) arden street oval 22000 1954-08-14
st kilda 13.14 (92) fitzroy 9.15 (69) junction oval 11500 1954-08-14

Step 2: Select rows where 'home_team' is 'carlton'.

SQL command for the step:

SELECT * FROM table_sql WHERE home_team = 'carlton';
home_team home_team_score away_team away_team_score venue crowd date
collingwood 12.16 (88) south melbourne 13.12 (90) victoria park 18556 1954-08-14

Step 3: Extract the numerical home team score from the 'home_team_score' column for collingwood then add column 'collingwood_score' to existing table.

SQL command for the step:

ALTER TABLE table_sql ADD COLUMN collingwood_score INT; UPDATE table_sql SET collingwood_score = home_team_score WHERE home_team = 'collingwood';
home_team home_team_score away_team away_team_score venue crowd date

Step 4: Extract the numerical home team score from the 'home_team_score' column for carlton then add column 'carlton_score' to existing table.

SQL command for the step:

ALTER TABLE table_sql ADD COLUMN carlton_score INT; UPDATE table_sql SET carlton_score = home_team_score WHERE home_team = 'carlton';
home_team home_team_score away_team away_team_score venue crowd date collingwood_score

Step 5: Select rows where 'collingwood_score' is 3.00 points higher than 'carlton_score'.

SQL command for the step:

SELECT * FROM table_sql WHERE collingwood_score = carlton_score + 3.00;
home_team home_team_score away_team away_team_score venue crowd date collingwood_score carlton_score

Step 6: 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 collingwood_score carlton_score score_difference

Verification:

The statement is FALSE