### Project / Work Support

Looking for some support who can help with difficult task/s and be there when you need them, TechBrothers have got your back!

OR

Have small or big project that needs to be finished by professionals on time, we are here to help.

We provide following services

• SQL Server Developement
• MSBI ( SSIS, SSRS,SSAS )
• Team Foundation Server (TFS)
• Setting up Windows Clusters
• MS Dynamix
• Data Warehouse
Either you need monthly/ yearly support or one time. Try TechBrothersIT one time!

Email : aamirsqlage@gmail.com
Phone : 505-414-1969

### What Arithmetic Operators are available in SQL Server - SQL Server / TSQL Part 129

Arithmetic Operators are used to perform mathematical operations such as Addition, Subtraction, Multiplication and Division etc.

1 ) + will be used for Addition
2) - will be used for Subtraction
3) * will be used for Multiplication
4) / will be used for Division
5) % will be used for Modulo ( Returns the integer remainder of a division)

Let's write our query to perform these operations

```Select
5-2 as Subtraction,
5*6 as Multiplication,
40/5 as Division,
6/4 as IntDivision,
6/4.0 as NumericDivision,
7%2 as Modulos```

 How to perform Arithmetic Operations in SQL Server - SQL Server / TSQL Tutorial

Noticed that when you divided 6/4 both integers, the output returned will be integer. In our case it returned 1. To get your output in decimals ,At least one of dividend or divisor need to be numeric(decimal).

I used static values for arithmetic operations. you can multiple columns, make sure they are numeric data types.

Addition ( + )  can also be used to concatenate the string in TSQL.

`Select 'Aamir ' + 'Shahzad' as MyName`
 How to use Addition Sign to perform Concatenation in SQL Server - SQL Server / TSQL Tutorial

The Addition and Subtraction signs can also be used to perform arithmetic operations on datetime and smalldatetime values.

Let's say that if I would like to add or subtract few days from Current date, I can use below query.

```Select GETDATE()+5 AS Add5eDaystoCurrentDate,
GETDATE()-30 AS Subtract30DaysfromCurrentDate```

### How to use Assignment Operator in SQL Server - SQL Server / TSQL Tutorial Part 128

The equal ( = ) sign is used in TSQL as assignment operator. You will be using assignment operator many time when you write your TSQL Code.

1) Assign Value to Variable
Assignment Operator can be used to set the value for variable. You can simply assign static value to variable by using Assignment Operator or value returned by query.

```Declare @i int
SET @i=2000
Print @i

Declare @Cnt int
SET @Cnt=(Select COUNT(*) from dbo.Customer)
Print @Cnt

Declare @CntRow int
Select @CntRow=Count(*) from dbo.Customer
Print @CntRow```

2) Let's say you have dbo.Customer table and you want to add a static value column, You can use Assignment Operator to assign value to newly added column. In below example I am adding Region column by assigning 'North America'.

```--Create Customer Table
Create table dbo.Customer
(Id int,
FName VARCHAR(50),
LName VARCHAR(50),
CountryShortName CHAR(2),
Age tinyint)
GO
--Insert Rows in dbo.Customer Table
insert into dbo.Customer
Values (
1,'Raza','M','PK',10),
(2,'Rita','John','US',12),
(3,'Sukhi','Singh',Null,25),
(4,'James','Smith','CA',60),
(6,'Alice','John','US',87)```

Also we can use the assignment operator for Alias. I have used CustomerID alias for ID column by using assignment operator.

```Select
CustomerId=Id,
FName,
LName,
CountryShortName,
Age,Region='North America'
From dbo.Customer```
```
```

 What is Assignment Operator in SQL Server and How to use Assignment Operator - SQL Server / TSQL Tutorial

### How to use ANY / SOME Logical Operator in SQL Server - SQL Server / TSQL Tutorial Part 127

ANY Logical operator returns TRUE if any one of a set of comparisons are TRUE. ANY compares a scalar value with a single column set of values.

Note :  SOME and ANY are equivalent. We are going to use ANY in our below examples.

## Scenario :

Let's say that we  have two tables dbo.Customer and dbo.Customer1. Both tables has the column Age. If you need to get all the records from dbo.Customer table where Age is at-least greater than one value from Age column from dbo.Customer1 table.

## Solution:

