SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL OPERATORS SQL COMMANDS SQL Queries

SQL Table

SQL TABLE SQL CREATE TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL UPDATE TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT INTO Statement

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL

SQL CASE

This page contains all the information about SQL CASE. The CASE is an If-Else type of logical query used in the statement. The CASE in Structured Query Language is similar to nested If-Else in programming Languages.

The CASE Statement goes through a condition if the first condition is true. It returns a value; otherwise, jump to another condition. If that condition matches, it returns the value; otherwise, jump to the else part.

CASE statement can have one condition or can have multiple conditions.

If there is no Else part in the CASE statement, it returns NULL directly.

The CASE statement always has one WHEN and THEN pair or can have more than one pair.

The CASE statement starts with CASE keywords and ends with END keywords.

We can use the WHERE clause, ORDER BY clause, and GROUP BY clause in the SQL query with the CASE statement.

There are two types of CASE statements in RDBMS are as follows:

  1. Simple CASE Statement.
  2. Searched CASE Statement.

CASE Statement Syntax in Structured Query Language:

CASE <case-expression>
WHEN conditions THEN statement
ELSE result
END [as Alias Name];

The CASE statement can have multiple conditions.

In the syntax, CASE will start from the first condition. If conditions match what is in the WHEN clause, the THEN clause statement will get a return or be executed, and the rest of the conditions will be get ignored.

If the first condition didn't match the first WHEN conditions, then it will jump to the next WHEN conditions, and if that condition matches, THEN part will get a return or be executed.

If the second condition also didn't match, then it will move to another, and if it matches the conditions, return the statement, and if no conditions match, then else part will get executed.

Example of CASE statement in Structured Query Language:

Consider the already existing table with the following data:

Table Name: Employee_Details

E_IdE_NameE_SalaryE_CityDesignationAgeGender
1001Kirti Kirtane60000MumbaiProject Manager26F
1002Akash Vani40000PuneSystem Engineer22M
1003Anupam Mishra55000HyderabadProject Manager25M
1004Anuj Rawat45000HyderabadSoftware Tester25M
1005Akanksha Yadav42000PuneAssociate Software Developer23F
1006Bhavesh Wani50000MumbaiAssociate Software Developer24M
2001Sakshi Sharma40000BangaloreSystem Engineer23F
2002Megha Ghatole45000BangaloreSoftware Tester24F
2003Surbhi Nahar60000PuneProject Manager26F
2004Supriya Shende55000MumbaiSoftware Developer25F
2005Prachi Sharma52000HyderabadSoftware Developer24F
2006Purva Dhandekar50000BangaloreSoftware Tester23F
3001Shruti Deshpande60000PuneProject Manager26F
3002Rohit Nikam40000HyderabadSystem Engineer23M
3003Sahil Jain50000MumbaiSoftware Developer24M

Consider the already existing table with the following data:

Table Name: Comp

Comp_IdMakeE_Id
101Dell1006
102Dell1003
103Lenovo1001
104Lenovo1005
105HP2002
106HP2004
107Asus2006
108Asus2001
109Acer3001
110Acer3002

Example 1: We will first see the single-use of WHEN and THEN condition to the CASE Statement:

SELECT E_ID, E_NAME, E_SALARY, E_CITY, CASE WHEN E_SALARY > 50000 THEN 'SALARY INCREMENT BY 5%' ELSE 'SALARY INCREMENT BY 10%' END AS SALARY_INCREMENT FROM EMPLOYEE_DETAILS;

The CASE statement checks the first condition in the above query if the employee salary is greater than 50000. If yes, it will write "Salary increment by 5%". Else it will write "Salary increment by 10%" in the Salary_Increement column.

The output in the above query is as follows:

E_IDE_NAMEE_SALARYE_CITYSALARY_INCREEMENT
1001Kirti Kirtane60000MumbaiSALARY INCREMENT BY 5%
1002Akash Vani40000PuneSALARY INCREMENT BY 10%
1003Anupam Mishra55000HyderabadSALARY INCREMENT BY 5%
1004Anuj Rawat45000HyderabadSALARY INCREMENT BY 10%
1005Akanksha Yadav42000PuneSALARY INCREMENT BY 10%
1006Bhavesh Wani50000MumbaiSALARY INCREMENT BY 10%
2001Sakshi Sharma40000BangaloreSALARY INCREMENT BY 10%
2002Megha Ghatole45000BangaloreSALARY INCREMENT BY 10%
2003Surbhi Nahar60000PuneSALARY INCREMENT BY 5%
2004Supriya Shende55000MumbaiSALARY INCREMENT BY 5%
2005Prachi Sharma52000HyderabadSALARY INCREMENT BY 5%
2006Purva Dhandekar50000BangaloreSALARY INCREMENT BY 10%
3001Shruti Deshpande60000PuneSALARY INCREMENT BY 5%
3002Rohit Nikam40000HyderabadSALARY INCREMENT BY 10%
3003Sahil Jain50000MumbaiSALARY INCREMENT BY 10%
SQL CASE

