Plan-of-SQLs Interface TN

Sample: 96/1952
← Previous Next →

Task: Verify the Statement against the Table

Statement: vclc 32t (35.3tons) has the same vcpc but vclc 160 mm (6.3in) or 350 mm (13.8in) rockets has a vcrt of 7.62 mm (0.3in) fn mag 60 - 20 machine gun

Table: tam (tank)

vctp vca_155 vctm vcpc vclc vcrt
28.2 t (31.1 tons ) 40t (44.1tons) 26t (28.7tons) 25t (27.6tons) 32t (35.3tons) 32t (35.3tons)
20 mm (79in) rh - 202 autocannon 155 mm (6.1in) l / 41 howitzer 120 mm (4.72in) mortar 7.62 mm (0.3in) fn mag 60 - 20 machine gun 160 mm (6.3in) or 350 mm (13.8in) rockets 7.62 mm (0.3in) fn mag 60 - 20 machine gun
km (mi) - km (mi) - km (mi) km (mi)
24hp / t (23hp / ton) 18hp / t ( 16.5hp / ton) 28.8hp / t (25.1hp / ton) 28.8hp / t (25.1hp / ton) 22.5hp / t (20.4hp / ton) 22.5hp / t (20.4hp / ton)
75 km / h (47 mph) 55 km / h (34 mph) 75 km / h (47 mph) 75 km / h (47 mph) 75 km / h (47 mph) 75 km / h (47 mph)
Generating plan to answer the query...

Generated steps

Step 1: Select rows where 'vclc' is '32t (35.3tons)'.

Step 2: Select rows where 'vcpc' is the same as the 'vcpc' value of the selected row from step 1.

Step 3: Select rows where 'vclc' is '160 mm (6.3in) or 350 mm (13.8in) rockets'.

Step 4: Select rows where 'vcrt' is '7.62 mm (0.3in) fn mag 60 - 20 machine gun'.

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

Step 1: Select rows where 'vclc' is '32t (35.3tons)'.

SQL command for the step:

SELECT * FROM table_sql WHERE vclc = '32t (35.3tons)';
vctp vca_155 vctm vcpc vclc vcrt
28.2 t (31.1 tons ) 40t (44.1tons) 26t (28.7tons) 25t (27.6tons) 32t (35.3tons) 32t (35.3tons)
20 mm (79in) rh - 202 autocannon 155 mm (6.1in) l / 41 howitzer 120 mm (4.72in) mortar 7.62 mm (0.3in) fn mag 60 - 20 machine gun 160 mm (6.3in) or 350 mm (13.8in) rockets 7.62 mm (0.3in) fn mag 60 - 20 machine gun
km (mi) - km (mi) - km (mi) km (mi)
24hp / t (23hp / ton) 18hp / t ( 16.5hp / ton) 28.8hp / t (25.1hp / ton) 28.8hp / t (25.1hp / ton) 22.5hp / t (20.4hp / ton) 22.5hp / t (20.4hp / ton)
75 km / h (47 mph) 55 km / h (34 mph) 75 km / h (47 mph) 75 km / h (47 mph) 75 km / h (47 mph) 75 km / h (47 mph)

Step 2: Select rows where 'vcpc' is the same as the 'vcpc' value of the selected row from step 1.

SQL command for the step:

SELECT * FROM table_sql WHERE vcpc = (SELECT vcpc FROM table_sql WHERE <condition_from_step_1>);
vctp vca_155 vctm vcpc vclc vcrt
28.2 t (31.1 tons ) 40t (44.1tons) 26t (28.7tons) 25t (27.6tons) 32t (35.3tons) 32t (35.3tons)

Step 3: Select rows where 'vclc' is '160 mm (6.3in) or 350 mm (13.8in) rockets'.

SQL command for the step:

SELECT * FROM table_sql WHERE vclc IN ('160 mm (6.3in) rockets', '350 mm (13.8in) rockets');
vctp vca_155 vctm vcpc vclc vcrt
28.2 t (31.1 tons ) 40t (44.1tons) 26t (28.7tons) 25t (27.6tons) 32t (35.3tons) 32t (35.3tons)

Step 4: Select rows where 'vcrt' is '7.62 mm (0.3in) fn mag 60 - 20 machine gun'.

SQL command for the step:

SELECT * FROM table_sql WHERE vcrt = '7.62 mm (0.3in) fn mag 60 - 20 machine gun';
vctp vca_155 vctm vcpc vclc vcrt

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

SQL command for the step:

SELECT CASE 
           WHEN (SELECT COUNT(*) FROM table_sql WHERE <condition_for_step_2>) = 1 
                AND (SELECT COUNT(*) FROM table_sql WHERE <condition_for_step_4>) = 1 
           THEN TRUE 
           ELSE FALSE 
       END AS verification;
vctp vca_155 vctm vcpc vclc vcrt

Verification:

The statement is FALSE