We can use subquery and MIN function to write our query for above requirement. Let's create the tables first.

```--Create Customer Table
Create table dbo.Customer
(Id int,
FName VARCHAR(50),
LName VARCHAR(50),
CountryShortName CHAR(2),
Age tinyint)
GO
--Insert Rows in dbo.Customer Table
insert into dbo.Customer
Values (
1,'Raza','M','PK',10),
(2,'Rita','John','US',12),
(3,'Sukhi','Singh',Null,25),
(4,'James','Smith','CA',60),
(6,'Alice','John','US',87)

--Create dbo.Customer1 table
Create table dbo.Customer1
(Id int,
FName VARCHAR(50),
LName VARCHAR(50),
CountryShortName CHAR(2),
Age tinyint)
GO
--Insert rows in dbo.Customer1 Table
insert into dbo.Customer1
Values
(7,'Raza','M','US',33),
(8,'Dita','M','US',15),
```

1) Get all the records from dbo.Customer table where Age is greater than min Age value of dbo.Customer1 table by using Subquery and Min function.
```
```
```Select * From dbo.Customer
where Age> ( Select MIN(age) from dbo.Customer1)```

2) Use ANY to get required results.
We can use ANY instead of using Min function with subquery.  As we want to get all rows from dbo.Customer where Age is greater than any value of Age column in dbo.Customer, We will use >Any.
>ANY means greater than at least one value, that is, greater than the minimum.

```Select * From dbo.Customer
where Age>ANY ( Select age from dbo.Customer1)```

We got the same records what were returned by our first query.
If you will use =ANY that is equal to IN. With ANY you can use different comparison operators such as = , <> , != , > , >= , !> , < , <= , !<

Video Demo : How to use ANY / SOME Logical Operator in SQL Server

### How to use ALL Logical Operator in SQL Server - SQL Server / TSQL Tutorial Part 126

ALL Logical operator returns TRUE if all of a set of comparisons are TRUE. ALL compares a scalar value with a single column set of values.

Let's understand ALL with examples.

## Scenario :

Think about a scenario where we have two tables dbo.Customer and dbo.Customer1. Both tables has the column Age. If you need to get all the records from dbo.Customer table where Age is greater than maximum value of Age column in dbo.Customer1 table.What would be your query.

## Solution:

We can use subquery and max function to write our query for above requirement. Let's create the tables first.

```--Create Customer Table
Create table dbo.Customer
(Id int,
FName VARCHAR(50),
LName VARCHAR(50),
CountryShortName CHAR(2),
Age tinyint)
GO
--Insert Rows in dbo.Customer Table
insert into dbo.Customer
Values (
1,'Raza','M','PK',20),
(2,'Rita','John','US',12),
(3,'Sukhi','Singh',Null,25),
(4,'James','Smith','CA',60),
(6,'Alice','John','US',87)

--Create dbo.Customer1 table
Create table dbo.Customer1
(Id int,
FName VARCHAR(50),
LName VARCHAR(50),
CountryShortName CHAR(2),
Age tinyint)
GO
--Insert rows in dbo.Customer1 Table
insert into dbo.Customer1
Values
(7,'Raza','M','US',33),
(8,'Dita','M','US',15),

1) Get all the records from dbo.Customer table where Age is greater than maximum Age value of dbo.Customer1 table by using Subquery and Max function.

2) using ALL with SubQuery
For above requirement we can use ALL logical operator. In that case we don't have to use Max function. ALL is going compare our outer query value to set of values from subquery. We can use >All,  >ALL means greater than every value returned by subquery, In other words greater than max value.

```Select * From dbo.Customer
where Age> All ( Select age from dbo.Customer1)```
 How to use ALL Logical Operator in SQL Server - SQL Server / TSQL Tutorial

With ALL you can use different comparison operators such as = , <> , != , > , >= , !> , < , <= , !<

Video Demo : How to use ALL Logical Operator in SQL / TSQL

### How to use EXISTS Logical Operator in SQL Server - SQL Server / TSQL Tutorial Part 125

Exists returns TRUE if a subquery contains any rows. EXISTS is used when we want to test for the existence of rows specified by a subquery.

Let's create dbo.Customer and dbo.Country Table and then use EXISTS to return records for different scenarios.

```Create table dbo.Customer
(Id int,
FName VARCHAR(50),
LName VARCHAR(50),
CountryShortName CHAR(2),
Age tinyint)
GO
insert into dbo.Customer
Values (
1,'Raza','M','PK',20),
(2,'Rita','John','US',12),
(3,'Sukhi','Singh',Null,25),
(4,'James','Smith','CA',60),
(6,'Alice','John','US',87),
(7,'Raza','M','US',33),
(8,'Dita','M','US',15),

