SQL Query Quick Examples

***  Various Usefull Examples ***

------RE-Seed Auto-number in Relational Table (if TRUNCATE not allowed on table)---

--PRIMARY TABLE

CREATE TABLE masterTbl (

id INT IDENTITY(1,1) CONSTRAINT pk_id PRIMARY KEY,

name VARCHAR(100) NOT NULL,

description VARCHAR(max))

--FORIGN TABLE

CREATE TABLE detailTbl (

id INT IDENTITY(1,1) PRIMARY KEY,

m_id INT NOT NULL CONSTRAINT fk_mid FOREIGN KEY REFERENCES masterTbl(id) ON DELETE CASCADE,

details VARCHAR(MAX))

GO

----THIS CODE WILL RE-SEED AND AUTONUBER BEGIN TO ONE AS LIKE TRUNCATE---

DELETE FROM [dbo].[masterTbl]

DBCC CHECKIDENT ('[dbo].[masterTbl]', RESEED, 1)

GO

-------------------------END------------------------------

IF NOT EXISTS (SELECT 1 FROM [tb1] WHERE [id]=101)

PRINT '101 Not Exist'

ELSE

PRINT '101 Exist'

GO

----------- CTE (Common Table Expressions)----------------------- 

WITH CTE (config_element, rowNum)

AS

(

SELECT config_element,

ROW_NUMBER() OVER(PARTITION BY config_element ORDER BY config_element) AS rowNum

FROM [myDb].[dbo].[temp_configuration]

)

--update cte set config_element='factoryShortKeys' WHERE rowNum=2

SELECT * from CTE

GO

SELECT MY_NAME = [name] FROM [myDb]..[myTable] --alias

SELECT  CAST([id] as FLOAT) FROM [myDb]..[myTable]

SELECT  CONVERT (FLOAT, [id]) FROM [myDb]..[myTable]

SELECT CONVERT(VARCHAR, [date], 101) as MY_Date FROM [myDb]..[myTable] ---four digit yr US format

SELECT CONVERT(VARCHAR, [date], 1) as MY_Date FROM [myDb]..[myTable]   ---two digit yr US format

SELECT CONVERT(VARCHAR, [date], 103) as MY_Date FROM [myDb]..[myTable] ---four digit yr UK format

SELECT CONVERT(VARCHAR, [date], 3) as MY_Date FROM [myDb]..[myTable]   ---two digit yr UK format

SELECT [date], DATEDIFF(YY, [date], GETDATE()) AS Age FROM [myDb]..[myTable] -------DATE DIFF() GETDATE()

SELECT CASE WHEN [name] = 'Ajay' THEN 'AJAY GARG' ELSE [name] END as [my name] FROM [myDb]..[myTable]

SELECT CASE WHEN [name] = 'Ajay' THEN 'AJAY GARG'  WHEN [name]='Vijay' THEN 'VEDA GARG' ELSE [name] END as [my name] FROM [myDb]..[myTable]

SELECT [name] FROM myTable1 WHERE name <> 'Ajay'

SELECT [name] FROM myTable1 WHERE (name<>'Vijay' and name <>'Ajay') or [salary] is null

SELECT [name] FROM myTable1 WHERE Left([name],2)='Aj'

SELECT [name] FROM myTable1 WHERE [name] like '[A-K]%'

SELECT [name] FROM myTable1 WHERE [name] like '[^A-K]%'

SELECT CHARINDEX('i','ANIAC',1)

SELECT ID FROM [RKDB].[dbo].[tb1] WHERE CONTAINS([name],'rk')

SELECT ID FROM [RKDB].[dbo].[tb1] WHERE CONTAINS([name],'"rk*"')

SELECT TOP 1000 [id],[name],[dob] FROM [RKDB].[dbo].[tb1] ORDER BY 1 DESC

SELECT TOP 1 WITH TIES [name],[salary] FROM [RKDB].[dbo].[tb1] ORDER BY salary DESC --salary is highest

SELECT TOP 50 PERCENT [id],[name],[dob],[salary] FROM [RKDB].[dbo].[tb1]

SELECT COUNT(*) FROM [myDb].[dbo].[customer]

SELECT COUNT_BIG(*) FROM [myDb].[dbo].[customer]

SELECT COUNT(distinct [customerId]) FROM [myDb].[dbo].[customer]

SELECT MIN([customerId]) FROM  [myDb].[dbo].[customer]

