Tsql Demos

Rajib Bahar e2D Services/iCSQL

   

TSQL? Demos Questions Reference

        

Select/Insert/Update/Delete Creating DB, Stored Proc, Functions Top, Distinct, Group by Subqueries – IN, EXISTS Apply Operator – CROSS, OUTER Common Table Expression – CTE Pivoting Data – PIVOT/ UNPIVOT Ranking Functions – ROW_NUMBER, RANK, DENSE_RANK, NTILE Exception Handling

   

 

SELECT - to read data Insert – to add more data into the database Update - to change existing record inside the database Delete – to remove existing data from the database CRUD TSQL Cheatsheet

  

Demo DB Demo Proc – Cons/Pros Demo Functions – Cons/Pros



Top – is used to specify how many of the first N results we want to get

Select top 10 * from [AdventureWorks2008].[dbo].[Address] 



We can get certain percentage of result set Select top 10 percent * from [AdventureWorks2008].[dbo].[Address] We can use variables to specify total as well Declare @amt int Set @amt = 5 Select top @amt * from [AdventureWorks2008].[dbo].[Address]



Distinct – allows us to get unique results from a query based on the columns specified

Select distinct [Column 1] from [AdventureWorks2008].[dbo].[Address]



Group By– is used to group data based on the columns specified

-- simple group by query Select [Column 0], [Column 1] from [AdventureWorks2008].[dbo]. [Address] Group by [Column 0], [Column 1] -- using group by to find duplicate entries Select [Column 0], count(*) as Total from [AdventureWorks2008].[dbo].[Address] Group by [Column 0] Having COUNT(*) > 1

   







Subqueries – is query on data from another query How to use IN? SELECT [Column 0], [Column 1], [Column 2] FROM [AdventureWorks2008].[dbo].[Address] where [Column 4] IN (‘77’, ‘79’, ‘9’) SELECT [Column 0], [Column 1], [Column 2] FROM [AdventureWorks2008].[dbo].[Address] where [Column 4] NOT IN(‘77’, ‘79’, ‘9’) SELECT [Column 0], [Column 1], [Column 2] FROM [AdventureWorks2008].[dbo].[Address] where [Column 4] IN (select [Column 4] from [AdventureWorks2008].[dbo].[Address] where [Column 4] = ‘77’) Why do we care? It helps us avoid writing… select [Column 1], [Column 2], [Column 4] from [AdventureWorks2008].[dbo].[Address] where [Column 4]= ‘77’or [Column 4]= ‘79’

 



EXISTS – is used to test for existence of rows IF EXISTS (select * from sys.objects where sys.objects.name='Address') print 'Address exists' ELSE print 'no it does not exist‘ SELECT [Column 0], [Column 1], [Column 2] FROM [AdventureWorks2008].[dbo].[Address] where EXISTS (select [Column 1] from master.dbo.Address where [Column 4] = ‘77’)







Correlated Subqueries – is a repeating or outer query which is repeated for each row in the result set SELECT [Column 1] ,[Column 2] ,[Column 3], (select MAX(CAST([Column 4] AS INT)) from dbo.Address) MaxNumber FROM [AdventureWorks2008].[dbo].[Address] where [Column 4] in (‘77’, ‘79’, ‘143’, ‘161’) The example before with IN operator is also correlated subquery





CROSS – applies the function against the resultset or outer table and brings value/entries that have same value OUTER – Does same thing as CROSS but brings in additional entries where they do not match. As far as the non-matching entries are concerned, we see null values displayed in the output. CREATE TABLE [dbo].[Address2]( [street] [varchar](128) NULL, [city] [varchar(128)] NOT NULL, [zip] [varchar](5) NOT NULL, [Num] INT, [ParentNum] INT ) ON [PRIMARY]

INSERT INTO [dbo].[Address2] (street, city, zip) SELECT [Column 1], [Column 3], [Column 5] FROM [AdventureWorks2008].[dbo].[Address] GO update Address2 set [street] = [street] + '...' where [zip] like ‘%9%’

create FUNCTION [dbo].[GetValues] ( @zip varchar(5) ) RETURNS TABLE AS RETURN ( SELECT * FROM [dbo].[Address2] where [zip] = @zip )

SELECT sv.[street] ,sv.[city] ,sv.[zip], fsv.* FROM [AdventureWorks2008].[dbo].[Address2] sv CROSS APPLY dbo.GetValues(sv.name) AS fsv SELECT sv.[street] ,sv.[city] ,sv.[zip], fsv.* FROM [AdventureWorks2008].[dbo].[Address2] sv OUTER APPLY dbo.GetValues(sv.name) AS fsv



Common Table Expression or CTE– Similar to temporary table… Temp table’s lifetime is limited to session only… It can refer to itself and allow recursive operation WITH SptList ([street], [city] , [zip] ) as ( SELECT [street], [city] , [zip] FROM [AdventureWorks2008].[dbo].[Address2] ) select * from SptList



Recursive CTE– Similar to temporary table… Temp table’s lifetime is limited to session only… It can refer to itself and allow recursive operation WITH SptListRec ([street], [city] , [zip] ) as ( -- anchor member SELECT [street], [city], [zip] FROM [dbo].[Address2] where ParentNum is null union all -- recursive member select sv.[street], sv.[city], sv.[zip] from dbo.Address2 sv inner join SptListRec slr on sv.ParentNum = slr.num where sv.ParentNum <> -1 ) select * from SptListRec





PIVOT– turns resulting rows into columns… who is it useful for? Most likely our bosses who use tons of spreadsheet. UNPIVOT– as the name suggests it turns columns into rows.

SELECT pvt.* FROM (SELECT [street], [city] FROM dbo.Address2) p PIVOT ( COUNT (p.[street]) FOR p.[city] IN ( [0], [-1] ) ) AS pvt select city, COUNT(*) from Address2 where zip like ‘9%’ group by city



ROW_NUMBER– it numbers the result set where we do not have any identity / sequential numbered list. Useful for paging grid results.

SELECT [city], [street] , [zip] , ROW_NUMBER() OVER(ORDER BY [City] desc) FROM [AdventureWorks2008].[dbo].[Address2]



Any MySql fan?

WITH SptList (city, street, zip, RowNum) as ( SELECT [city], [street] , [zip] , ROW_NUMBER() OVER(ORDER BY [city] desc) as RowNum FROM [AdventureWorks2008].[dbo].[Address2] ) select * from sptlist where RowNum between 5 and 10



Rank()

select ROW_NUMBER() OVER (ORDER BY g.grade desc) as RowNum, s.[Name], g.[grade], RANK() OVER (order by g.grade desc) as 'Ranks' from TestDb.dbo.Grade g inner join students s on g.studentid = s.id



Dense_Rank() - resolve the gap issues from previous query

select ROW_NUMBER() OVER (ORDER BY g.grade desc) as RowNum, s.[Name], g.[grade], DENSE_RANK() OVER (order by g.grade desc) as 'Ranks' from TestDb.dbo.Grade g inner join students s on g.studentid = s.id



NTILE() – batch output into groups

SELECT s.name, g.grade, NTILE(3) OVER(ORDER BY g.grade DESC) AS 'Grade Grouping' FROM Grade g inner join students s on g.studentid = s.id







RaiseError – was pre SQL 2005 way to do error handling Begin Try ... End Try and Begin Catch … End Catch – is the new way to deal with this http://www.codeproject.com/KB/database/ExceptionH andlingSql2005.aspx



Books Online

Thank You!!!