Example 2: We will use the GROUP BY clause with the CASE Statement

SELECT E_ID, E_NAME, E_SALARY, E_CITY, CASE WHEN E_SALARY < 42000 THEN E_SALARY * 1.5 WHEN E_SALARY >= 42000 AND E_SALARY < 55000 THEN E_SALARY * 1.2 ELSE E_SALARY * 1 END AS EMPLOYEE_INC FROM EMPLOYEE_DETAILS GROUP BY E_NAME;

In the above query, we increment the salary of employees. If employee salary is less than 42000, then employee salary * 1.5, else if employee salary is greater than equal to 42000 and less than 55000, then employee salary increments by 1.2, else no increment of the employee in this query, we used GROUP BY Employee Name column.

The output of the above query is as follows:

E_IDE_NAMEE_SALARYE_CITYEMPLOYEE_INC
1005Akanksha Yadav42000Pune50400.0
1002Akash Vani40000Pune60000.0
1004Anuj Rawat45000Hyderabad54000.0
1003Anupam Mishra55000Hyderabad55000.0
1006Bhavesh Wani50000Mumbai60000.0
1001Kirti Kirtane60000Mumbai60000.0
2002Megha Ghatole45000Bangalore54000.0
2005Prachi Sharma52000Hyderabad62400.0
2006Purva Dhandekar50000Bangalore60000.0
3002Rohit Nikam40000Hyderabad60000.0
3003Sahil Jain50000Mumbai60000.0
2001Sakshi Sharma40000Bangalore60000.0
3001Shruti Deshpande60000Pune60000.0
2004Supriya Shende55000Mumbai55000.0
2003Surbhi Nahar60000Pune60000.0
SQL CASE

Example 3: Use of ORDER BY Clause with CASE Statement:

SELECT * FROM COMP ORDER BY CASE WHEN MAKE = 'DELL' THEN 0 ELSE 1 END, E_ID;

In the above query, we used the ORDER BY clause and displayed the data in order by making the name. Still, they also used one condition that employees' names are "Dell" should be displayed first and then the rest of the data order by make as well employee id.

The output of the above query is as follows:

Comp_IdMakeE_Id
102Dell1003
101Dell1006
103Lenovo1001
104Lenovo1005
108Asus2001
105HP2002
106HP2004
107Asus2006
109Acer3001
110Acer3002
SQL CASE

Example 4: In the given an example, we want to sort the result in the following methods:

  1. The employee salaries should come in ascending order where the employee gender is male.
  2. The employee salaries should come in descending order where employee gender is female.
SELECT E_ID, E_NAME, E_SALARY, E_CITY, GENDER FROM EMPLOYEE_DETAILS ORDER BY CASE GENDER WHEN 'M' THEN E_SALARY END ASC, CASE WHEN GENDER = 'F' THEN E_SALARY END DESC;

In the above, we sort the employee salary in ascending order if an employee is male; else employee salary will be descending where an employee is female.

The output of the above query is as follows:

E_IdE_NameE_SalaryE_CityGender
1001Kirti Kirtane60000MumbaiF
3001Shruti Deshpande60000PuneF
2003Surbhi Nahar60000PuneF
2004Supriya Shende55000MumbaiF
2005Prachi Sharma52000HyderabadF
2006Purva Dhandekar50000BangaloreF
2002Megha Ghatole45000BangaloreF
1005Akanksha Yadav42000PuneF
2001Sakshi Sharma40000BangaloreF
1002Akash Vani40000PuneM
3002Rohit Nikam40000HyderabadM
1004Anuj Rawat45000HyderabadM
1006Bhavesh Wani50000MumbaiM
3003Sahil Jain50000MumbaiM
1003Anupam Mishra55000HyderabadM
SQL CASE



ADVERTISEMENT
ADVERTISEMENT