What is SQL?
The abbreviation of SQL stands for Structured Query Language. This language is used to perform several operations in the database. It is called Query language, which was designed in the year 1974. It allows users to perform several operations like modify, create, retrieve, delete and several other functions. SQL is used to perform operations in Relational Database System. From 1970 to 1986, it started and developed the wide number of commands helps execute the operation in Relational Database System.
SQL Operator
As the name indicates, SQL Operators are used to perform some arithmetic operations or some complex calculations. Usually, in SQL, the calculations are performed in the Where class. It is a conditional statement used for specific operations.
The SQL operators are classified into six types. They are,
- Arithmetic Operator
- Bitwise Operator
- Comparison Operator
- Compound Operator
- Logical Operators
- String Operators
Arithmetic Operator
The usual arithmetic operator includes addition, Subtraction, multiplication, division and modulus operator.
Addition-It operates by adding two numbers
SELECT 11+11;
The ‘+’ symbol adds the two numbers.
Subtraction-It operates by subtracting the two numbers
SELECT 11-11;
The ‘-‘ symbol denotes subtracting the values from one another.
Multiplication-It operates by multiplying two numbers
SELECT 11*11;
This ‘*’ symbol resembles the multiplication of two values
Division-It operates by dividing one number by another
SELECT 11/11;
This ‘/’ symbol resembles the dividing the number by another number
Modulo-This operation is called modulus or remainder
SELECT 11%11;
This ‘%’ operates by returning the remainder while dividing each other.
Bitwise Operator
The bitwise operations are performed between two values or expressions. It includes Bitwise AND (&), Bitwise OR (|, ^), Bitwise Not (~).
Bitwise AND (&)
The bitwise AND operator compares the two values and returns the result according to that.
DECLARE @BitOne BIT =5
DECLARE @BitTwo BIT=6
Select @BitOne & @BitTwo;
The result will be displayed as 30.
DECLARE @BitOne BIT =1
DECLARE @BitTwo BIT=0
Select @BitOne & @BitTwo;
The result will be displayed as 0.
The bitwise AND Assignment (&=) is another type of operation performed in Bitwise AND.
If both the values are 1, the result is 1. If one is 0, the result is 0.
Bitwise OR (|, ^)
The bitwise OR operator represents the value in the format of if both the numbers are 0, then the result is 0. If one is 1, the result is displayed as 1.
DECLARE @BitOne BIT =1
DECLARE @BitTwo BIT=0
Select @BitOne | @BitTwo;
The result is displayed as 1.
The other type of operators performed are (|=), (^), that is, Bitwise OR Assignment, Bitwise exclusive OR, which operates similar to Bitwise OR, but it determines the variable’s value when the result is returned.
The Bitwise Not operator (~) displays the result as 0 if the value is 1 and 1 if the value is 0. It does the process of inversion.
Comparison Operator
As the name denotes, it performs the task by comparing each value to check whether they are the same. The comparison operator is subdivided into the following types.
Equal to (=) It checks whether the given value is equal to the assigned value.
Not equal to! = It checks whether the given value does not equal the assigned value.
Greater than > this operations check whether the value is greater than the assigned value
Not greater than!> this operation checks whether the value is not greater than the assigned value
Less than < It checks whether the value is lesser than the assigned value.
Not lesser than! < It checks whether the given value is not lesser than the assigned value.
Greater than equal to >= It checks whether the given value is not lesser than the assigned value
Lesser than equal to <= It checks whether the given value is not greater than the assigned value
<>Not equal to It checks whether the two values are not equal.
Compound Operator
The compound operator is subdivided into,
+= Add equal
-= Subtract equal
*= Multiply equal
/= Divide equal
%= modulo equals
Logical Operators
The logical operators are sub-divided into the following types,
ALL
ANY or SOME
AND
BETWEEN
EXIST
IN
LIKE
NOT
OR
IS NULL
String Operators
Concatenation is mainly used in string operators to combine two strings.
The string operators are classified as
+ String Concatenation
+= String Concatenation Assignment
% Wildcard
[] Character matches
[^] Character not to match
_(Wildcard match one Character)
Summary
The above article describes the various types of operators and their functions in SQL in detail. Each has its unique functions and properties.