Plan-of-SQLs Interface TN

Sample: 1761/1952
← Previous Next →

Task: Verify the Statement against the Table

Statement: on 21 august 1943 , north melbourne was the away team at glenferrie oval

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 the 'date' is '1943-08-21'.

Step 2: Select rows where the 'away_team' is 'north melbourne'.

Step 3: Select rows where the 'venue' is 'glenferrie oval'.

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 the 'date' is '1943-08-21'.

SQL command for the step:

SELECT * FROM table_sql WHERE date = '1943-08-21';
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 the 'away_team' is 'north melbourne'.

SQL command for the step:

SELECT * FROM table_sql WHERE away_team = 'north melbourne';
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 3: Select rows where the 'venue' is 'glenferrie oval'.

SQL command for the step:

SELECT * FROM table_sql WHERE venue = 'glenferrie oval';
home_team home_team_score away_team away_team_score venue crowd date
carlton 15.23 (113) north melbourne 7.5 (47) princes park 8000 1943-08-21

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