Plan-of-SQLs Interface TP

Sample: 1/1952
← Previous Next →

Task: Verify the Statement against the Table

Statement: the wildcats kept the opposing team scoreless in four games

Table: 1947 kentucky wildcats football team

game date opponent result wildcats_points opponents record
1 9999-09-20 ole miss loss 7 14 0 - 1
2 9999-09-27 cincinnati win 20 0 1 - 1
3 9999-10-04 xavier win 20 7 2 - 1
4 9999-10-11 9 georgia win 26 0 3 - 1 , 20
5 9999-10-18 10 vanderbilt win 14 0 4 - 1 , 14
6 9999-10-25 michigan state win 7 6 5 - 1 , 13
7 9999-11-01 18 alabama loss 0 13 5 - 2
8 9999-11-08 west virginia win 15 6 6 - 2
9 9999-11-15 evansville win 36 0 7 - 2
10 9999-11-22 tennessee loss 6 13 7 - 3
Generating plan to answer the query...

Generated steps

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

Step 2: Select rows where 'opponents' is 0.

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

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

SQL command for the step:

SELECT * FROM table_sql ORDER BY opponents ASC;
game date opponent result wildcats_points opponents record
1 9999-09-20 ole miss loss 7 14 0 - 1
2 9999-09-27 cincinnati win 20 0 1 - 1
3 9999-10-04 xavier win 20 7 2 - 1
4 9999-10-11 9 georgia win 26 0 3 - 1 , 20
5 9999-10-18 10 vanderbilt win 14 0 4 - 1 , 14
6 9999-10-25 michigan state win 7 6 5 - 1 , 13
7 9999-11-01 18 alabama loss 0 13 5 - 2
8 9999-11-08 west virginia win 15 6 6 - 2
9 9999-11-15 evansville win 36 0 7 - 2
10 9999-11-22 tennessee loss 6 13 7 - 3

Step 2: Select rows where 'opponents' is 0.

SQL command for the step:

SELECT * FROM table_sql WHERE opponents = 0;
game date opponent result wildcats_points opponents record
2 9999-09-27 cincinnati win 20 0 1 - 1
4 9999-10-11 9 georgia win 26 0 3 - 1 , 20
5 9999-10-18 10 vanderbilt win 14 0 4 - 1 , 14
9 9999-11-15 evansville win 36 0 7 - 2
6 9999-10-25 michigan state win 7 6 5 - 1 , 13
8 9999-11-08 west virginia win 15 6 6 - 2
3 9999-10-04 xavier win 20 7 2 - 1
7 9999-11-01 18 alabama loss 0 13 5 - 2
10 9999-11-22 tennessee loss 6 13 7 - 3
1 9999-09-20 ole miss loss 7 14 0 - 1

Step 3: 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;
game date opponent result wildcats_points opponents record
2 9999-09-27 cincinnati win 20 0 1 - 1
4 9999-10-11 9 georgia win 26 0 3 - 1 , 20
5 9999-10-18 10 vanderbilt win 14 0 4 - 1 , 14
9 9999-11-15 evansville win 36 0 7 - 2

Verification:

The statement is TRUE