Plan-of-SQLs Interface TP

Sample: 1944/1952
← Previous Next →

Task: Verify the Statement against the Table

Statement: the hurricanes won 90% of their games in 2007 which got them 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 'season' is 2007.

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

Step 3: Calculate the percentage of games won by dividing the 'wins' column by the 'total_games' column, then multiply by 100 and add column 'win_percentage' to the existing table.

Step 4: Select rows where 'win_percentage' is equal to or greater than 90.

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

Step 1: Select rows where '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 summing the 'wins', 'losses', and 'ties' columns, then add column 'total_games' to the existing table.

SQL command for the step:

ALTER TABLE table_sql ADD total_games INT; UPDATE table_sql SET total_games = wins + losses + ties;
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 percentage of games won by dividing the 'wins' column by the 'total_games' column, then multiply by 100 and add column 'win_percentage' to the existing table.

SQL command for the step:

ALTER TABLE table_sql ADD COLUMN win_percentage DECIMAL(5,2);
UPDATE table_sql SET win_percentage = (wins / total_games) * 100;
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 'win_percentage' is equal to or greater than 90.

SQL command for the step:

SELECT * FROM table_sql WHERE win_percentage >= 90;
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 90.0

Step 5: Use a `CASE` statement to return TRUE if the number of rows is greater than or 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
2007 bafl division 2 north 9 1 0 1 / 6 promoted to division 1 north 10 90.0

Verification:

The statement is TRUE