Plan-of-SQLs Interface TN

Sample: 1817/1952
← Previous Next →

Task: Verify the Statement against the Table

Statement: in 2011 , 0.535 is the average win percentage of a season with a gb of 5

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: Extract the numerical win percentage from the 'record' column then add column 'win_percentage' to existing table.

Step 2: Extract the numerical gb from the 'gb' column then add column 'gb_num' to existing table.

Step 3: Select rows where 'year' is 2011.

Step 4: Select rows where 'gb_num' is 5.

Step 5: Calculate the average win percentage of the selected rows.

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

Step 1: 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
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 gb from the 'gb' column then add column 'gb_num' to existing table.

SQL command for the step:

ALTER TABLE table_sql ADD COLUMN gb_num NUMERIC; UPDATE table_sql SET gb_num = CAST(SUBSTRING(gb FROM '([0-9]+)') AS NUMERIC);
year record win__ league division gb post___season_record post___season_win__ mlb_affiliate win_percentage
1978 64 - 77 454 9th 4th 22½ - - cincinnati reds 1.203125
1979 83 - 61 576 2nd 1st - 5 - 2 714 cincinnati reds 0.7349397590361446
1980 ♦ 97 - 46 678 1st 1st - 1 - 3 250 new york yankees 0.4742268041237113
1981 81 - 62 566 1st 1st - 4 - 3 571 new york yankees 0.7654320987654321
1982 77 - 67 535 2nd 1st - 6 - 2 750 new york yankees 0.8701298701298701
1983 ♦ 88 - 58 603 2nd 2nd 3½ 2 - 3 400 new york yankees 0.6590909090909091
1984 ♦ 74 - 73 503 5th 1st - 1 - 3 250 new york yankees 0.9864864864864865
1985 71 - 70 504 5th 2nd 2½ - - detroit tigers 0.9859154929577465
1986 68 - 74 479 6th 3rd 12 - - detroit tigers 1.088235294117647
1987 64 - 76 457 7th 7th 15 - - cincinnati reds 1.1875
1988 73 - 69 514 4th 2nd 16 - - cincinnati reds 0.9452054794520548
1989 74 - 72 507 3rd (tie) 3rd 13 - - cincinnati reds 0.972972972972973
1990 86 - 61 585 2nd 1st - 2 - 3 400 cincinnati reds 0.7093023255813954
1991 65 - 78 455 6th 3rd 16 - - cincinnati reds 1.2
1992 67 - 77 465 6th (tie) 4th 20 - - cincinnati reds 1.1492537313432836
1993 81 - 62 566 2nd 1st - 3 - 4 429 chicago white sox 0.7654320987654321
1994 ♦ 83 - 61 576 2nd 2nd 3½ 4 - 3 571 chicago white sox 0.7349397590361446
1995 68 - 76 472 6th 6th 20 - - chicago white sox 1.1176470588235294
1996 77 - 67 535 4th 3rd 7 - - chicago white sox 0.8701298701298701
1997 74 - 69 517 3rd (tie) 3rd 12½ - - chicago white sox 0.9324324324324325
1998 67 - 76 469 12th 4th 9½ - - pittsburgh pirates 1.1343283582089552
1999 80 - 60 571 4th 2nd 2 - - pittsburgh pirates 0.75
2000 63 - 79 444 13th 4th 19 - - pittsburgh pirates 1.253968253968254
2001 64 - 77 454 13th 3rd 19 - - pittsburgh pirates 1.203125
2002 72 - 71 503 8th (tie) 3rd 2½ - - pittsburgh pirates 0.9861111111111112
2003 81 - 62 566 2nd 1st - 3 - 4 429 pittsburgh pirates 0.7654320987654321
2004 63 - 79 444 14th 4th 17 - - pittsburgh pirates 1.253968253968254
2005 ¤ 75 - 69 521 6th 1st - 6 - 2 750 milwaukee brewers 0.92
2006 76 - 68 528 5th (tie) 1st (tie) - 2 - 3 400 milwaukee brewers 0.8947368421052632
2007 89 - 55 618 1st 1st - 1 - 3 250 milwaukee brewers 0.6179775280898876
2008 59 - 81 421 16th 4th 23 - - milwaukee brewers 1.3728813559322033
2009 75 - 69 521 5th 2nd 2 - - milwaukee brewers 0.92
2010 77 - 67 535 5th 4th 5 - - milwaukee brewers 0.8701298701298701
2011 71 - 73 493 6th (tie) 3rd 9 - - milwaukee brewers 1.028169014084507
2012 67 - 77 465 12th 2nd 16 - - milwaukee brewers 1.1492537313432836
2013 57 - 87 396 16th 4th 13 - - milwaukee brewers 1.5263157894736843

Step 3: Select rows where 'year' is 2011.

SQL command for the step:

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

Step 4: Select rows where 'gb_num' is 5.

SQL command for the step:

SELECT * FROM table_sql WHERE gb_num = 5;
year record win__ league division gb post___season_record post___season_win__ mlb_affiliate win_percentage gb_num
2011 71 - 73 493 6th (tie) 3rd 9 - - milwaukee brewers 1.028169014084507 0.0

Step 5: Calculate the average win percentage of the selected rows.

SQL command for the step:

SELECT AVG(win_percentage) AS average_win_percentage FROM table_sql WHERE <condition>;
year record win__ league division gb post___season_record post___season_win__ mlb_affiliate win_percentage gb_num

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

SQL command for the step:

SELECT CASE WHEN AVG(win_percentage) = 0.535 THEN TRUE ELSE FALSE END AS verification FROM table_sql;
avg_win_percentage
None

Verification:

The statement is FALSE