Plan-of-SQLs Interface TN

Sample: 1893/1952
← Previous Next →

Task: Verify the Statement against the Table

Statement: when 16.22 (118) was the away team score , the home team score is the highest

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' in descending order.

Step 2: Select row number 1.

Step 3: Select rows where 'away_team_score' is '16.22 (118)'.

Step 4: 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' in descending order.

SQL command for the step:

SELECT * FROM table_sql ORDER BY home_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 row number 1.

SQL command for the step:

SELECT * FROM table_sql LIMIT 1;
home_team home_team_score away_team away_team_score venue crowd date
north melbourne 22.18 (150) geelong 11.16 (82) arden street oval 11634 1982-08-07
fitzroy 21.16 (142) st kilda 11.12 (78) junction oval 9987 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
footscray 7.8 (50) richmond 16.16 (112) western oval 16259 1982-08-07

Step 3: Select rows where 'away_team_score' is '16.22 (118)'.

SQL command for the step:

SELECT * FROM table_sql WHERE away_team_score = '16.22 (118)';
home_team home_team_score away_team away_team_score venue crowd date
north melbourne 22.18 (150) geelong 11.16 (82) arden street oval 11634 1982-08-07

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