Sql Server Sample Queries

1. How to use SQL CROSS JOIN in automatic sequence generation?
use AdventureWorks
go

-- Create a cte to give the sequence of 1-9
with cteSequence
as
(select 0 SeqNo
union select 1
union select 2
union select 3
union select 4
union select 5
union select 6
union select 7
union select 8
union select 9)

select DayInMonth=dateadd(d,10*b.SeqNo+a.SeqNo,
       convert(varchar,getdate(),111))
from cteSequence a
cross join cteSequence b
where b.SeqNo < 4
and 10*b.SeqNo + a.SeqNo < 31 

2. How to use CROSS APPLY with an aggregation subquery?

-- The outer query adds customer name to the inner query aggregation results
SELECT [Customer]=s.Name,

-- This is a special money type currency formatting option
[Total$ Sales] = '$'+convert(varchar,convert(money,SalesAmount.OrderTotal),1)
FROM   Sales.Customer as c

-- The customer name is in this table
INNER JOIN Sales.Store as s
    ON s.CustomerID = c.CustomerID

-- The inner query is a correlated GROUP BY subquery
CROSS APPLY (SELECT soh.CustomerId, sum(sod.LineTotal) as OrderTotal
               FROM   Sales.SalesOrderHeader as soh
               INNER JOIN Sales.SalesOrderDetail as sod
                   ON sod.SalesOrderId = soh.SalesOrderId
              
               -- This is the correlation to the outer query
               WHERE soh.CustomerId = c.CustomerId

               -- Filter data
               AND     YEAR(OrderDate)= 2004
               AND     MONTH(OrderDate) = 1
               GROUP by soh.CustomerId) as SalesAmount
ORDER BY [Customer]
 

3. How to architect running total using cursor?
ALTER TABLE Sales.SalesOrderHeader
  ADD CumulativeTotal MONEY NOT NULL
  CONSTRAINT dfSalesOrderHeader DEFAULT(0)
go --



SET NoCount ON
DECLARE
  @SalesOrderID INT,
  @TotalDue MONEY,
  @CumulativeTotal MONEY

SET @CumulativeTotal = 0
  -- 1) set up the cursor     
  DECLARE cRun CURSOR FAST_FORWARD
    FOR
      SELECT SalesOrderID, TotalDue
        FROM Sales.SalesOrderHeader
        ORDER BY OrderDate, SalesOrderID

   -- 2) open the cursor
  OPEN cRun

  FETCH cRun INTO @SalesOrderID, @TotalDue  -- prime the cursor
  WHILE @@Fetch_Status = 0
    BEGIN
      SET @CumulativeTotal = @CumulativeTotal + @TotalDue
      UPDATE Sales.SalesOrderHeader
        SET CumulativeTotal = @CumulativeTotal
        WHERE SalesOrderID = @SalesOrderID   
      FETCH cRun INTO @SalesOrderID, @TotalDue  -- fetch next
    END
  CLOSE cRun

  DEALLOCATE cRun
go --

4. How to use the CASE function in a WHERE clause?

USE AdventureWorks
GO
SELECT Category=c.Name,
Subcategory=sc.Name,
ProductName = p.Name,
ProductNumber,
Color = isnull(Color,''),
Cost = '$'+convert(varchar,StandardCost,1),
ListPrice = '$'+convert(varchar,ListPrice,1),
ProductLine
FROM Production.Product p
JOIN Production.ProductSubcategory sc
ON p.ProductSubcategoryID=sc.ProductSubcategoryID
JOIN Production.ProductCategory c
ON sc.ProductCategoryID = c.ProductCategoryID
WHERE p.Name like (
CASE ProductLine
WHEN 'R' THEN 'Road%'
WHEN 'M' THEN 'Mountain%'
WHEN 'T' THEN 'Touring%'
WHEN 'S' THEN '%Shorts%'
ELSE 'Not for sale%'
END)
ORDER BY Category, Subcategory, ProductName;
GO
 

5. How to find top 10 for each group?

USE AdventureWorks;

WITH cteTop10Sales
AS
(SELECT ROW_NUMBER() OVER (PARTITION BY sod.ProductID ORDER BY SUM(sod.LineTotal) DESC) As SeqNo
, FirstName +' '+LastName AS [Name]
, ProductName = p.Name

, '$'+convert(varchar,convert(money,SUM(sod.LineTotal)),1) AS TotalBySalesPerson
, p.ProductNumber
, sod.ProductID
FROM Sales.SalesOrderDetail AS sod
INNER JOIN Production.Product AS p
ON sod.ProductID = p.ProductID
INNER JOIN Sales.SalesOrderHeader soh
ON sod.SalesOrderID = soh.SalesOrderID
INNER JOIN Person.Contact c
ON soh.SalesPersonID = c.ContactID
WHERE soh.SalesPersonID IS NOT NULL
GROUP BY FirstName +' '+LastName, sod.ProductID, p.ProductNumber, p.Name)

SELECT *
FROM cteTop10Sales cte
WHERE SeqNo <= 10
ORDER BY ProductID, SeqNo
GO

6. How to resolve string concatenation collation conflict?


use tempdb
go
select ContactID, FirstName, LastName
into ContactAS
from AdventureWorks.Person.Contact
go

