Plan-of-SQLs Interface TN

Sample: 23/1952
← Previous Next →

Task: Verify the Statement against the Table

Statement: softball is the sport with the second latest date

Table: iowa corn cy - hawk series

date site sport winning_team series
2007-09-04 cedar rapids m golf iowa state iowa state 2 - 0
2007-09-08 des moines volleyball iowa state iowa state 4 - 0
2007-09-09 iowa city w soccer tie iowa state 5 - 1
2007-09-15 ames football iowa state iowa state 8 - 1
2007-11-10 peoria m cross country iowa state iowa state 10 - 1
2007-11-10 peoria w cross country iowa iowa state 10 - 3
2007-12-05 ames w basketball iowa state iowa state 12 - 3
2007-12-07 ames w swimming iowa state iowa state 14 - 3
2007-12-08 ames m basketball iowa state iowa state 16 - 3
2007-12-09 ames wrestling iowa iowa state 16 - 5
2008-02-22 ames w gymnastics iowa state iowa state 18 - 5
2008-03-07 iowa city w gymnastics iowa iowa state 18 - 7
2008-04-01 ames softball iowa iowa state 18 - 9
Generating plan to answer the query...

Generated steps

Step 1: Order the table by 'date' in descending order.

Step 2: Select the row number 2.

Step 3: Select rows where 'sport' is 'softball'.

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

SQL command for the step:

SELECT * FROM table_sql ORDER BY date DESC;
date site sport winning_team series
2007-09-04 cedar rapids m golf iowa state iowa state 2 - 0
2007-09-08 des moines volleyball iowa state iowa state 4 - 0
2007-09-09 iowa city w soccer tie iowa state 5 - 1
2007-09-15 ames football iowa state iowa state 8 - 1
2007-11-10 peoria m cross country iowa state iowa state 10 - 1
2007-11-10 peoria w cross country iowa iowa state 10 - 3
2007-12-05 ames w basketball iowa state iowa state 12 - 3
2007-12-07 ames w swimming iowa state iowa state 14 - 3
2007-12-08 ames m basketball iowa state iowa state 16 - 3
2007-12-09 ames wrestling iowa iowa state 16 - 5
2008-02-22 ames w gymnastics iowa state iowa state 18 - 5
2008-03-07 iowa city w gymnastics iowa iowa state 18 - 7
2008-04-01 ames softball iowa iowa state 18 - 9

Step 2: Select the row number 2.

SQL command for the step:

SELECT * FROM table_sql ORDER BY some_column LIMIT 1 OFFSET 1;
date site sport winning_team series
2008-04-01 ames softball iowa iowa state 18 - 9
2008-03-07 iowa city w gymnastics iowa iowa state 18 - 7
2008-02-22 ames w gymnastics iowa state iowa state 18 - 5
2007-12-09 ames wrestling iowa iowa state 16 - 5
2007-12-08 ames m basketball iowa state iowa state 16 - 3
2007-12-07 ames w swimming iowa state iowa state 14 - 3
2007-12-05 ames w basketball iowa state iowa state 12 - 3
2007-11-10 peoria m cross country iowa state iowa state 10 - 1
2007-11-10 peoria w cross country iowa iowa state 10 - 3
2007-09-15 ames football iowa state iowa state 8 - 1
2007-09-09 iowa city w soccer tie iowa state 5 - 1
2007-09-08 des moines volleyball iowa state iowa state 4 - 0
2007-09-04 cedar rapids m golf iowa state iowa state 2 - 0

Step 3: Select rows where 'sport' is 'softball'.

SQL command for the step:

SELECT * FROM table_sql WHERE sport = 'softball';
date site sport winning_team series
2008-03-07 iowa city w gymnastics iowa iowa state 18 - 7

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;
date site sport winning_team series

Verification:

The statement is FALSE