Plan-of-SQLs Interface TP

Sample: 1863/1952
← Previous Next →

Task: Verify the Statement against the Table

Statement: mark cavendish is the only person to win two consecutive stages

Table: 2010 vuelta a españa

stage winner general_classification points_classification mountains_classification combination_classification team_classification
1 team htc - columbia mark cavendish mark cavendish 1 not awarded mark cavendish team htc - columbia
2 yauheni hutarovich mark cavendish yauheni hutarovich mickaël delage javier ramírez team htc - columbia
3 philippe gilbert philippe gilbert philippe gilbert serafín martínez serafín martínez team htc - columbia
4 igor antón philippe gilbert igor antón serafín martínez vincenzo nibali caisse d'epargne
5 tyler farrar philippe gilbert igor antón serafín martínez vincenzo nibali caisse d'epargne
6 thor hushovd philippe gilbert philippe gilbert serafín martínez philippe gilbert caisse d'epargne
7 alessandro petacchi philippe gilbert mark cavendish serafín martínez philippe gilbert caisse d'epargne
8 david moncoutié igor antón mark cavendish serafín martínez vincenzo nibali caisse d'epargne
9 david lópez igor antón mark cavendish david moncoutié vincenzo nibali caisse d'epargne
10 imanol erviti joaquim rodríguez mark cavendish david moncoutié david moncoutié caisse d'epargne
11 igor antón igor antón igor antón david moncoutié igor antón caisse d'epargne
12 mark cavendish igor antón mark cavendish david moncoutié igor antón caisse d'epargne
13 mark cavendish igor antón mark cavendish david moncoutié igor antón caisse d'epargne
14 joaquim rodríguez vincenzo nibali mark cavendish david moncoutié joaquim rodríguez caisse d'epargne
15 carlos barredo vincenzo nibali mark cavendish david moncoutié joaquim rodríguez team katusha
16 mikel nieve joaquim rodríguez mark cavendish david moncoutié joaquim rodríguez team katusha
17 peter velits vincenzo nibali mark cavendish david moncoutié joaquim rodríguez team katusha
18 mark cavendish vincenzo nibali mark cavendish david moncoutié joaquim rodríguez team katusha
19 philippe gilbert vincenzo nibali mark cavendish david moncoutié joaquim rodríguez team katusha
20 ezequiel mosquera 2 vincenzo nibali mark cavendish david moncoutié vincenzo nibali team katusha
21 tyler farrar vincenzo nibali mark cavendish david moncoutié vincenzo nibali team katusha
Generating plan to answer the query...

Generated steps

Step 1: Order the table by 'stage' in ascending order.

Step 2: Select rows where 'winner' is 'mark cavendish'.

Step 3: Select rows where 'winner' in the current row is the same as the 'winner' in the next row.

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

Step 1: Order the table by 'stage' in ascending order.

SQL command for the step:

SELECT * FROM table_sql ORDER BY stage ASC;
stage winner general_classification points_classification mountains_classification combination_classification team_classification
1 team htc - columbia mark cavendish mark cavendish 1 not awarded mark cavendish team htc - columbia
2 yauheni hutarovich mark cavendish yauheni hutarovich mickaël delage javier ramírez team htc - columbia
3 philippe gilbert philippe gilbert philippe gilbert serafín martínez serafín martínez team htc - columbia
4 igor antón philippe gilbert igor antón serafín martínez vincenzo nibali caisse d'epargne
5 tyler farrar philippe gilbert igor antón serafín martínez vincenzo nibali caisse d'epargne
6 thor hushovd philippe gilbert philippe gilbert serafín martínez philippe gilbert caisse d'epargne
7 alessandro petacchi philippe gilbert mark cavendish serafín martínez philippe gilbert caisse d'epargne
8 david moncoutié igor antón mark cavendish serafín martínez vincenzo nibali caisse d'epargne
9 david lópez igor antón mark cavendish david moncoutié vincenzo nibali caisse d'epargne
10 imanol erviti joaquim rodríguez mark cavendish david moncoutié david moncoutié caisse d'epargne
11 igor antón igor antón igor antón david moncoutié igor antón caisse d'epargne
12 mark cavendish igor antón mark cavendish david moncoutié igor antón caisse d'epargne
13 mark cavendish igor antón mark cavendish david moncoutié igor antón caisse d'epargne
14 joaquim rodríguez vincenzo nibali mark cavendish david moncoutié joaquim rodríguez caisse d'epargne
15 carlos barredo vincenzo nibali mark cavendish david moncoutié joaquim rodríguez team katusha
16 mikel nieve joaquim rodríguez mark cavendish david moncoutié joaquim rodríguez team katusha
17 peter velits vincenzo nibali mark cavendish david moncoutié joaquim rodríguez team katusha
18 mark cavendish vincenzo nibali mark cavendish david moncoutié joaquim rodríguez team katusha
19 philippe gilbert vincenzo nibali mark cavendish david moncoutié joaquim rodríguez team katusha
20 ezequiel mosquera 2 vincenzo nibali mark cavendish david moncoutié vincenzo nibali team katusha
21 tyler farrar vincenzo nibali mark cavendish david moncoutié vincenzo nibali team katusha

