- In MySQL, the IF, ELSE, and CASE statements are used for conditional branching within queries or procedures. They allow you to execute different blocks of code based on certain conditions.
- Suppose we have a 'students' table that contains id, name, age, and marks. The schema of this table is as follows.
-- Create the students table
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255),
age INT,
marks INT
);
-- Insert data into the students table
INSERT INTO students (name, age, marks) VALUES
('John Doe', 20, 85),
('Jane Smith', 21, 92),
('Michael Johnson', 19, 33),
('Emily Brown', 22, 110),
('David Lee', 20, 70),
('Sophia Williams', 21, 20),
('William Davis', 19, 62),
('Olivia Jones', 22, 44),
('Daniel Martinez', 20, 52),
('Ava Taylor', 21, -30);
- Now, we will check the condition: if the student's marks are greater than 32, they pass; otherwise, they fail.
SELECT *, IF(marks >=33, "PASS", "FAIL") as RESULT from students
- Now, we will check the condition: if the student's marks are greater than 32, they pass; otherwise, they fail. We will also display records of students whose age has surpassed 20.
SELECT *, IF(marks >=33, "PASS", "FAIL") as RESULT from students WHERE age > 20
- Now, we are checking the student's marks under multiple conditions using a case statement.
SELECT *,
CASE
WHEN marks >= 75 THEN "MERIT"
WHEN marks >= 60 THEN "1st Div"
WHEN marks >= 45 THEN "2nd Div"
WHEN marks >= 33 THEN "3rd Div"
WHEN marks < 33 THEN "FAILED"
END as GRADE
FROM students
- Now, we are checking the student's marks under multiple conditions using a case statement with else as default value.
SELECT *,
CASE
WHEN marks >= 75 and marks <= 100 THEN "MERIT"
WHEN marks >= 60 and marks < 75 THEN "1st Div"
WHEN marks >= 45 and marks < 60 THEN "2nd Div"
WHEN marks >= 33 and marks < 45 THEN "3rd Div"
WHEN marks >= 0 and marks < 33 THEN "FAILED"
ELSE "% is not valid"
END as GRADE
FROM students
No comments:
Post a Comment