Operator in SQL Server

Operator:
  - Operators are used to performing operations on two or more operands.

Example:      

       2 + 3  = 5 Here 2 and 3 are operands and + is the operator. So + operator perform addition operation on 2 and 3 operands

  - There are four types of operators.

1. Comparison Operator

2. Logical Operator

3. Arithmetic Operator

4. In and Not In

5. Between and Not Between

6. Like
   
   

For Working on the below operator we first create the below table and insert data into the table.

create table employee1 (EID int, FirstName varchar(20), LastName varchar(20), Loc varchar(20), Dept varchar(20), salary int)

insert into employee1 values(1,'Rohan','Mane','Sangali','HR',15000)

insert into employee1 values(2,'Sheetal','Chavan','Parbhani','Finance',25000)

insert into employee1 values(3,'Amit','Patil','Latur','HR',16000)

insert into employee1 values(4,'Riya','Verma','Pune','Account',20000)

insert into employee1 values(5,'Sita','Sharma','Patna','HR',15000)

insert into employee1 values(6,'Kirti','Gold','Solapur','Stafing',35000)

insert into employee1 values(7,'Sohan','Jadhav','Miraj','Account',45000)

insert into employee1 values(8,'Priyanka','Sharma','Nagpur','Finance',46000)

insert into employee1 values(9,'Virat','Patil','Jaipur','Stafing',34000)

insert into employee1 values(10,'Sohil','Khan','Mumbai','HR',33000)

insert into employee1 values(11,'Ronit','Patil','Miraj','Admin',NULL)

insert into employee1 values(12,'Satyam','Patil','Solapur','Admin','')

 1. Comparison Operator

   1.1 Equal To (=)

   1.2 Greater Than (>)

   1.3 Less Than (<)

   1.4 Greater than equal To (>=)

   1.5 Less Than equal To (<=)

   1.6 Not Equal To (!= or <>)

1.1 Equal To operator

• When two operands are the same then only it will return true. 

   select * from employee1 where salary = 15000

   select * from employee1 where EID = 7

   select FirstName,Dept from employee1 where EID = 9

  Get all employee details who working under admin DEpt

   select * from employee1 where dept = 'Admin'

1.2 Greater Than (>)

• When 1st operands is greater than 2nd operands then only it will return true otherwise return false.

select * from employee1 where salary > 15000

1.3 Less Than (<)

• When 1st operands is less than 2nd operands then only it will return true otherwise return false.

select * from employee1 where salary < 46000

  1.4 Greater than equal To (>=)

• When 1st operands are greater than 2nd operands as well as if they match exactly then only it will return true otherwise return false.

select * from employee1 where salary >= 15000

  1.5 Less than equal To (<=)

• When 1st operands are less than 2nd operands as well as if they match exactly then only it will return true otherwise return false.

select * from employee1 where salary <= 46000

 1.6 Not Equal To (!= or <>)

• When 1st operands are not matched with 2nd operands then only it will return true otherwise return false.

To get all record whose salary is not equal to 45000

select * from employee1 where salary != 45000

2. Logical Operator

  2.1. AND

  2.2 OR

2.1 And
   - By using and operator we are able to perform two operations when 1st operation and 2nd operation result are true then only it will return true otherwise return false.

To get all details whose salary is greater than 15000 and less than 25000

select * from employee1 where salary > 15000 and salary < 25000

To get fisrtname and loc of employees whose working in HR and finace department

select FirstName,Loc from employee1 where dept = 'hr' and dept = 'finace'

2.1 OR
   - By using OR operator we are able to perform two operations when 1st operation and 2nd operation result are false then only it will return false otherwise return true.

select * from employee1 where dept = 'HR' or dept = 'finance'

To get empid,lastname and loc of employee whose salary is greater than 15000 or less than 50000

select EID,LastName,loc from employee1 where salary > 15000 or salary < 50000

To get empid,lastname and Salary of employee whose salary is greater than 15000 or working in HR department.

select EID,LastName,Salary from employee1 where salary > 15000 or dept = 'HR'

3. Arithmetic Operator
- It is used to perform arithmetic operations such as +,-,*,/ and %.

• To Just show salary of all employee by adding 3000 in existing salary without updating table value
  Select MonthlySalary = salary + 3000 from employee1
  
  

• Get all details with monthly increment salary

select *,MonthlySalary = salary + 3000 from employee1

• Get only EID,FirstName from table and show monthly increment salary

select EID,FirstName,MonthlySalary = salary + 3000 from employee1

• Add 1000 in all employee salary 

       Update employee1 set salary = salary + 1000

Minus all employee salary by 1000

    Update employee1 set salary = salary - 1000

• EID 10 has took 5 leaves in current month then how much salary he will get in current month

Select currentMonthSalary = Salary - 5*(salary/30) from employee1 where EID = 10

Get all employee loss of pay for the current month considering every employee took 2 days leaves.

Select lossofpay =  2*(salary/30) from employee1

Suppose if we want to get employee details whose employee id are 3,6,8
Select * from employee1 where EID = 3,6,8 //Incorrect syntax near ','.

Second way is using OR operator
Select * from employee1 where EID = 3 or EID = 6 or EID = 8

Suppose we want to more than 10 employee details based on employee id then query become large not good for read also so sql server introduce a IN operator

4. IN and NOT IN
IN: 

• Return all records those mentioned in the IN clause

• Allowing us  to provide multiple values in the Where clause.

• Best replacement for multiple OR conditions

Select * from employee1 where EID IN (3,6,8)

Get all empluyees whose salary is 1600,45000,50000

Select * from employee1 where salary IN (16000,45000,50000)

NOT IN

• Return all records those mentioned in the Not IN clause (opposite to IN clause)

Select * from employee1 where EID Not IN (3,6,8)

Get all empluyees whose salary is 1600,45000,50000

Select * from employee1 where salary NOT IN (16000,45000,50000)

5. Between and NOT Between

Between:

• Display all records/rows from table between the range we provided 

• This operator works along with and operator

select * from employee1 where EID between 4 and 7

Get all employee details whose loc is between A and N

select * from employee1 where Loc between 'A' and 'N'  //excluding 'A' and 'N'

select * from employee1 where Loc between 'Ad' and 'Np' //If we added any extra character it include both character

Not Between:

• Display all records/rows from table Not between the range we provided 

• This operator works along with and operator

select * from employee1 where EID not between 4 and 7

Get all employee details whose loc is not between A and N

select * from employee1 where Loc not between 'A' and 'N'  //including 'A' and 'N'

select * from employee1 where Loc not between 'A' and 'Np' //If we added any extra character it exclude both character

- Rename Column Name
Syntax:
EXEC sp_RENAME tblName.oldColumnName', newColumnName

Example

EXEC sp_RENAME 'employee.emp_name', 'firstName'

- Rename Table NAME

Syntax:
EXEC sp_RENAME ‘tblName’,’ newtblname’

Example

EXEC sp_RENAME 'employee', 'MyEmployee'