Step 2: Select rows where 'winner' is 'mark cavendish'.

SQL command for the step:

SELECT * FROM table_sql WHERE winner = 'mark cavendish';
stage winner general_classification points_classification mountains_classification combination_classification team_classification
1 team htc - columbia mark cavendish mark cavendish 1 not awarded mark cavendish team htc - columbia
2 yauheni hutarovich mark cavendish yauheni hutarovich mickaël delage javier ramírez team htc - columbia
3 philippe gilbert philippe gilbert philippe gilbert serafín martínez serafín martínez team htc - columbia
4 igor antón philippe gilbert igor antón serafín martínez vincenzo nibali caisse d'epargne
5 tyler farrar philippe gilbert igor antón serafín martínez vincenzo nibali caisse d'epargne
6 thor hushovd philippe gilbert philippe gilbert serafín martínez philippe gilbert caisse d'epargne
7 alessandro petacchi philippe gilbert mark cavendish serafín martínez philippe gilbert caisse d'epargne
8 david moncoutié igor antón mark cavendish serafín martínez vincenzo nibali caisse d'epargne
9 david lópez igor antón mark cavendish david moncoutié vincenzo nibali caisse d'epargne
10 imanol erviti joaquim rodríguez mark cavendish david moncoutié david moncoutié caisse d'epargne
11 igor antón igor antón igor antón david moncoutié igor antón caisse d'epargne
12 mark cavendish igor antón mark cavendish david moncoutié igor antón caisse d'epargne
13 mark cavendish igor antón mark cavendish david moncoutié igor antón caisse d'epargne
14 joaquim rodríguez vincenzo nibali mark cavendish david moncoutié joaquim rodríguez caisse d'epargne
15 carlos barredo vincenzo nibali mark cavendish david moncoutié joaquim rodríguez team katusha
16 mikel nieve joaquim rodríguez mark cavendish david moncoutié joaquim rodríguez team katusha
17 peter velits vincenzo nibali mark cavendish david moncoutié joaquim rodríguez team katusha
18 mark cavendish vincenzo nibali mark cavendish david moncoutié joaquim rodríguez team katusha
19 philippe gilbert vincenzo nibali mark cavendish david moncoutié joaquim rodríguez team katusha
20 ezequiel mosquera 2 vincenzo nibali mark cavendish david moncoutié vincenzo nibali team katusha
21 tyler farrar vincenzo nibali mark cavendish david moncoutié vincenzo nibali team katusha

Step 3: Select rows where 'winner' in the current row is the same as the 'winner' in the next row.

SQL command for the step:

SELECT * FROM table_sql t1 WHERE t1.winner = (SELECT t2.winner FROM table_sql t2 WHERE t2.id = t1.id + 1);
stage winner general_classification points_classification mountains_classification combination_classification team_classification
12 mark cavendish igor antón mark cavendish david moncoutié igor antón caisse d'epargne
13 mark cavendish igor antón mark cavendish david moncoutié igor antón caisse d'epargne
18 mark cavendish vincenzo nibali mark cavendish david moncoutié joaquim rodríguez team katusha

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;
stage winner general_classification points_classification mountains_classification combination_classification team_classification
12 mark cavendish igor antón mark cavendish david moncoutié igor antón caisse d'epargne

Verification:

The statement is TRUE