SQLite Case Statement

  Total Views : 38
  Comments : 0
 
  Keywords : SQLite case expression with example, SQLite case statement with when then conditions in select query example, SQLite case statement to define multiple conditions example

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 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 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.

Example of SQLite Case Statement

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,

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 STUDENT table execute following query to check records in 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 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 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.

 
 

 Comments (0)

Be the first to give your valuable feedback

Leave a comment

  • Default Tutlane User