T-SQL Query designing on Microsoft SQL Server 2005

--Create Database


USE [Employee]
GO


CREATE TABLE employeetest
(employeeID int NOT NULL PRIMARY KEY,
FirstName nvarchar(50) NOT NULL,
LastName nvarchar(50) NOT NULL,
PhoneNumber int NOT NULL)


--Grouping Results using GROUP BY


SELECT Title,
COUNT (*) as EmployeeHavingTitle
FROM
HumanResources.Employee
GROUP BY 
Title




--Returning Unique Values using DISTINCT 
--DISTINCT >> To avoid duplicate records


SELECT DISTINCT 
Title
FROM
HumanResources.Employee


--combining Results using Union Operator


SELECT * FROM Production.ProductCategory
UNION
SELECT 5, 'Test Category',NEWID(), '02/02/2011'
UNION ALL
SELECT * FROM Production.ProductCategory




--Returning Differences using INTERSECT
--To display records which are 'common' between two queries


SELECT * FROM Production.ProductCategory WHERE ProductCategoryID IN (1,2,3,4)
INTERSECT
SELECT * FROM Production.ProductCategory WHERE ProductCategoryID IN (1,2,3)


--Returning Differences using EXCEPT
--To display records which are 'uncommon' between two queries


SELECT * FROM Production.ProductCategory WHERE ProductCategoryID IN (1,2,3,4)
EXCEPT
SELECT * FROM Production.ProductCategory WHERE ProductCategoryID IN (1,2,3)


/*Returining related data with JOIN conditions */


--Returning matching rows using the INNER JOIN condition


SELECT 
PC.Title as PersonTitle
PC.FirstName,
PC.LastName,
HE.Title as PositionTitle,
HE.VacationHours,
HE.SickLeaveHours
FROM
HumanResources.Employee HE
INNER JOIN
Person.Contact PC ON HE.ContactID = PC.ContactID
WHERE
HE.SalariedFlag = 1
ORDER BY
LastName, FirstName


--Returning all rows from one table using OUTER JOIN (RIGHT/LEFT) condition

SELECT 
PP.(Name),
PP.Rating,
PP.Comments
FROM
Production.Product PP
LEFT OUTER JOIN
Production.ProductReview PR ON PP.ProductID = PR.ProductID
ORDER BY
PP.(Name)


USE [master]
GO


IF DB_ID(N'employee') IS NOT NULL
DROP DATABASE employee
GO


--Creating a simple database
CREATE DATABASE (CS) ON PRIMARY
(
NAME = N'CS_data',
FILENAME = N'C:\Program files\Microsoft SQL Server\MSSQL.1\MSSQL\data\cs.mdf'
)
LOG ON
(
NAME = N'CS_log',
FILENAME = N'C:\Program files\Microsoft SQL Server\MSSQL.1\MSSQL\data\cs.ldf'
)


--Creating a database with filegroups
CREATE DATABASE (CS) ON PRIMARY
(
NAME = N'CS_primary1',
FILENAME = N'C:\Program files\Microsoft SQL Server\MSSQL.1\MSSQL\data\cs_primary1.ndf'
)




USE [ktm]
/* Creating Tables */
--Create Train table
CREATE TABLE trains(
TrainID int IDENTITY(1,1) NOT NULL,
Source nvarchar(100) NOT NULL,
Destination nvarchar(100) NOT NULL,
StartDate smalldatatime NOT NULL CONSTRAINT DF_Train_Date DEFAULT (getdate()),
RunningFlag bit NULL CONTRAINT DF_Train_RunningFlag DEFAULT (1),
CONTRAINT PK_Train PRIMARY KEY CLUSTERED (TrainID) ON [PRIMARY])


--Create CCTV table
CREATE TABLE cctv
VideoID int IDENTITY(1,1) NOT NULL,
SeriesID smallint NOT NULL,
VideoName nvarchar(100) NOT NULL,
Lenth float NOT NULL,
[size] numeric (2,2) NOT NULL,
CreationDate datetime NOT NULL,
CONTRAINT PK_cctv PRIMARY KEY CLUSTERED (VideoID) ON [PRIMARY])


--Create Ticket table
CREATE TABLE cctv
TicketID int IDENTITY(1,1) NOT NULL,
Source nvarchar(100) NOT NULL,
TicketCounterNumber int NOT NULL,
Price money NOT NULL,
VideoID int NOT NULL,
CreationDate datetime NOT NULL,
CONTRAINT PK_Ticket PRIMARY KEY CLUSTERED (Source) ON [PRIMARY])


