Here we will learn SQLite Case statement with example and how to use SQLite case statement with select, update, delete, where, order by, having statements to get required data by defining multiple conditions.
In SQLite CASE statement is like an if...then...else condition in other programming languages or like C Language switch statements.
Generally, the SQLite Case statement contains an optional expression followed by one or more WHEN ... THEN clauses and it is finished with an optional ELSE clause and a required END keyword.
In SQLite we can use Case statement with Select, Update, Delete, Where, Order By, Having clauses to get required values by defining multiple conditions based on our requirement.
Following is the syntax of the SQLite CASE statement.
CASE test_expression
WHEN [condition.1] THEN [expression.1]
WHEN [condition.2] THEN [expression.2]
...
WHEN [condition.n] THEN [expression.n]
ELSE [expression]
END
In the above SQLite Case Statement syntax, we defined multiple conditions to get the required values.
Here in SQLite Case statement each WHEN. .. THEN clauses evaluated in an orderly manner. First, it evaluated condition 1 in case if it satisfied then it returns expression 1 otherwise it will execute condition 2 and so on. If no condition is satisfied, then finally execution goes to ELSE block, and expression under ELSE is evaluated.
We will see how to use the SQLite case statement with Select query for that create one table called STUDENT and insert some data by using the following queries.
CREATE TABLE STUDENT
(ID INTEGER PRIMARY KEY,
NAME TEXT NOT NULL,
EMAIL TEXT,
MARKS FLOAT);
INSERT INTO STUDENT
values (1,'Shweta','shweta@gmail.com',80),
(2,'Yamini','rani@gmail.com', 60),
(3,'Sonal','sonal@gmail.com', 50),
(4,'Jagruti','jagu@gmail.com', 30);
Once we create and insert data in the STUDENT table execute the following query to check records in the table.
sqlite> SELECT * FROM STUDENT;
ID NAME EMAIL MARKS
---------- ---------- ---------------- ----------
1 Shweta shweta@gmail.com 80
2 Yamini rani@gmail.com 60
3 Sonal sonal@gmail.com 50
4 Jagruti jagu@gmail.com 30
Following is the example of using the SQLite Case Statement with Select query.
SELECT ID, NAME, MARKS,
CASE
WHEN MARKS >=80 THEN 'A+'
WHEN MARKS >=70 THEN 'A'
WHEN MARKS >=60 THEN 'B'
WHEN MARKS >=50 THEN 'C'
ELSE 'Sorry!! Failed'
END as 'Grade'
FROM STUDENT;
In the above SQLite Case statement example, we added multiple conditions using a case statement to get Grade of the student based on the marks.
In this example first, it checks that marks greater than 80 if so then it will print grade “A+”, if not it will check whether marks greater than 70 if so then it will print grade “A”, if not then it checks for marks greater than 60 if so then it will print grade “B” and not then it checks for marks greater than 50 if so then it will print grade “C” and if no condition satisfy then it will print “Sorry!! Failed”.
Now we will run and check the result of the above query that will be like as shown below.
ID NAME MARKS Grade
---------- ---------- ---------- ----------
1 Shweta 80.0 A+
2 Yamini 60.0 B
3 Sonal 50.0 C
4 Jagruti 30.0 Sorry!! Failed
This is how we can use the SQLite Case statement in our Select or Update statements based on our requirements.