Create table dbo.Country (
CId tinyint,
CountryName VARCHAR(50),
CountryShortName CHAR(2))
go
Insert into dbo.Country
Values
(1,'Pakistan','Pk'),
(2,'United States of America','US')```

1) EXISTS will return TRUE if subquery contains any rows.

That is right, if our subquery will return any rows and we have used EXISTS, the outer query is going to return all rows.

Select * from dbo.Customer
`WHERE Exists ( Select 1)`
```
```

 How to use EXISTS in SQL Server - SQL Server / TSQL Tutorial

Noticed that our subquery is static query ( Select 1). As subquery did return row and EXISTS returned TRUE so all the records from dbo.Customer table are displayed.

2) Use EXISTS and Join with SubQuery
The more real time example of EXISTS would be when we want to find all the records from dbo.Customer table which has matching CountryShortName from dbo.Country Table.

```SELECT *
FROM dbo.Customer a
WHERE EXISTS
(SELECT 1
FROM dbo.Country b
WHERE a.CountryShortName=b.CountryShortName)```

Noticed that I have compared CountryShortName from dbo.Customer and dbo.Country. Each outer row is going to be compared with subquery results and if matches , then we get the row.

We can use the IN clause for same requirement.

```SELECT *
FROM dbo.Customer a
WHERE a.CountryShortName IN
(SELECT b.CountryShortName
FROM dbo.Country b
WHERE a.CountryShortName=b.CountryShortName)```

 How to use EXISTS in SQL Server to return matching records - SQL Server / TSQL Tutorial

How to use Exists and Not Exits in SQL Server

## What is BETWEEN Logical Operator :

BETWEEN returns TRUE if the operand is within range. BETWEEN logical operator is used when we want to return the row if operand is within range.

## Scenario:

Let's say that we have dbo.Cutomer table and one of the column is Age. If we would like to return all the records from dbo.Customer table where age between 33 and 60. What Logical Operator we can use?

## Solution:

We can use BETWEEN logical operator to test ranges. If True then row will be returned otherwise not.

```Create table dbo.Customer
(Id int,
FName VARCHAR(50),
LName VARCHAR(50),
CountryShortName CHAR(2),
Age tinyint)
GO
insert into dbo.Customer
Values (
1,'Raza','M','PK',20),
(2,'Rita','John','US',12),
(3,'Sukhi','Singh',Null,25),
(4,'James','Smith','CA',60),
(6,'Alice','John','US',87),
(7,'Raza','M','US',33),
(8,'Dita','M','US',15),

BETWEEN returns TRUE if the value of test_expression is greater than or equal to the value of begin_expression and less than or equal to the value of end_expression.

We can use below query to return all the rows if age is between 33 and 60.

```Select * From dbo.Customer
where Age between 33 and 60```
```
```
 How to use BETWEEN Logical Operator to check range and return rows - SQL Server / TSQL Tutorial

We can also use NOT BETWEEN that will retrun TRUE if the value of test expression is less than the value of begin expression or greater than the value of end expression.
If we want to return all the records where Age is less than 33 or greater than 60 we can use NOT BETWEEN as shown below.

```Select * From dbo.Customer
where Age NOT between 33 and 60```
 How to use NOT BETWEEN in SQL Server Query - SQL Server / TSQL Tutorial Video Demo : How to use Between and Not Between in SQL Server

## What is LIKE Logical Operator :

LIKE logical operator is used when we want to return the row if operand matches a pattern. Like operator returns TRUE if the operand matches a pattern.

Sometime we need to perform pattern matching instead of equal or not equal. Like is used when we want to return the row if specific character string matches a specified pattern. Pattern can be combination of regular characters and wildcard characters.
To return the row back, regular characters must exactly match the characters specified in the character string.The wildcard characters can be matched with arbitrary parts of the character string.

Let's create dbo.Customer table and then create some real time examples

