Operators in SQL Server

To test Operators Example .Create a table in data base as

Create table

Person

(

pid int IDENTITY(1,1) NOT NULL,

pname VARCHAR(200) NULL,

dob DATETIME NULL,

address VARCHAR(200) NULL,

city VARCHAR(200) NULL,

salary NUMERIC NULL,

status BIT DEFAULT 1

)

INSERT INTO Person(pname,dob,address,city,salary,status) values ('PQR','01/08/1988','Delhi','New Delhi',20000,1);

INSERT INTO Person(pname,dob,address,city,salary,status) values ('XYZ','01/08/1988','Delhi','New Delhi',50000,1);

INSERT INTO Person(pname,dob,address,city,salary,status) values ('ABC','01/08/1989','Delhi','Delhi',40000,1)

Logical Operators:

1.        ALL: TRUE if all of a set of comparisons are TRUE.

if 20000<ALL(Select salary from Person)

PRINT 'TRUE'

ELSE

PRINT 'FALSE'

Output:

FALSE

if 10000<ALL(Select salary from Person)

PRINT 'TRUE'

ELSE

PRINT 'FALSE'

Output:

TRUE

2.        AND:    Combines two Boolean expressions and returns TRUE when both expressions are TRUE and false if any one false.

if 10000=10000 AND 200=200

PRINT 'TRUE'

ELSE

PRINT 'FALSE'

Output:

TRUE

select pname from Person where salary>1000 AND city='New Delhi'

Output:

ABC

XYZ

PQR

3.        ANY: TRUE if any one of a set of comparisons are TRUE.

if 10000 < ANY(SELECT salary from Person)

PRINT 'TRUE'

ELSE

PRINT 'FALSE'

Output:

TRUE

4.        SOME: TRUE if some of a set of comparisons are TRUE. Functionally ANY and SOME are same.

if 10000 < SOME(SELECT salary from Person)

PRINT 'TRUE'

ELSE

PRINT 'FALSE'

Output:

TRUE

5.        BETWEEN: TRUE if the operand is within a range.

SELECT pname from Person where salary between 10000 and 30000;

Output:

PQR

6.        EXISTS: Specifies a sub query to test for the existence of rows.

                if EXISTS(SELECT pname from Person where salary>10000)

print 'TRUE'

Output:

TRUE

7.        IN: TRUE if the operand is equal to one of a list of expressions.

SELECT pname from Person where salary IN(10000,40000,50000)

Output:

ABC

XYZ

Comparison Operator:

·          = (Equals):

SELECT pname from Person where salary=40000

Output:

XYZ

·          >(Greater than)

SELECT pname from Person where salary>40000

Output:

ABC

·          <(Less than)

SELECT pname from Person where salary<40000

Output:

PQR

·          >= (Greater Than or Equal To)

SELECT pname from Person where salary>=40000

Output:

ABC

XYZ

·          <= (Less Than or Equal To)

SELECT pname from Person where salary<=40000

Output:

XYZ

Both are functionally same

PQR

·          <> (Not Equal To)

·          !=  ( Not Equal To)

SELECT pname from Person where salary !=40000

Or you can use as

SELECT pname from Person where salary <>40000

Output:

ABC

PQR

Compound Operators (+=,-=,*=, /=, %=)

DECLARE @v1 varchar(40);

SET @v1 = 1000;

--Example of += Operator

SET @v1 += 50;

PRINT @v1;

--OUTPUT: 1050

--Example of -= Operator

SET @v1-= 50;

PRINT @v1;

 --OUTPUT: 1000

--Example of *= Operator

SET @v1*=20

PRINT @v1; 

--OUTPUT: 20000

--Example of /= Operator

SET @v1/=20

PRINT @v1;

 --OUTPUT: 1000

--Example of %= Operator

SET @v1%=9

PRINT @v1

 --OUTPUT: 1

String Operators:

·         +(String Concatenation)

DECLARE @v1 varchar(40);

SET @v1 = 'Querying'+'SQL';

PRINT @v1

Output:

QueryingSQL

·         +=( String Concatenation)

DECLARE @v1 varchar(40);

SET @v1 = 'Querying';

SET @v1 +='SQL';

PRINT @v1

Output:

QueryingSQL

·         Percent(%) for Wilcard: Matches any string of zero or more characters.% char can be used as prefix or suffix.

Select salary from Person where pname like 'ab%'

Output:

50000

·         [] for Wildcard : Matches any single character within the specified range

Select salary from Person where pname like '[A-C][A-C][A-C]'

Output:

50000

·         [^]: Matches any single character that is not within the range or set specified between the square brackets.

Ex: returns salary of person whose name start with AB and his third character not P.

Select salary from Person where pname like 'AB[^P]'

Output:

50000

·         - : Match single character

Select salary from Person where pname like '_BC'

Other Operators:

·          UNION: Specifies that multiple result sets are to be combined and returned as a single result set.

select pname from Person where salary=40000 UNION select pname from Person where salary=50000

Output:

ABC

XYZ

·         EXCEPT: Returns any distinct values from the query to the left of the EXCEPT operand that are not also returned from the right query.

select pname from Person EXCEPT select pname from Person where salary=50000

Output:

PQR

XYZ

·          INTERSECT: Returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand.

select pname from Person INTERSECT select pname from Person where salary=50000

Output:

ABC

Prev--->Data Types in T-SQL                                                                     Next--->DataBase Creation using T-SQL