SQL Constrains

Constrains are the limitations or rules. We are controlling the data using Constrains.

 

NOT NULL Constrains

We can create a table without null values using NOT NULL constrains.

Example:

 

CREATE TABLE EMPLOYEE(

EmpID INT NOT NULL,

EmpName varchar(255) NOT NULL,

EmpMail varchar(255) NOT NULL

);

PRIMARY KEY Constrains

• Primary key must contain unique values
• Primary Key cannot hold any null values
• Each column can have only one primary key
• Primary keys can contain single and multiple columns

Example:

CREATE TABLE EMPLOYEE(

EmpID INT NOT NULL PRIMARY KEY,

EmpName varchar(255) NOT NULL,

EmpMail varchar(255) NOT NULL

);

INSERT INTO EMPLOYEE VALUES(1,'TOM','tom@gmail.com');

INSERT INTO EMPLOYEE VALUES(1,'TOM','tom@gmail.com');

It will throw an error like:

Error: near line 8: UNIQUE constraint failed: EMPLOYEE.EmpID

Primary Key on Multiple columns

Example:

CREATE TABLE EMPLOYEE(

EmpID INT NOT NULL,

EmpName varchar(255) NOT NULL,

EmpMail varchar(255) NOT NULL,

CONSTRAINT PK_EMPLOYEE PRIMARY KEY(EmpID,EmpName)

);

INSERT INTO EMPLOYEE VALUES(1,'TOM','tom@gmail.com');

INSERT INTO EMPLOYEE VALUES(1,'TOMY','tom@gmail.com');

Select * from EMPLOYEE

When we give same values for ID and First name like

INSERT INTO EMPLOYEE VALUES(1,'TOM','tom@gmail.com');

INSERT INTO EMPLOYEE VALUES(1,'TOM','tom@gmail.com');

It will throw an error like:

Error: near line 11: UNIQUE constraint failed: EMPLOYEE.EmpID, EMPLOYEE.EmpName

Add primary key to an existing table

ALTER TABLE EMPLOYEE ADD PRIMARY KEY(EmpName);

Drop primary key of an existing table

ALTER TABLE EMPLOYEE DROP PRIMARY KEY;

Interview Question: Nth Highest Salary

1.To Find the maximum salary first create an Employee table:

create table Employee (

    EmpID int,

    EmpName varchar(255),

    EmpPhNum int,

    EmpAge int,

    EmpAddress varchar(255),

    EmpSalary int

);

2.Then insert data into the table:

insert into Employee values(1,'John',989756,21,'Street 1,LF',10000);

insert into Employee values(2,'JohnP',989757,24,'Street 2,GH',50000);

insert into Employee values(3,'Peter',989456,27,'Street 3,CF',30000);

insert into Employee values(3,'Peter',989456,27,'Street 3,CF',40000);

insert into Employee values(4,'Johnson',983656,45,'Street 4,WE',75000);

insert into Employee values(5,'Johny',989866,25,'Street 5,DF',85000);

insert into Employee values(6,'Johny',989866,35,'Street 5,DF',60000);

3.Check whether all the data is inserted properly into the table

select * from Employee;

4.Get the maximum salary in the table using the max function

select max(EmpSalary) from Employee;

5.To get the second highest salary in a table, we need to use the inner query as below:

For second highest salary we need to use 1 inner query.

select max(EmpSalary) from Employee where EmpSalary<(select max(EmpSalary) from Employee);

For 3rd highest salary we need to use 2 inner query

select max(EmpSalary) from Employee where EmpSalary<(select max(EmpSalary) from Employee where EmpSalary < (select max(EmpSalary) from Employee));

For nth highest salary we need to use n-1 inner query

6.We can simplify this with LIMIT option

select * from Employee LIMIT 2;

It will list the first 2 rows

For second higest salary

select EMPSalary from Employee order by EMPSalary desc LIMIT 2-1,1; 

For third higest salary

select EMPSalary from Employee order by EMPSalary desc LIMIT 3-1,1; 

For nth higest salary

select EMPSalary from Employee order by EMPSalary desc LIMIT n-1,1;

Operators and Keywords in SQL

1.Read the data with ‘Order by’

select * from Employee order by EmpAge;

By default, it sorts the value in ascending order or we can use:

 select * from Employee order by EmpAge;

For sorting the value in descending order:

select * from Employee order by EmpAge DESC;

When we give the query like below it first gives the priority to EmpAge then to EmpID:

select * from Employee order by EmpAge, EmpID;

2.Read the data using the ‘AND’ Operator

select * from Employee where EmpID>2 and EmpAge<30;

3.Read the data using the ‘OR’ Operator

select * from Employee where EmpID>2 AND EmpAge<30;

select * from Employee where Empname='Johny' and (Empage=25 or EmpPhNum=989866)

4.Read the data using the ‘NOT’ keyword

select * from Employee where not EmpAge>30;

select * from Employee where EmpPhNum='989866' and not Empage=25

5.Read the data using the LIKE keyword

Query for the data starts with John:

select * from Employee where EmpName Like 'John%';

Query for the data ends with son:

select * from Employee where EmpName Like '%son';

Query for the data have ‘oh’ in the middle:

select * from Employee where EmpName Like '%oh%';

Query for the data ends with ‘n’ and another one character. Here _ represent a character

select * from Employee where EmpName Like '%n_';

Query for the data start with ‘J’ and ends with ‘n’.

select * from Employee where EmpName Like 'J%n'

6.Read the data using ‘NULL’ keywords

select * from Employee where EmpPhNum is NULL

select * from Employee where EmpPhNum is NOT NULL