Plan-of-SQLs Interface TN

Sample: 1805/1952
← Previous Next →

Task: Verify the Statement against the Table

Statement: the solheim cup was hosted outside the us on seven occasions from 1990 - 2013

Table: solheim cup

year venue winning_team score usa_captain europe_captain
2013-01-01 colorado golf club , colorado , usa europe 18 - 10 meg mallon liselotte neumann
2011-01-01 killeen castle golf resort , ireland europe 15 - 13 rosie jones alison nicholas
2009-01-01 rich harvest farms , illinois , usa united states 16 - 12 beth daniel alison nicholas
2007-01-01 halmstad gk , sweden united states 16 - 12 betsy king helen alfredsson
2005-01-01 crooked stick golf club , indiana , usa united states 15½ - 12½ nancy lopez catrin nilsmark
2003-01-01 barsebäck golf & country club , sweden europe 17½ - 10½ patty sheehan catrin nilsmark
2002-01-01 interlachen country club , minnesota , usa united states 15½ - 12½ patty sheehan dale reid
2000-01-01 loch lomond golf club , scotland europe 14½ - 11½ pat bradley dale reid
1998-01-01 muirfield village , ohio , usa united states 16 - 12 judy rankin pia nilsson
1996-01-01 st pierre golf & country club , wales united states 17 - 11 judy rankin mickey walker
1994-01-01 the greenbrier , west virginia , usa united states 13 - 7 joanne carner mickey walker
1992-01-01 dalmahoy country club , scotland europe 11½ - 6½ kathy whitworth mickey walker
1990-01-01 lake nona golf & country club , florida , usa united states 11½ - 4½ kathy whitworth mickey walker
Generating plan to answer the query...

Generated steps

Step 1: Extract the numerical year from the 'year' column then add column 'num_year' to existing table.

Step 2: Select rows where 'num_year' is between 1990 and 2013.

Step 3: Select rows where 'venue' does not contain 'usa'.

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

Step 1: Extract the numerical year from the 'year' column then add column 'num_year' to existing table.

SQL command for the step:

ALTER TABLE table_sql ADD COLUMN num_year INT GENERATED ALWAYS AS (CAST(year AS INT)) STORED;
year venue winning_team score usa_captain europe_captain
2013-01-01 colorado golf club , colorado , usa europe 18 - 10 meg mallon liselotte neumann
2011-01-01 killeen castle golf resort , ireland europe 15 - 13 rosie jones alison nicholas
2009-01-01 rich harvest farms , illinois , usa united states 16 - 12 beth daniel alison nicholas
2007-01-01 halmstad gk , sweden united states 16 - 12 betsy king helen alfredsson
2005-01-01 crooked stick golf club , indiana , usa united states 15½ - 12½ nancy lopez catrin nilsmark
2003-01-01 barsebäck golf & country club , sweden europe 17½ - 10½ patty sheehan catrin nilsmark
2002-01-01 interlachen country club , minnesota , usa united states 15½ - 12½ patty sheehan dale reid
2000-01-01 loch lomond golf club , scotland europe 14½ - 11½ pat bradley dale reid
1998-01-01 muirfield village , ohio , usa united states 16 - 12 judy rankin pia nilsson
1996-01-01 st pierre golf & country club , wales united states 17 - 11 judy rankin mickey walker
1994-01-01 the greenbrier , west virginia , usa united states 13 - 7 joanne carner mickey walker
1992-01-01 dalmahoy country club , scotland europe 11½ - 6½ kathy whitworth mickey walker
1990-01-01 lake nona golf & country club , florida , usa united states 11½ - 4½ kathy whitworth mickey walker

Step 2: Select rows where 'num_year' is between 1990 and 2013.

SQL command for the step:

