Plan-of-SQLs Interface TN

Sample: 1774/1952
← Previous Next →

Task: Verify the Statement against the Table

Statement: there are six episode numbers for episodes directed by wayne rose

Table: smallville (season 9)

no _ title directed_by written_by us_air_date production_code us_viewers__million_
175 1 savior kevin g fair kelly souders & brian peterson 2009-09-25 3x5251 2.58
176 2 metallo mairzee almas don whitehead & holly henderson 2009-10-02 3x5252 2.24
177 3 rabid michael rohl jordan hawley 2009-10-09 3x5253 2.3
178 4 echo wayne rose bryan miller 2009-10-16 3x5254 2.61
179 5 roulette kevin g fair genevieve sparling 2009-10-23 3x5256 2.52
180 6 crossfire michael rohl don whitehead & holly henderson 2009-10-30 3x5257 2.57
181 7 kandor jeannot szwarc al septien & turi meyer 2009-11-06 3x5258 2.63
182 8 idol glen winter anne cofell saunders 2009-11-13 3x5255 2.68
183 9 pandora morgan beggs drew landis & julia swift 2009-11-20 3x5259 2.45
184 10 disciple mairzee almas jordan hawley 2010-01-29 3x5260 2.52
185 11 absolute justice glen winter (1st hour) tom welling (2nd hour) geoff johns 2010-02-05 3x5262 / 3x5263 2.77
186 12 warrior allison mack bryan miller 2010-02-12 3x5261 2.48
187 13 persuasion christopher petry anne cofell saunders 2010-02-19 3x5264 2.42
188 14 conspiracy turi meyer al septien & turi meyer 2010-02-26 3x5265 2.53
189 15 escape kevin g fair genevieve sparling 2010-04-02 3x5266 2.13
190 16 checkmate tim scanlan john chisholm 2010-04-09 3x5267 2.13
191 17 upgrade michael rohl drew landis & julia swift 2010-04-16 3x5268 1.84
192 18 charade brian peterson don whitehead & holly henderson 2010-04-23 3x5269 2.23
193 19 sacrifice kevin g fair justin hartley , bryan miller & walter wong 2010-04-30 3x5270 1.89
194 20 hostage glen winter jordan hawley & anne cofell saunders 2010-05-07 3x5271 1.92
Generating plan to answer the query...

Generated steps

Step 1: Select rows where 'directed_by' is 'wayne rose'.

Step 2: Extract the number of distinct 'no' values from the selected rows and add a column 'distinct_no' to the existing table.

Step 3: Select rows where 'distinct_no' is 6.

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

Step 1: Select rows where 'directed_by' is 'wayne rose'.

SQL command for the step:

SELECT * FROM table_sql WHERE directed_by = 'wayne rose';
no _ title directed_by written_by us_air_date production_code us_viewers__million_
175 1 savior kevin g fair kelly souders & brian peterson 2009-09-25 3x5251 2.58
176 2 metallo mairzee almas don whitehead & holly henderson 2009-10-02 3x5252 2.24
177 3 rabid michael rohl jordan hawley 2009-10-09 3x5253 2.3
178 4 echo wayne rose bryan miller 2009-10-16 3x5254 2.61
179 5 roulette kevin g fair genevieve sparling 2009-10-23 3x5256 2.52
180 6 crossfire michael rohl don whitehead & holly henderson 2009-10-30 3x5257 2.57
181 7 kandor jeannot szwarc al septien & turi meyer 2009-11-06 3x5258 2.63
182 8 idol glen winter anne cofell saunders 2009-11-13 3x5255 2.68
183 9 pandora morgan beggs drew landis & julia swift 2009-11-20 3x5259 2.45
184 10 disciple mairzee almas jordan hawley 2010-01-29 3x5260 2.52
185 11 absolute justice glen winter (1st hour) tom welling (2nd hour) geoff johns 2010-02-05 3x5262 / 3x5263 2.77
186 12 warrior allison mack bryan miller 2010-02-12 3x5261 2.48
187 13 persuasion christopher petry anne cofell saunders 2010-02-19 3x5264 2.42
188 14 conspiracy turi meyer al septien & turi meyer 2010-02-26 3x5265 2.53
189 15 escape kevin g fair genevieve sparling 2010-04-02 3x5266 2.13
190 16 checkmate tim scanlan john chisholm 2010-04-09 3x5267 2.13
191 17 upgrade michael rohl drew landis & julia swift 2010-04-16 3x5268 1.84
192 18 charade brian peterson don whitehead & holly henderson 2010-04-23 3x5269 2.23
193 19 sacrifice kevin g fair justin hartley , bryan miller & walter wong 2010-04-30 3x5270 1.89
194 20 hostage glen winter jordan hawley & anne cofell saunders 2010-05-07 3x5271 1.92

Step 2: Extract the number of distinct 'no' values from the selected rows and add a column 'distinct_no' to the existing table.

SQL command for the step:

ALTER TABLE table_sql ADD COLUMN distinct_no INT;
UPDATE table_sql SET distinct_no = (SELECT COUNT(DISTINCT no) FROM table_sql);
no _ title directed_by written_by us_air_date production_code us_viewers__million_
178 4 echo wayne rose bryan miller 2009-10-16 3x5254 2.61

Step 3: Select rows where 'distinct_no' is 6.

SQL command for the step:

SELECT * FROM table_sql WHERE distinct_no = 6;
no _ title directed_by written_by us_air_date production_code us_viewers__million_ distinct_no
178 4 echo wayne rose bryan miller 2009-10-16 3x5254 2.61 1

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

SQL command for the step:

SELECT CASE WHEN COUNT(*) = 1 THEN TRUE ELSE FALSE END AS verification FROM table_sql;
no _ title directed_by written_by us_air_date production_code us_viewers__million_ distinct_no

Verification:

The statement is FALSE