Example of SQL query describing the conditional processing

SQLSQLiteDatabase

Problem: Write a SQL query to display 2 columns. First column should have ORDER_ID, the second column should give the value as YES/NO for free shipping based on ORDER_TOTAL > 500.

Solution

The query to display ORDER_ID and free shipping result based on the ORDER_TOTAL criteria can be written as below.

Example

SELECT ORDER_ID,
   CASE WHEN ORDER_TOTAL > 500 THEN ‘YES’
      ELSE ‘NO’ AS FREE_SHIPPING
   END
FROM ORDERS

We will use CASE expressions through which we can implement a logic to check the ORDER_TOTAL. If the ORDER_TOTAL is greater than 500 then we will get ‘YES’ for the free shipping else, we will get ‘NO’. The query will return two columns ORDER_ID and FREE_SHIPPING.

For example, if we have below ORDERS DB2 table.

ORDER_ID
ORDER_TOTAL
Z22345
342
Z62998
543
Z56990
431
Z56902
6743
Z99781
443
Z56112
889

 

Then the SQL query with CASE WHEN expression will return the following result.

ORDER_ID
FREE_SHIPPING
Z22345
NO
Z62998
YES
Z56990
NO
Z56902
YES
Z99781
NO
Z56112
YES
raja
Published on 30-Nov-2020 14:12:01
Advertisements