Write MySQL case statement to set custom messages for student’s result

For this, set conditions using MySQL CASE statement −

mysql> create table DemoTable1916
   (
   StudentName varchar(20),
   StudentMarks int
   );
Query OK, 0 rows affected (0.00 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable1916 values('Chris',59);
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1916 values('David',89);
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1916 values('Sam',94);
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1916 values('Mike',75);
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1916 values('Carol',69);
Query OK, 1 row affected (0.00 sec)

Display all records from the table using select statement −

mysql> select * from DemoTable1916;

This will produce the following output −

+-------------+--------------+
| StudentName | StudentMarks |
+-------------+--------------+
| Chris       |           59 |
| David       |           89 |
| Sam         |           94 |
| Mike        |           75 |
| Carol       |           69 |
+-------------+--------------+
5 rows in set (0.00 sec)

Here is the query to set custom message for Student Marks −

mysql> select StudentName,
   case when StudentMarks > 70 Then 'Good Marks'
   else
   'Not Good Marks'
   end
   as Result
   from DemoTable1916;

This will produce the following output −

+-------------+----------------+
| StudentName | Result         |
+-------------+----------------+
| Chris       | Not Good Marks |
| David       |    Good Marks  |
| Sam         |    Good Marks  |
| Mike        |    Good Marks  |
| Carol       |  Not Good Marks|
+-------------+----------------+
5 rows in set (0.00 sec)

AmitDiwan

Published on 30-Dec-2019 06:46:30

• Related Questions & Answers
• Set custom messages for enum values in MySQL
• Set custom messages on the basis of a column with student marks in MySQL
• Using CASE statement in MySQL to display custom name for empty value
• MySQL CASE statement to place custom values in place of NULL
• MySQL IF() to display custom YES or NO messages
• How to write custom Python Exceptions with Error Codes and Error Messages?
• MySQL case statement inside a select statement?
• What is the meaning of ‘empty set’ in MySQL result set?
• Set multiple values for custom columns in MySQL?
• Write a program for Happy Woman’s Day in c++
• Determine what a MySQL DB’s charset is set to
• MySQL query to get the dates between range of records displaying student’s Date of Birth?
• Set custom messages by working with MySQL IF Statements and SELECT in a user-defined variable
• Why should we use MySQL CASE Statement?
• MySQL order by field using CASE Statement