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.
The query to display ORDER_ID and free shipping result based on the ORDER_TOTAL criteria can be written as below.
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.