Plan-of-SQLs Interface TP

Sample: 1916/1952
← Previous Next →

Task: Verify the Statement against the Table

Statement: the shortest time between two consecutive matches was 2 days

Table: 2000 - 01 sheffield wednesday f.c. season

date opponent venue result attendance
2000-08-13 wolverhampton wanderers a 1 - 1 19086
2000-08-19 huddersfield town h 2 - 3 22704
2000-08-26 grimsby town a 1 - 0 7755
2000-08-28 blackburn rovers h 1 - 1 15646
2000-09-09 wimbledon h 0 - 5 15856
2000-09-13 nottingham forest h 0 - 1 15700
2000-09-16 tranmere rovers a 0 - 2 9352
2000-09-23 preston north end h 1 - 3 17379
2000-09-30 gillingham a 0 - 2 9099
2000-10-08 west bromwich albion h 1 - 2 15338
2000-10-14 portsmouth a 1 - 2 13376
2000-10-17 burnley a 0 - 1 16372
2000-10-22 birmingham city h 1 - 0 14695
2000-10-25 queens park rangers a 2 - 1 10353
2000-10-28 fulham h 3 - 3 17559
2000-11-04 crystal palace a 1 - 4 15333
2000-11-07 watford a 3 - 1 11166
2000-11-01 norwich city h 3 - 2 16956
2000-11-18 barnsley a 0 - 1 19989
2000-11-25 crewe alexandra a 0 - 1 7103
2000-12-02 queens park rangers h 5 - 2 21782
2000-12-09 stockport county h 2 - 4 16337
2000-12-16 sheffield united a 1 - 1 25156
2000-12-23 wolverhampton wanderers h 0 - 1 17787
2000-12-26 bolton wanderers a 0 - 2 21316
2000-12-30 huddersfield town a 0 - 0 18931
2001-01-01 grimsby town h 1 - 0 17004
2001-01-13 blackburn rovers a 0 - 2 19308
2001-01-20 bolton wanderers h 0 - 3 17638
2001-02-03 watford h 2 - 3 16134
2001-02-10 wimbledon a 1 - 4 6741
2001-02-13 tranmere rovers h 1 - 0 15444
2001-02-21 nottingham forest a 1 - 0 23266
2001-02-24 preston north end a 0 - 2 14379
2001-03-03 gillingham h 2 - 1 18702
2001-03-07 portsmouth h 0 - 0 20503
2001-03-10 west bromwich albion a 2 - 1 18662
2001-03-17 burnley h 2 - 0 20184
2001-03-24 birmingham city a 2 - 1 19733
2001-04-01 sheffield united h 1 - 2 38433
2001-04-07 stockport county a 1 - 2 9666
2001-04-14 crystal palace h 4 - 1 19877
2001-04-16 fulham a 1 - 1 17500
2001-04-21 barnsley h 2 - 1 23498
2001-04-28 norwich city a 0 - 1 21241
2001-05-06 crewe alexandra h 0 - 0 28007
Generating plan to answer the query...

Generated steps

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

Step 2: Add a new column 'next_date' to the table, which contains the date of the next match.

Step 3: Calculate the time difference between 'date' and 'next_date' in days, and add a new column 'time_diff' to the table.

Step 4: Select rows where 'time_diff' is equal to 2.