SELECT * FROM table_sql WHERE num_year BETWEEN 1990 AND 2013;
year venue winning_team score usa_captain europe_captain num_year
2013-01-01 colorado golf club , colorado , usa europe 18 - 10 meg mallon liselotte neumann 2013
2011-01-01 killeen castle golf resort , ireland europe 15 - 13 rosie jones alison nicholas 2011
2009-01-01 rich harvest farms , illinois , usa united states 16 - 12 beth daniel alison nicholas 2009
2007-01-01 halmstad gk , sweden united states 16 - 12 betsy king helen alfredsson 2007
2005-01-01 crooked stick golf club , indiana , usa united states 15½ - 12½ nancy lopez catrin nilsmark 2005
2003-01-01 barsebäck golf & country club , sweden europe 17½ - 10½ patty sheehan catrin nilsmark 2003
2002-01-01 interlachen country club , minnesota , usa united states 15½ - 12½ patty sheehan dale reid 2002
2000-01-01 loch lomond golf club , scotland europe 14½ - 11½ pat bradley dale reid 2000
1998-01-01 muirfield village , ohio , usa united states 16 - 12 judy rankin pia nilsson 1998
1996-01-01 st pierre golf & country club , wales united states 17 - 11 judy rankin mickey walker 1996
1994-01-01 the greenbrier , west virginia , usa united states 13 - 7 joanne carner mickey walker 1994
1992-01-01 dalmahoy country club , scotland europe 11½ - 6½ kathy whitworth mickey walker 1992
1990-01-01 lake nona golf & country club , florida , usa united states 11½ - 4½ kathy whitworth mickey walker 1990

Step 3: Select rows where 'venue' does not contain 'usa'.

SQL command for the step:

SELECT * FROM table_sql WHERE venue NOT LIKE '%usa%';
year venue winning_team score usa_captain europe_captain num_year
2013-01-01 colorado golf club , colorado , usa europe 18 - 10 meg mallon liselotte neumann 2013
2011-01-01 killeen castle golf resort , ireland europe 15 - 13 rosie jones alison nicholas 2011
2009-01-01 rich harvest farms , illinois , usa united states 16 - 12 beth daniel alison nicholas 2009
2007-01-01 halmstad gk , sweden united states 16 - 12 betsy king helen alfredsson 2007
2005-01-01 crooked stick golf club , indiana , usa united states 15½ - 12½ nancy lopez catrin nilsmark 2005
2003-01-01 barsebäck golf & country club , sweden europe 17½ - 10½ patty sheehan catrin nilsmark 2003
2002-01-01 interlachen country club , minnesota , usa united states 15½ - 12½ patty sheehan dale reid 2002
2000-01-01 loch lomond golf club , scotland europe 14½ - 11½ pat bradley dale reid 2000
1998-01-01 muirfield village , ohio , usa united states 16 - 12 judy rankin pia nilsson 1998
1996-01-01 st pierre golf & country club , wales united states 17 - 11 judy rankin mickey walker 1996
1994-01-01 the greenbrier , west virginia , usa united states 13 - 7 joanne carner mickey walker 1994
1992-01-01 dalmahoy country club , scotland europe 11½ - 6½ kathy whitworth mickey walker 1992
1990-01-01 lake nona golf & country club , florida , usa united states 11½ - 4½ kathy whitworth mickey walker 1990

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

SQL command for the step:

SELECT CASE WHEN COUNT(*) = 7 THEN TRUE ELSE FALSE END AS verification FROM table_sql;
year venue winning_team score usa_captain europe_captain num_year
2011-01-01 killeen castle golf resort , ireland europe 15 - 13 rosie jones alison nicholas 2011
2007-01-01 halmstad gk , sweden united states 16 - 12 betsy king helen alfredsson 2007
2003-01-01 barsebäck golf & country club , sweden europe 17½ - 10½ patty sheehan catrin nilsmark 2003
2000-01-01 loch lomond golf club , scotland europe 14½ - 11½ pat bradley dale reid 2000
1996-01-01 st pierre golf & country club , wales united states 17 - 11 judy rankin mickey walker 1996
1992-01-01 dalmahoy country club , scotland europe 11½ - 6½ kathy whitworth mickey walker 1992

Verification:

The statement is FALSE