Plan-of-SQLs Interface TN

Sample: 1868/1952
← Previous Next →

Task: Verify the Statement against the Table

Statement: mar cavendish consecutively won 5 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: Use a `CASE` statement to return TRUE if the number of consecutive rows is equal to 5, 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: Use a `CASE` statement to return TRUE if the number of consecutive rows is equal to 5, otherwise return FALSE.

SQL command for the step:

SELECT CASE WHEN COUNT(*) = 5 THEN TRUE ELSE FALSE END AS verification
FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY some_column) - ROW_NUMBER() OVER (PARTITION BY some_group_column ORDER BY some_column) AS grp
    FROM table_sql
) AS subquery
GROUP BY grp
HAVING COUNT(*) = 5;
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

Verification:

The statement is FALSE