Plan-of-SQLs Interface TP

Sample: 82/1952
← Previous Next →

Task: Verify the Statement against the Table

Statement: there have been 19 games since 13 august 2005 which had an attendance of 60000 or more

Table: 2005 - 06 manchester united f.c. season

date opponents h___a result_f___a attendance league_position
2005-08-13 everton a 2 - 0 38610 4th
2005-08-20 aston villa h 1 - 0 67934 4th
2005-08-28 newcastle united a 2 - 0 52327 4th
2005-09-10 manchester city h 1 - 1 67839 4th
2005-09-18 liverpool a 0 - 0 44917 3rd
2005-09-24 blackburn rovers h 1 - 2 67765 6th
2005-10-01 fulham a 3 - 2 21862 4th
2005-10-15 sunderland a 3 - 1 39085 3rd
2005-10-22 tottenham hotspur h 1 - 1 67856 5th
2005-10-29 middlesbrough a 1 - 4 30579 7th
2005-11-06 chelsea h 1 - 0 67864 4th
2005-11-19 charlton athletic a 3 - 1 26730 3rd
2005-11-27 west ham united a 2 - 1 34755 2nd
2005-12-03 portsmouth h 3 - 0 67684 2nd
2005-12-11 everton h 1 - 1 67831 3rd
2005-12-14 wigan athletic h 4 - 0 67793 2nd
2005-12-17 aston villa a 2 - 0 37128 2nd
2005-12-26 west bromwich albion h 3 - 0 67972 2nd
2005-12-28 birmingham city a 2 - 2 28459 2nd
2005-12-31 bolton wanderers h 4 - 1 67858 2nd
2006-01-03 arsenal a 0 - 0 38313 2nd
2006-01-14 manchester city a 1 - 3 47192 2nd
2006-01-22 liverpool h 1 - 0 67874 2nd
2006-02-01 blackburn rovers a 3 - 4 25484 2nd
2006-02-04 fulham h 4 - 2 67844 2nd
2006-02-11 portsmouth a 3 - 1 20206 2nd
2006-03-06 wigan athletic a 2 - 1 23524 2nd
2006-03-12 newcastle united h 2 - 0 67858 2nd
2006-03-18 west bromwich albion a 2 - 1 27623 2nd
2006-03-26 birmingham city h 3 - 0 69070 2nd
2006-03-29 west ham united h 1 - 0 69522 2nd
2006-04-01 bolton wanderers a 2 - 1 27718 2nd
2006-04-09 arsenal h 2 - 0 70908 2nd
2006-04-14 sunderland h 0 - 0 72519 2nd
2006-04-17 tottenham hotspur a 2 - 1 36141 2nd
2006-04-29 chelsea a 0 - 3 42219 2nd
2006-05-01 middlesbrough h 0 - 0 69531 2nd
2006-05-07 charlton athletic h 4 - 0 73006 2nd
Generating plan to answer the query...

Generated steps

Step 1: Select rows where the 'attendance' is greater than or equal to 60000.

Step 2: Select rows where the 'date' is after or equal to '2005-08-13'.

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

Step 1: Select rows where the 'attendance' is greater than or equal to 60000.

SQL command for the step:

SELECT * FROM table_sql WHERE attendance >= 60000;
date opponents h___a result_f___a attendance league_position
2005-08-13 everton a 2 - 0 38610 4th
2005-08-20 aston villa h 1 - 0 67934 4th
2005-08-28 newcastle united a 2 - 0 52327 4th
2005-09-10 manchester city h 1 - 1 67839 4th
2005-09-18 liverpool a 0 - 0 44917 3rd
2005-09-24 blackburn rovers h 1 - 2 67765 6th
2005-10-01 fulham a 3 - 2 21862 4th
2005-10-15 sunderland a 3 - 1 39085 3rd
2005-10-22 tottenham hotspur h 1 - 1 67856 5th
2005-10-29 middlesbrough a 1 - 4 30579 7th
2005-11-06 chelsea h 1 - 0 67864 4th
2005-11-19 charlton athletic a 3 - 1 26730 3rd
2005-11-27 west ham united a 2 - 1 34755 2nd
2005-12-03 portsmouth h 3 - 0 67684 2nd
2005-12-11 everton h 1 - 1 67831 3rd
2005-12-14 wigan athletic h 4 - 0 67793 2nd
2005-12-17 aston villa a 2 - 0 37128 2nd
2005-12-26 west bromwich albion h 3 - 0 67972 2nd
2005-12-28 birmingham city a 2 - 2 28459 2nd
2005-12-31 bolton wanderers h 4 - 1 67858 2nd
2006-01-03 arsenal a 0 - 0 38313 2nd
2006-01-14 manchester city a 1 - 3 47192 2nd
2006-01-22 liverpool h 1 - 0 67874 2nd
2006-02-01 blackburn rovers a 3 - 4 25484 2nd
2006-02-04 fulham h 4 - 2 67844 2nd
2006-02-11 portsmouth a 3 - 1 20206 2nd
2006-03-06 wigan athletic a 2 - 1 23524 2nd
2006-03-12 newcastle united h 2 - 0 67858 2nd
2006-03-18 west bromwich albion a 2 - 1 27623 2nd
2006-03-26 birmingham city h 3 - 0 69070 2nd
2006-03-29 west ham united h 1 - 0 69522 2nd
2006-04-01 bolton wanderers a 2 - 1 27718 2nd
2006-04-09 arsenal h 2 - 0 70908 2nd
2006-04-14 sunderland h 0 - 0 72519 2nd
2006-04-17 tottenham hotspur a 2 - 1 36141 2nd
2006-04-29 chelsea a 0 - 3 42219 2nd
2006-05-01 middlesbrough h 0 - 0 69531 2nd
2006-05-07 charlton athletic h 4 - 0 73006 2nd

Step 2: Select rows where the 'date' is after or equal to '2005-08-13'.

SQL command for the step:

SELECT * FROM table_sql WHERE date >= '2005-08-13';
date opponents h___a result_f___a attendance league_position
2005-08-20 aston villa h 1 - 0 67934 4th
2005-09-10 manchester city h 1 - 1 67839 4th
2005-09-24 blackburn rovers h 1 - 2 67765 6th
2005-10-22 tottenham hotspur h 1 - 1 67856 5th
2005-11-06 chelsea h 1 - 0 67864 4th
2005-12-03 portsmouth h 3 - 0 67684 2nd
2005-12-11 everton h 1 - 1 67831 3rd
2005-12-14 wigan athletic h 4 - 0 67793 2nd
2005-12-26 west bromwich albion h 3 - 0 67972 2nd
2005-12-31 bolton wanderers h 4 - 1 67858 2nd
2006-01-22 liverpool h 1 - 0 67874 2nd
2006-02-04 fulham h 4 - 2 67844 2nd
2006-03-12 newcastle united h 2 - 0 67858 2nd
2006-03-26 birmingham city h 3 - 0 69070 2nd
2006-03-29 west ham united h 1 - 0 69522 2nd
2006-04-09 arsenal h 2 - 0 70908 2nd
2006-04-14 sunderland h 0 - 0 72519 2nd
2006-05-01 middlesbrough h 0 - 0 69531 2nd
2006-05-07 charlton athletic h 4 - 0 73006 2nd

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

SQL command for the step:

SELECT CASE WHEN COUNT(*) = 19 THEN TRUE ELSE FALSE END AS verification FROM table_sql;
date opponents h___a result_f___a attendance league_position
2005-08-20 aston villa h 1 - 0 67934 4th
2005-09-10 manchester city h 1 - 1 67839 4th
2005-09-24 blackburn rovers h 1 - 2 67765 6th
2005-10-22 tottenham hotspur h 1 - 1 67856 5th
2005-11-06 chelsea h 1 - 0 67864 4th
2005-12-03 portsmouth h 3 - 0 67684 2nd
2005-12-11 everton h 1 - 1 67831 3rd
2005-12-14 wigan athletic h 4 - 0 67793 2nd
2005-12-26 west bromwich albion h 3 - 0 67972 2nd
2005-12-31 bolton wanderers h 4 - 1 67858 2nd
2006-01-22 liverpool h 1 - 0 67874 2nd
2006-02-04 fulham h 4 - 2 67844 2nd
2006-03-12 newcastle united h 2 - 0 67858 2nd
2006-03-26 birmingham city h 3 - 0 69070 2nd
2006-03-29 west ham united h 1 - 0 69522 2nd
2006-04-09 arsenal h 2 - 0 70908 2nd
2006-04-14 sunderland h 0 - 0 72519 2nd
2006-05-01 middlesbrough h 0 - 0 69531 2nd
2006-05-07 charlton athletic h 4 - 0 73006 2nd

Verification:

The statement is TRUE