select ContactID,
FirstName = FirstName COLLATE SQL_Latin1_General_CP1_CI_AI,
LastName = LastName COLLATE SQL_Latin1_General_CP1_CI_AI
into ContactAI
from AdventureWorks.Person.Contact
go
-- Collation conflict
select [Name] = s.FirstName + ' '+i.LastName
from ContactAS s
join ContactAI i
on s.ContactID = i.ContactID
order by [Name]
go
-- Error message: Cannot resolve collation conflict for column 1 in SELECT statement.
-- Collation conflict resolved
select [Name] = s.FirstName + ' '+i.LastName COLLATE DATABASE_DEFAULT
from ContactAS s
join ContactAI i
on s.ContactID = i.ContactID
order by [Name]
go
select [Name] = s.FirstName + ' '+i.LastName COLLATE SQL_Latin1_General_CP1_CI_AI
from ContactAS s
join ContactAI i
on s.ContactID = i.ContactID
order by [Name]
go

7. How to use the XML value method?
 

USE AdventureWorks
SELECT distinct Demographics.value('declare namespace
AWMI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";
(AWMI:StoreSurvey/AWMI:BankName)[1]','varchar(30)')
as [Store Banks]
FROM Sales.Store

8. How to architect a trigger on column update?

CREATE TRIGGER trgEmployeeUpdate
ON Employee
AFTER UPDATE AS
IF (UPDATE(Salary) or UPDATE(SSN))
BEGIN
INSERT INTO CorpSecurityEmployeeHistory
(auditlogtype,auditEmployeeDeptID,auditEmployeeID,
auditEmployeeSalary,auditEmployeeSSN,auditUpdatedBy)
SELECT 'PREVIOUSDATA',DeptID, EmployeeID,Salary,
SSN,USER_NAME()FROM DELETED
INSERT INTO CorpSecurityEmployeeHistory
(auditlogtype,auditEmployeeDeptID,auditEmployeeID,
auditEmployeeSalary,auditEmployeeSSN,auditUpdatedBy)
SELECT 'NEWDATA',DeptID, EmployeeID,Salary,SSN,
USER_NAME()FROM INSERTED END;
GO

9. How to sort on multiple conditions?

DECLARE @SortBy CHAR(10), @SortByDirection CHAR(1)
SET @SortBy = 'City'
SET @SortByDirection = 'A' -- or D for descending
SELECT
AddressLine1,
City,
State=p.Name,
PostalCode
FROM Person.Address a
JOIN Person.StateProvince p
ON a.StateProvinceID=p.StateProvinceID
WHERE AddressLine1 like '%drive%'
ORDER BY
CASE WHEN @SortBy = 'Postal' AND @SortByDirection = 'D' THEN PostalCode END DESC,
CASE WHEN @SortBy = 'Postal' AND @SortByDirection = 'A' THEN PostalCode END,
CASE WHEN @SortBy = 'State' AND @SortByDirection = 'D' THEN p.[Name] END DESC,
CASE WHEN @SortBy = 'State' AND @SortByDirection = 'A' THEN p.[Name] END,
CASE WHEN @SortBy = 'City' AND @SortByDirection = 'D' THEN City END DESC,
CASE WHEN @SortBy = 'City' AND @SortByDirection = 'A' THEN City END
GO


10. How to swap rows to columns?

use AdventureWorks
go
select ProductName=p.Name,
'$'+convert(varchar,sum (case when year(TransactionDate)=2000 then ActualCost end),1) as Y2000,
'$'+convert(varchar,sum (case when year(TransactionDate)=2001 then ActualCost end),1) as Y2001,
'$'+convert(varchar,sum (case when year(TransactionDate)=2002 then ActualCost end),1) as Y2002,
'$'+convert(varchar,sum (case when year(TransactionDate)=2003 then ActualCost end),1) as Y2003,
'$'+convert(varchar,sum (case when year(TransactionDate)=2004 then ActualCost end),1) as Y2004,
'$'+convert(varchar,sum (case when year(TransactionDate)=2005 then ActualCost end),1) as Y2005
from Production.TransactionHistory th
join Production.Product p
on p.ProductID = th.ProductID
group by p.Name order by p.Name

11. How to find the number of occurances of a string?

CREATE FUNCTION fnCountOccurances(
@ShortString varchar(100),
@LongString varchar(8000))
RETURNS int
AS
BEGIN
DECLARE @Text varchar(8000), @Frequency int
SET @Text = @LongString
SET @Text= REPLACE(@Text, @ShortString, '')
SET @Frequency = (len (rtrim(@LongString))
- len (rtrim(@Text)))
/len(rtrim(@ShortString))
RETURN (@Frequency)
END
GO

SELECT dbo.fnCountOccurances ('fox', 'The fox ran in the forest after other foxes.')
GO

SELECT dbo.fnCountOccurances ('o', 'The fox ran in the forest after other foxes.')
GO

12. How to generate a date sequence with ROW_NUMBER?

use AdventureWorks
select top 1000 [Date] = dateadd(day, ROW_NUMBER()
over(order by c1.name, c2.name), convert(char(10),getdate()-1,110))
from sys.columns c1
cross join sys.columns c2

13. How to remove multiple spaces from a string?

CREATE FUNCTION fnRemoveMultipleSpaces(@InputString varchar(1024))
RETURNS varchar(1024)
AS
BEGIN
WHILE CHARINDEX(' ', @InputString) > 0
SET @InputString = REPLACE(@InputString, ' ', ' ') -- replace 2 spaces with 1 space
RETURN @InputString
END
-- SELECT dbo.fnRemoveMultipleSpaces ('The fox ran in the forest!')