Step 5: Use a `CASE` statement to return TRUE if the number of rows is greater than or 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 opponent venue result attendance
2000-08-13 wolverhampton wanderers a 1 - 1 19086
2000-08-19 huddersfield town h 2 - 3 22704
2000-08-26 grimsby town a 1 - 0 7755
2000-08-28 blackburn rovers h 1 - 1 15646
2000-09-09 wimbledon h 0 - 5 15856
2000-09-13 nottingham forest h 0 - 1 15700
2000-09-16 tranmere rovers a 0 - 2 9352
2000-09-23 preston north end h 1 - 3 17379
2000-09-30 gillingham a 0 - 2 9099
2000-10-08 west bromwich albion h 1 - 2 15338
2000-10-14 portsmouth a 1 - 2 13376
2000-10-17 burnley a 0 - 1 16372
2000-10-22 birmingham city h 1 - 0 14695
2000-10-25 queens park rangers a 2 - 1 10353
2000-10-28 fulham h 3 - 3 17559
2000-11-04 crystal palace a 1 - 4 15333
2000-11-07 watford a 3 - 1 11166
2000-11-01 norwich city h 3 - 2 16956
2000-11-18 barnsley a 0 - 1 19989
2000-11-25 crewe alexandra a 0 - 1 7103
2000-12-02 queens park rangers h 5 - 2 21782
2000-12-09 stockport county h 2 - 4 16337
2000-12-16 sheffield united a 1 - 1 25156
2000-12-23 wolverhampton wanderers h 0 - 1 17787
2000-12-26 bolton wanderers a 0 - 2 21316
2000-12-30 huddersfield town a 0 - 0 18931
2001-01-01 grimsby town h 1 - 0 17004
2001-01-13 blackburn rovers a 0 - 2 19308
2001-01-20 bolton wanderers h 0 - 3 17638
2001-02-03 watford h 2 - 3 16134
2001-02-10 wimbledon a 1 - 4 6741
2001-02-13 tranmere rovers h 1 - 0 15444
2001-02-21 nottingham forest a 1 - 0 23266
2001-02-24 preston north end a 0 - 2 14379
2001-03-03 gillingham h 2 - 1 18702
2001-03-07 portsmouth h 0 - 0 20503
2001-03-10 west bromwich albion a 2 - 1 18662
2001-03-17 burnley h 2 - 0 20184
2001-03-24 birmingham city a 2 - 1 19733
2001-04-01 sheffield united h 1 - 2 38433
2001-04-07 stockport county a 1 - 2 9666
2001-04-14 crystal palace h 4 - 1 19877
2001-04-16 fulham a 1 - 1 17500
2001-04-21 barnsley h 2 - 1 23498
2001-04-28 norwich city a 0 - 1 21241
2001-05-06 crewe alexandra h 0 - 0 28007

Step 2: Add a new column 'next_date' to the table, which contains the date of the next match.

SQL command for the step:

ALTER TABLE table_sql ADD COLUMN next_date DATE;
date opponent venue result attendance
2000-08-13 wolverhampton wanderers a 1 - 1 19086
2000-08-19 huddersfield town h 2 - 3 22704
2000-08-26 grimsby town a 1 - 0 7755
2000-08-28 blackburn rovers h 1 - 1 15646
2000-09-09 wimbledon h 0 - 5 15856
2000-09-13 nottingham forest h 0 - 1 15700
2000-09-16 tranmere rovers a 0 - 2 9352
2000-09-23 preston north end h 1 - 3 17379
2000-09-30 gillingham a 0 - 2 9099
2000-10-08 west bromwich albion h 1 - 2 15338
2000-10-14 portsmouth a 1 - 2 13376
2000-10-17 burnley a 0 - 1 16372
2000-10-22 birmingham city h 1 - 0 14695
2000-10-25 queens park rangers a 2 - 1 10353
2000-10-28 fulham h 3 - 3 17559
2000-11-01 norwich city h 3 - 2 16956
2000-11-04 crystal palace a 1 - 4 15333
2000-11-07 watford a 3 - 1 11166
2000-11-18 barnsley a 0 - 1 19989
2000-11-25 crewe alexandra a 0 - 1 7103
2000-12-02 queens park rangers h 5 - 2 21782
2000-12-09 stockport county h 2 - 4 16337
2000-12-16 sheffield united a 1 - 1 25156
2000-12-23 wolverhampton wanderers h 0 - 1 17787
2000-12-26 bolton wanderers a 0 - 2 21316
2000-12-30 huddersfield town a 0 - 0 18931
2001-01-01 grimsby town h 1 - 0 17004
2001-01-13 blackburn rovers a 0 - 2 19308
2001-01-20 bolton wanderers h 0 - 3 17638
2001-02-03 watford h 2 - 3 16134
2001-02-10 wimbledon a 1 - 4 6741
2001-02-13 tranmere rovers h 1 - 0 15444
2001-02-21 nottingham forest a 1 - 0 23266
2001-02-24 preston north end a 0 - 2 14379
2001-03-03 gillingham h 2 - 1 18702
2001-03-07 portsmouth h 0 - 0 20503
2001-03-10 west bromwich albion a 2 - 1 18662
2001-03-17 burnley h 2 - 0 20184
2001-03-24 birmingham city a 2 - 1 19733
2001-04-01 sheffield united h 1 - 2 38433
2001-04-07 stockport county a 1 - 2 9666
2001-04-14 crystal palace h 4 - 1 19877
2001-04-16 fulham a 1 - 1 17500
2001-04-21 barnsley h 2 - 1 23498
2001-04-28 norwich city a 0 - 1 21241
2001-05-06 crewe alexandra h 0 - 0 28007

