Plan-of-SQLs Interface TP

Sample: 227/1952
← Previous Next →

Task: Verify the Statement against the Table

Statement: spiral galaxy have the least apparent magnitude

Table: list of ngc objects (5001 - 6000)

ngc_number object_type constellation right_ascension___j2000__ declination___j2000__ apparent_magnitude
5408 irregular galaxy centaurus 14h03 m21.0s degree22′44″ 14.0
5457 spiral galaxy ursa major 14h03 m12.5s degree20′53″ 8.7
5466 globular cluster boötes 14h05 m27.4s degree32′04″ 10.5
5474 spiral galaxy ursa major 14h05 m01.5s degree39′45″ 11.9
5477 irregular galaxy ursa major 14h05 m33.1s degree27′40″ 14.5
Generating plan to answer the query...

Generated steps

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

Step 2: Select row number 1.

Step 3: Select rows where 'object_type' is 'spiral galaxy'.

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 'apparent_magnitude' in ascending order.

SQL command for the step:

SELECT * FROM table_sql ORDER BY apparent_magnitude ASC;
ngc_number object_type constellation right_ascension___j2000__ declination___j2000__ apparent_magnitude
5408 irregular galaxy centaurus 14h03 m21.0s degree22′44″ 14.0
5457 spiral galaxy ursa major 14h03 m12.5s degree20′53″ 8.7
5466 globular cluster boötes 14h05 m27.4s degree32′04″ 10.5
5474 spiral galaxy ursa major 14h05 m01.5s degree39′45″ 11.9
5477 irregular galaxy ursa major 14h05 m33.1s degree27′40″ 14.5

Step 2: Select row number 1.

SQL command for the step:

SELECT * FROM table_sql LIMIT 1;
ngc_number object_type constellation right_ascension___j2000__ declination___j2000__ apparent_magnitude
5457 spiral galaxy ursa major 14h03 m12.5s degree20′53″ 8.7
5466 globular cluster boötes 14h05 m27.4s degree32′04″ 10.5
5474 spiral galaxy ursa major 14h05 m01.5s degree39′45″ 11.9
5408 irregular galaxy centaurus 14h03 m21.0s degree22′44″ 14.0
5477 irregular galaxy ursa major 14h05 m33.1s degree27′40″ 14.5

Step 3: Select rows where 'object_type' is 'spiral galaxy'.

SQL command for the step:

SELECT * FROM table_sql WHERE object_type = 'spiral galaxy';
ngc_number object_type constellation right_ascension___j2000__ declination___j2000__ apparent_magnitude
5457 spiral galaxy ursa major 14h03 m12.5s degree20′53″ 8.7

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;
ngc_number object_type constellation right_ascension___j2000__ declination___j2000__ apparent_magnitude
5457 spiral galaxy ursa major 14h03 m12.5s degree20′53″ 8.7

Verification:

The statement is TRUE