Good Friday Sale : Flat 30% off on live classes + 2 free self-paced courses! - SCHEDULE CALL

- SQL Server Blogs -

Brief Introduction To Different SQL Server Operators



Introduction

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: 241 Excel Shortcut Keys that You Should Know in 2022-23

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.

types of sql operatorsIn 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.

Different Types of Operators in SQL Server

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 

1). Arithmetic Operator Examples

Following are the SQL server Arithmetic operators examples.

(+)

Adds two values together

select 1 + 2

Brief Introduction To Different Sql Server Operators 1

select 'a' + 'b'

Brief Introduction To Different Sql Server Operators 2

(-)

Subtracts one value from another

Read: Advanced SQL Server Interview Questions and Answers

select 2- 1

Brief Introduction To Different Sql Server Operators 3

(*)

Multiplies two values

Brief Introduction To Different Sql Server Operators 4

(/)

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%'

Brief Introduction To Different Sql Server Operators 7

Test Your Knowledge in SQL - Quiz for Your Self-Assessment

2). Bitwise Operators example

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

Brief Introduction To Different Sql Server Operators 8

3). Comparison operator Example

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

Brief Introduction To Different Sql Server Operators 9

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: DDL, DML, DCL, TCL & DQL -- The Complete SQL Commands Tutorial

select * from [Sales].[SalesOrderDetail] where UnitPrice

Brief Introduction To Different Sql Server Operators 9

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

Brief Introduction To Different Sql Server Operators 11

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

Brief Introduction To Different Sql Server Operators 12

Shows all records which are not equal to 400.

select * from [Sales].[SalesOrderDetail] where UnitPrice <> 400

Brief Introduction To Different Sql Server Operators

Free Demos for SQL server training available now, hurry and save the dates

SQL Server Training & Certification

  • No cost for a Demo Class
  • Industry Expert as your Trainer
  • Available as per your schedule
  • Customer Support Available

4). Compound Operator Examples

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;  

Brief Introduction To Different Sql Server Operators 13

(*=)

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;

Brief Introduction To Different Sql Server Operators14

(-=)

Subtracts some value with the existing value and outputs the result.

DECLARE @x2 int = 27;  

SET @x2 -= 2;  

SELECT @x2 AS Compoundsubstract;  

SELECT @x2 AS Compoundsubstract;

 (/=)

Divides some value with the existing value and outputs the result.

Read: What are Data Types and Their Usage in SQL Server Tables?

DECLARE @x4 int = 27;  

SET @x4 /= 2;  

SELECT @x4 AS Compounddivide;

Brief Introduction To Different Sql Server Operators

(%=)

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;

Brief Introduction To Different Sql Server Operators 15

(&=)

Performs a bitwise AND and adds with the original value

DECLARE @x6 int = 9;  

SET @x6 &= 13 ;  

SELECT @x6 AS Bitwise_AND; 

SELECT @x6 AS Bitwise_AND;

Try Free Demo of Instructor-led Live Online SQL Classes

5). Logical Operator Examples

Following are the SQL server logical operators examples:

All

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 

Brief Introduction To Different Sql Server Operators 16

ANY

Compares a single value with a column of values

Read: Introduction to Stored Procedures and its benefits

SELECT * FROM  [Person].[Address]

WHERE 79 > ANY

(

      SELECT StateProvinceID FROM [Person].[Address]

)

)

Brief Introduction To Different Sql Server Operators 17

Between

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

Brief Introduction To Different Sql Server Operators 18

Exists

Checks the existence of a row

SELECT * FROM [Person].[Address] WHERE EXISTS

(

 SELECT * FROM [Person].[Address]

WHERE StateProvinceID=79

)

Brief Introduction To Different Sql Server Operators 18IN

SELECT * FROM [Person].[Address]

WHERE StateProvinceID IN (79,77)

Brief Introduction To Different Sql Server Operators19

SQL Server Training & Certification

  • Personalized Free Consultation
  • Access to Our Learning Management System
  • Access to Our Course Curriculum
  • Be a Part of Our Free Demo Class

Summary

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: SQL Server Reporting Service: All You Need to Know about Parameterized Reports

SQL Tutorial Overview

fbicons FaceBook twitterTwitter google+Google+ lingedinLinkedIn pinterest Pinterest emailEmail

     Logo

    Sanchayan Banerjee

    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.


Comments

Trending Courses

Cyber Security Course

Cyber Security

  • Introduction to cybersecurity
  • Cryptography and Secure Communication 
  • Cloud Computing Architectural Framework
  • Security Architectures and Models
Cyber Security Course

Upcoming Class

0 day 29 Mar 2024

QA Course

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing
QA Course

Upcoming Class

0 day 29 Mar 2024

Salesforce Course

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL
Salesforce Course

Upcoming Class

-1 day 28 Mar 2024

Business Analyst Course

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum
Business Analyst Course

Upcoming Class

0 day 29 Mar 2024

MS SQL Server Course

MS SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design
MS SQL Server Course

Upcoming Class

7 days 05 Apr 2024

Data Science Course

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning
Data Science Course

Upcoming Class

0 day 29 Mar 2024

DevOps Course

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing
DevOps Course

Upcoming Class

7 days 05 Apr 2024

Hadoop Course

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation
Hadoop Course

Upcoming Class

0 day 29 Mar 2024

Python Course

Python

  • Features of Python
  • Python Editors and IDEs
  • Data types and Variables
  • Python File Operation
Python Course

Upcoming Class

7 days 05 Apr 2024

Artificial Intelligence Course

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks
Artificial Intelligence Course

Upcoming Class

8 days 06 Apr 2024

Machine Learning Course

Machine Learning

  • Introduction to Machine Learning & Python
  • Machine Learning: Supervised Learning
  • Machine Learning: Unsupervised Learning
Machine Learning Course

Upcoming Class

21 days 19 Apr 2024

 Tableau Course

Tableau

  • Introduction to Tableau Desktop
  • Data Transformation Methods
  • Configuring tableau server
  • Integration with R & Hadoop
 Tableau Course

Upcoming Class

7 days 05 Apr 2024

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews