Plan-of-SQLs Interface TN

Sample: 1951/1952
← Previous Next →

Task: Verify the Statement against the Table

Statement: the highest number of ties in a season by the hurricanes was in 2003

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: Order the table by the 'ties' column in descending order.

Step 2: Select row number 1.

Step 3: Select rows where the 'season' is 2003.

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

Step 1: Order the table by the 'ties' column in descending order.

SQL command for the step:

SELECT * FROM table_sql ORDER BY ties DESC;
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 row number 1.

SQL command for the step:

SELECT * FROM table_sql LIMIT 1;
season division wins losses ties final_position notes
2008 bafl division 1 north 5 2 3 3 / 6 -
2004 bafl division 2 north 6 2 1 1 / 3 -
2009 bafl division 1 north 2 6 1 5 / 7 -
2003 bafl division 2 north 6 4 0 4 / 11 -
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

Step 3: Select rows where the 'season' is 2003.

SQL command for the step:

SELECT * FROM table_sql WHERE season = 2003;
season division wins losses ties final_position notes
2008 bafl division 1 north 5 2 3 3 / 6 -

Step 4: 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;
season division wins losses ties final_position notes

Verification:

The statement is FALSE