SELECT MAX([customerId]) FROM  [myDb].[dbo].[customer]

SELECT SUM([customerId]) FROM  [myDb].[dbo].[customer]

SELECT '$' + CAST ( CAST(SUM([customerId]) AS DECIMAL(17,2)) AS VARCHAR) FROM  [myDb].[dbo].[customer]

SELECT AVG([customerId]) FROM  [myDb].[dbo].[customer]

SELECT STDEV([customerId]) FROM  [myDb].[dbo].[customer]

SELECT STDEV(ALL [customerId]) FROM  [myDb].[dbo].[customer]

SELECT STDEV(DISTINCT [customerId]) FROM  [myDb].[dbo].[customer]

SELECT COUNT([customerId]) AS COUNT,[customerId] into #NewDimProduct FROM  [myDb].[dbo].[customer]  GROUP BY [customerId]

//------BOTH are similar query---

SELECT DISTINCT userId FROM TB1 WHERE exists (SELECT projectId FROM TB2 T2 WHERE T2.userId='121')

SELECT DISTINCT userId FROM TB1 WHERE projectId IN (SELECT projectId FROM TB2 T2 WHERE T2.userId='121')

//-------------------

SELECT  * FROM TB1  //--UNION TABLE WITH NO REPEAT ROW

UNION

SELECT  * FROM TB2

SELECT  * FROM TB1  //--UNION TABLE WITH REPEAT ROW

UNION ALL

SELECT  * FROM TB2

SELECT  * FROM TB1 //--SELECT FROM TB1 WHICH IS NOT IN TB2

EXCEPT

SELECT  * FROM TB2

SELECT  * FROM TB1 //--SELECT ONLY COMMON ROW

INTERSECT

SELECT  * FROM TB2

//------------------------------GUID as ID by default-------------------

CREATE TABLE [tb1] (guidCol UNIQUEIDENTIFIER DEFAULT newsequentialid() ROWGUIDCOL, name VARCHAR(60))

INSERT INTO [tb1] (name) SELECT 'abc'

INSERT INTO [tb1] (name) SELECT 'xyz'

SELECT $rowguid FROM [tb1]

SELECT guidCol FROM [tb1]

SELECT CURRENT_TIMESTAMP

SELECT DAY(CURRENT_TIMESTAMP)

SELECT MONTH(GETDATE())

SELECT YEAR('2012-04-24')

---DATE DIFFERENCES---

SELECT DATEDIFF(SECOND, GETUTCDATE(), GETDATE()) AS SEC_DIFF      --SECOND DIFFERENCE   **RESULT 19800

SELECT DATEDIFF(MI, GETUTCDATE(), GETDATE()) AS MIN_DIFF          --MIN DIFFERENCE   **RESULT 330

SELECT DATEDIFF(YEAR, GETUTCDATE(), GETDATE()) AS YEAR_DIFF       --YEAR DIFFERENCE   **RESULT 0

SELECT DATEDIFF(YEAR,'2011-04-15', '2012-05-09') AS YEAR_DIFF     --YEAR DIFFERENCE   **RESULT 1

SELECT  DATEADD (MINUTE , 50 , GETDATE()) ADD MINUTE IN DATE

SELECT SIGN(3-7)  --RESULT  -1  

SELECT SIGN(3-1)  --RESULT  +1

SELECT SIGN(3-3)  --RESULT  0

SELECT STUFF ('ABCDEF',2,0,'xyz') --syntax: STUFF( char_expression, start, length, replaceWith_expression)

SELECT COALESCE([salary],12000) FROM [tb1] -- If salary then salary else 12000

--CONCATENATE VALUES OF A COLUMN

DECLARE @name nvarchar(max)

SELECT @name = coalesce(@NAME+', ', '')+[name] FROM [tb1]

SELECT @name

SELECT STUFF ((SELECT N', ' + name FROM tb1 FOR XML PATH(''),TYPE).value('text()[1]','nvarchar(max)'),1,2,N'')

SELECT STUFF ((SELECT','+ [name] FROM [tb2] FOR XML PATH('')),1,1,'') AS [name_csv_col]

BULK INSERT [tb3] FROM 'c:\a\data.txt' WITH (FIELDTERMINATOR=',', ROWTERMINATOR='\n') --IMPORT DATA FROM CSV FILE

--GET EXT NAME OF GIVEN FILE NAME

