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 SQLite CASE statement.
WHEN [condition.1] THEN [expression.1]
WHEN [condition.2] THEN [expression.2]
WHEN [condition.n] THEN [expression.n]
In above SQLite Case Statement syntax, we defined multiple conditions to get required values.
Here in SQLite Case statement each WHEN. .. THEN clauses evaluated in order manner. First it evaluated condition 1 in case if it satisfied then it return expression 1 otherwise it will execute condition 2 and so on. If no condition is satisfied, then finally execution go to ELSE block and expression under ELSE is evaluated.
We will see how to use SQLite case statement with Select query for that create one table called STUDENT and insert some data by using following queries.
CREATE TABLE STUDENT
(ID INTEGER PRIMARY KEY,
NAME TEXT NOT NULL,
INSERT INTO STUDENT
Once we create and insert data in STUDENT table execute following query to check records in table.
sqlite> SELECT * FROM STUDENT;
ID NAME EMAIL MARKS
---------- ---------- ---------------- ----------
1 Shweta email@example.com 80
2 Yamini firstname.lastname@example.org 60
3 Sonal email@example.com 50
4 Jagruti firstname.lastname@example.org 30
Following is the example of using SQLite Case Statement with Select query.
SELECT ID, NAME, MARKS,
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'
In above SQLite Case statement example, we added multiple conditions using case statement to get Grade of 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 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 SQLite Case statement in our Select or Update statements based on our requirements.