COMMONLY USED DATE AND STRING FUNCTIONS

Date and String Functions:

• Date time functions

• String functions

UNARY OPERATORS

We can perform unary operations on numerical expressions only.

• Positive “+”
• Negative “-“
• Bitwise NOT “~”

Positive “+”: Numeric value is positive.

Example: SELECT +350

Negative “-”: Numeric value is negative.

Example: SELECT -350

Bitwise NOT “~”: Returns the ones complement of the number.
Example:
SELECT ~(350)
SELECT ~(-350)

STRING CONCATENATION OPERATOR

            Using string concatenation operator is “+”, we can combine two or more strings.

Example: Extract full name from EMP table.

SELECT FirstName+' '+LastName AS FullName FROM EMP

SET OPERATORS

            Set operators are used to combine two or more data sets into single data-set.

·        Union/Union All

·        Except

·        Intersect

Union/Union All:

Union or Union all set operators are used to combine tow or more similar data sets (same number of columns, column order, and similar data types).

Note: If data sets are having duplicates or same records,

“UNION” eliminates duplicate rows from the combined result set.

“UNION ALL” includes duplicate rows from the combined result set.

Example: Union1 and Union2 are tow tables with same number of columns and data types.

Table creation script:

Table UNION1:

      CREATE TABLE UNION1

      (

      ID INT,FirstName varchar (30),LastName Varchar(30),Gender Char(1),Designation Varchar(10),

      ManagerID INT,Dept INT,Sal DECIMAL(18,4),Comm DECIMAL(18,4),HireDate DATE

      )

      Go

      INSERT INTO UNION1 VALUES (101,'Yagenti','Charan','M','Developer',105,10,4000,NULL,'2001-05-21')

      INSERT INTO UNION1 VALUES (103,'Jagarla','Mahi','M','Sales man',104,20,3000,500,'2011-05-23')

      INSERT INTO UNION1 VALUES (104,'Dama','Puja','F','Manager',110,20,8000,NULL,'2009-05-19')

Table UNION2:

      CREATE TABLE UNION2

      (

      ID INT,FirstName varchar (30),LastName Varchar(30),Gender Char(1),Designation Varchar(10),

      ManagerID INT,Dept INT,Sal DECIMAL(18,4),Comm DECIMAL(18,4),HireDate DATE

      )

      Go

      INSERT INTO UNION2 VALUES (101,'Yagenti','Charan','M','Developer',105,10,4000,NULL,'2001-05-21')

      INSERT INTO UNION2 VALUES (102,'Mare','Roja','F','Developer',105,10,3500,NULL,'2005-03-18')

      INSERT INTO UNION2 VALUES (103,'Jagarla','Mahi','M','Sales man',104,20,3000,500,'2011-05-23')

·        Combine both the data sets into single dataset without duplicates.

SELECT * FROM UNION1

UNION

SELECT * FROM UNION2

·        Combine the data sets into single data-set with entire data (including duplicates).

SELECT * FROM UNION1

UNION ALL

SELECT * FROM UNION2

Except: EXCEPT returns any distinct values from the left query and that value should not be in right query.

Example:

SELECT * FROM UNION1

EXCEPT

SELECT * FROM UNION2

Intersect: INTERSECT returns any distinct values that are returned by both the query.

Example:

SELECT * FROM UNION1

INTERSECT

SELECT * FROM UNION2

LOGICAL OPERATORS

            Using Logical operators we can test the some conditions. Logical operators, like comparison operators a Boolean data type with a value of “True, False, or Unknown.

• AND
• OR
• IN
• LIKE
• BETWEEN
• ALL
• ANY
• EXISTS
• SOME
• NOT

AND: The expression evaluates to TRUE if all conditions are TRUE.

Example: Find the employee details from “EMP table”, which are getting salary more than 8000 and less than 15000.

SELECT * FROM EMP

WHERE Sal>8000 AND Sal<15000

OR: The expression evaluates to TRUE if at least one condition is TRUE.

Example: Find the employee details from “EMP table”, which are getting salary less than 8000 and more than 15000.

SELECT * FROM EMP

WHERE Sal<8000 or Sal>15000

IN: Determines whether a specified value matches any value in a sub query or a list.

Example: Find the employee details from “EMP table”, which are working under the departments 10 and 20.

SELECT * FROM EMP

WHERE Dept in (10,20)

LIKE: Determines whether a specific character string matches a specified pattern.

Example: Extract the employee details from “EMP table”, whose first name is starting with “J”.

SELECT * FROM EMP

WHERE FirstName like 'J%'

BETWEEN: Specifies a range to test.

Example: Find the employee details from “EMP table”, which are getting salary between 8000 and 15000.

SELECT * FROM EMP

WHERE Sal BETWEEN 8000 AND 15000

ALL: It Returns TRUE if all of a set of comparisons are TRUE other wise returns FALSE.

Example: Extract all employee details from “EMP table”, which are getting salary more than employees who are working under 10^th department.