DECLARE @TEXT VARCHAR(50) ='MY_FILE.JPG'

SELECT SUBSTRING(@TEXT, CHARINDEX('.',@TEXT)+1 ,LEN(@TEXT))

--GET LENTH OF DATA IN BYTE

SELECT DATALENGTH(dob) FROM [tb2]

--** CREATE FUNCTION UDF TO EXTRACT INTEGER FROM STRING **---

CREATE FUNCTION ExtractInteger(@String VARCHAR(2000))

      RETURNS VARCHAR(1000)

AS

BEGIN

      DECLARE @Count INT

      DECLARE @IntNumbers VARCHAR(1000)

      SET @Count = 0

      SET @IntNumbers = ''

      WHILE @Count <= LEN(@String)

      BEGIN

            IF SUBSTRING(@String,@Count,1) >= '0'

            AND SUBSTRING(@String,@Count,1) <= '9'

            BEGIN

                  SET @IntNumbers = @IntNumbers + SUBSTRING(@String,@Count,1)

            END

            SET @Count = @Count + 1

      END

      RETURN @IntNumbers

END

GO

SELECT dbo.ExtractInteger('ASDFASDFASDF-AS-DF-ASD-F43QW25-432-534-345-ASDF')

GO

-------**END FUNCTION**----------

--------TABLE VARIABLE DOES NOT KNOW ROLLBACK UNLIKE TEMP TABLE ----

CREATE TABLE #MY_TEMP (col1 VARCHAR(100)) --CREATE TEMP TABLE

INSERT INTO #MY_TEMP (col1)                           --INSERT ROW IN TEMP TABLE

VALUES ('TEMP TABLE ----- OUT SIDE TRANSACTION')

DECLARE @TBL_VAR TABLE (col1 VARCHAR(100))      --DECLARE TABLE VARIABLE

INSERT INTO @TBL_VAR (col1)                           --INSERT INTO TABLE VARIABLE

VALUES('TABLE VARIABLE-------OUT SIDE TRANSACTION')

SELECT [col1] AS tempTable_beforeTrans            --CHECK VALUE IN TEMP TABLE

FROM #MY_TEMP

SELECT [col1] as tempVariable_beforeTrans         --CHECK VALUE IN TABLE VARIABLE

FROM @TBL_VAR

BEGIN TRAN                                                  --BEGIN TRANSCTION

INSERT INTO #MY_TEMP (col1)

VALUES ('TEMP TABLE --------AFTER TRANS') --INSERT INTO TEMP IN TRANS

INSERT INTO @TBL_VAR (col1)

VALUES ('TABLE VARIABLE -------- AFTER TRANS')-- INSERT INTO TABLE VARIABLE IN TRANS

ROLLBACK

SELECT [col1] AS tempTable_afterTrans                   --NOW CHECK VALUE

FROM #MY_TEMP

SELECT [col1] as tempVariable_afterTrans

FROM @TBL_VAR

GO

-----------END DIFF TABLE VARIABLE AND TEMP TABLE--------

SELECT [create_date] FROM sys.server_principals WHERE sid = 0x010100000000000512000000 --TO GET SQL-SERVER INSTALLATION DATE

---------------------------BEGIN-------------------

--CASE SENSITIVE COLLECTION ON RESULT SET --Latin1_General_CI_AS (CASE INSENSITIVE), Latin1_General_CS_AS (CASE SENSITIVE)

CREATE TABLE [MyTable] (

Col1 VARCHAR(15) COLLATE Latin1_General_CI_AS,

Col2 VARCHAR(14) COLLATE Latin1_General_CS_AS) ;

INSERT [MyTable](Col1, Col2)

VALUES ('Hyna','Hyna'),

('hyna','hyna'),

('starfish','starfish'),

('Starfish','Starfish');

GO

-- Retrieve SORTED Data will retrieve case in-sensitve order

SELECT * FROM [MyTable] ORDER BY Col1

GO

-- Retrieve SORTED Data will retrieve case sensitve order

SELECT * FROM [MyTable] ORDER BY Col2

GO

//----------------END--------------------------

----Short Key to jump Parenthesis---

SELECT *

FROM (SELECT *

FROM (SELECT *

FROM (SELECT *

FROM myTable)x)y)z

--CTRL + SHIFT + ]  --Shortcut to Select Code Between Two Parenthesis

--CTRL + ]              --Shortcut to Switch Between Two Parenthesis