SQL Query

1) Delete , joining two tables

DELETE a.*, b.* 
FROM tbl_member a 
INNER JOIN tbl_member_details b 
ON b.member_id = a.member_id
WHERE a.roll_no>3

2)Update , joining two tables

UPDATE  a

SET a.Name=b.PName 

FROM tbl_member a  

INNER JOIN tbl_member_deatils b

ON a.id=b.pid

3)Common Table Expression ,Cross Table Query

WITH CTES

AS

(

SELECT id,pid,Name from tbl_member

UNION ALL 
SELECT a.id,b.pid,c.Name from tbl_member a

INNER JOIN tbl_member b ON a.id=b.pid

)

SELECT * FROM CTES

4)Function Returns Table

CREATE FUNCTION  Function_Returns_Table(@complaiceid INT)

RETURNS @Return_table TABLE 

(

@complanceid INT, 
@complancename VARCHAR(2000)

)

AS

BEGIN

INSERT INTO @Return_table (@complanceid ,@complancename )

SELECT complanceid ,complancename FROM tbl_member_complance

WHERE  
complanceid >@complaiceid

RETURN

END 

5)Comma Deliminated Sql Output

 
 DECLARE @output VARCHAR(MAX)
 SET @output='' 
 SELECT @output=@output+(CASE @output WHEN '' THEN '' ELSE  ',' END)+compliance_name FROM tbl_sref_member_compliance
 PRINT @output
 

6)Clustered and Non-Clustered Index

http://www.codeproject.com/Articles/173275/Clustered-and-Non-Clustered-Index-in-SQL-2005 

8)Find Nth Highest Value from a Table

SELECT TOP 1 marks
FROM  

(
SELECT DISTINCT TOP n marks
FROM tbl_students
ORDER BY marksDESC

) a
ORDER BY marks

9) SQL Optimization

a)sql query  becomes faster if column name is used instead of *

slower 

Select * from Student_table

Faster 

Select Student_name from Student Table

b)To store large binary object , use file group instead on file

c) Try to write minimised number of sub query

d) Use "Exists" operator instead of "In"

Slower

Select * from tbl_student

  where id in (Select id from tbl_student where roll_no>5)

Faster

Select * from tbl_student 

  where exists (Select id from tbl_student where roll_no>5)

10) sp_executesql

Executes a Transact-SQL statement or batch that can be reused many times .

for example

Declare @class_id INT,@param INT

sp_executesql sp_stydebt @class_id ,@param  OUTPUT

12) Select Top N from Table

 
DECLARE  @MyTable table (Name varchar(200),salary decimal(18,2))

INSERT INTO @MyTable(Name,salary)
VALUES('AYAN',200),('SAYAN',300),('Jatin',250),('Jatin lal',400),('Soumay',280)


select * from @MyTable ;

WITH ABC AS 
(
 SELECT salary , Name ,
 ROW_NUMBER() OVER (ORDER BY salary) AS 'Row_Number'
 FROM @MyTable  
)
SELECT * FROM ABC WHERE Row_Number=N

13) INNER JOIN AND CROSS JOIN

    Inner join only results of matching records..for example we have created 

table A and B...

Results is                                                                                   

Cross Join result all combination of records irrespective of any matching records

Result is

 14)Common Table Expression

  http://www.codeproject.com/Articles/265371/Common-Table-Expressions-CTE-in-SQL-SERVER 

 15)Another PIVOT EXample
 

16)Row_Number Over 

17)Find The N Th highest number

Here N=5 

 18) How to show only even rows ?

SELECT * FROM 
( 
 SELECT role_id,
 role_name,
 ROW_NUMBER() OVER(ORDER BY role_id DESC) AS 'RowNumber' 

        FROM tbl_sref_app_role
) X where (RowNumber % 2) = 0
ORDER BY role_id 

 19) Find the n th highest number

  
    select max(member_id) from tbl_event_roster

    --nth highest

   select top 1 member_id from 
   (
    select top 5 member_id from tbl_event_roster order by member_id desc
   ) a
   order by a.member_id desc

   --
   with  a (member_id)
   as 
   (
     select top 10 member_id from tbl_event_roster order by member_id desc
   ) 
   select top 6 member_id from a order by member_id desc

 20)CUBE

 SELECT  i.shif,
         i.location,
         i.bin, 
  CASE GROUPIN_ID(i.shif,i.location,i.bin )
   WHEN 1 THEN 'Shelf/Location Total'
   WHEN 2 THEN 'Shelf/Bin Total'
   WHEN 3 THEN 'Shelf Total'
   WHEN 4 THEN 'Location Bin Total'
   WHEN 5 THEN 'Location Total'
   WHEN 6 THEN 'Bin Total'
   WHEN 7 THEN 'Grand Total'
   ELSE 'regular row'
  END,
  SUM(i.quantity) AS Total
FROM Production
GROUP BY CUBE(i.shif,i.location,i.bin)
ORDER BY i.shif,
         i.location,
         i.bin

SELECT  member_id,
  role_id,
  reference_id,
  SUM(reference_type_id)
  FROM tbl_user_role
  GROUP BY CUBE(member_id,role_id,reference_id)
  ORDER BY member_id,role_id,reference_id

 21)PERCENT

 SELECT TOP (5) PERCENT
              member_id FROM 
              tbl_user_role

 22)UNPIVOT

SELECT Employee_ID,
       PhoneType,
       PhoneValue 
       FROM Contact C
UNPIVOT
(
PhoneValue FOR PhoneType IN ([Ph1],[Ph2],[Ph3],[Ph4],[Ph5])
)AS P 

23) CROSS ApplY

CREATE TABLE Company 
(
    companyId int identity(1,1),

    companyName varchar(100),

    zipcode varchar(10) ,

    constraint PK_Company primary key (companyId)
)
GO

CREATE TABLE Person 
(
    personId int identity(1,1),

    personName varchar(100),

    companyId int ,

    constraint FK_Person_CompanyId foreign key (companyId) references dbo.Company(companyId),

    constraint PK_Person primary key (personId)
)
GO

INSERT Company
SELECT 'ABC Company', '19808' 
UNION
SELECT 'XYZ Company', '08534' 
UNION
SELECT '123 Company', '10016'


INSERT Person
SELECT 'Alan', 1 
UNION
SELECT 'Bobby', 1 
UNION
SELECT 'Chris', 1 
UNION
SELECT 'Xavier', 2 
UNION
SELECT 'Yoshi', 2 
UNION
SELECT 'Zambrano', 2 
UNION
SELECT 'Player 1', 3 
UNION
SELECT 'Player 2', 3 
UNION
SELECT 'Player 3', 3 


SELECT *
FROM Person p
CROSS APPLY
(
    SELECT *
    FROM Company c
    WHERE p.companyid = c.companyId
) Czip

21)

DECLARE @quotedcountrynames NVARCHAR(MAX)

SET @quotedcountrynames = STUFF((
            SELECT DISTINCT ',' + QUOTENAME(CustomerCountry)
            FROM Customer
            FOR XML PATH('')
                ,TYPE
            ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

PRINT @quotedcountrynames

output

[Dave],[Johny],[Tommy]