Plan-of-SQLs Interface TN

Sample: 1908/1952
← Previous Next →

Task: Verify the Statement against the Table

Statement: four of the cleveland brown 's starting quarterbacks have two different uniform numbers

Table: list of cleveland browns starting quarterbacks

quarterback uniform_no__s_ games_started wins losses ties winning_pct
sipe , brian 17 112 57 55 0 589.0
kosar , bernie 19 105 53 51 1 595.0
ryan , frank 13 76 52 22 2 697.0
graham , otto 60 , 14 71 57 13 1 810.0
couch , tim 2 59 22 37 0 373.0
nelsen , bill 16 51 34 16 1 676.0
phipps , mike 15 51 24 25 2 490.0
plum , milt 16 51 33 16 2 667.0
anderson , derek 3 34 16 18 0 471.0
testaverde , vinny 12 31 16 15 0 516.0
mcdonald , paul 16 21 8 13 0 381.0
mccoy , colt 12 21 6 15 0 286.0
frye , charlie 9 19 6 13 0 316.0
weeden , brandon 3 19 5 14 0 263.0
o'connell , tommy 15 14 10 3 1 750.0
holcomb , kelly 10 12 4 8 0 333.0
quinn , brady 10 12 3 9 0 250.0
ninowski , jim 15 , 11 11 5 6 0 455.0
dilfer , trent 8 11 4 7 0 364.0
garcia , jeff 5 10 3 7 0 300.0
danielson , gary 18 8 5 3 0 625.0
tomczak , mike 18 8 4 4 0 500.0
pederson , doug 18 8 1 7 0 125.0
pagel , mike 10 7 2 5 0 286.0
wallace , seneca 6 7 1 6 0 143.0
ratterman , george 12 , 16 5 2 3 0 400.0
philcox , todd 17 5 2 3 0 400.0
delhomme , jake 17 4 2 2 0 500.0
mays , dave 10 4 1 3 0 250.0
zeier , eric 10 4 1 3 0 250.0
mccown , luke 12 4 0 4 0 0.0
parilli , babe 18 3 1 2 0 333.0
rypien , mark 11 3 2 1 0 667.0
dorsey , ken 11 3 0 3 0 0.0
hoyer , brian 6 3 3 0 0 1.0
strock , don 12 2 2 0 0 1.0
christensen , jeff 11 2 1 1 0 500.0
detmer , ty 11 2 0 2 0 0.0
campbell , jason 17 2 1 1 0 500.0
gault , don 11 1 1 0 0 1.0
lane , gary 15 1 0 1 0 0.0
dawson , len 18 1 1 0 0 1.0
wynn , spergon 13 1 0 1 0 0.0
luck , terry 7 1 0 1 0 0.0
cureton , will 16 1 0 1 0 0.0
gradkowski , bruce 7 1 0 1 0 0.0
lewis , thaddeus 9 1 0 1 0 0.0
Generating plan to answer the query...

Generated steps

Step 1: Select rows where the 'quarterback' column contains a comma (',') and the 'uniform_no__s_' column contains a comma (',').

Step 2: Split the values in the 'uniform_no__s_' column by comma (',') and add a new column 'uniform_no_1' to store the first value and 'uniform_no_2' to store the second value.

Step 3: Select rows where 'uniform_no_1' is not equal to 'uniform_no_2'.

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

Step 1: Select rows where the 'quarterback' column contains a comma (',') and the 'uniform_no__s_' column contains a comma (',').

SQL command for the step:

SELECT * FROM table_sql WHERE quarterback LIKE '%,%' AND uniform_no__s_ LIKE '%,%';
quarterback uniform_no__s_ games_started wins losses ties winning_pct
sipe , brian 17 112 57 55 0 589.0
kosar , bernie 19 105 53 51 1 595.0
ryan , frank 13 76 52 22 2 697.0
graham , otto 60 , 14 71 57 13 1 810.0
couch , tim 2 59 22 37 0 373.0
nelsen , bill 16 51 34 16 1 676.0
phipps , mike 15 51 24 25 2 490.0
plum , milt 16 51 33 16 2 667.0
anderson , derek 3 34 16 18 0 471.0
testaverde , vinny 12 31 16 15 0 516.0
mcdonald , paul 16 21 8 13 0 381.0
mccoy , colt 12 21 6 15 0 286.0
frye , charlie 9 19 6 13 0 316.0
weeden , brandon 3 19 5 14 0 263.0
o'connell , tommy 15 14 10 3 1 750.0
holcomb , kelly 10 12 4 8 0 333.0
quinn , brady 10 12 3 9 0 250.0
ninowski , jim 15 , 11 11 5 6 0 455.0
dilfer , trent 8 11 4 7 0 364.0
garcia , jeff 5 10 3 7 0 300.0
danielson , gary 18 8 5 3 0 625.0
tomczak , mike 18 8 4 4 0 500.0
pederson , doug 18 8 1 7 0 125.0
pagel , mike 10 7 2 5 0 286.0
wallace , seneca 6 7 1 6 0 143.0
ratterman , george 12 , 16 5 2 3 0 400.0
philcox , todd 17 5 2 3 0 400.0
delhomme , jake 17 4 2 2 0 500.0
mays , dave 10 4 1 3 0 250.0
zeier , eric 10 4 1 3 0 250.0
mccown , luke 12 4 0 4 0 0.0
parilli , babe 18 3 1 2 0 333.0
rypien , mark 11 3 2 1 0 667.0
dorsey , ken 11 3 0 3 0 0.0
hoyer , brian 6 3 3 0 0 1.0
strock , don 12 2 2 0 0 1.0
christensen , jeff 11 2 1 1 0 500.0
detmer , ty 11 2 0 2 0 0.0
campbell , jason 17 2 1 1 0 500.0
gault , don 11 1 1 0 0 1.0
lane , gary 15 1 0 1 0 0.0
dawson , len 18 1 1 0 0 1.0
wynn , spergon 13 1 0 1 0 0.0
luck , terry 7 1 0 1 0 0.0
cureton , will 16 1 0 1 0 0.0
gradkowski , bruce 7 1 0 1 0 0.0
lewis , thaddeus 9 1 0 1 0 0.0

Step 2: Split the values in the 'uniform_no__s_' column by comma (',') and add a new column 'uniform_no_1' to store the first value and 'uniform_no_2' to store the second value.

SQL command for the step:

ALTER TABLE table_sql 
ADD COLUMN uniform_no_1 VARCHAR(255), 
ADD COLUMN uniform_no_2 VARCHAR(255);

UPDATE table_sql 
SET uniform_no_1 = SPLIT_PART(uniform_no__s_, ',', 1), 
    uniform_no_2 = SPLIT_PART(uniform_no__s_, ',', 2);
quarterback uniform_no__s_ games_started wins losses ties winning_pct
graham , otto 60 , 14 71 57 13 1 810.0
ninowski , jim 15 , 11 11 5 6 0 455.0
ratterman , george 12 , 16 5 2 3 0 400.0

Step 3: Select rows where 'uniform_no_1' is not equal to 'uniform_no_2'.

SQL command for the step:

SELECT * FROM table_sql WHERE uniform_no_1 <> uniform_no_2;
quarterback uniform_no__s_ games_started wins losses ties winning_pct uniform_no_1 uniform_no_2
graham , otto 60 , 14 71 57 13 1 810.0 60 14
ninowski , jim 15 , 11 11 5 6 0 455.0 15 11
ratterman , george 12 , 16 5 2 3 0 400.0 12 16

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

SQL command for the step:

SELECT CASE WHEN COUNT(*) = 4 THEN TRUE ELSE FALSE END AS verification FROM table_sql;
quarterback uniform_no__s_ games_started wins losses ties winning_pct uniform_no_1 uniform_no_2
graham , otto 60 , 14 71 57 13 1 810.0 60 14
ninowski , jim 15 , 11 11 5 6 0 455.0 15 11
ratterman , george 12 , 16 5 2 3 0 400.0 12 16

Verification:

The statement is FALSE