--Create Data Types
--Create IC Number for malaysians
CREATE TYPE ICN varchar(12) NULL


/*Creating Defaults */
--Create Default for an empty IC number
CREATE DEFAULT DF_ICN AS '123-20-19901'


/*Creating Check Constraints */
--Add check constraint to StartDate column of Trains
ALTER TABLE Train1 WITH CHECK ADD CONSTRAINT CK_TrainCheckDate CHECK (StartDate>='1/1/1990')


USE [AdventureWorks]
GO
--Creating Non-Clustered Indexes
CREATE NONCLUSTERED INDEX [IX_HR_Employee] ON [HumanResources].[Employee] 
([Title] ASC, [BirthDate] ASC)
WITH
(PAD_INDEX = OFF,
SORT_IN_TEMPDB = OFF,
DROP_EXISTING = OFF,
IGNORE_DUP_KEY = OFF,
ONLINE = ON) ON [PRIMARY]


--Creating Standard Views
CREATE VIEW HumanResources.vEmployeeTime
AS
SELECT 
hre.EmployeeID,
pc.FirstName,
pc.LastName,
hre.Title,
hre.VacationHours,
hre.SickLeaveHours
FROM
HumanResources.Employee hre
JOIN
Person.Contact pc ON hre.ContactID = pc.ContactID
WHERE
SalariedFlag = 1
AND
CurrentFlag = 1




--Creating Indexed Views
CREATE VIEW Sales.vixAllTimeSales
WITH SCHEMABINDING
AS
SELECT 
pr.Name,
SUM(sod.OrderQty) as TotalQuantity,
SUM(soh.SubTotal) as TotalSales,
COUNT_BIG(*) as NumSales
FROM
Production.Product pr
INNER JOIN
Sales.SalesOrderDetail sod ON pr.ProductID = sod.ProductID
INNER JOIN
Sales.SalesOrderHeader soh ON sod.SalesOrderID = soh.SalesOrderID
WHERE
pr.ProductSubcategoryID = 1
GROUP BY
pr.Name
Go




--Creating Unique Clusterd Index


CREATE UNIQUE CLUSTERED INDEX IX_AllTimeBikeSales
ON Sales.vixAllTimeBikeSales (Name)

SELECT * FROM Sales.vixAllTimeBikeSales




--Creating Partitioned Views


--Distributed Partition Views


--Server1


CREATE TABLE sales.Customers_west
(
CustomerID int PRIMARY KEY,
TerritoryID int CHECK (TerritoryID BETWEEN 1 AND 5) NULL,
CustomerType nchar (1) NOT NULL
)


INSERT Sales.Customers_west
(CustomerID, TerritoryID, CustomerType)
SELECT 
CustomerID, TerritoryID, CustomerType
FROM 
sales.Customer
WHERE 
TerritoryID BETWEEN 1 AND 5


CREATE VIEW Sales.vCustomers
AS
SELECT * FROM Sales.Customers_West




/* Creating Stored Procedures */


--Basic Stored Procedure


CREATE PROCEDURE Sales.spGetYearlyBikeSales
AS
SELECT
pr.Name,
YEAR(soh.OrderDate) as SalesYear,
SUM(sod.OrderQty) as TotalQuantity,
SUM(soh.SubTotal) as TotalSales
FROM
Production.Product pr
INNER JOIN
Sales.SalesOrderDetail sod ON pr.ProductID = sod.ProductID
INNER JOIN
Sales.SalesOrderHeader soh ON sod.SalesOrderID = soh.SalesOrderID
WHERE 
pr.ProductSubcategoryID = 1
GROUP BY
pr.Name, YEAR(soh.OrderDate)
ORDER BY
pr.Name, YEAR(soh.OrderDate)



Sales.spGetYearlyBikeSales




--Stored Procedure with Input Parameters


CREATE PROCEDURE sales.spGetYearlyProductSalesByID
@ProductCategoryID int
AS
SELECT
pr.Name,
YEAR(soh.OrderDate) as SalesYear,
SUM(sod.OrderQty) as TotalQuantity,
SUM(soh.SubTotal) as TotalSales
FROM
Production.Product pr
INNER JOIN
Sales.SalesOrderDetail sod ON pr.ProductID = sod.ProductID
INNER JOIN
Sales.SalesOrderHeader soh ON sod.SalesOrderID = soh.SalesOrderID
WHERE 
pr.ProductSubcategoryID = @ProductCategoryID
GROUP BY
pr.Name, YEAR(soh.OrderDate)
ORDER BY
pr.Name, YEAR(soh.OrderDate)