```Create table dbo.Customer
(Id int,
FName VARCHAR(50),
LName VARCHAR(50),
CountryShortName CHAR(2))
GO
insert into dbo.Customer
Values (
1,'Raza','M','PK'),
(2,'Rita','John','US'),
(3,'Sukhi','Singh',Null),
(4,'James','Smith','CA'),
(6,'Alice','John','US'),
(7,'Raza','M','US'),```
```(8,'Dita','M','US'),
```

1) Using %
Let's say if we want to find all the rows where FName contains "i" in them. We can use below query

```Select * From dbo.Customer
where FName like '%i%'```
```
```
 How to use % with Like Operator in SQL Server - SQL Server / TSQL Tutorial
```
```

Noticed that by using % before and after "i", we are telling the query to find all rows in which FName has "i" character and does not matter what other characters are before and after "i".

2) Using _ (underscore)
The underscore can be used when we want to check single character that can be anything and provide the rest of the characters for our match. Let's say that if I want to find all rows where FName first character can be anything but rest of them should  be "ita". I can use below query.

```Select * From dbo.Customer
where FName like '_ita'```
 How to use underscore( _ ) with Like Operator in SQL Server - SQL Server / TSQL Tutorial
```
```
3)  Using [ ] - Any single character within the specified rang  [a-t] or set [abc]
Like operator with [ ] can be used when we want to have range. Let's say if I want to find all the rows where FName first character start with [a-f]. We can use below query.

```Select * From dbo.Customer
where FName like '[a-f]%'```
```
```
 How to use Rang with Like operator in SQL Server for search - SQL Server / TSQL Tutorial
```
```
As  you can see that I have used [a-f]%. That means I want the first character from a to f and after that any characters are fine as I used %.

4) [^]   Any single character NOT within the specified rang  [a-t] or set [abc]
Let's say if I want to find all the rows where FName first character Dost NOT start with [a to f]. We can use below query.

Select * From dbo.Customer
`where FName like '[^a-f]%'`
```
```
 How to use Not in Range with Like Operator in SQL Server - SQL Server / TSQL Tutorial
```
```

Noticed that it only returned us the rows which does not start with any character from a-f.

Let's say that if we are want to get all the rows where FName does not start with a,d,j. we can use below query.

Select * From dbo.Customer
`where FName like '[^adj]%'`
```
```

Video Demo : How to use Logical Like Operator in SQL Server
```
```

## What is IN Logical Operator :

IN logical operator is used when we want to return the row if specified value matches with any value in the sub-query or a list.

The IN Logical operator is going to be true if the operand is equal to one of the list of expressions.

Let's say that we have dbo.Customer table with below rows. We want to return the rows where CountryShortName is equal to 'PK' OR 'US'.

Create table dbo.Customer
``` (Id int,
FName VARCHAR(50),
LName VARCHAR(50),
CountryShortName CHAR(2))
GO
insert into dbo.Customer
Values (
1,'Raza','M','PK'),
(2,'Rita','John','US'),
(3,'Sukhi','Singh',Null),
(4,'James','Smith','CA'),
(6,'Alice','John','US')```

Let's write our query by using IN logical operator.

Select * From dbo.Customer
`where CountryShortName IN ('US','PK')`
```
```

 How to use IN Logical Operator in SQL Server - SQL Server / TSQL Tutorial Video Demo How to use IN Logical Operator in SQL Server

### What is NOT Logical Operator in SQL Server - SQL Server / TSQL Tutorial Part 121

Logical Operators are used to test some conditions. If the condition is true, the row will be selected for output.

## What is NOT Logical Operator :

NOT logical operator is used when we want to return the row if specified condition is false.

Scenario :
Let's say that you have dbo.Customer table and you would like to return all the records but where FName is not equal to 'Raza'.

```Create table dbo.Customer
(Id int,
FName VARCHAR(50),
LName VARCHAR(50),
CountryShortName CHAR(2))
GO
insert into dbo.Customer
Values (
1,'Raza','M','PK'),
(2,'Rita','John','US'),
(3,'Sukhi','Singh',Null),
(4,'James','Smith','CA'),
(6,'Alice','John','US'),
(7,'Raza','M','US')```

We can use NOT Logical Operation to get our required results.

