Plan-of-SQLs Interface FP

Sample: 1950/1952
← Previous Next →

Task: Verify the Statement against the Table

Statement: the dundee hurricanes had four losing seasons , 2003 , 2005 , 2007 , 2008

Table: dundee hurricanes

season division wins losses ties final_position notes
2003 bafl division 2 north 6 4 0 4 / 11 -
2004 bafl division 2 north 6 2 1 1 / 3 -
2005 bafl division 2 scottish 5 5 0 2 / 4 -
2006 bafl division 2 scottish 3 4 0 2 / 3 -
2007 bafl division 2 north 9 1 0 1 / 6 promoted to division 1 north
2008 bafl division 1 north 5 2 3 3 / 6 -
2009 bafl division 1 north 2 6 1 5 / 7 -
Generating plan to answer the query...

Generated steps

Step 1: Select rows where 'losses' is greater than 0.

Step 2: Select rows where 'season' is 2003, 2005, 2007, or 2008.

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 'losses' is greater than 0.

SQL command for the step:

SELECT * FROM table_sql WHERE losses > 0;
season division wins losses ties final_position notes
2003 bafl division 2 north 6 4 0 4 / 11 -
2004 bafl division 2 north 6 2 1 1 / 3 -
2005 bafl division 2 scottish 5 5 0 2 / 4 -
2006 bafl division 2 scottish 3 4 0 2 / 3 -
2007 bafl division 2 north 9 1 0 1 / 6 promoted to division 1 north
2008 bafl division 1 north 5 2 3 3 / 6 -
2009 bafl division 1 north 2 6 1 5 / 7 -

Step 2: Select rows where 'season' is 2003, 2005, 2007, or 2008.

SQL command for the step:

SELECT * FROM table_sql WHERE season IN (2003, 2005, 2007, 2008);
season division wins losses ties final_position notes
2003 bafl division 2 north 6 4 0 4 / 11 -
2004 bafl division 2 north 6 2 1 1 / 3 -
2005 bafl division 2 scottish 5 5 0 2 / 4 -
2006 bafl division 2 scottish 3 4 0 2 / 3 -
2007 bafl division 2 north 9 1 0 1 / 6 promoted to division 1 north
2008 bafl division 1 north 5 2 3 3 / 6 -
2009 bafl division 1 north 2 6 1 5 / 7 -

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;
season division wins losses ties final_position notes
2003 bafl division 2 north 6 4 0 4 / 11 -
2005 bafl division 2 scottish 5 5 0 2 / 4 -
2007 bafl division 2 north 9 1 0 1 / 6 promoted to division 1 north
2008 bafl division 1 north 5 2 3 3 / 6 -

Verification:

The statement is TRUE