Plan-of-SQLs Interface TN

Sample: 1821/1952
← Previous Next →

Task: Verify the Statement against the Table

Statement: the average services is less than 7 , when year is greater than 2003 , when industry is less than 1465 , and when regional gva is greater than 9432

Table: none

year regional_gva agriculture industry services
1995 4753 11 1110 3632
2000 6584 10 1302 5277
2003 8201 11 1374 6816
2005 8978 11 1465 7502
2007 9432 11 1565 7856
Generating plan to answer the query...

Generated steps

Step 1: Select rows where 'year' is greater than 2003.

Step 2: Select rows where 'industry' is less than 1465.

Step 3: Select rows where 'regional_gva' is greater than 9432.

Step 4: Calculate the average value of the 'services' column.

Step 5: Use a `CASE` statement to return TRUE if the average value of 'services' is less than 7, otherwise return FALSE.

Step 1: Select rows where 'year' is greater than 2003.

SQL command for the step:

SELECT * FROM table_sql WHERE year > 2003;
year regional_gva agriculture industry services
1995 4753 11 1110 3632
2000 6584 10 1302 5277
2003 8201 11 1374 6816
2005 8978 11 1465 7502
2007 9432 11 1565 7856

Step 2: Select rows where 'industry' is less than 1465.

SQL command for the step:

SELECT * FROM table_sql WHERE industry < 1465;
year regional_gva agriculture industry services
2005 8978 11 1465 7502
2007 9432 11 1565 7856

Step 3: Select rows where 'regional_gva' is greater than 9432.

SQL command for the step:

SELECT * FROM table_sql WHERE regional_gva > 9432;
year regional_gva agriculture industry services

Step 4: Calculate the average value of the 'services' column.

SQL command for the step:

SELECT AVG(services) AS average_services FROM table_sql;
year regional_gva agriculture industry services

Step 5: Use a `CASE` statement to return TRUE if the average value of 'services' is less than 7, otherwise return FALSE.

SQL command for the step:

SELECT CASE WHEN AVG(services) < 7 THEN TRUE ELSE FALSE END AS verification FROM table_sql;
avg_services
None

Verification:

The statement is FALSE