/* 030- IF & CASE Statement */
-- create table "students" and insert records --
CREATE TABLE students(
id INT NOT NULL,
name VARCHAR(50) NOT NULL,
percentage INT NOT NULL,
age INT NOT NULL,
gender VARCHAR(1) NOT NULL,
city INT NOT NULL,
courses INT NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (city) REFERENCES City (cid),
FOREIGN KEY (courses) REFERENCES Courses (course_id)
);
INSERT INTO students(id,name,percentage,age,gender,city,courses)
VALUES
(1,"Ram Kumar","45","19","M",1,1),
(2,"Sarita Kumari","85","22","F",2,2),
(3,"Salman Khan","29","20","M",1,1),
(4,"Juhi Chawla","47","18","F",3,3),
(5,"Anil Kapoor","74","22","M",1,3),
(6,"John Abraham","64","21","M",2,2),
(7,"Shahid Kapoor","120","20","M",1,1);
-- IF Clause --
SELECT id,name,percentage,
IF(percentage >= 33,"Pass","Fail") AS Result
FROM students;
-- CASE Clause --
SELECT id,name,percentage,
CASE
WHEN percentage >= 80 AND percentage <=100 THEN "Merit"
WHEN percentage >= 60 AND percentage <=80 THEN "Ist Division"
WHEN percentage >= 45 AND percentage <=60 THEN "IInd Division"
WHEN percentage >= 33 AND percentage <=45 THEN "IIIrd Division"
WHEN percentage < 33 THEN "Fail"
ELSE "Not Correct %"
END AS Grade
FROM students;
UPDATE students SET
percentage = (CASE id
WHEN 3 THEN 39
WHEN 7 THEN 62
END)
WHERE id IN (3,7);