Plan-of-SQLs Interface TP

Sample: 46/1952
← Previous Next →

Task: Verify the Statement against the Table

Statement: the party with 21 total members has 18 more members than the party with the fewest total members

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 'party' is not 'total'.

Step 2: Order the table by 'total' in ascending order.

Step 3: Select row number 1.

Step 4: Select rows where 'total' is equal to the 'total' value from step 3.

Step 5: Select rows where 'total' is equal to the 'total' value from step 3 minus 18.

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

Step 1: Select rows where 'party' is not 'total'.

SQL command for the step:

SELECT * FROM table_sql WHERE party <> '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: Order the table by 'total' in ascending order.

SQL command for the step:

SELECT * FROM table_sql ORDER BY total ASC;
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

Step 3: Select row number 1.

SQL command for the step:

SELECT * FROM table_sql LIMIT 1;
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
clann na talmhan 0 2 0 0 1 0 0 0 3
labour party 0 0 0 1 5 0 0 2 8
fine gael 1 3 2 2 0 1 0 0 9
independent 0 1 0 1 0 1 3 3 9
fianna fáil 4 4 2 3 0 1 0 7 21

Step 4: Select rows where 'total' is equal to the 'total' value from step 3.

SQL command for the step:

SELECT * FROM table_sql WHERE total = (SELECT total FROM table_sql WHERE <condition_from_step_3>);
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
clann na talmhan 0 2 0 0 1 0 0 0 3

Step 5: Select rows where 'total' is equal to the 'total' value from step 3 minus 18.

SQL command for the step:

SELECT * FROM table_sql WHERE total = (SELECT total FROM table_sql WHERE <condition_from_step_3>) - 18;
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
clann na talmhan 0 2 0 0 1 0 0 0 3

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

SQL command for the step:

SELECT CASE WHEN (SELECT COUNT(*) FROM table_sql WHERE <condition_for_step_4>) = (SELECT COUNT(*) FROM table_sql WHERE <condition_for_step_5>) THEN TRUE ELSE FALSE END AS verification;
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

Verification:

The statement is TRUE