Plan-of-SQLs Interface TP

Sample: 53/1952
← Previous Next →

Task: Verify the Statement against the Table

Statement: administrative panel has four more members than national university of ireland

Table: members of the 5th seanad

party administrative_panel agricultural_panel cultural_and_educational_panel industrial_and_commercial_panel labour_panel national_university_of_ireland university_of_dublin nominated_by_the_taoiseach total
fianna fáil 4 4 2 3 0 1 0 7 21
fine gael 1 3 2 2 0 1 0 0 9
labour party 0 0 0 1 5 0 0 2 8
clann na talmhan 0 2 0 0 1 0 0 0 3
independent 0 1 0 1 0 1 3 3 9
total 7 11 5 9 11 3 3 11 60
Generating plan to answer the query...

Generated steps

Step 1: Select rows where 'administrative_panel' is not equal to 'total'.

Step 2: Select rows where 'national_university_of_ireland' is not equal to 'total'.

Step 3: Subtract the value of 'national_university_of_ireland' from 'administrative_panel' and add column 'difference' to the existing table.

Step 4: Select rows where 'difference' is equal to 4.

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

Step 1: Select rows where 'administrative_panel' is not equal to 'total'.

SQL command for the step:

SELECT * FROM table_sql WHERE administrative_panel <> 'total';
party administrative_panel agricultural_panel cultural_and_educational_panel industrial_and_commercial_panel labour_panel national_university_of_ireland university_of_dublin nominated_by_the_taoiseach total
fianna fáil 4 4 2 3 0 1 0 7 21
fine gael 1 3 2 2 0 1 0 0 9
labour party 0 0 0 1 5 0 0 2 8
clann na talmhan 0 2 0 0 1 0 0 0 3
independent 0 1 0 1 0 1 3 3 9
total 7 11 5 9 11 3 3 11 60

Step 2: Select rows where 'national_university_of_ireland' is not equal to 'total'.

SQL command for the step:

SELECT * FROM table_sql WHERE national_university_of_ireland <> 'total';
party administrative_panel agricultural_panel cultural_and_educational_panel industrial_and_commercial_panel labour_panel national_university_of_ireland university_of_dublin nominated_by_the_taoiseach total
fianna fáil 4 4 2 3 0 1 0 7 21
fine gael 1 3 2 2 0 1 0 0 9
labour party 0 0 0 1 5 0 0 2 8
clann na talmhan 0 2 0 0 1 0 0 0 3
independent 0 1 0 1 0 1 3 3 9
total 7 11 5 9 11 3 3 11 60

Step 3: Subtract the value of 'national_university_of_ireland' from 'administrative_panel' and add column 'difference' to the existing table.

SQL command for the step:

ALTER TABLE table_sql ADD COLUMN difference FLOAT;
UPDATE table_sql SET difference = administrative_panel - national_university_of_ireland;
party administrative_panel agricultural_panel cultural_and_educational_panel industrial_and_commercial_panel labour_panel national_university_of_ireland university_of_dublin nominated_by_the_taoiseach total
fianna fáil 4 4 2 3 0 1 0 7 21
fine gael 1 3 2 2 0 1 0 0 9
labour party 0 0 0 1 5 0 0 2 8
clann na talmhan 0 2 0 0 1 0 0 0 3
independent 0 1 0 1 0 1 3 3 9
total 7 11 5 9 11 3 3 11 60

Step 4: Select rows where 'difference' is equal to 4.

SQL command for the step:

SELECT * FROM table_sql WHERE difference = 4;
party administrative_panel agricultural_panel cultural_and_educational_panel industrial_and_commercial_panel labour_panel national_university_of_ireland university_of_dublin nominated_by_the_taoiseach total difference
fianna fáil 4 4 2 3 0 1 0 7 21 3
fine gael 1 3 2 2 0 1 0 0 9 0
labour party 0 0 0 1 5 0 0 2 8 0
clann na talmhan 0 2 0 0 1 0 0 0 3 0
independent 0 1 0 1 0 1 3 3 9 -1
total 7 11 5 9 11 3 3 11 60 4

Step 5: Use a `CASE` statement to return TRUE if the number of rows is 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;
party administrative_panel agricultural_panel cultural_and_educational_panel industrial_and_commercial_panel labour_panel national_university_of_ireland university_of_dublin nominated_by_the_taoiseach total difference
total 7 11 5 9 11 3 3 11 60 4

Verification:

The statement is TRUE