Plan-of-SQLs Interface TN

Sample: 1886/1952
← Previous Next →

Task: Verify the Statement against the Table

Statement: the boston celtics lost more games than they won in the 1984 - 1985 season

Table: 1984 - 85 boston celtics season

game date opponent score location record
33 9999-01-02 new jersey nets 110 - 95 brendan byrne arena 27 - 6
34 9999-01-04 new york knicks 105 - 94 boston garden 28 - 6
35 9999-01-07 new york knicks 108 - 97 madison square garden 29 - 6
36 9999-01-09 chicago bulls 111 - 108 boston garden 30 - 6
37 9999-01-11 washington bullets 103 - 101 boston garden 31 - 6
38 9999-01-12 atlanta hawks 119 - 111 the omni 32 - 6
39 9999-01-16 los angeles lakers 104 - 102 boston garden 33 - 6
40 9999-01-18 indiana pacers 86 - 91 market square arena 33 - 7
41 9999-01-20 philadelphia 76ers 113 - 97 boston garden 34 - 7
42 9999-01-23 seattle supersonics 97 - 107 boston garden 34 - 8
43 9999-01-25 indiana pacers 125 - 94 boston garden 35 - 8
44 9999-01-27 portland trail blazers 128 - 127 boston garden 36 - 8
45 9999-01-29 detroit pistons 131 - 130 hartford civic center 37 - 8
46 9999-01-30 philadelphia 76ers 104 - 122 the spectrum 37 - 9
Generating plan to answer the query...

Generated steps

Step 1: Select rows where the 'record' column contains the win-loss record in the format 'W - L'.

Step 2: Extract the number of wins and losses from the 'record' column and add columns 'wins' and 'losses' to the existing table.

Step 3: Use a `CASE` statement to return TRUE if the number of losses is greater than the number of wins, otherwise return FALSE.

Step 1: Select rows where the 'record' column contains the win-loss record in the format 'W - L'.

SQL command for the step:

SELECT * FROM table_sql WHERE record REGEXP '^[0-9]+ - [0-9]+$';
game date opponent score location record
33 9999-01-02 new jersey nets 110 - 95 brendan byrne arena 27 - 6
34 9999-01-04 new york knicks 105 - 94 boston garden 28 - 6
35 9999-01-07 new york knicks 108 - 97 madison square garden 29 - 6
36 9999-01-09 chicago bulls 111 - 108 boston garden 30 - 6
37 9999-01-11 washington bullets 103 - 101 boston garden 31 - 6
38 9999-01-12 atlanta hawks 119 - 111 the omni 32 - 6
39 9999-01-16 los angeles lakers 104 - 102 boston garden 33 - 6
40 9999-01-18 indiana pacers 86 - 91 market square arena 33 - 7
41 9999-01-20 philadelphia 76ers 113 - 97 boston garden 34 - 7
42 9999-01-23 seattle supersonics 97 - 107 boston garden 34 - 8
43 9999-01-25 indiana pacers 125 - 94 boston garden 35 - 8
44 9999-01-27 portland trail blazers 128 - 127 boston garden 36 - 8
45 9999-01-29 detroit pistons 131 - 130 hartford civic center 37 - 8
46 9999-01-30 philadelphia 76ers 104 - 122 the spectrum 37 - 9

Step 2: Extract the number of wins and losses from the 'record' column and add columns 'wins' and 'losses' to the existing table.

SQL command for the step:

ALTER TABLE table_sql ADD COLUMN wins INT, ADD COLUMN losses INT;
UPDATE table_sql SET wins = CAST(SUBSTRING_INDEX(record, '-', 1) AS UNSIGNED), losses = CAST(SUBSTRING_INDEX(record, '-', -1) AS UNSIGNED);
game date opponent score location record

Step 3: Use a `CASE` statement to return TRUE if the number of losses is greater than the number of wins, otherwise return FALSE.

SQL command for the step:

SELECT CASE WHEN losses > wins THEN TRUE ELSE FALSE END AS verification FROM table_sql;
game date opponent score location record wins losses

Verification:

The statement is FALSE