SQLite Case Statement

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.

SQLite Case Statement

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.

Syntax of SQLite Case Statement

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.

Example of SQLite Case Statement

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.

 

sqliteSELECT 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 IDNAMEMARKS,

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.

Topics Covered