SELECT * FROM EMP

WHERE Sal > ALL (SELECT Sal FROM EMP WHERE Dept=10)

ANY: ANY returns TRUE when the comparison specified is TRUE for ANY pair, otherwise, returns FALSE.

Example: Extract all employee details from “EMP table”, which are getting salary more than any one of the employees who are working under 10^th department.

SELECT * FROM EMP

WHERE Sal > ANY (SELECT Sal FROM EMP WHERE Dept=10)

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

Example: I have tow table EMP1 and EMP2 (Table creation script is attached below). Check the EMP2 table records are present in EMP1 or not.

SELECT * FROM EMP1 A

WHERE EXISTS

(SELECT * FROM EMP2 B WHERE A.Dept=B.Dept)

Table creation script:

EMP1:

      CREATE TABLE EMP1

      (

      ID INT,FirstName varchar (30),LastName Varchar(30),Gender Char(1),Designation Varchar(10),

      ManagerID INT,Dept INT,Sal DECIMAL(18,4),Comm DECIMAL(18,4),HireDate DATE

      )

      Go

      INSERT INTO EMP1 VALUES (101,'Yagenti','Charan','M','Developer',105,10,4000,NULL,'2001-05-21')

      INSERT INTO EMP1 VALUES (102,'Mare','Roja','F','Developer',105,10,3500,NULL,'2005-03-18')

      INSERT INTO EMP1 VALUES (103,'Jagarla','Mahi','M','Sales man',104,20,3000,500,'2011-05-23')

      INSERT INTO EMP1 VALUES (104,'Dama','Puja','F','Manager',110,20,8000,NULL,'2009-05-19')

      INSERT INTO EMP1 VALUES (105,'Kanti','Vijay','M','Manager',110,10,10000,NULL,'2001-10-30')

      INSERT INTO EMP1 VALUES (106,'Konda','Krishna','M','Sales man',104,20,2500,600,'2010-09-05')

EMP2:

      CREATE TABLE EMP2

      (

      ID INT,FirstName varchar (30),LastName Varchar(30),Gender Char(1),Designation Varchar(10),

      ManagerID INT,Dept INT,Sal DECIMAL(18,4),Comm DECIMAL(18,4),HireDate DATE

      )

      Go

      INSERT INTO EMP2 VALUES (101,'Yagenti','Charan','M','Developer',105,10,4000,NULL,'2001-05-21')

      INSERT INTO EMP2 VALUES (102,'Mare','Roja','F','Developer',105,10,3500,NULL,'2005-03-18')

      INSERT INTO EMP2 VALUES (103,'Jagarla','Mahi','M','Sales man',104,20,3000,500,'2011-05-23')

Some: Compares a scalar value with a single-column set of values. SOME and ANY are equivalent.

NOT: Negates a Boolean input.

Example: Find the employee details from “EMP table”, which are not working under the departments 10 and 20.

SELECT * FROM EMP

WHERE Dept NOT IN (10,20)

Note: When more than one logical operator is used in a statement, the AND operators are evaluated first. You can change the order of evaluation by using parentheses.

COMPOUND OPERATORS

Compound operators execute some operation and set an original value to the result of the operation. For example, if a variable @x equals 35, then @x += 2 takes the original value of @x, add 2 and sets @x to that new value (37).

• Add Equals “+=”
• Subtract Equals “-=”
• Multiply Equals “*=”
• Divide Equals “/=”
• Modulo Equals “%=”
• Bitwise AND Equals “&=”
• Bitwise OR Equals “|=”
• Bitwise Exclusive OR Equals “^=”

Add Equals (+=): Adds some amount to the original value and sets the original value to the result.

Example: DECLARE @v1 INT;

SET @v1 = 10;

SET @v1 += 2;

select @v1;

Subtract Equals (-=): Subtracts some amount from the original value and sets the original value to the result.

Example: DECLARE @v1 INT;

SET @v1 = 10;

SET @v1 -= 2;

select @v1;

Multiply Equals (*=): Multiplies by an amount and sets the original value to the result.

Example: DECLARE @v1 INT;

SET @v1 = 10;

SET @v1 *= 2;

select @v1;

Divide Equals (/=): Divides by an amount and sets the original value to the result.

Example: DECLARE @v1 INT;

SET @v1 = 10;

SET @v1 /= 2;

select @v1;

Modulo Equals (%=): Divides by an amount and sets the original value to the modulo.

Example: DECLARE @v1 INT;

SET @v1 = 15;

SET @v1 %= 2;

select @v1;

Bitwise AND Equals (&=): Performs a bitwise AND and sets the original value to the result.

Example: DECLARE @v1 INT;

SET @v1 = 10;

SET @v1 &= 2;

select @v1;

Bitwise OR Equals (|=): Performs a bitwise OR and sets the original value to the result.

Example: DECLARE @v1 INT;

SET @v1 = 10;

SET @v1 |= 2;

select @v1;

 

