Plan-of-SQLs Interface FN

Sample: 1814/1952
← Previous Next →

Task: Verify the Statement against the Table

Statement: the average win percentage of a season with a gb of 5 is 535

Table: list of nashville sounds seasons

year record win__ league division gb post___season_record post___season_win__ mlb_affiliate
1978 64 - 77 454 9th 4th 22½ - - cincinnati reds
1979 83 - 61 576 2nd 1st - 5 - 2 714 cincinnati reds
1980 ♦ 97 - 46 678 1st 1st - 1 - 3 250 new york yankees
1981 81 - 62 566 1st 1st - 4 - 3 571 new york yankees
1982 77 - 67 535 2nd 1st - 6 - 2 750 new york yankees
1983 ♦ 88 - 58 603 2nd 2nd 3½ 2 - 3 400 new york yankees
1984 ♦ 74 - 73 503 5th 1st - 1 - 3 250 new york yankees
1985 71 - 70 504 5th 2nd 2½ - - detroit tigers
1986 68 - 74 479 6th 3rd 12 - - detroit tigers
1987 64 - 76 457 7th 7th 15 - - cincinnati reds
1988 73 - 69 514 4th 2nd 16 - - cincinnati reds
1989 74 - 72 507 3rd (tie) 3rd 13 - - cincinnati reds
1990 86 - 61 585 2nd 1st - 2 - 3 400 cincinnati reds
1991 65 - 78 455 6th 3rd 16 - - cincinnati reds
1992 67 - 77 465 6th (tie) 4th 20 - - cincinnati reds
1993 81 - 62 566 2nd 1st - 3 - 4 429 chicago white sox
1994 ♦ 83 - 61 576 2nd 2nd 3½ 4 - 3 571 chicago white sox
1995 68 - 76 472 6th 6th 20 - - chicago white sox
1996 77 - 67 535 4th 3rd 7 - - chicago white sox
1997 74 - 69 517 3rd (tie) 3rd 12½ - - chicago white sox
1998 67 - 76 469 12th 4th 9½ - - pittsburgh pirates
1999 80 - 60 571 4th 2nd 2 - - pittsburgh pirates
2000 63 - 79 444 13th 4th 19 - - pittsburgh pirates
2001 64 - 77 454 13th 3rd 19 - - pittsburgh pirates
2002 72 - 71 503 8th (tie) 3rd 2½ - - pittsburgh pirates
2003 81 - 62 566 2nd 1st - 3 - 4 429 pittsburgh pirates
2004 63 - 79 444 14th 4th 17 - - pittsburgh pirates
2005 ¤ 75 - 69 521 6th 1st - 6 - 2 750 milwaukee brewers
2006 76 - 68 528 5th (tie) 1st (tie) - 2 - 3 400 milwaukee brewers
2007 89 - 55 618 1st 1st - 1 - 3 250 milwaukee brewers
2008 59 - 81 421 16th 4th 23 - - milwaukee brewers
2009 75 - 69 521 5th 2nd 2 - - milwaukee brewers
2010 77 - 67 535 5th 4th 5 - - milwaukee brewers
2011 71 - 73 493 6th (tie) 3rd 9 - - milwaukee brewers
2012 67 - 77 465 12th 2nd 16 - - milwaukee brewers
2013 57 - 87 396 16th 4th 13 - - milwaukee brewers
Generating plan to answer the query...

Generated steps

Step 1: Select rows where 'gb' is equal to 5.

Step 2: Extract the numerical win percentage from the 'record' column then add column 'win_percentage' to existing table.

Step 3: Select rows where 'win_percentage' is not null.

Step 4: Calculate the average of the 'win_percentage' column.

Step 5: Use a `CASE` statement to return TRUE if the average win percentage is equal to 535, otherwise return FALSE.

Step 1: Select rows where 'gb' is equal to 5.

SQL command for the step:

SELECT * FROM table_sql WHERE gb = 5;
year record win__ league division gb post___season_record post___season_win__ mlb_affiliate
1978 64 - 77 454 9th 4th 22½ - - cincinnati reds
1979 83 - 61 576 2nd 1st - 5 - 2 714 cincinnati reds
1980 ♦ 97 - 46 678 1st 1st - 1 - 3 250 new york yankees
1981 81 - 62 566 1st 1st - 4 - 3 571 new york yankees
1982 77 - 67 535 2nd 1st - 6 - 2 750 new york yankees
1983 ♦ 88 - 58 603 2nd 2nd 3½ 2 - 3 400 new york yankees
1984 ♦ 74 - 73 503 5th 1st - 1 - 3 250 new york yankees
1985 71 - 70 504 5th 2nd 2½ - - detroit tigers
1986 68 - 74 479 6th 3rd 12 - - detroit tigers
1987 64 - 76 457 7th 7th 15 - - cincinnati reds
1988 73 - 69 514 4th 2nd 16 - - cincinnati reds
1989 74 - 72 507 3rd (tie) 3rd 13 - - cincinnati reds
1990 86 - 61 585 2nd 1st - 2 - 3 400 cincinnati reds
1991 65 - 78 455 6th 3rd 16 - - cincinnati reds
1992 67 - 77 465 6th (tie) 4th 20 - - cincinnati reds
1993 81 - 62 566 2nd 1st - 3 - 4 429 chicago white sox
1994 ♦ 83 - 61 576 2nd 2nd 3½ 4 - 3 571 chicago white sox
1995 68 - 76 472 6th 6th 20 - - chicago white sox
1996 77 - 67 535 4th 3rd 7 - - chicago white sox
1997 74 - 69 517 3rd (tie) 3rd 12½ - - chicago white sox
1998 67 - 76 469 12th 4th 9½ - - pittsburgh pirates
1999 80 - 60 571 4th 2nd 2 - - pittsburgh pirates
2000 63 - 79 444 13th 4th 19 - - pittsburgh pirates
2001 64 - 77 454 13th 3rd 19 - - pittsburgh pirates
2002 72 - 71 503 8th (tie) 3rd 2½ - - pittsburgh pirates
2003 81 - 62 566 2nd 1st - 3 - 4 429 pittsburgh pirates
2004 63 - 79 444 14th 4th 17 - - pittsburgh pirates
2005 ¤ 75 - 69 521 6th 1st - 6 - 2 750 milwaukee brewers
2006 76 - 68 528 5th (tie) 1st (tie) - 2 - 3 400 milwaukee brewers
2007 89 - 55 618 1st 1st - 1 - 3 250 milwaukee brewers
2008 59 - 81 421 16th 4th 23 - - milwaukee brewers
2009 75 - 69 521 5th 2nd 2 - - milwaukee brewers
2010 77 - 67 535 5th 4th 5 - - milwaukee brewers
2011 71 - 73 493 6th (tie) 3rd 9 - - milwaukee brewers
2012 67 - 77 465 12th 2nd 16 - - milwaukee brewers
2013 57 - 87 396 16th 4th 13 - - milwaukee brewers

Step 2: Extract the numerical win percentage from the 'record' column then add column 'win_percentage' to existing table.

SQL command for the step:

ALTER TABLE table_sql ADD COLUMN win_percentage FLOAT; UPDATE table_sql SET win_percentage = CAST(SUBSTRING_INDEX(record, ' ', 1) AS UNSIGNED) / CAST(SUBSTRING_INDEX(record, ' ', -1) AS UNSIGNED);
year record win__ league division gb post___season_record post___season_win__ mlb_affiliate
2010 77 - 67 535 5th 4th 5 - - milwaukee brewers

Step 3: Select rows where 'win_percentage' is not null.

SQL command for the step:

SELECT * FROM table_sql WHERE win_percentage IS NOT NULL;
year record win__ league division gb post___season_record post___season_win__ mlb_affiliate win_percentage
2010 77 - 67 535 5th 4th 5 - - milwaukee brewers 0

Step 4: Calculate the average of the 'win_percentage' column.

SQL command for the step:

SELECT AVG(win_percentage) AS average_win_percentage FROM table_sql;
year record win__ league division gb post___season_record post___season_win__ mlb_affiliate win_percentage
2010 77 - 67 535 5th 4th 5 - - milwaukee brewers 0

Step 5: Use a `CASE` statement to return TRUE if the average win percentage is equal to 535, otherwise return FALSE.

SQL command for the step:

SELECT CASE WHEN AVG(win_percentage) = 535 THEN TRUE ELSE FALSE END AS verification FROM table_sql;
average_win_percentage
0.0

Verification:

The statement is FALSE