SQL SERVER -- Case Statements -- Part-11

Case Statement:-

                                      Case statements are used to validate conditions same as IF ELSE statements

-- How to Create New DataBase

CREATE DATABASE SQLSERVER

GO

-- How to go inside to the Database

USE SQLSERVER

GO

-- How to Create New Table

CREATE TABLE EMP

(

       EMPID INT

      ,EMPNAME VARCHAR(50)

      ,SAL MONEY

      ,DOJ DATE

      ,LOC VARCHAR(50)

      ,GENDER CHAR(6)

      ,MagrID INT

      ,DEPTNO INT

)

-- INSERT Sample Data Into the Table.

INSERT INTO EMP(EMPID, EMPNAME, SAL, DOJ, LOC, GENDER, MagrID, DEPTNO)

VALUES(1,'Santhosh',10000,'01-12-2014','Bangalore','M',3,101)

INSERT INTO EMP

VALUES(2,'Kumari',8000,'11-05-2013','Chennai','F',3,104)

INSERT INTO EMP(EMPID, EMPNAME, SAL, DOJ, LOC, GENDER, MagrID, DEPTNO)

VALUES(3,'Kamesh',75000,'01-12-2012','Mumbai','M',NULL,105)

INSERT INTO EMP(EMPID, EMPNAME, SAL, LOC, GENDER, MagrID, DEPTNO)

VALUES(4,'Arun',44000,'Hyderabad','m',5,103)

INSERT INTO EMP(EMPID, EMPNAME, SAL, DOJ, LOC, GENDER, MagrID, DEPTNO)

VALUES(5,'Aishwarya', 15000, '01-12-2014','Bangalore','f',3,103)

Case Statement with SELECT statement:-

-- Case Statement with SELECT statement

SELECT EMPNAME

      ,SAL

         ,CASE WHEN SAL BETWEEN 0 AND 10000 THEN 'LowSalary'

               WHEN SAL BETWEEN 10001 AND 30000 THEN 'AvgSalary'

          ELSE 'HighSalary' END AS SalaryGrade

FROM EMP

Case Statement with UPDATE statement:-

-- Case Statement with UPDATE statement

UPDATE EMP

SET SAL= CASE WHEN SAL>0 AND SAL<=10000 

                        THEN SAL+5000

              WHEN SAL>10000 AND SAL<=30000 

                        THEN SAL+3000

         ELSE SAL+1000 END