MySQL CASE WHEN IF

MySQL CASE WHEN statements and IF statement example.

view plaincopy to clipboardprint?

1. CREATE TABLE student (  
2.     id int(11) NOT NULL AUTO_INCREMENT,  
3.     name varchar(100) NOT NULL,  
4.     marks int(11) NOT NULL,  
5.     course_type varchar(2) NOT NULL,  
6.     PRIMARY KEY (`id`)  
7. ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;   

CREATE TABLE student (
    id int(11) NOT NULL AUTO_INCREMENT,
    name varchar(100) NOT NULL,
    marks int(11) NOT NULL,
    course_type varchar(2) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1; 

view plaincopy to clipboardprint?

1. SELECT name, marks, CASE  
2.                        WHEN marks>75 THEN 'A'  
3.                        WHEN marks>65 THEN 'B'  
4.                        WHEN marks>50 THEN 'C'  
5.                        ELSE  'F'  
6.                     END  
7.                     AS results   
8. FROM student ORDER BY name  

SELECT name, marks, CASE
                       WHEN marks>75 THEN 'A'
                       WHEN marks>65 THEN 'B'
                       WHEN marks>50 THEN 'C'
                       ELSE  'F'
                    END
                    AS results 
FROM student ORDER BY name

view plaincopy to clipboardprint?

1. SELECT name,course_type,  
2.        CASE course_type  
3.          WHEN 'WE' THEN 'Week End'  
4.          WHEN 'WD' THEN 'Week Days'  
5.        END AS course  
6. FROM student ORDER BY name  

SELECT name,course_type,
       CASE course_type
         WHEN 'WE' THEN 'Week End'
         WHEN 'WD' THEN 'Week Days'
       END AS course
FROM student ORDER BY name

view plaincopy to clipboardprint?

1. SELECT name, marks, IF(marks >= 50,'PASS','FAIL') AS result  
2. FROM student  
3. ORDER BY name  

SELECT name, marks, IF(marks >= 50,'PASS','FAIL') AS result
FROM student
ORDER BY name