/* display a different value based on existing column value */
/* more powerful than decode and replace */

select fname, lname, 
	case sex 
     		when 'F' then 'female' 
    		when 'M' then 'male' 
     		else 'unknown?' 
	end as gender
from student;



/* Using CASE with different syntax.  More powerful than above */

SELECT fname, lname, description,
	CASE
		WHEN sex='M' and description like '%SQL%'  THEN  'SQL/Male'
		WHEN sex='F' and description like '%SQL%'  THEN  'SQL/Female'
		WHEN sex='M' and description like '%HTML%' THEN  'Web/Male'
		WHEN sex='F' and description like '%HTML%' THEN  'Web/Female'
	END as "skills/gender"
FROM student s, class c, course co
WHERE ssn=stu_ssn
  AND c.course_id=co.course_id;





/* Another example */

SELECT description, price,
	CASE 
		WHEN price BETWEEN     0 AND 750 	THEN 'Cheap'
		WHEN price > 750 AND price < 1500	THEN 'Moderate'
		WHEN price > 1500  			THEN 'Expensive'
		ELSE 'Unknown'
	END as "Price Category"
FROM course;



/* Using CASE to separated the sexes */

select c.course_id, 
	case s.sex 
           	when 'M' then concat(s.fname, s.lname) 
		else null 
	end as "Male", 
	case s.sex 
           	when 'F' then concat(s.fname, s.lname) 
		else null 
	end as "Female",
	ssn, session_id 
from   class c, student s
where  c.stu_ssn = s.ssn ;



/* Nested CASE */

select fname, lname, 
case
    when sex='M' then 
        case 
            when student_id <= 10 then 'male < 10'
            when student_id >  10 then 'male > 10'
        end 
    when sex='F' then 
        case 
            when student_id <= 10 then 'female < 10'
            when student_id >  10 then 'female > 10'
        end 
end as "sex & id"
from student;



/* Using nested CASE Expression */

select course_id, description, price,
       case
           when price < 1000 then
               case
                   when description like '%Dev%'  then 'cheap development course'
                   when description like '%Java%' then 'cheap Java course'
                                                  else 'cheap course'
               end          
           when price < 2000 then  
               case
                   when description like '%Dev%'  then 'moderately priced development course'
                   when description like '%Java%' then 'moderately priced Java course'
                                                  else 'moderately priced course'
               end
           else 'expensive'
       end as "results"
from course ;