```Select * From dbo.Customer
where NOT FName='Raza'```
```
```
 How to use Logical Not Operator in SQL Server - SQL Server / TSQL Tutorial
```
```
```
```
Noticed that it returned all the rows for which the condition is false.

### What is Logical AND Operator in SQL Server - SQL Server / TSQL Tutorial Part 120

Logical Operators are used to test some conditions. If the condition is true, the row will be selected for output.

## What is AND Logical Operator :

Logical AND operator is used when we want to return the row if all the specified conditions are true.

## Scenario :

Think about a situation where you need to return all the records from a dbo.Customer table where FName='Raza' and CountryShortName='US'

Create the dbo.Customer table with some sample records by using below scripts.

```Create table dbo.Customer
(Id int,
FName VARCHAR(50),
LName VARCHAR(50),
CountryShortName CHAR(2))
GO
insert into dbo.Customer
Values (
1,'Raza','M','PK'),
(2,'Rita','John','US'),
(3,'Sukhi','Singh',Null),
(4,'James','Smith','CA'),
(6,'Alice','John','US'),
(7,'Raza','M','US')```

To return the records where FName='Raza' and CountryShortName='US' we can use below query.

```Select * From dbo.Customer
where FName='Raza'
AND CountryShortName='US'```
```
```
```
```
Noticed that our query returned single record as both conditions are only true for single record.

### What is Logical OR Operator in SQL Server - SQL Server / TSQL Tutorial Part 119

Logical Operators are used to test some conditions. If the condition is true, the row will be selected for output.

## What is OR Logical Operator :

OR logical operator is used when we want to return the row if at least one of the condition is true.

## Scenario :

Let's say that you have dbo.Customer table and you want to return all the rows if FName='Raza' Or CountryShortName='US'.

Create the dbo.Customer table by using below script.

```Create table dbo.Customer
(Id int,
FName VARCHAR(50),
LName VARCHAR(50),
CountryShortName CHAR(2))
GO
insert into dbo.Customer
Values (
1,'Raza','M','PK'),
(2,'Rita','John','US'),
(3,'Sukhi','Singh',Null),
(4,'James','Smith','CA'),
(6,'Alice','John','US')```

We can use below query with OR Logical operator to return all rows where FName='Raza' Or CountryShortName='US'.

```Select * From dbo.Customer
where FName='Raza'
OR CountryShortName='US'```
```
```
```
```
As we have used OR logical operator in our query, the row will be returned if at-least one condition is true. We can see that for FName='Raza', the row is selected even second condition is not true.
Same goes for other three records which are returned even FName is not equal to 'Raza' but CountryShortName is equal to 'US' that makes one condition true.

## Scenario :

You are working as SQL Server developer with front end development team.The front end team needs to implement pagination.  Confuse about Pagination? No problem. Think about viewing your bank statement or credit card statement. Where applications shows only 10 or 20 records per page  and you have to click next to see next records.That is called pagination.

Now you understand pagination, the front end development needs SQL query from you that can be used to returned required results and they should be able to pass page number to return records.

## Solution:

There are multiple ways to write Pagination queries, One of them is by using OFFSET FETCH clause. You have to sort the records if you want to use OFFSET FETCH.

Let's create dbo.TotalSale table and insert some sample records. I have only inserted 11 records.

