Example of SQL query describing the conditional processing

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.

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