Plan-of-SQLs Interface TP

Sample: 184/1952
← Previous Next →

Task: Verify the Statement against the Table

Statement: there were 4 test matches in the last 2 months of 1978

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: Select rows where the 'status' is 'test match'.

Step 2: Extract the month and year from the 'date' column then add columns 'month' and 'year' to existing table.

Step 3: Select rows where the 'year' is 1978 and the 'month' is either November or December.

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

Step 1: Select rows where the 'status' is 'test match'.

SQL command for the step:

SELECT * FROM table_sql WHERE status = 'test match';
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: Extract the month and year from the 'date' column then add columns 'month' and 'year' to existing table.

SQL command for the step:

ALTER TABLE table_sql ADD COLUMN month INT, ADD COLUMN year INT; UPDATE table_sql SET month = EXTRACT(MONTH FROM date), year = EXTRACT(YEAR FROM date); SET verification = TRUE;
opposing_team against date venue status
ireland 6 1978-11-04 lansdowne road , dublin test match
wales 12 1978-11-01 cardiff arms park , cardiff test match
england 6 1978-11-25 twickenham , london test match
scotland 9 1978-12-09 murrayfield , edinburgh test match

Step 3: Select rows where the 'year' is 1978 and the 'month' is either November or December.

SQL command for the step:

SELECT * FROM table_sql WHERE year = 1978 AND month IN ('November', 'December');
opposing_team against date venue status month year
ireland 6 1978-11-04 lansdowne road , dublin test match 11 1978
wales 12 1978-11-01 cardiff arms park , cardiff test match 11 1978
england 6 1978-11-25 twickenham , london test match 11 1978
scotland 9 1978-12-09 murrayfield , edinburgh test match 12 1978

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

SQL command for the step:

SELECT CASE WHEN COUNT(*) = 4 THEN TRUE ELSE FALSE END AS verification FROM table_sql;
opposing_team against date venue status month year
ireland 6 1978-11-04 lansdowne road , dublin test match 11 1978
wales 12 1978-11-01 cardiff arms park , cardiff test match 11 1978
england 6 1978-11-25 twickenham , london test match 11 1978
scotland 9 1978-12-09 murrayfield , edinburgh test match 12 1978

Verification:

The statement is TRUE