SQL Server

SQL Server with examples

SQL Server scenarios for practice

Introduction DDL (Data Definition Language) Create Alter Drop Truncate DML (Data Manipulation Language) Select  Insert Update Delete DCL (Data Control Language) Grant Revoke TCL (Transactional Control Language) Rollback Save Transaction SQL Server Data Types Difference Between Delete, Truncate and Drop Operators Arithmetic operators Assignment operator Bitwise operators Comparison operators Compound operators Logical operators Set operators String concatenation operator Unary operators Commonly used Date and String Functions String Functions Date Functions Constraints Not null Unique Primary key Foreign key Check Defoult Views Indexes Joins Inner Join Outer Join Cross Join Self Join  Subqueries

Diffrence between Rank, Dense_rank and Row_number

          Using these three functions we can calculate RowID for a query result set. These three functions works slightly different way.

Example:

Table Creation:

CREATE TABLE TEST

(

ID INT,

NAME VARCHAR(10),

SALARY NUMERIC(10,4)

)

INSERT INTO TEST VALUES (1,'A',500)

                        ,(2,'B',500)

                        ,(3,'C',500)

                        ,(4,'D',600)

                        ,(5,'E',600)

                        ,(6,'F',700)

Sample Query:

          In the above test table is having 'Salary' column is there, based on salary i am going to generate RowID using row_number, Rank and Desne_rank.

SELECT *

,ROW_NUMBER() OVER (ORDER BY SALARY) AS ROWNUMBER

,RANK() OVER (ORDER BY SALARY) AS RANKS

,DENSE_RANK() OVER (ORDER BY SALARY) AS DENSRANK

FROM TEST

O/P:

ROW_NUMBER():

·        This function will assign a unique id to each record returned from the query.

RANK():

·        In the above example 500 is the minimum value so rank is 1. 500 is repeated for three times so same rank is assigned. 

·        Next value is 600 but rank is 4, because previous rank '1'(For salary 500) is repeated three times so the rank function is skipped three numbers and assigned rank 4.

·        Next value is 700 rank is 6, because the previous rank '4' is repeated two times so next rank is 6.

DENSE_RANK()

·        This function is similar to Rank, the difference is, this will not leave gaps between groups.

Subqueries

        A subquery is a query within a query. You can create subqueries within your SQL statements. These subqueries can reside in the ‘WHERE’ clause, the ‘FROM’ clause, or the ‘SELECT’ clause.

• Single-row-subquery
• Multi-row-subquery
• Correlated-subquery

Correlated subquery

      Inner query depends on outer query, outer query depends on inner query. Means subquery is executed repeatedly, once for each row that might be selected by the outer query.

Example:

           Employee table

           Department table

           1.

SELECT * FROM EMPLOYEE E

WHERE EXISTS

(

      SELECT 1

      FROM DEPT D

      WHERE D.DEPTID = E.DEPT_ID 

)

    In the above example inner query depends on outer query, outer query depends inner query. Above query displays the all employee details if the 'department id' is matched with 'department table department id'.

           2.

SELECT * FROM EMPLOYEE E

WHERE NOT EXISTS

(

      SELECT 1

      FROM DEPT D

      WHERE D.DEPTID = E.DEPT_ID 

)

    Above query displays all employee details if the department id is not matched with 'department table department id'.

SQL Server Date Formates

Date Format	Code	SQL Statement	Sample output
mon dd yyyy hh:miAM (or PM)	0	SELECT CONVERT(VARCHAR(20),GETDATE(),0)	Nov 25 2014  2:13PM
	100	SELECT CONVERT(VARCHAR(20),GETDATE(),100)
