Last June 19, 2007, at the Pearl Continental Hotel here in Lahore, I attended the Pakistan Developer Conference 2007. The speaker was Stephen Forte.

He had shared the latest Database Training techniques on SQL Server 2005 and I would like to share these techniques and updates because of its simplicity and for everybody to be in with the latest with the database programming.

Visit my blog and learn a lot from it.

I would like to personally thank Stephen Forte for imparting his knowledge at the conference and I hope you too will have get an addition point from this topic.  

—————————————————————————————–

Setup TSQL Problems
CREATE TABLE Classes (
  ClassID int NOT NULL,
  Class varchar (50),
  Students int NOT NULL, CONSTRAINT PK_Classes PRIMARY KEY CLUSTERED (ClassID
) )
Â
GO

CREATE TABLE Rooms (
  Room int NOT NULL,
  Capacity int NOT NULL,
CONSTRAINT PK_Rooms PRIMARY KEY CLUSTERED (Room)
)
GO

CREATE TABLE Products (
  Product_ID int NOT NULL,
  Product_Name varchar (25),
  Price money NULL,
CONSTRAINT PK_Products PRIMARY KEY NONCLUSTERED (Product_ID)Â
)
GO
INSERT INTO Classes (ClassID, Class, Students) VALUES (1, ‘Advanced SQL Queries’, 65)
INSERT INTO Classes (ClassID, Class, Students) VALUES (2, ‘Introduction to C#’, 52)
INSERT INTO Classes (ClassID, Class, Students) VALUES (3, ‘XQuery Deep Dive’, 35)
GO

INSERT INTO Products (Product_ID, Product_Name, Price) VALUES (1, ‘Widgets’, 25)
INSERT INTO Products (Product_ID, Product_Name, Price) VALUES (2, ‘Gadgets’, 50)
INSERT INTO Products (Product_ID, Product_Name, Price) VALUES (3, ‘Thingies’, 75)
INSERT INTO Products (Product_ID, Product_Name, Price) VALUES (4, ‘Whoozits’, 90)
INSERT INTO Products (Product_ID, Product_Name, Price) VALUES (5, ‘Whatzits’, 5)
INSERT INTO Products (Product_ID, Product_Name, Price) VALUES (6, ‘Gizmos’, 15)
INSERT INTO Products (Product_ID, Product_Name, Price) VALUES (7, ‘Widgets’, 8 )

GO

INSERT INTO Rooms (Room, Capacity) VALUES (221, 30)
INSERT INTO Rooms (Room, Capacity) VALUES (222, 40)
INSERT INTO Rooms (Room, Capacity) VALUES (223, 50)
INSERT INTO Rooms (Room, Capacity) VALUES (224, 60)
INSERT INTO Rooms (Room, Capacity) VALUES (225, 70)
GO

—————————————————————————————

/*
Example 1: Subqueries
*/

– Finding and removing duplicates

USE AdvancedQueries
SELECT * FROM Products

INSERT INTO Products (Product_ID, Product_Name, Price)
 VALUES (7, ‘Widgets’, 24)
INSERT INTO Products (Product_ID, Product_Name, Price)
 VALUES (8, ‘Gizmos’, 36)
INSERT INTO Products (Product_ID, Product_Name, Price)
 VALUES (9, ‘Gizmos’, 36)

SELECT * FROM Products WHERE Product_ID NOT IN
(SELECT MIN(Product_ID) FROM Products AS P
 WHERE Products.Product_Name = P.Product_Name)

DELETE FROM Products WHERE Product_ID NOT IN
(SELECT MIN(Product_ID) FROM Products AS P
 WHERE Products.Product_Name = P.Product_Name)

–Stephen (SQL Server 2005)
– Finding and removing duplicates with a CTE
INSERT INTO Products (Product_ID, Product_Name, Price)
 VALUES (7, ‘Widgets’, 24)
INSERT INTO Products (Product_ID, Product_Name, Price)
 VALUES (8, ‘Gizmos’, 36)
INSERT INTO Products (Product_ID, Product_Name, Price)
 VALUES (9, ‘Gizmos’, 36)

WITH CTEMinProductRecords AS (
 SELECT MIN(Product_ID) AS Product_ID, Product_Name
 FROM Products
 GROUP BY Product_Name
 HAVING COUNT(*) > 1)
SELECT * — DELETE Products
FROM Products JOIN CTEMinProductRecords ON
 Products.Product_Name = CTEMinProductRecords.Product_Name
 AND Products.Product_ID > CTEMinProductRecords.Product_ID

– Range matching
USE AdvancedQueries
SELECT Class, Students, Room, Capacity
FROM Rooms, Classes
WHERE Capacity =
 (SELECT MIN(Capacity) FROM Rooms
  WHERE Capacity > Classes.Students)

– Break the query
UPDATE Classes SET Students = 32 WHERE ClassID = 2

– Fixing the duplicate room problem

CREATE TABLE #classlist (
classid int,
students int,
room int,
capacity int)

