Plan-of-SQLs Interface TN

Sample: 1760/1952
← Previous Next →

Task: Verify the Statement against the Table

Statement: richmond , with the home team score of 9.10 (64) , was away team melbourne 's home team opponent

Table: 1943 vfl season

home_team home_team_score away_team away_team_score venue crowd date
hawthorn 13.10 (88) south melbourne 7.14 (56) glenferrie oval 15000 1943-08-21
collingwood 16.17 (113) fitzroy 9.9 (63) victoria park 6500 1943-08-21
carlton 15.23 (113) north melbourne 7.5 (47) princes park 8000 1943-08-21
richmond 15.19 (109) melbourne 12.13 (85) punt road oval 9000 1943-08-21
footscray 9.10 (64) essendon 6.15 (51) western oval 6000 1943-08-21
Generating plan to answer the query...

Generated steps

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

Step 2: Select rows where 'home_team_score' is '9.10 (64)'.

Step 3: Select rows where 'away_team' is 'melbourne'.

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

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

SQL command for the step:

SELECT * FROM table_sql WHERE home_team = 'richmond';
home_team home_team_score away_team away_team_score venue crowd date
hawthorn 13.10 (88) south melbourne 7.14 (56) glenferrie oval 15000 1943-08-21
collingwood 16.17 (113) fitzroy 9.9 (63) victoria park 6500 1943-08-21
carlton 15.23 (113) north melbourne 7.5 (47) princes park 8000 1943-08-21
richmond 15.19 (109) melbourne 12.13 (85) punt road oval 9000 1943-08-21
footscray 9.10 (64) essendon 6.15 (51) western oval 6000 1943-08-21

Step 2: Select rows where 'home_team_score' is '9.10 (64)'.

SQL command for the step:

SELECT * FROM table_sql WHERE home_team_score = '9.10 (64)';
home_team home_team_score away_team away_team_score venue crowd date
richmond 15.19 (109) melbourne 12.13 (85) punt road oval 9000 1943-08-21

Step 3: Select rows where 'away_team' is 'melbourne'.

SQL command for the step:

SELECT * FROM table_sql WHERE away_team = 'melbourne';
home_team home_team_score away_team away_team_score venue crowd date

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

Verification:

The statement is FALSE