Plan-of-SQLs Interface TP

Sample: 17/1952
← Previous Next →

Task: Verify the Statement against the Table

Statement: volleyball is the sport with the 2nd earliest 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 ascending order.

Step 2: Select the row number 2.

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

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 ascending order.

SQL command for the step:

SELECT * FROM table_sql ORDER BY date ASC;
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
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 3: Select rows where 'sport' is 'volleyball'.

SQL command for the step:

SELECT * FROM table_sql WHERE sport = 'volleyball';
date site sport winning_team series
2007-09-08 des moines volleyball iowa state iowa state 4 - 0

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
2007-09-08 des moines volleyball iowa state iowa state 4 - 0

Verification:

The statement is TRUE