INSERT INTO #classlist (classid, students)
SELECT classid, students FROM classes

DECLARE @curclass int
DECLARE @roomnum int

SELECT @curclass = MIN(classid)
FROM #classlist WHERE room IS NULL

WHILE @curclass IS NOT NULL
BEGIN
  SELECT @roomnum = MIN(room) FROM rooms
  WHERE capacity >=
   (SELECT students FROM #classlist
    WHERE classid = @curclass)
   AND room NOT IN
   (SELECT room FROM #classlist
    WHERE room IS NOT NULL)
  IF @roomnum IS NULL
  BEGIN
    PRINT ‘Uh oh.’
    BREAK
  END
  UPDATE #classlist SET
   #classlist.room = @roomnum,
   #classlist.capacity = rooms.capacity
   FROM rooms WHERE rooms.room = @roomnum
    and #classlist.classid = @curclass
  SELECT @curclass = MIN(classid)
  FROM #classlist WHERE room IS NULL
END

SELECT classes.class, #classlist.students,
 #classlist.room, #classlist.capacity
FROM #classlist INNER JOIN classes ON #classlist.classid = classes.classid

DROP TABLE #classlist

–better solution
–problem 2
–add more data one
INSERT INTO Classes (ClassID, Class, Students) VALUES (4, ‘Ranking and Windowning’, 35)
INSERT INTO Classes (ClassID, Class, Students) VALUES (5, ‘SQL CLR in Action!’, 65)
INSERT INTO Classes (ClassID, Class, Students) VALUES (6, ‘ShowPlan for Developers’, 75)
INSERT INTO Classes (ClassID, Class, Students) VALUES (7, ‘CrossTabs for Dummies’, 1)
– Break the query

UPDATE Classes SET Students = 32 WHERE ClassID = 2
– Fixing the duplicate room problem

CREATE TABLE #tempClasses1 (
  ClassID int,
  Class varchar(50),
  Students int)
insert into #tempClasses1
select * from Classes

CREATE TABLE #tempClasses2 (
  ClassID int,
  Class varchar(50),
  Students int,
  Room int,
  Capacity int )

insert into #tempClasses2
select ClassId, Class, Students, null,null from Classes

CREATE TABLE #tempRooms (
  Room int,
  Capacity int)

insert into #tempRooms
select * from Rooms
–====
–Code
–======

