Plan-of-SQLs Interface TN

Sample: 1949/1952
← Previous Next →

Task: Verify the Statement against the Table

Statement: the hurricanes only won 50% of their games in 2007 and did not get promoted to a higher division

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 the 'season' is 2007.

Step 2: Calculate the total number of games played by adding the 'wins', 'losses', and 'ties' columns, then add a column 'total_games' to the existing table.

Step 3: Calculate the number of games won by dividing the 'wins' column by the 'total_games' column, then add a column 'win_percentage' to the existing table.

Step 4: Select rows where the 'win_percentage' is equal to 0.5.

Step 5: Select rows where the 'final_position' is '1 / 6'.

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

Step 1: Select rows where the 'season' is 2007.

SQL command for the step:

SELECT * FROM table_sql WHERE season = 2007;
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: Calculate the total number of games played by adding the 'wins', 'losses', and 'ties' columns, then add a column 'total_games' to the existing table.

SQL command for the step:

ALTER TABLE table_sql ADD total_games INT GENERATED ALWAYS AS (wins + losses + ties) STORED;
season division wins losses ties final_position notes
2007 bafl division 2 north 9 1 0 1 / 6 promoted to division 1 north

Step 3: Calculate the number of games won by dividing the 'wins' column by the 'total_games' column, then add a column 'win_percentage' to the existing table.

SQL command for the step:

ALTER TABLE table_sql ADD COLUMN win_percentage FLOAT AS (wins / total_games) STORED;
season division wins losses ties final_position notes total_games
2007 bafl division 2 north 9 1 0 1 / 6 promoted to division 1 north 10

Step 4: Select rows where the 'win_percentage' is equal to 0.5.

SQL command for the step:

SELECT * FROM table_sql WHERE win_percentage = 0.5;
season division wins losses ties final_position notes total_games win_percentage
2007 bafl division 2 north 9 1 0 1 / 6 promoted to division 1 north 10 0.9

Step 5: Select rows where the 'final_position' is '1 / 6'.

SQL command for the step:

SELECT * FROM table_sql WHERE final_position = '1 / 6';
season division wins losses ties final_position notes total_games win_percentage

Step 6: 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 total_games win_percentage

Verification:

The statement is FALSE