SQL CASE STATEMENT & SQL CHECK CONSTRAINT

SQL CASE STATEMENT

SQL CASE STATEMENT

SQL CASE statement is used to provide Conditional (if-then-else) type of logic in 

SQL.

SYNTAX

CASE column_name WHEN condition THEN result ELSE result_2 END

EXAMPLE

E-Id         LastName          FirstName               Department

1              Jameson                John                   H

2              Smith                      Kate                   H

3              Kristensen              Olya                   S

4              Nilsen                    Tomas                 N

Now we want to change “H” to “Hardware” and “S” to “Service”.

EXAMPLE

SELECT FirstName, LastName, Department =CASE Department

WHEN ‘H’ THEN ‘Hardware’ WHEN’S’ THEN ‘Service’ ELSE ‘Other’ END

FROM Employees

RESULT

LastName                FirstName          Department

Jameson                  John                   Hardware

Smith                        Kate                   Hardware

Kristensen                Olya                   Service

Nilsen                       Tomas                Other

SQL CHECK CONSTRAINT

SQL CHECK CONSTRAINT

The CHECK constraint is used to limit the value range that can be placed in a 

column.

SQL CHECK CONSTRAINT ON CREATE TABLE

SQL creates a CHECK constraint on the “P_ld” column when the ” Persons” 

table is created. The CHECK constraint specifies that the column ” P_ ld” must

only include integers greater than 0.

CREATE TABLE Persons

(

P-ld int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255)

City varchar (255),

CHECK (P_ Id>0))

SQL CHECK CONSTRAINT ON ALTER TABLE

To create a CHECK constraint on the “P_ Id” column when the table is 

already created, use the following SQL:

ALTER TABLE Persons

ADD CHECK (P_Id>0)

TO DROP A CHECK CONSTRAINT

To drop a CHECK constraint, use the following SQL:

ALTER TABLE Persons DROP CONSTRAINT chk_Person