declare @countClassId int
set @countClassId = (select count(ClassId) from #tempClasses1)

declare @currentRecCount int
set @currentRecCount = 1

declare @ClassId int, @room int, @Capacity int

while @currentRecCount <= @countClassId
begin
 set @ClassId = (select top 1 ClassId from #tempClasses1)

 set @room = (select top 1 Room from #tempRooms, #tempClasses1
     where Capacity >= Students
     and ClassId = @ClassId)
 set @Capacity = (select top 1 Capacity from #tempRooms, #tempClasses1
     where Capacity >= Students
     and ClassId = @ClassId)
 update #tempClasses2
 set Room = @room, Capacity = @Capacity
 where ClassId = @ClassId

 delete #tempClasses1 where ClassId = @ClassId
 if @room is not null
  delete #tempRooms where Room = @room

 set @currentRecCount = @currentRecCount + 1
end

drop table #tempClasses1
drop table #tempRooms
select *
from #tempClasses2
Order by Capacity DESC

drop table #tempClasses2

/*
Example 2: Self Joining
*/

–Use a Custom Table with tree data
–ReportsTo is a “domestic key” back to Employee_id

create table Employee_Tree
 (Employee_NM nvarchar(50),
  Employee_ID int Primary Key,
  ReportsTo int)
–insert some data, build a reporting tree
insert into employee_tree values(’Richard’, 1, null)

–notice who the boss is

insert into employee_tree values(’Stephen’, 2, 1)

–I wrote this example and I am still not the boss

insert into employee_tree values(’Clemens’, 3, 2)
insert into employee_tree values(’Malek’, 4, 2)
insert into employee_tree values(’Goksin’, 5, 4)
insert into employee_tree values(’Kimberly’, 6, 1)
insert into employee_tree values(’Ramesh’, 7, 5)

–Richard (SQL Server 2000)

– Self join example, reports to
SELECT e1.Employee_NM AS Employee,
 e2.Employee_NM AS ReportsTo
FROM Employee_Tree AS e1 INNER JOIN Employee_Tree AS e2
 ON e1.ReportsTo = e2.Employee_ID

– Round-robin example using self joining

SELECT e1.Employee_NM, e2.Employee_NM
FROM Employee_Tree AS e1 INNER JOIN Employee_Tree AS e2
 ON e1.Employee_NM > e2.Employee_NM

–Stephen (SQL Server 2005)
–Use the new feature: Common Table Expressions
–to do a recrusive query

WITH SimpleRecurvice(Employee_NM, Employee_ID, ReportsTO)
 AS
(SELECT Employee_NM, Employee_ID, ReportsTO
  FROM Employee_Tree WHERE Employee_ID = 2
UNION ALL
SELECT p.Employee_NM, p.Employee_ID, p.ReportsTO
 FROM Employee_Tree  P  INNER JOIN
 SimpleRecurvice A ON A.Employee_ID = P.ReportsTO
)
SELECT sr.Employee_NM as Employee, et.employee_nm as Boss
FROM SimpleRecurvice sr inner join Employee_Tree et
on sr.reportsto=et.employee_id
–OPTION(MAXRECURSION 2) –if you only want 2 levels

/*
Example 3: Ranking
*/

– Finding the nth item in a list

USE AdvancedQueries
SELECT p1.Product_Name, p1.Price, count(*) AS Rank
FROM Products AS p1, Products AS p2
WHERE p1.Price >= p2.Price
GROUP BY p1.Product_Name, p1.Price
HAVING COUNT(*) = 3

–Stephen (SQL Server 2005)
–Use the new Ranking Functions
–Eliminate the Self Join and use a Common Table Expression (CTE)

With CTEExpensiveProd
As
(
select Product_ID, Product_Name,
Price, Rank() Over (Order By Price DESC) As RankPrice
from Products
)
Select * from CTEExpensiveProd where RankPrice=3

–Stephen (SQL Server 2005)
–Demonstarate the new Ranking Functions
With CustomerSum
As
(
Select CustomerID,
round(convert(int, sum(totaldue))/100,8) *100 as totalamt
From Sales.SalesOrderHeader
Group by CustomerID
)
Select *,
 Rank() Over (Order By totalamt Desc) as Rank,
 Dense_Rank() Over (Order By totalamt Desc) as DenseRank,
 Row_Number() Over (Order By totalamt Desc) as RowNumber,
 NTile(100) Over (Order By totalamt Desc) as Percentile
Â
From CustomerSum

/*
Example 4: Performing a Cross Tab Query
*/

–Richard (SQL Server 2000)

USE AdvancedQueries

– Using Subqueries

SELECT Salespeople.Salesperson, SUM(S1.Quantity*S1.Price) AS Total,
  (SELECT SUM(S2.Quantity*S2.Price) FROM Sales AS S2 WHERE
    DatePart(mm,S2.Invoice_Date)=1 AND S2.Sales_ID = S1.Sales_ID) AS Jan,
  (SELECT SUM(S2.Quantity*S2.Price) FROM Sales AS S2 WHERE
    DatePart(mm,S2.Invoice_Date)=2 AND S2.Sales_ID = S1.Sales_ID) AS Feb,
  (SELECT SUM(S2.Quantity*S2.Price) FROM Sales AS S2 WHERE
    DatePart(mm,S2.Invoice_Date)=3 AND S2.Sales_ID = S1.Sales_ID) AS Mar,
  (SELECT SUM(S2.Quantity*S2.Price) FROM Sales AS S2 WHERE
    DatePart(mm,S2.Invoice_Date)=4 AND S2.Sales_ID = S1.Sales_ID) AS Apr,
  (SELECT SUM(S2.Quantity*S2.Price) FROM Sales AS S2 WHERE
    DatePart(mm,S2.Invoice_Date)=5 AND S2.Sales_ID = S1.Sales_ID) AS May,
  (SELECT SUM(S2.Quantity*S2.Price) FROM Sales AS S2 WHERE
    DatePart(mm,S2.Invoice_Date)=6 AND S2.Sales_ID = S1.Sales_ID) AS Jun,
  (SELECT SUM(S2.Quantity*S2.Price) FROM Sales AS S2 WHERE
    DatePart(mm,S2.Invoice_Date)=7 AND S2.Sales_ID = S1.Sales_ID) AS Jul,
  (SELECT SUM(S2.Quantity*S2.Price) FROM Sales AS S2 WHERE
    DatePart(mm,S2.Invoice_Date)=8 AND S2.Sales_ID = S1.Sales_ID) AS Aug,
  (SELECT SUM(S2.Quantity*S2.Price) FROM Sales AS S2 WHERE
    DatePart(mm,S2.Invoice_Date)=9 AND S2.Sales_ID = S1.Sales_ID) AS Sep,
  (SELECT SUM(S2.Quantity*S2.Price) FROM Sales AS S2 WHERE
    DatePart(mm,S2.Invoice_Date)=10 AND S2.Sales_ID = S1.Sales_ID) AS Oct,
  (SELECT SUM(S2.Quantity*S2.Price) FROM Sales AS S2 WHERE
    DatePart(mm,S2.Invoice_Date)=11 AND S2.Sales_ID = S1.Sales_ID) AS Nov,
  (SELECT SUM(S2.Quantity*S2.Price) FROM Sales AS S2 WHERE
    DatePart(mm,S2.Invoice_Date)=12 AND S2.Sales_ID = S1.Sales_ID) AS Dec
FROM Sales AS S1 INNER JOIN Salespeople ON S1.Sales_ID = Salespeople.Sales_ID
GROUP BY Salespeople.Salesperson, S1.Sales_ID;

– Rozenshtein

SELECT Salespeople.Salesperson, SUM(Sales.Quantity*Sales.Price) AS Total,
  SUM(Sales.Quantity*Sales.Price*(1-ABS(SIGN(DatePart(mm,Sales.Invoice_Date)-1)))) AS Jan,
  SUM(Sales.Quantity*Sales.Price*(1-ABS(SIGN(DatePart(mm,Sales.Invoice_Date)-2)))) AS Feb,
  SUM(Sales.Quantity*Sales.Price*(1-ABS(SIGN(DatePart(mm,Sales.Invoice_Date)-3)))) AS Mar,
  SUM(Sales.Quantity*Sales.Price*(1-ABS(SIGN(DatePart(mm,Sales.Invoice_Date)-4)))) AS Apr,
  SUM(Sales.Quantity*Sales.Price*(1-ABS(SIGN(DatePart(mm,Sales.Invoice_Date)-5)))) AS May,
  SUM(Sales.Quantity*Sales.Price*(1-ABS(SIGN(DatePart(mm,Sales.Invoice_Date)-6)))) AS Jun,
  SUM(Sales.Quantity*Sales.Price*(1-ABS(SIGN(DatePart(mm,Sales.Invoice_Date)-7)))) AS Jul,
  SUM(Sales.Quantity*Sales.Price*(1-ABS(SIGN(DatePart(mm,Sales.Invoice_Date)-8)))) AS Aug,
  SUM(Sales.Quantity*Sales.Price*(1-ABS(SIGN(DatePart(mm,Sales.Invoice_Date)-9)))) AS Sep,
  SUM(Sales.Quantity*Sales.Price*(1-ABS(SIGN(DatePart(mm,Sales.Invoice_Date)-10)))) AS Oct,
  SUM(Sales.Quantity*Sales.Price*(1-ABS(SIGN(DatePart(mm,Sales.Invoice_Date)-11)))) AS Nov,
  SUM(Sales.Quantity*Sales.Price*(1-ABS(SIGN(DatePart(mm,Sales.Invoice_Date)-12)))) AS Dec
FROM Sales INNER JOIN Salespeople ON Sales.Sales_ID = Salespeople.Sales_ID
GROUP BY Salespeople.Salesperson;

– Using CASE
 

SELECT Salespeople.Salesperson, SUM(Sales.Quantity*Sales.Price) AS Total,
  SUM(CASE DATEPART(mm, Sales.Invoice_Date) WHEN 1 THEN Sales.Quantity*Sales.Price ELSE 0 END) AS Jan,
  SUM(CASE DATEPART(mm, Sales.Invoice_Date) WHEN 2 THEN Sales.Quantity*Sales.Price ELSE 0 END) AS Feb,
  SUM(CASE DATEPART(mm, Sales.Invoice_Date) WHEN 3 THEN Sales.Quantity*Sales.Price ELSE 0 END) AS Mar,
  SUM(CASE DATEPART(mm, Sales.Invoice_Date) WHEN 4 THEN Sales.Quantity*Sales.Price ELSE 0 END) AS Apr,
  SUM(CASE DATEPART(mm, Sales.Invoice_Date) WHEN 5 THEN Sales.Quantity*Sales.Price ELSE 0 END) AS May,
  SUM(CASE DATEPART(mm, Sales.Invoice_Date) WHEN 6 THEN Sales.Quantity*Sales.Price ELSE 0 END) AS Jun,
  SUM(CASE DATEPART(mm, Sales.Invoice_Date) WHEN 7 THEN Sales.Quantity*Sales.Price ELSE 0 END) AS Jul,
  SUM(CASE DATEPART(mm, Sales.Invoice_Date) WHEN 8 THEN Sales.Quantity*Sales.Price ELSE 0 END) AS Aug,
  SUM(CASE DATEPART(mm, Sales.Invoice_Date) WHEN 9 THEN Sales.Quantity*Sales.Price ELSE 0 END) AS Sep,
  SUM(CASE DATEPART(mm, Sales.Invoice_Date) WHEN 10 THEN Sales.Quantity*Sales.Price ELSE 0 END) AS Oct,
  SUM(CASE DATEPART(mm, Sales.Invoice_Date) WHEN 11 THEN Sales.Quantity*Sales.Price ELSE 0 END) AS Nov,
  SUM(CASE DATEPART(mm, Sales.Invoice_Date) WHEN 12 THEN Sales.Quantity*Sales.Price ELSE 0 END) AS Dec
FROM Sales INNER JOIN Salespeople ON Sales.Sales_ID = Salespeople.Sales_ID
GROUP BY Salespeople.Salesperson

– Dynamic Crosstab

DECLARE @SQL nvarchar(4000)
DECLARE @Salesperson varchar(25)

CREATE TABLE #SalespersonList (
Salesperson varchar(25))

INSERT INTO #SalespersonList
SELECT Salesperson FROM Salespeople

SET @SQL = ‘SELECT Products.Product_Name, SUM(Sales.Quantity) AS Total, ‘

SELECT @Salesperson = (SELECT MIN(Salesperson) FROM #SalespersonList)

WHILE @Salesperson IS NOT NULL
BEGIN
  SET @SQL = @SQL + ‘SUM(Sales.Quantity*(1-ABS(SIGN(CHARINDEX(’ +
   ”” + @Salesperson + ”’, Salespeople.Salesperson)-1)))) AS ‘ +
   @Salesperson + ‘, ‘
  DELETE FROM #SalespersonList WHERE Salesperson = @Salesperson
  SELECT @Salesperson = (SELECT MIN(Salesperson) FROM #SalespersonList)
END

SET @SQL = LEFT(@SQL, LEN(@SQL)-1) +
 ‘ FROM (Sales INNER JOIN Salespeople ON Sales.Sales_ID = Salespeople.Sales_ID) ‘ +
 ‘ INNER JOIN Products ON Sales.Product_ID = Products.Product_ID ‘ +
 ‘ GROUP BY Products.Product_Name’

EXEC sp_executesql @SQL

–Stephen (SQL Server 2005)
–Yukon introduces a new SQL 92 Keyword: Pivot
–pivot the salesorderheader data by summed years aggergates

Use AdventureWorks
SELECT CustomerID, [2001] AS Y2001, [2002] AS Y2002, [2003] AS Y2003, [2004] AS Y2004
FROM
(
SELECT CustomerID, datepart(yyyy, OrderDate) as OrderYear, TotalDue
From Sales.SalesOrderHeader
) piv
PIVOT
(
Sum (TotalDue)
For OrderYear IN
([2001], [2002], [2003], [2004])
) As chld
Order by CustomerID

SELECT *
FROM (SELECT CustomerID, YEAR(OrderDate) AS orderyear, TotalDue
      FROM Sales.SalesOrderHeader) as Header
PIVOT(SUM(TotalDue) FOR orderyear IN([2002],[2003],[2004])) as Piv

–Dynamic Pivot

DECLARE @tblOrderDate AS TABLE(y int NOT NULL PRIMARY KEY)
INSERT INTO @tblOrderDate SELECT DISTINCT YEAR(OrderDate) FROM Sales.SalesOrderHeader
– Construct the column list for the IN clause
– e.g., [2002],[2003],[2004]
DECLARE @cols AS nvarchar(MAX), @years AS int
SET @years = (SELECT MIN(y) FROM @tblOrderDate)
SET @cols = N”
WHILE @years IS NOT NULL
BEGIN
  SET @cols = @cols + N’,['+CAST(@years AS nvarchar(10))+N']‘
  SET @years = (SELECT MIN(y) FROM @tblOrderDate WHERE y > @years)
END
SET @cols = SUBSTRING(@cols, 2, LEN(@cols))
– Construct the full T-SQL statement and execute it dynamically.
DECLARE @sql AS nvarchar(MAX)
SET @sql = N’SELECT *
FROM (SELECT CustomerID, YEAR(OrderDate) AS orderyear, TotalDue
      FROM Sales.SalesOrderHeader) as Header
  PIVOT(SUM(TotalDue) FOR orderyear IN(’ + @cols + N’)) AS Piv’
PRINT @sql — for debugging
EXEC sp_executesql @sql