Plan-of-SQLs Interface TN

Sample: 1894/1952
← Previous Next →

Task: Verify the Statement against the Table

Statement: 21.16 (142) home team score had a away team of fitzroy

Table: 1982 vfl season

home_team home_team_score away_team away_team_score venue crowd date
footscray 7.8 (50) richmond 16.16 (112) western oval 16259 1982-08-07
fitzroy 21.16 (142) st kilda 11.12 (78) junction oval 9987 1982-08-07
north melbourne 22.18 (150) geelong 11.16 (82) arden street oval 11634 1982-08-07
hawthorn 20.20 (140) collingwood 16.22 (118) princes park 18699 1982-08-07
essendon 20.17 (137) melbourne 14.17 (101) vfl park 28379 1982-08-07
swans 15.16 (106) carlton 9.18 (72) scg 25601 1982-08-01
Generating plan to answer the query...

Generated steps

Step 1: Select rows where the 'home_team_score' is '21.16 (142)'.

Step 2: Select rows where the 'away_team' is 'fitzroy'.

Step 3: 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 'home_team_score' is '21.16 (142)'.

SQL command for the step:

SELECT * FROM table_sql WHERE home_team_score = '21.16 (142)';
home_team home_team_score away_team away_team_score venue crowd date
footscray 7.8 (50) richmond 16.16 (112) western oval 16259 1982-08-07
fitzroy 21.16 (142) st kilda 11.12 (78) junction oval 9987 1982-08-07
north melbourne 22.18 (150) geelong 11.16 (82) arden street oval 11634 1982-08-07
hawthorn 20.20 (140) collingwood 16.22 (118) princes park 18699 1982-08-07
essendon 20.17 (137) melbourne 14.17 (101) vfl park 28379 1982-08-07
swans 15.16 (106) carlton 9.18 (72) scg 25601 1982-08-01

Step 2: Select rows where the 'away_team' is 'fitzroy'.

SQL command for the step:

SELECT * FROM table_sql WHERE away_team = 'fitzroy';
home_team home_team_score away_team away_team_score venue crowd date
fitzroy 21.16 (142) st kilda 11.12 (78) junction oval 9987 1982-08-07

Step 3: 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