--CASE

SELECT CASE num_heads

           WHEN 0 THEN 'Zombie'

           WHEN 1 THEN 'Human'

           ELSE 'Alien'

        END AS race

FROM user

SELECT s.ct, s.grp FROM ( 

SELECT count(*) as ct,

    CASE 

        WHEN COUNT(*) < 25 THEN '1-25'

        WHEN COUNT(*) >= 25 AND COUNT(*) < 50 THEN '25-50'

        WHEN COUNT(*) >= 50 AND COUNT(*) < 100 THEN '50-100'

        WHEN COUNT(*) >= 100 AND COUNT(*) < 250 THEN '100-250'

        WHEN COUNT(*) >= 250 AND COUNT(*) < 500 THEN '250-500'

        WHEN COUNT(*) >= 500 AND COUNT(*) < 1000 THEN '500-1000'

        ELSE '1000+'

    END AS grp

    FROM records r,accounts a

    WHERE r.account_id=a.id) as s

Group BY s.grp;

CASE 

    WHEN user_role = 'Manager' then 4

    WHEN user_name = 'Tom' then 27

    WHEN columnA <> columnB then 99

    ELSE -1 --unknown

END

CASE user_role

    WHEN 'Manager' then 4

    WHEN 'Part Time' then 7

    ELSE -1 --unknown

END

SELECT CASE 1 WHEN 1 THEN 'this is case one'  

WHEN 2 THEN 'this is case two'   

ELSE 'this is not in the case'  

END as 'how to execute case statement'  

    SELECT CASE 2 WHEN 1 THEN 'this is case one'   

    WHEN 2 THEN 'this is case two'             

    ELSE 'this is not in the case'  

    END as 'how to execute case statement'  

    SELECT CASE  WHEN 2>3 THEN 'this is true'  

       ELSE 'this is false' END;   

    SELECT CASE  WHEN 2<3 THEN 'this is true'  

       ELSE 'this is false' END;   

    SELECT CASE BINARY 'A' WHEN 'a' THEN 1  

       WHEN 'b' THEN 2 END;  

--IF

SELECT IF(1>3,'true','false'); 

    SELECT IF((SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END),'true','false');  

    SELECT IFNULL(0,2);  

SELECT IFNULL(1,2); 

    SELECT IFNULL(NULL,2);  

    SELECT NULLIF(2,2);  

    SELECT NULLIF(2,3);  

    SELECT pub_NAME,COALESCE(estd,country,pub_city)  

      FROM newpublisher;  

MySQL coalesce() function returns the first non-NULL value of a list, or NULL if there are no non-NULL values.