Plan-of-SQLs Interface TN

Sample: 1752/1952
← Previous Next →

Task: Verify the Statement against the Table

Statement: for the 1983 season , the air force - navy score is tbd

Table: commander - in - chief 's trophy

season trophy_winner air_force___navy_score air_force___army_score army___navy_score
1972-01-01 army 21 - 17 17 - 14 23 - 15
1973-01-01 navy 42 - 6 43 - 10 51 - 0
1974-01-01 shared (navy retained trophy) 19 - 16 17 - 16 19 - 0
1975-01-01 navy 17 - 0 33 - 3 30 - 6
1976-01-01 shared (navy retained trophy) 13 - 3 24 - 7 38 - 10
1977-01-01 army 10 - 7 31 - 6 17 - 14
1978-01-01 navy 37 - 8 28 - 14 28 - 0
1979-01-01 navy 13 - 9 28 - 7 31 - 7
1980-01-01 shared (navy retained trophy) 21 - 20 47 - 24 33 - 6
1981-01-01 navy 30 - 13 7 - 3 3 - 3
1982-01-01 air force 24 - 21 27 - 9 24 - 7
1983-01-01 air force 44 - 17 41 - 20 42 - 13
1984-01-01 army 29 - 22 24 - 12 28 - 11
1985-01-01 air force 24 - 7 45 - 7 17 - 7
1986-01-01 army 40 - 6 21 - 11 27 - 7
1987-01-01 air force 23 - 13 27 - 10 17 - 3
1988-01-01 army 34 - 24 28 - 15 20 - 15
1989-01-01 air force 35 - 7 29 - 3 19 - 17
1990-01-01 air force 24 - 7 15 - 3 30 - 20
1991-01-01 air force 46 - 6 25 - 0 24 - 3
1992-01-01 air force 18 - 16 7 - 3 25 - 24
1993-01-01 shared (af retained trophy) 28 - 24 25 - 6 16 - 14
1994-01-01 air force 43 - 21 10 - 6 22 - 20
1995-01-01 air force 30 - 20 38 - 20 14 - 13
1996-01-01 army 20 - 17 23 - 7 28 - 24
1997-01-01 air force 10 - 7 24 - 0 39 - 7
1998-01-01 air force 49 - 7 35 - 7 34 - 30
1999-01-01 air force 19 - 14 28 - 0 19 - 9
2000-01-01 air force 27 - 13 41 - 27 30 - 28
2001-01-01 air force 24 - 18 34 - 24 26 - 17
2002-01-01 air force 48 - 7 49 - 30 58 - 12
2003-01-01 navy 28 - 25 31 - 3 34 - 6
2004-01-01 navy 24 - 21 31 - 22 42 - 13
2005-01-01 navy 27 - 24 27 - 24 42 - 23
2006-01-01 navy 24 - 17 43 - 7 26 - 14
2007-01-01 navy 31 - 20 30 - 10 38 - 3
2008-01-01 navy 33 - 27 16 - 7 34 - 0
2009-01-01 navy 16 - 13 (ot) 35 - 7 17 - 3
2010-01-01 air force 14 - 6 42 - 22 31 - 17
2011-01-01 air force 35 - 34 (ot) 24 - 14 27 - 21
2012-01-01 navy 28 - 21 (ot) 41 - 21 17 - 13
2014-01-01 tbd 4 oct 2014 at air force 1 nov 2014 at army 13 dec 2014 at baltimore , md (m&t bank stadium)
2016-01-01 tbd tbd tbd 10 dec 2016 at baltimore , md (m&t bank stadium)
2018-01-01 tbd tbd tbd 8 dec 2018
Generating plan to answer the query...

Generated steps

Step 1: Select rows where 'season' is '1983-01-01'.

Step 2: Extract the 'air_force___navy_score' from the 'air_force___navy_score' column.

Step 3: Use a `CASE` statement to return TRUE if the 'air_force___navy_score' is 'tbd', otherwise return FALSE.

Step 1: Select rows where 'season' is '1983-01-01'.

SQL command for the step:

SELECT * FROM table_sql WHERE season = '1983-01-01';
season trophy_winner air_force___navy_score air_force___army_score army___navy_score
1972-01-01 army 21 - 17 17 - 14 23 - 15
1973-01-01 navy 42 - 6 43 - 10 51 - 0
1974-01-01 shared (navy retained trophy) 19 - 16 17 - 16 19 - 0
1975-01-01 navy 17 - 0 33 - 3 30 - 6
1976-01-01 shared (navy retained trophy) 13 - 3 24 - 7 38 - 10
1977-01-01 army 10 - 7 31 - 6 17 - 14
1978-01-01 navy 37 - 8 28 - 14 28 - 0
1979-01-01 navy 13 - 9 28 - 7 31 - 7
1980-01-01 shared (navy retained trophy) 21 - 20 47 - 24 33 - 6
1981-01-01 navy 30 - 13 7 - 3 3 - 3
1982-01-01 air force 24 - 21 27 - 9 24 - 7
1983-01-01 air force 44 - 17 41 - 20 42 - 13
1984-01-01 army 29 - 22 24 - 12 28 - 11
1985-01-01 air force 24 - 7 45 - 7 17 - 7
1986-01-01 army 40 - 6 21 - 11 27 - 7
1987-01-01 air force 23 - 13 27 - 10 17 - 3
1988-01-01 army 34 - 24 28 - 15 20 - 15
1989-01-01 air force 35 - 7 29 - 3 19 - 17
1990-01-01 air force 24 - 7 15 - 3 30 - 20
1991-01-01 air force 46 - 6 25 - 0 24 - 3
1992-01-01 air force 18 - 16 7 - 3 25 - 24
1993-01-01 shared (af retained trophy) 28 - 24 25 - 6 16 - 14
1994-01-01 air force 43 - 21 10 - 6 22 - 20
1995-01-01 air force 30 - 20 38 - 20 14 - 13
1996-01-01 army 20 - 17 23 - 7 28 - 24
1997-01-01 air force 10 - 7 24 - 0 39 - 7
1998-01-01 air force 49 - 7 35 - 7 34 - 30
1999-01-01 air force 19 - 14 28 - 0 19 - 9
2000-01-01 air force 27 - 13 41 - 27 30 - 28
2001-01-01 air force 24 - 18 34 - 24 26 - 17
2002-01-01 air force 48 - 7 49 - 30 58 - 12
2003-01-01 navy 28 - 25 31 - 3 34 - 6
2004-01-01 navy 24 - 21 31 - 22 42 - 13
2005-01-01 navy 27 - 24 27 - 24 42 - 23
2006-01-01 navy 24 - 17 43 - 7 26 - 14
2007-01-01 navy 31 - 20 30 - 10 38 - 3
2008-01-01 navy 33 - 27 16 - 7 34 - 0
2009-01-01 navy 16 - 13 (ot) 35 - 7 17 - 3
2010-01-01 air force 14 - 6 42 - 22 31 - 17
2011-01-01 air force 35 - 34 (ot) 24 - 14 27 - 21
2012-01-01 navy 28 - 21 (ot) 41 - 21 17 - 13
2014-01-01 tbd 4 oct 2014 at air force 1 nov 2014 at army 13 dec 2014 at baltimore , md (m&t bank stadium)
2016-01-01 tbd tbd tbd 10 dec 2016 at baltimore , md (m&t bank stadium)
2018-01-01 tbd tbd tbd 8 dec 2018

Step 2: Extract the 'air_force___navy_score' from the 'air_force___navy_score' column.

SQL command for the step:

SELECT air_force___navy_score FROM table_sql;
season trophy_winner air_force___navy_score air_force___army_score army___navy_score
1983-01-01 air force 44 - 17 41 - 20 42 - 13

Step 3: Use a `CASE` statement to return TRUE if the 'air_force___navy_score' is 'tbd', otherwise return FALSE.

SQL command for the step:

SELECT CASE WHEN air_force___navy_score = 'tbd' THEN TRUE ELSE FALSE END AS verification FROM table_sql;
air_force_score navy_score
44 17

Verification:

The statement is FALSE