Plan-of-SQLs Interface TN

Sample: 191/1952
← Previous Next →

Task: Verify the Statement against the Table

Statement: all 5 matches held in october of 1978 took place after the 25th of the month

Table: 1978 new zealand rugby union tour of britain and ireland

opposing_team against date venue status
cambridge university 12 1978-10-18 grange road , cambridge tour match
cardiff 7 1978-10-21 cardiff arms park , cardiff tour match
west wales xv 7 1978-10-25 st helen 's , swansea tour match
london counties 12 1978-10-28 twickenham , london tour match
munster 12 1978-10-31 thomond park , limerick tour match
ireland 6 1978-11-04 lansdowne road , dublin test match
ulster 7 1978-11-07 ravenhill , belfast tour match
wales 12 1978-11-01 cardiff arms park , cardiff test match
south and south - west counties 0 1978-11-15 memorial ground , bristol tour match
midland counties 15 1978-11-18 welford road , leicester tour match
combined services 6 1978-11-21 aldershot military stadium , aldershot tour match
england 6 1978-11-25 twickenham , london test match
monmouthshire 9 1978-11-29 rodney parade , newport tour match
north of england 6 1978-12-02 birkenhead park , birkenhead tour match
north and midland of scotland 3 1978-12-05 linksfield stadium , aberdeen tour match
scotland 9 1978-12-09 murrayfield , edinburgh test match
bridgend 6 1978-12-13 brewery field , bridgend tour match
barbarians 16 1978-12-16 cardiff arms park , cardiff tour match
Generating plan to answer the query...

Generated steps

Step 1: Extract the numerical day from the 'date' column then add column 'day' to existing table.

Step 2: Select rows where the 'day' is less than or equal to 25.

Step 3: Select rows where the 'date' is in October.

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

Step 1: Extract the numerical day from the 'date' column then add column 'day' to existing table.

SQL command for the step:

ALTER TABLE table_sql ADD COLUMN day INT;
UPDATE table_sql SET day = EXTRACT(DAY FROM date);
opposing_team against date venue status
cambridge university 12 1978-10-18 grange road , cambridge tour match
cardiff 7 1978-10-21 cardiff arms park , cardiff tour match
west wales xv 7 1978-10-25 st helen 's , swansea tour match
london counties 12 1978-10-28 twickenham , london tour match
munster 12 1978-10-31 thomond park , limerick tour match
ireland 6 1978-11-04 lansdowne road , dublin test match
ulster 7 1978-11-07 ravenhill , belfast tour match
wales 12 1978-11-01 cardiff arms park , cardiff test match
south and south - west counties 0 1978-11-15 memorial ground , bristol tour match
midland counties 15 1978-11-18 welford road , leicester tour match
combined services 6 1978-11-21 aldershot military stadium , aldershot tour match
england 6 1978-11-25 twickenham , london test match
monmouthshire 9 1978-11-29 rodney parade , newport tour match
north of england 6 1978-12-02 birkenhead park , birkenhead tour match
north and midland of scotland 3 1978-12-05 linksfield stadium , aberdeen tour match
scotland 9 1978-12-09 murrayfield , edinburgh test match
bridgend 6 1978-12-13 brewery field , bridgend tour match
barbarians 16 1978-12-16 cardiff arms park , cardiff tour match

Step 2: Select rows where the 'day' is less than or equal to 25.

SQL command for the step:

SELECT * FROM table_sql WHERE day <= 25;
opposing_team against date venue status day
cambridge university 12 1978-10-18 grange road , cambridge tour match 18
cardiff 7 1978-10-21 cardiff arms park , cardiff tour match 21
west wales xv 7 1978-10-25 st helen 's , swansea tour match 25
london counties 12 1978-10-28 twickenham , london tour match 28
munster 12 1978-10-31 thomond park , limerick tour match 31
ireland 6 1978-11-04 lansdowne road , dublin test match 4
ulster 7 1978-11-07 ravenhill , belfast tour match 7
wales 12 1978-11-01 cardiff arms park , cardiff test match 1
south and south - west counties 0 1978-11-15 memorial ground , bristol tour match 15
midland counties 15 1978-11-18 welford road , leicester tour match 18
combined services 6 1978-11-21 aldershot military stadium , aldershot tour match 21
england 6 1978-11-25 twickenham , london test match 25
monmouthshire 9 1978-11-29 rodney parade , newport tour match 29
north of england 6 1978-12-02 birkenhead park , birkenhead tour match 2
north and midland of scotland 3 1978-12-05 linksfield stadium , aberdeen tour match 5
scotland 9 1978-12-09 murrayfield , edinburgh test match 9
bridgend 6 1978-12-13 brewery field , bridgend tour match 13
barbarians 16 1978-12-16 cardiff arms park , cardiff tour match 16

Step 3: Select rows where the 'date' is in October.

SQL command for the step:

SELECT * FROM table_sql WHERE MONTH(date) = 10;
opposing_team against date venue status day
cambridge university 12 1978-10-18 grange road , cambridge tour match 18
cardiff 7 1978-10-21 cardiff arms park , cardiff tour match 21
west wales xv 7 1978-10-25 st helen 's , swansea tour match 25
ireland 6 1978-11-04 lansdowne road , dublin test match 4
ulster 7 1978-11-07 ravenhill , belfast tour match 7
wales 12 1978-11-01 cardiff arms park , cardiff test match 1
south and south - west counties 0 1978-11-15 memorial ground , bristol tour match 15
midland counties 15 1978-11-18 welford road , leicester tour match 18
combined services 6 1978-11-21 aldershot military stadium , aldershot tour match 21
england 6 1978-11-25 twickenham , london test match 25
north of england 6 1978-12-02 birkenhead park , birkenhead tour match 2
north and midland of scotland 3 1978-12-05 linksfield stadium , aberdeen tour match 5
scotland 9 1978-12-09 murrayfield , edinburgh test match 9
bridgend 6 1978-12-13 brewery field , bridgend tour match 13
barbarians 16 1978-12-16 cardiff arms park , cardiff tour match 16

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

SQL command for the step:

SELECT CASE WHEN COUNT(*) = 5 THEN TRUE ELSE FALSE END AS verification FROM table_sql;
opposing_team against date venue status day
cambridge university 12 1978-10-18 grange road , cambridge tour match 18
cardiff 7 1978-10-21 cardiff arms park , cardiff tour match 21
west wales xv 7 1978-10-25 st helen 's , swansea tour match 25

Verification:

The statement is FALSE