mm/dd/yy	1	SELECT CONVERT(VARCHAR(20),GETDATE(),1)	11/25/14
mm/dd/yyyy	101	SELECT CONVERT(VARCHAR(20),GETDATE(),101)	11/25/2014
yy.mm.dd	2	SELECT CONVERT(VARCHAR(20),GETDATE(),2)	14.11.25
yyyy.mm.dd	102	SELECT CONVERT(VARCHAR(20),GETDATE(),102)	2014.11.25
dd/mm/yy	3	SELECT CONVERT(VARCHAR(20),GETDATE(),3)	25/11/14
dd/mm/yyyy	103	SELECT CONVERT(VARCHAR(20),GETDATE(),103)	25/11/2014
dd.mm.yy	4	SELECT CONVERT(VARCHAR(20),GETDATE(),4)	25.11.14
dd.mm.yyyy	104	SELECT CONVERT(VARCHAR(20),GETDATE(),104)	25.11.2014
dd-mm-yy	5	SELECT CONVERT(VARCHAR(20),GETDATE(),5)	25-11-14
dd-mm-yyyy	105	SELECT CONVERT(VARCHAR(20),GETDATE(),105)	25-11-2014
dd mon yy	6	SELECT CONVERT(VARCHAR(20),GETDATE(),6)	25 Nov 14
dd mon yyyy	106	SELECT CONVERT(VARCHAR(20),GETDATE(),106)	25 Nov 2014
Mon dd, yy	7	SELECT CONVERT(VARCHAR(20),GETDATE(),7)	Nov 25, 14
Mon dd, yyyy	107	SELECT CONVERT(VARCHAR(20),GETDATE(),107)	Nov 25, 2014
hh:mi:ss	8	SELECT CONVERT(VARCHAR(20),GETDATE(),8)	15:17:41
hh:mi:ss	108	SELECT CONVERT(VARCHAR(20),GETDATE(),108)	15:17:41
mon dd yyyy hh:mi:ss:mmmAM (or PM)	9	SELECT CONVERT(VARCHAR(30),GETDATE(),9)	Nov 25 2014  3:26:56:273PM
	109	SELECT CONVERT(VARCHAR(30),GETDATE(),109)
mm-dd-yy	10	SELECT CONVERT(VARCHAR(20),GETDATE(),10)	11-25-14
mm-dd-yyyy	110	SELECT CONVERT(VARCHAR(20),GETDATE(),110)	11-25-2014
yy/mm/dd	11	SELECT CONVERT(VARCHAR(20),GETDATE(),11)	14/11/25
yyyy/mm/dd	111	SELECT CONVERT(VARCHAR(20),GETDATE(),111)	2014/11/25
yymmdd	12	SELECT CONVERT(VARCHAR(20),GETDATE(),12)	141125
yyyymmdd	112	SELECT CONVERT(VARCHAR(20),GETDATE(),112)	20141125
dd mon yyyy hh:mi:ss:mmm(24h)	13	SELECT CONVERT(VARCHAR(30),GETDATE(),13)	25 Nov 2014 15:28:11:493
	113	SELECT CONVERT(VARCHAR(30),GETDATE(),113)
hh:mi:ss:mmm(24h)	14	SELECT CONVERT(VARCHAR(20),GETDATE(),14)	15:29:54:083
	114	SELECT CONVERT(VARCHAR(20),GETDATE(),114)
yyyy-mm-dd hh:mi:ss(24h)	20	SELECT CONVERT(VARCHAR(20),GETDATE(),20)	2014-11-25 15:31:40
	120	SELECT CONVERT(VARCHAR(20),GETDATE(),120)
yyyy-mm-dd  hh:mi:ss.mmm(24h)	21	SELECT CONVERT(VARCHAR(30),GETDATE(),21)	2014-11-25  15:33:15.337
	121	SELECT CONVERT(VARCHAR(30),GETDATE(),121)
yyyy-mm- ddThh:mi:ss.mmm (no spaces)	126	SELECT CONVERT(VARCHAR(30),GETDATE(),126)	2014-11-  25T15:34:36.280
yyyy-mm- ddThh:mi:ss.mmmZ (no spaces)	127	SELECT CONVERT(VARCHAR(30),GETDATE(),127)	2014-11-  25T15:35:41.170
dd mon yyyy hh:mi:ss:mmmAM	130	SELECT CONVERT(VARCHAR(30),GETDATE(),130)	25 Nov 2014 3:36:29:180PM
dd/mm/yy hh:mi:ss:mmmAM		SELECT CONVERT(VARCHAR(30),GETDATE(),131)	3/02/1436 3:38:40:430PM