Plan-of-SQLs Interface TP

Sample: 1891/1952
← Previous Next →

Task: Verify the Statement against the Table

Statement: 16.22 (118) was the away team score against a home team score of 20.20 (140)

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: Order the table by 'home_team_score' and 'away_team_score' in descending order.

Step 2: Select rows where 'home_team_score' is equal to '20.20 (140)' and 'away_team_score' is equal to '16.22 (118)'.

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

Step 1: Order the table by 'home_team_score' and 'away_team_score' in descending order.

SQL command for the step:

SELECT * FROM table_sql ORDER BY home_team_score DESC, away_team_score DESC;
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 'home_team_score' is equal to '20.20 (140)' and 'away_team_score' is equal to '16.22 (118)'.

SQL command for the step:

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

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 home_team_score_num away_team_score_num
hawthorn 20.20 (140) collingwood 16.22 (118) princes park 18699 1982-08-07 140 118

Verification:

The statement is TRUE