Plan-of-SQLs Interface FP

Sample: 1800/1952
← Previous Next →

Task: Verify the Statement against the Table

Statement: the solheim cup was hosted in the us on 7 occassions from 1990 through 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: Select rows where 'venue' contains 'usa'.

Step 2: Extract the numerical year from the 'year' column then add column 'year_num' to existing table.

Step 3: Select rows where 'year_num' is between 1990 and 2013.

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

Step 1: Select rows where 'venue' contains 'usa'.

SQL command for the step:

SELECT * FROM table_sql WHERE venue LIKE '%usa%';
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: Extract the numerical year from the 'year' column then add column 'year_num' to existing table.

SQL command for the step:

ALTER TABLE table_sql ADD COLUMN year_num INT; UPDATE table_sql SET year_num = CAST(year AS INT);
year venue winning_team score usa_captain europe_captain
2013-01-01 colorado golf club , colorado , usa europe 18 - 10 meg mallon liselotte neumann
2009-01-01 rich harvest farms , illinois , usa united states 16 - 12 beth daniel alison nicholas
2005-01-01 crooked stick golf club , indiana , usa united states 15½ - 12½ nancy lopez catrin nilsmark
2002-01-01 interlachen country club , minnesota , usa united states 15½ - 12½ patty sheehan dale reid
1998-01-01 muirfield village , ohio , usa united states 16 - 12 judy rankin pia nilsson
1994-01-01 the greenbrier , west virginia , usa united states 13 - 7 joanne carner mickey walker
1990-01-01 lake nona golf & country club , florida , usa united states 11½ - 4½ kathy whitworth mickey walker

Step 3: Select rows where 'year_num' is between 1990 and 2013.

SQL command for the step:

SELECT * FROM table_sql WHERE year_num BETWEEN 1990 AND 2013;
year venue winning_team score usa_captain europe_captain year_num
2013-01-01 colorado golf club , colorado , usa europe 18 - 10 meg mallon liselotte neumann 2013
2009-01-01 rich harvest farms , illinois , usa united states 16 - 12 beth daniel alison nicholas 2009
2005-01-01 crooked stick golf club , indiana , usa united states 15½ - 12½ nancy lopez catrin nilsmark 2005
2002-01-01 interlachen country club , minnesota , usa united states 15½ - 12½ patty sheehan dale reid 2002
1998-01-01 muirfield village , ohio , usa united states 16 - 12 judy rankin pia nilsson 1998
1994-01-01 the greenbrier , west virginia , usa united states 13 - 7 joanne carner mickey walker 1994
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 year_num
2013-01-01 colorado golf club , colorado , usa europe 18 - 10 meg mallon liselotte neumann 2013
2009-01-01 rich harvest farms , illinois , usa united states 16 - 12 beth daniel alison nicholas 2009
2005-01-01 crooked stick golf club , indiana , usa united states 15½ - 12½ nancy lopez catrin nilsmark 2005
2002-01-01 interlachen country club , minnesota , usa united states 15½ - 12½ patty sheehan dale reid 2002
1998-01-01 muirfield village , ohio , usa united states 16 - 12 judy rankin pia nilsson 1998
1994-01-01 the greenbrier , west virginia , usa united states 13 - 7 joanne carner mickey walker 1994
1990-01-01 lake nona golf & country club , florida , usa united states 11½ - 4½ kathy whitworth mickey walker 1990

Verification:

The statement is TRUE