--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]
