An operator is a symbol which is used to perform some action on given expressions.
- Arithmetic Operators
- Logical Operators
- Set Operators
- Comparison Operators
- Special Operators
- Assignment Operators
- String Concat Operator
Arithmetic Operators
Arithmetic Operators are used for performing the mathematical operations on the given values.
Operator | Meaning |
+ | Add |
- | Subtraction |
* | Multiplication |
/ | Divide |
% | Modulo |
Create a Table with a Name student.
- create Table student(id int,name varchar(50),Eng int,hindi int,math int,totalmarks int,avg int)
Insert some values in the Table.
- insert into student(id,name,Eng,hindi,math)values(1,'A',65,58,49)
- insert into student(id,name,Eng,hindi,math)values(2,'B',45,33,41)
- insert into student(id,name,Eng,hindi,math)values(3,'C',65,88,35)
- insert into student(id,name,Eng,hindi,math)values(4,'D',61,88,62)
- insert into student(id,name,Eng,hindi,math)values(5,'E',41,81,81)
Example - Write a query to find total marks and average marks of a student
Total marks
- Update student set Totalmarks =Eng+hindi+math
Avg Marks
- update student set avg=Totalmarks /3
Select * from student
In the same manner, we can use others Arithmetic operators like -,*, %.
Assignment operator
The assignment operator is used to assign values to operands.
Assignment operator: =
Example
Write a query to display student details whose id=4?
- Select * from student where id=4
Result
Comparison Operators
Comparison Operators are used for comparing values with the given specific conditions.
Set Operators It is used to combine the result of 2 or more tables as a single set of values.
These operators are <,>,<=,>=,!=
Example
Write a query to display student details whose avg marks are greater than 60.
- select * from student where avg>60
Set Operators
It is used to combine the result of 2 or more Tables as a single set of values.
Type of Set Operators
- Union
- Union All
- Intersect
- Except
Some important Rules we should follow to use Set Operators
- Number of columns and order must be the same in all the queries.
- Columns Datatypes should be compatible.
- Create table class_A(id int,studnetname varchar(50),Marks int,Age int)
- Create table class_B(id int,studentname varchar(50),marks int ,Age int)
Insert values in Table class_A
- insert into class_A values(1,'A',88,21)
- insert into class_A values(2,'B',76,19)
- insert into class_A values(3,'C',81,22)
- insert into class_A values(4,'D',57,20)
Insert values in Table class_B
- insert into class_B values(1,'A',88,21)
- insert into class_B values(5,'E',57,20)
- insert into class_B values(6,'P',57,20)
Union
Union fetches all the values from the tables without Duplicates.
Example
Write a query to display all student details from both the classes,
- Select * from class_a
- Union
- Select * from class_b
Result
Union all
Union all is same as union but it returns duplicate values too.
- Select * from class_a
- Union all
- Select * from class_b
Result
We can see that in the union it does not return the duplicate values while in the union all duplicate values are returned.
Intersect
It returns the common values from the Tables.
Example
Write a query to display students' names who are present in both the classes,
- Select studnetname from class_a
- intersect
- Select studentname from class_b
Result
Except
Example - Write a query to display students' names who are in a class_a but not in class_b.
- Select studnetname from class_a
- except
- Select studentname from class_b
Result
Special Operators
These are special operators in SQL Server,
Between
Between is used to fetch values from a given Range. Between operator is to return the values from source values.
- It can be applied on small to big range values only.
- It doesn’t support Big to small values range.
- We should use and operator when we implement between operators.
Example
Write a query to get students whose age is between 22 to 26.
- Select * from class_A where age Between 20 and 22
In
It works on the given list of values in the given condition.
- It is an extension of Or operator.
- The performance of In operator is faster than OR operator. When we use OR operator we will repeat the column names again and again in the query but when we use In operator there is no need to repeat the column name in the query.
- In OR operator query length will be increased.
Example
Write a query to display student details whose names are a,c,d.
- select * from class_A where studnetname in('a','c','d')
IS NULL
IS NULL operator is to compare the values with Null in the table.
- It occupies 0-byte memory.
Like
Like is used to filter the values in the given expression or condition.
We can use like operator with this given expression. These are called Wildcard operators.
- % It represents any char in the given expression.
- -It represents a single char in the expression.
- []-it represents a group of char.
Example
Write a query to display student details whose name starts with a.
- select * from class_A where studnetname like 'a%'
Logical Operator
These are the logical operators,
AND - AND is used to perform the operations when given conditions should be true.
OR - OR is used to perform some operations if any one condition is true from given conditions.
NOT - We can understand the NOT operator with the help of the following example.
String Concatenation Operator
String concatenation operators are used to combine two or more char or columns into a single expression.
Following are the
String Concatenation Operator in SQL Server,
- + (String Concatenation)
- % (Wildcard Character)
- [ ] (Wildcard Character)
- _ (Wildcard Match One Character)