```CREATE TABLE [dbo].[TotalSale]
(
[id] [int] NOT NULL ,
[SalePersonFName] [varchar](100) NULL ,
[SalePersonLName] [varchar](100) NULL ,
[ProductName] [varchar](100) NULL ,
[ItemsSold] [int] NULL ,
[SoldPrice] [float] NULL ,
[SoldDate] [date] NULL ,
[City] [varchar](100) NULL ,
[State] [varchar](100) NULL ,
[Country] [varchar](100) NULL ,
[Region] [varchar](100) NULL
)

INSERT  [dbo].[TotalSale]
( [id], [SalePersonFName], [SalePersonLName], [ProductName],
[ItemsSold], [SoldPrice], [SoldDate], [City], [State], [Country],
[Region] )
VALUES  ( 1, N'Aamir', N'Shahzad', N'TV', 1, 700, CAST(N'2015-07-15' AS DATE),
N'Charlotte', N'NC', N'USA', N'North America' )
,     ( 2, N'M', N'Raza', N'Cell Phone', 2, 800, CAST(N'2015-07-15' AS DATE),
N'Charlotte', N'NC', N'USA', N'North America' )
,     ( 3, N'Christy', N'Ladson', N'TV', 3, 1600,
CAST(N'2015-04-02' AS DATE), N'High Point', N'NC', N'USA',
N'North America' )
,     ( 4, N'John', N'Rivers', N'Laptop', 5, 2400,
CAST(N'2014-03-09' AS DATE), N'Jersey City', N'NJ', N'USA',
N'North America' )
,     ( 5, N'Najaf', N'Ali', N'Computer', 1, 300,
CAST(N'2015-06-20' AS DATE), N'Karachi', N'Sindh', N'Pakistan',
N'Asia' )
,     ( 6, N'Sukhjeet', N'Singh', N'TV', 2, 900, CAST(N'2015-06-21' AS DATE),
N'ChandiGar', N'Punjab', N'India', N'Asia' )
,     ( 7, N'Chirag', N'Patel', N'Cell Phone', 5, 1500,
N'Asia' )
,     ( 8, N'Aleena', N'Aman', N'Laptop', 2, 800,
CAST(N'2015-05-25' AS DATE), N'Lahore', N'Punjab', N'Pakistan',
N'Asia' )
,     ( 9, N'Petra', N'Henry', N'TV', 10, 5000, CAST(N'2015-04-08' AS DATE),
N'Paris', N'Île-de-France', N'France', N'Europe' )
,     ( 10, N'Rita', N'Roger', N'Laptop', 7, 2100,
CAST(N'2015-04-11' AS DATE), N'Paris', N'Île-de-France', N'France',
N'Europe' )
,     ( 11, N'Tamara', N'Tony', N'Cell Phone', 2, 1200,
CAST(N'2015-03-03' AS DATE), N'Frankfurt', N'Hesse', N'Germany',
N'Europe' )```

1) Let's say if we would like to skip first 5 rows and want to show all rest of the rows we can use below query.

```Select
[id], [SalePersonFName], [SalePersonLName], [ProductName],
[ItemsSold], [SoldPrice]
from dbo.TotalSale
order by id
OFFSET 5 rows
```
 How to use OFFSET FETCH Clause in SQL Server to skip first X rows and show all rest of them - SQL Server Tutorial

2) Now if we would like to show 3 records per page, we can use below query. In this case we are going to show first page

```Select
[id], [SalePersonFName], [SalePersonLName], [ProductName],
[ItemsSold], [SoldPrice]
from dbo.TotalSale
order by id
OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;```

 How to use OFFSET FETCH clause to return records per page in SQL Server - SQL Server Tutorial

Noticed that I have OFFSET 0, that means that I want to show first page and with 3 rows. If I want to show second page records, I will set to OFFSET 1, the Next 3 ROWS ONLY part will stay the same as want to show only 3 rows per page.

We can use variables so we don't have to make changes in the query and by changing the value of variables, we can return our required results. You can create Stored Procedure if you like by using below query.

```Declare @PageNumber int
Declare @RowsPerPage int
set @RowsPerPage=3
SET @PageNumber=1

Select
[id], [SalePersonFName], [SalePersonLName], [ProductName],
[ItemsSold], [SoldPrice]
from dbo.TotalSale
order by id
OFFSET (@PageNumber-1)*@RowsPerPage ROWS FETCH NEXT @RowsPerPage ROWS ONLY;```

If we need to provide Stored Procedure to Front End team, which accepts page number and number of rows they would like to return for each page, you can use below to create Stored Procedure.

Create procedure dbo.sp_GetSaleRecordsPerPage
``` @PageNumber int,
@RowsPerPage int
AS
BEGIN
Select
[id], [SalePersonFName], [SalePersonLName], [ProductName],
[ItemsSold], [SoldPrice]
from dbo.TotalSale
order by id
OFFSET (@PageNumber-1)*@RowsPerPage
ROWS FETCH NEXT @RowsPerPage ROWS ONLY;
END```

Let's say if we would like to return second page with 4 records, we can use the dbo.sp_GetSaleRecordsPerPage by providing below parameter values.

`EXEC dbo.sp_GetSaleRecordsPerPage 2,4`
 How to perform Pagination in SQL Server by using OFFSET FETCH clause - TSQL Tutorial