EXEC sales.spGetYearlyProductSalesByID 4


--Stored Procedure with using wild cards


CREATE PROCEDURE HumanResources.spGetEmployeeByName
@FirstName nvarchar(50) = '%',
@LastName nvarchar(50) = '%'
AS
SELECT
pc.Title,
pc.FirstName,
pc.Lastname,
pc.EmailAddress,
pc.Phone,
hre.BirthDate,
hre.HireDate
FROM
HumanResources.Employee hre
JOIN
Person.Contact pc ON hre.ContactID = pc.ContactID


WHERE
pc.FirstName LIKE @FirstName + '%'
AND
pc.LastName LIKE @LastName + '%'


EXEC HumanResources.spGetEmployeeByName DEFAULT,'a'


/* Creating User-defined Fuctions */


--Creating SCALAR Fuctions


CREATE FUNCTION ufnFormatCurrency (@Amount Money)
RETURNS VarChar(100)
AS
BEGIN
RETURN '$' + CONVERT(VarChar, CONVERT (Money, @Amount),1)
END

---Usage


SELECT 
p.Name,
soh.OrderDate,
soh.SubTotal as SubTotalWithoutFuction,
dbo.ufnFormatCurrency(soh.SubTotal) as SubTotal,
dbo.ufnFormatCurrency(soh.TaxAmt) as TaxAmount,
dbo.ufnFormatCurrency(soh.Freight) as Freight,
dbo.ufnFormatCurrency(soh.TotalDue) as TotalDue
FROM
Sales.SalesOrderHeader soh
JOIN
Sales.SalesOrderDetail sod ON soh.SalesOrderID = Sod.SalesOrderID
JOIN
Production.Product p ON sod.ProductID = p.ProductID
WHERE
Year(soh.OrderDate) = 2004



---Data Access Scalar Function


CREATE FUNCTION ufnGetProductStock (@ProductID int)
RETURNS int
AS
BEGIN
DECLARE @ret int

SELECT
@ret = SUM(ppi.Quantity)
FROM
Production.ProductInventory ppi
WHERE
ppi.ProductID = @ProductID
IF (@ret IS NULL)
SET @ret = 0
RETURN @ret
END


--Usage


SELECT 
Name,
dbo.ufnGetStock(ProductID) AS Supply
FROM 
Production.Product




---Data Access Scalar User Defined Function


CREATE FUNCTION ufnGetProductStock (@ProductID int)
RETURNS int
AS
BEGIN
DECLARE @ret int

SELECT 
@ret = SUM(ppi.Quantity)
FROM 
Production.ProductInventory ppi
WHERE
ppi.ProductID = @ProductID
IF (@ret IS NULL)
SET @ret = 0
RETURN @ret
END


---Usage 
SELECT 
Name,
dbo.ufnGetProductStock(ProductID) AS Supply
FROM
Production.Product




--Creating Inline Table - Valued Functions


CREATE FUNCTION sales.ufnStoreYTDSales (@StoreID int)
RETURNS Table
AS
RETURN
(
SELECT
p.Name,
SUM(sod.LineTotal) AS YTDSales
FROM
Production.Product AS p
JOIN
sales.SalesOrderDetail AS sod ON sod.ProductID = p.ProductID
JOIN
sales.SalesOrderHeader AS soh ON soh.SalesOrderID = sod.SalesOrderID

WHERE 
soh.CustomerID = @StoreID
GROUP BY
p.ProductID, P.Name
)




---Usage 
SELECT * FROM Sales.ufnStoreYTDSales(1)

---Creating a Parameterized View From an Inline Table-Valued Function


CREATE FUNCTION Sales.ufnStoreWithDemographics (@StoreID int)
RETURNS Table
AS
RETURN
(
SELECT
*
FROM
Sales.vStoreWithDemographics
WHERE
CustomerID = @StoreID
)

--Usage


SELECT * FROM Sales.ufnStoreWithDemographics(1)


/* Working with TABLE Encryption */


--Encryption


ALTER FUNCTION ufnFormatCurrency (@Amount Money)
RETURNS VarChar(100)
WITH ENCRYPTION
AS
BEGIN
RETURN '$' + CONVERT(VarChar, CONVERT(Money,@Amount),1)
END




NOTE: All above queries are tested using built-in tables, databases & templates of Microsoft SQL 2005. Some are self-created. 
[Source Reference: Nuggets for SQL 2005]