Bitwise Exclusive OR Equals (^=): Performs a bitwise exclusive OR and sets the original value to the result.

Example: DECLARE @v1 INT;

SET @v1 = 10;

SET @v1 ^= 2;

select @v1;

COMPARISON OPERATORS

Using “Comparison operators” we can check weather two expressions are same, except the expressions which are having text, ntext, or image data types.

• Equals: “=”
• Greater than: “>”
• Less than: “<”
• Greater than or equal to: “>=”
• Less than or equal to: “<=”
• Not equal to: “<>” and “!=  (Not ISO standard)”
• Not less than: “!<”
• Not greater than: “!>”

Equals (=): Compares the equality of two expressions

Example:

è Find the employee details from EMP table, which employee is having “106” as employee ID.

Solution:

SELECT * FROM EMP

where ID=106

Greater than (>): Compares two expressions (not null) and returns “True” when the left expression has higher value than right expression. Otherwise the result is “False”.

Example:

è Find the employee details from EMP table, which are getting salary more than 10,000.

Solution:

SELECT * FROM EMP

where Sal>10000

Less than (<): Compares two expressions (not null) and returns “True” when the left expression has lower value than right expression. Otherwise the result is “False”.

Example:

è Find the employee details from EMP table, which are getting salary less than 10,000.

Solution:

SELECT * FROM EMP

where Sal<10000

Greater than or equal to (>=): Compares two expressions (not null) and returns “True” when the left expression has higher or equal value than right expression. Otherwise the result is “False”.

Example:

è Find the employee details from EMP table, which are getting salary more than or equal to 10,000.

Solution:

SELECT * FROM EMP

where Sal>=10000

Less than or equal to (<=): Compares two expressions (not null) and returns “True” when the left expression has lower or equal value than right expression. Otherwise the result is “False”.

Example:

è Find the employee details from EMP table, which are getting salary less than or equal to 10,000.

Solution:

SELECT * FROM EMP

where Sal<=10000

Not equal to (<> or !=): Compares two expressions (not null) and returns “True” when the left expression value is not equal to the right expression value. Otherwise the result is “False”.

Example:

è Find the all employee details, except the employees which are getting salary exactly 10,000 from EMP table.

Solution:

SELECT * FROM EMP

where Sal<>10000

OR

SELECT * FROM EMP

where Sal != 10000

Not less than (!<):Compares two expressions (not null) and returns “True” when the left expression does not have a value lower than right expression. Otherwise the result is “False”.

Example:

è Find the employee details from EMP table; their salary should not less than 10,000.

Solution:

SELECT * FROM EMP

where Sal !< 10000

Not greater than (!>): Compares two expressions (not null) and returns “True” when the left expression does not have value higher than right expression. Otherwise the result is “False”.

Example:

è Find the employee details from EMP table; their salary should not greater than 10,000.

Solution:

SELECT * FROM EMP

where Sal !> 10000

SAMPLE EMP TABLE CREATION WITH DATA

CREATE TABLE EMPLOYEE

(

ID INT

,FirstName varchar (30)

,LastName Varchar(30)

,Gender Char(1)

,Designation Varchar(10)

,ManagerID INT

,Dept_ID INT

,Salary DECIMAL(18,4)

,Commission DECIMAL(18,4)

,HireDate DATE

)

Go

INSERT INTO EMPLOYEE VALUES (101,'Charan','Yagenti','M','Developer',105,10,4000,NULL,'2001-05-21')

INSERT INTO EMPLOYEE VALUES (102,'Roja','Mare','F','Developer',105,10,3500,NULL,'2005-03-18')

INSERT INTO EMPLOYEE VALUES (103,'Mahi','Jagarla','M','Sales man',104,20,3000,500,'2011-05-23')

INSERT INTO EMPLOYEE VALUES (104,'Puja','Dama','F','Manager',110,20,8000,NULL,'2009-05-19')

INSERT INTO EMPLOYEE VALUES (105,'Vijay','Kanti','M','Manager',110,10,10000,NULL,'2001-10-30')

INSERT INTO EMPLOYEE VALUES (106,'Krishna','Konda','M','Sales man',104,20,2500,600,'2010-09-05')

INSERT INTO EMPLOYEE VALUES (107,'Prabhaker','Venna','M','Account',109,30,8000,NULL,'2006-03-11')

INSERT INTO EMPLOYEE VALUES (108,'Nagini','Nagavara','F','Account',109,30,8500,NULL,'2000-11-25')

INSERT INTO EMPLOYEE VALUES (109,'Giri','Pinniboina','M','Manager',110,30,15000,NULL,'2008-04-04')

INSERT INTO EMPLOYEE VALUES (110,'Raja','Pedaraju','M','Manager',Null,40,20000,NULL,'2000-01-10')

INSERT INTO EMPLOYEE VALUES (111,'Charan','Jammi','M','Developer',105,10,6000,NULL,'2007-10-25')

See Also: Sample Department table creation with data