Dec 10, 2019 
5.3k
29
NovBlack Friday Deal : Up to 40% OFF! + 2 free self-paced courses + Free Ebook - SCHEDULE CALL
Black Friday Deal : Up to 40% OFF! + 2 free self-paced courses + Free Ebook - SCHEDULE CALL
- SQL Server Blogs -
Writing SQL queries is an essential part of any software development package. We use queries to get data from a database; we need them to insert data into the database and so on. Most of the data that we work with when we write a query is stored within the database tables. But there are some values that are created dynamically. For example, in most cases, you only store the date of birth of a certain employee in the employee details table. But how would you get the present age of that employee? Similarly, how would you calculate the years of service of a particular employee if you know his date of joining?
Read: SAS Tutorial Guide for Beginners
This is where SQL Server Operators come into play. There are different operators like Arithmetic, Comparison or Logical operators each of them has a separate purpose.
In the following, we will try to give you a sneak peek of these different SQL Server Operators along with their usages to clear your basic SQL concepts before you approach advanced SQL class.
Following are the types of operators in SQL server:
|
SQL Server Arithmetic Operators |
SQL server Bitwise Operators |
SQL Server Comparison Operators |
SQL Server Compound Operators |
SQL Server Logical Operators |
|
Operators used for arithmetic operations in query |
Used to perform bit manipulation in SQL |
Used to compare one expressions with another |
Execute some operation and set an original value to the result of the operation |
Compare two conditions at a time to determine whether a row can be selected for the output |
|
Examples are +,-,*,/,% |
Examples are &,I,^ |
Example are >,<,=,>=,<=,<> |
Example are +=,*=,-=,/=,%=,&= |
Example are ALL,AND,ANY,BETWEEN, EXISTS,IN |
Following are the SQL server Arithmetic operators examples.
(+)
Adds two values together
select 1 + 2
select 'a' + 'b'
(-)
Subtracts one value from another
Read: SQL Server Recovery Models-Simple, Full and Bulk Log
select 2- 1
(*)
Multiplies two values
(/)
Divides one value from another.
select 4/2
(%)
Shows all the value that begins with a particular character
select * from [Person].[Person] where FirstName like 'N%'
Following are the few SQL server Bitwise operators examples.
(&)
Performs bitwise AND between two values
Select 2 & 2
(I)
Performs bitwise OR
select 1 | 2
(^)
Used to perform a bitwise XOR
select 1 ^ 0
Following are the SQL server comparison operators examples.
(>)
Shows those records which is greater than the provided value
select * from [Sales].[SalesOrderDetail] where UnitPrice > 400
Here the above SQL Statement returns all records from SalesOrderDetail table which has a unit price greater than 400.
(<)
Shows those records which are less than a certain value
Read: What is the Difference Between Control Flow & Data Flow in SSIS?
select * from [Sales].[SalesOrderDetail] where UnitPrice
Here the above SQL Statement returns all records from SalesOrderDetail table which has unit price less than 400.
(=)
Shows those records where UnitPrice is equal to 400
select * from [Sales].[SalesOrderDetail] where UnitPrice = 400
Here the above SQL Statement returns all records from SalesOrderDetail table which has unit price equal to 400.
(>=)
Shows those records where row values are greater than equal to a given value
select * from [Sales].[SalesOrderDetail] where UnitPrice >= 400
Shows those values where UnitPrice is greater than equal to 400.
(<=)
Shows those records where row values are greater than equal to a given value
select * from [Sales].[SalesOrderDetail] where UnitPrice <= 400
Shows all records which are not equal to 400.
select * from [Sales].[SalesOrderDetail] where UnitPrice <> 400
Free Demos for SQL server training available now, hurry and save the dates
SQL Server Training & Certification
Following are the SQL server compound operators examples.
(+=)
Adds some value to the existing value and outputs the result
DECLARE @x1 int = 27;
SET @x1 += 2;
SELECT @x1 AS Compoundadd;
(*=)
Read: What is NoSQL? NoSQL Tutorial Guide for Beginner
Multiply some value with the existing value and output the result
DECLARE @x3 int = 27;
SET @x3 *= 2;
SELECT @x3 AS compoundmultiply;
(-=)
Subtracts some value with the existing value and outputs the result.
DECLARE @x2 int = 27;
SET @x2 -= 2;
SELECT @x2 AS Compoundsubstract;
(/=)
Divides some value with the existing value and outputs the result.
Read: Snapping a Picture of a Database-Database snapshot
DECLARE @x4 int = 27;
SET @x4 /= 2;
SELECT @x4 AS Compounddivide;
(%=)
Divides by an amount and adds with the original value to the result.
DECLARE @x5 int = 27;
SET @x5 %= 2;
SELECT @x5 AS Modulo_of_27_divided_by_2;
(&=)
Performs a bitwise AND and adds with the original value
DECLARE @x6 int = 9;
SET @x6 &= 13 ;
SELECT @x6 AS Bitwise_AND;
Try Free Demo of Instructor-led Live Online SQL Classes
Following are the SQL server logical operators examples:
Gives true if all values in the comparison set is true
SELECT * FROM
[Person].[Address]
WHERE 'Ottawa' > ALL
(
SELECT City FROM [Person].[Address]
)
And
Does a Logical AND process
SELECT * FROM [Person].[Address]
WHERE (StateProvinceID > 79 AND StateProvinceID
Compares a single value with a column of values
Read: How to Create Stored Procedure & Trigger in SQL Server
SELECT * FROM [Person].[Address]
WHERE 79 > ANY
(
SELECT StateProvinceID FROM [Person].[Address]
)
)
Uses a query within a query to check the existence of a certain row of data
SELECT * FROM [Person].[Address]
WHERE StateProvinceID BETWEEN 79 AND 80
Checks the existence of a row
SELECT * FROM [Person].[Address] WHERE EXISTS
(
SELECT * FROM [Person].[Address]
WHERE StateProvinceID=79
)
IN
SELECT * FROM [Person].[Address]
WHERE StateProvinceID IN (79,77)
SQL Server Training & Certification
The above document gives a comprehensive study of different SQL Server Operators and their uses to clear your basic SQL concepts before you approach for advanced SQL class
It gives query syntaxes of types of operators in SQL serveralong with a screenshot of the output.
This would give the reader a fair bit of idea of what are the different types of SQL Operators currently in practice and how to use them. If you want detailed comprehension of the SQL server operators, their types like SQL server bitwise operators and uses of all operators in SQL server, enroll for the online certification training SQL class with JanBask Training to become a competent SQL professional.
Read: What Is The Difference Between Having And Where Clause In SQL?
FaceBook
Twitter
LinkedIn
Pinterest
Email
I love to learn new things and also like sharing my knowledge with others. As an experienced IT Professional I like to update myself constantly with new and upcoming technologies. The database management system is one of my favorite subjects which I constantly explore.
Cyber Security
QA
Salesforce
Business Analyst
MS SQL Server
Data Science
DevOps
Hadoop
Python
Artificial Intelligence
Machine Learning
Tableau
Search Posts
Related Posts
SQL BETWEEN: Retrieve Desired Range of Values with Examples
4k
Difference Between Stored Procedure and Function in SQL Server
11.7k
SQL Server Analysis Services – All You Need to Know
4.2k
How to Prevent SQL Injection Attacks?
6.9k
SQL Server DBA Roles and Responsibilities: What Should You Know?
16.6k
Receive Latest Materials and Offers on SQL Server Course
Interviews
Oct 04, 2024
4k
