Plan-of-SQLs Interface TN

Sample: 185/1952
← Previous Next →

Task: Verify the Statement against the Table

Statement: there were four teams that scored exactly 12 against cambridge university

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 'opposing_team' is 'cambridge university'.

Step 2: Select rows where 'against' is 12.

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

Step 1: Select rows where 'opposing_team' is 'cambridge university'.

SQL command for the step:

SELECT * FROM table_sql WHERE opposing_team = 'cambridge university';
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 'against' is 12.

SQL command for the step:

SELECT * FROM table_sql WHERE against = 12;
opposing_team against date venue status
cambridge university 12 1978-10-18 grange road , cambridge tour match

Step 3: 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
cambridge university 12 1978-10-18 grange road , cambridge tour match

Verification:

The statement is FALSE