Step 3: Calculate the time difference between 'date' and 'next_date' in days, and add a new column 'time_diff' to the table.

SQL command for the step:

ALTER TABLE table_sql ADD COLUMN time_diff INT;
UPDATE table_sql SET time_diff = DATEDIFF(next_date, date);
date opponent venue result attendance next_date
2000-08-13 wolverhampton wanderers a 1 - 1 19086 2000-08-19
2000-08-19 huddersfield town h 2 - 3 22704 2000-08-26
2000-08-26 grimsby town a 1 - 0 7755 2000-08-28
2000-08-28 blackburn rovers h 1 - 1 15646 2000-09-09
2000-09-09 wimbledon h 0 - 5 15856 2000-09-13
2000-09-13 nottingham forest h 0 - 1 15700 2000-09-16
2000-09-16 tranmere rovers a 0 - 2 9352 2000-09-23
2000-09-23 preston north end h 1 - 3 17379 2000-09-30
2000-09-30 gillingham a 0 - 2 9099 2000-10-08
2000-10-08 west bromwich albion h 1 - 2 15338 2000-10-14
2000-10-14 portsmouth a 1 - 2 13376 2000-10-17
2000-10-17 burnley a 0 - 1 16372 2000-10-22
2000-10-22 birmingham city h 1 - 0 14695 2000-10-25
2000-10-25 queens park rangers a 2 - 1 10353 2000-10-28
2000-10-28 fulham h 3 - 3 17559 2000-11-01
2000-11-01 norwich city h 3 - 2 16956 2000-11-04
2000-11-04 crystal palace a 1 - 4 15333 2000-11-07
2000-11-07 watford a 3 - 1 11166 2000-11-18
2000-11-18 barnsley a 0 - 1 19989 2000-11-25
2000-11-25 crewe alexandra a 0 - 1 7103 2000-12-02
2000-12-02 queens park rangers h 5 - 2 21782 2000-12-09
2000-12-09 stockport county h 2 - 4 16337 2000-12-16
2000-12-16 sheffield united a 1 - 1 25156 2000-12-23
2000-12-23 wolverhampton wanderers h 0 - 1 17787 2000-12-26
2000-12-26 bolton wanderers a 0 - 2 21316 2000-12-30
2000-12-30 huddersfield town a 0 - 0 18931 2001-01-01
2001-01-01 grimsby town h 1 - 0 17004 2001-01-13
2001-01-13 blackburn rovers a 0 - 2 19308 2001-01-20
2001-01-20 bolton wanderers h 0 - 3 17638 2001-02-03
2001-02-03 watford h 2 - 3 16134 2001-02-10
2001-02-10 wimbledon a 1 - 4 6741 2001-02-13
2001-02-13 tranmere rovers h 1 - 0 15444 2001-02-21
2001-02-21 nottingham forest a 1 - 0 23266 2001-02-24
2001-02-24 preston north end a 0 - 2 14379 2001-03-03
2001-03-03 gillingham h 2 - 1 18702 2001-03-07
2001-03-07 portsmouth h 0 - 0 20503 2001-03-10
2001-03-10 west bromwich albion a 2 - 1 18662 2001-03-17
2001-03-17 burnley h 2 - 0 20184 2001-03-24
2001-03-24 birmingham city a 2 - 1 19733 2001-04-01
2001-04-01 sheffield united h 1 - 2 38433 2001-04-07
2001-04-07 stockport county a 1 - 2 9666 2001-04-14
2001-04-14 crystal palace h 4 - 1 19877 2001-04-16
2001-04-16 fulham a 1 - 1 17500 2001-04-21
2001-04-21 barnsley h 2 - 1 23498 2001-04-28
2001-04-28 norwich city a 0 - 1 21241 2001-05-06
2001-05-06 crewe alexandra h 0 - 0 28007 None

Step 4: Select rows where 'time_diff' is equal to 2.

SQL command for the step:

SELECT * FROM table_sql WHERE time_diff = 2;
date opponent venue result attendance next_date time_diff
2000-08-13 wolverhampton wanderers a 1 - 1 19086 2000-08-19 6.0
2000-08-19 huddersfield town h 2 - 3 22704 2000-08-26 7.0
2000-08-26 grimsby town a 1 - 0 7755 2000-08-28 2.0
2000-08-28 blackburn rovers h 1 - 1 15646 2000-09-09 12.0
2000-09-09 wimbledon h 0 - 5 15856 2000-09-13 4.0
2000-09-13 nottingham forest h 0 - 1 15700 2000-09-16 3.0
2000-09-16 tranmere rovers a 0 - 2 9352 2000-09-23 7.0
2000-09-23 preston north end h 1 - 3 17379 2000-09-30 7.0
2000-09-30 gillingham a 0 - 2 9099 2000-10-08 8.0
2000-10-08 west bromwich albion h 1 - 2 15338 2000-10-14 6.0
2000-10-14 portsmouth a 1 - 2 13376 2000-10-17 3.0
2000-10-17 burnley a 0 - 1 16372 2000-10-22 5.0
2000-10-22 birmingham city h 1 - 0 14695 2000-10-25 3.0
2000-10-25 queens park rangers a 2 - 1 10353 2000-10-28 3.0
2000-10-28 fulham h 3 - 3 17559 2000-11-01 4.0
2000-11-01 norwich city h 3 - 2 16956 2000-11-04 3.0
2000-11-04 crystal palace a 1 - 4 15333 2000-11-07 3.0
2000-11-07 watford a 3 - 1 11166 2000-11-18 11.0
2000-11-18 barnsley a 0 - 1 19989 2000-11-25 7.0
2000-11-25 crewe alexandra a 0 - 1 7103 2000-12-02 7.0
2000-12-02 queens park rangers h 5 - 2 21782 2000-12-09 7.0
2000-12-09 stockport county h 2 - 4 16337 2000-12-16 7.0
2000-12-16 sheffield united a 1 - 1 25156 2000-12-23 7.0
2000-12-23 wolverhampton wanderers h 0 - 1 17787 2000-12-26 3.0
2000-12-26 bolton wanderers a 0 - 2 21316 2000-12-30 4.0
2000-12-30 huddersfield town a 0 - 0 18931 2001-01-01 2.0
2001-01-01 grimsby town h 1 - 0 17004 2001-01-13 12.0
2001-01-13 blackburn rovers a 0 - 2 19308 2001-01-20 7.0
2001-01-20 bolton wanderers h 0 - 3 17638 2001-02-03 14.0
2001-02-03 watford h 2 - 3 16134 2001-02-10 7.0
2001-02-10 wimbledon a 1 - 4 6741 2001-02-13 3.0
2001-02-13 tranmere rovers h 1 - 0 15444 2001-02-21 8.0
2001-02-21 nottingham forest a 1 - 0 23266 2001-02-24 3.0
2001-02-24 preston north end a 0 - 2 14379 2001-03-03 7.0
2001-03-03 gillingham h 2 - 1 18702 2001-03-07 4.0
2001-03-07 portsmouth h 0 - 0 20503 2001-03-10 3.0
2001-03-10 west bromwich albion a 2 - 1 18662 2001-03-17 7.0
2001-03-17 burnley h 2 - 0 20184 2001-03-24 7.0
2001-03-24 birmingham city a 2 - 1 19733 2001-04-01 8.0
2001-04-01 sheffield united h 1 - 2 38433 2001-04-07 6.0
2001-04-07 stockport county a 1 - 2 9666 2001-04-14 7.0
2001-04-14 crystal palace h 4 - 1 19877 2001-04-16 2.0
2001-04-16 fulham a 1 - 1 17500 2001-04-21 5.0
2001-04-21 barnsley h 2 - 1 23498 2001-04-28 7.0
2001-04-28 norwich city a 0 - 1 21241 2001-05-06 8.0
2001-05-06 crewe alexandra h 0 - 0 28007 None None

Step 5: Use a `CASE` statement to return TRUE if the number of rows is greater than or 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 opponent venue result attendance next_date time_diff
2000-08-26 grimsby town a 1 - 0 7755 2000-08-28 2.0
2000-12-30 huddersfield town a 0 - 0 18931 2001-01-01 2.0
2001-04-14 crystal palace h 4 - 1 19877 2001-04-16 2.0

Verification:

The statement is TRUE