--- Introducing SQL Server 2012
USE AdventureWorks2012;
SELECT SalesPersonID, YEAR(OrderDate) AS OrderYear
FROM Sales.SalesOrderHeader
WHERE CustomerID = 29974
GROUP BY SalesPersonID, YEAR(OrderDate)
HAVING COUNT(*) > 1
ORDER BY SalesPersonID, OrderYear;
select 1; -- quick way to perform validate performance
SELECT unitprice, OrderQty, (unitprice * OrderQty)
FROM sales.salesorderdetail;
-- AS is the recommended way
SELECT s.unitprice, s.OrderQty, (s.unitprice * s.OrderQty) as TotalCost
FROM sales.salesorderdetail as s;
SELECT s.unitprice, s.OrderQty, (s.unitprice * s.OrderQty) TotalCost
FROM sales.salesorderdetail s;
SELECT s.unitprice, s.OrderQty, TotalCost=(s.unitprice * s.OrderQty)
FROM sales.salesorderdetail s;
-- Advanced SELECT Statements
SELECT DISTINCT StoreID
FROM Sales.Customer;
SELECT ProductID, Name, ProductSubCategoryID,
CASE ProductSubCategoryID
WHEN 1 THEN 'Beverages'
ELSE 'Unknown Category'
END
FROM Production.Product;
SELECT SOH.SalesOrderID,
SOH.OrderDate,
SOD.ProductID,
SOD.UnitPrice,
SOD.OrderQty
FROM Sales.SalesOrderHeader AS SOH
JOIN Sales.SalesOrderDetail AS SOD
ON SOH.SalesOrderID = SOD.SalesOrderID;
SELECT SOH.SalesOrderID,
SOH.OrderDate,
SOD.ProductID,
SOD.UnitPrice,
SOD.OrderQty
FROM Sales.SalesOrderHeader AS SOH,
Sales.SalesOrderDetail AS SOD
WHERE SOH.SalesOrderID = SOD.SalesOrderID;
-- Customers that did not place orders:
SELECT CUST.CustomerID, CUST.StoreID, ORD.SalesOrderID, ORD.OrderDate
FROM Sales.Customer AS CUST
LEFT OUTER JOIN Sales.SalesOrderHeader AS ORD
ON CUST.CustomerID = ORD.CustomerID
WHERE ORD.SalesOrderID IS NULL;
-- Combine each row from first table with each row from second table All possible combinations are displayed
SELECT EMP1.BusinessEntityID, EMP2.JobTitle
FROM HumanResources.Employee AS EMP1
CROSS JOIN HumanResources.Employee AS EMP2;
-- Return all employees with ID of employee’s manager when a manager exists (INNER JOIN):
SELECT EMP.EmpID, EMP.LastName,
EMP.JobTitle, EMP.MgrID, MGR.LastName
FROM HR.Employees AS EMP
INNER JOIN HR.Employees AS MGR
ON EMP.MgrID = MGR.EmpID ;
-- Return all employees with ID of manager (OUTER JOIN). This will return NULL for the CEO:
SELECT EMP.EmpID, EMP.LastName,
EMP.Title, MGR.MgrID
FROM HumanResources.Employee AS EMP
LEFT OUTER JOIN HumanResources.Employee AS MGR
ON EMP.MgrID = MGR.EmpID;
-- Filter rows for customers to display top 20 TotalDue items
SELECT TOP (20) SalesOrderID, CustomerID, TotalDue
FROM Sales.SalesOrderHeader
ORDER BY TotalDue DESC;
-- Filter rows for customers to display top 20 TotalDue items with ties (output could be more than 20 if ties exist)
SELECT TOP (20) WITH TIES SalesOrderID, CustomerID, TotalDue
FROM Sales.SalesOrderHeader
ORDER BY TotalDue DESC;
-- Filter rows for customers to display top 1% of TotalDue items
SELECT TOP (1) PERCENT SalesOrderID, CustomerID, TotalDue
FROM Sales.SalesOrderHeader
ORDER BY TotalDue DESC;
-- pagination
SELECT * FROM Production.Product ORDER BY ProductID
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY
SELECT CHOOSE (3, 'A', 'B', 'C', 'D', 'E', 'F') AS Result -- Result is 'C'
SELECT PARSE('02/12/2012' AS datetime2 USING 'en-US') AS parse_result;
SELECT format(getdate(), 'yyyy-MM-dd hh:mm:ss', 'en-US') AS format_result;
SELECT GETDATE() as "GETDATE()",
GETUTCDATE() as "GETUTCDATE",
CURRENT_TIMESTAMP as "CURRENT_TIMESTAMP",
SYSDATETIME() as "SYSDATETIME()",
SYSUTCDATETIME() as "SYSUTCDATETIME()",
SYSDATETIMEOFFSET() as "SYSDATETIMEOFFSET()";
/*-----------
GETDATE() GETUTCDATE CURRENT_TIMESTAMP SYSDATETIME() SYSUTCDATETIME() SYSDATETIMEOFFSET()
----------------------- ----------------------- ----------------------- --------------------------- --------------------------- ----------------------------------
2015-03-08 20:40:31.733 2015-03-08 12:40:31.733 2015-03-08 20:40:31.733 2015-03-08 20:40:31.7268304 2015-03-08 12:40:31.7268304 2015-03-08 20:40:31.7268304 +08:00
-----------
*/
SELECT DATEADD(day,1,'20120212');
-- Returns last day of month as start date, with optional offset
SELECT EOMONTH('20120212');
SELECT DATEDIFF(day,'20120925',SYSDATETIME())
-- CAST is ANSI
SELECT CAST(SYSDATETIME() AS date) AS 'TodaysDate';
SELECT CONVERT(CHAR(8), CURRENT_TIMESTAMP,112) AS ISO_style;
SELECT SalesOrderID, YEAR(OrderDate) AS OrderYear;
FROM Sales.SalesOrderHeader;
SELECT DB_NAME() AS current_database;
-- IIF returns one of two values, depending on a logical test Shorthand for a two-outcome CASE expression
SELECT ProductID, ListPrice,
IIF(ListPrice > 50, 'high', 'low') AS PricePoint
FROM Production.Product;
-- Grouping and Aggregating Data
SELECT COUNT (DISTINCT SalesOrderID) AS UniqueOrders,
AVG(UnitPrice) AS Avg_UnitPrice,
MIN(OrderQty)AS Min_OrderQty,
MAX(LineTotal) AS Max_LineTotal
FROM Sales.SalesOrderDetail;
SELECT * FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY OrderDate) AS RN
,*
From Sales.SalesOrderHeader
) AS a
WHERE RN=1;
SELECT * FROM (
SELECT RANK () OVER (PARTITION BY CustomerId ORDER BY OrderDate DESC) AS RN
,*
From Sales.SalesOrderHeader
) AS a
WHERE RN=1;
SELECT * FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY OrderDate) AS RN
,*
From Sales.SalesOrderHeader
) AS a
WHERE RN=1;
SELECT *
FROM Sales.SalesOrderHeader a
WHERE a.SalesOrderID=(select top 1 SalesOrderID from Sales.SalesOrderHeader b where a.CustomerID=b.CustomerID order by OrderDate)
order by a.CustomerID;
WITH a AS (
SELECT ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY OrderDate) AS RN
,*
From Sales.SalesOrderHeader
)
SELECT * FROM a where RN=1;
CREATE FUNCTION Sales.fn_LineTotal (@SalesOrderID INT)
RETURNS TABLE
AS
RETURN
SELECT SalesOrderID,
CAST((OrderQty * UnitPrice * (1 - SpecialOfferID))
AS DECIMAL(8, 2)) AS LineTotal
FROM Sales.SalesOrderDetail
WHERE SalesOrderID = @SalesOrderID ;
DECLARE @OrderId INT=43826;
SELECT *FROM Sales.fn_LineTotal(@OrderId);
GO
-- SET Operators, Windows Functions, and Grouping
-- APPLY is a table operator used in the FROM clause and can be either a CROSS APPLY or OUTER APPLY
-- Operates on two input tables, left and right
-- Right table is often a derived table or a table-valued function
SELECT c.CustomerID
,c.AccountNumber
,o.*
From Sales.Customer AS c
OUTER APPLY (
SELECT TOP 5 soh.OrderDate, Soh.SalesOrderID FROM sales.SalesOrderHeader AS soh
WHERE soh.CustomerID=c.CustomerID
ORDER BY soh.OrderDate DESC
) AS o
WHERE c.TerritoryID=3;
/*
RANK Returns the rank of each row within the partition of a result set. May include ties and gaps.
DENSE_RANK Returns the rank of each row within the partition of a result set. May include ties but will not include gaps.
ROW_NUMBER Returns a unique sequential row number within partition based on current order.
NTILE Distributes the rows in an ordered partition into a specified number of groups. Returns the number of the group to which the current row belongs.
LAG Returns an expression from a previous row that is a defined offset from the current row. Returns NULL if no row at specified position.
LEAD Returns an expression from a later row that is a defined offset from the current row. Returns NULL if no row at specified position.
FIRST_VALUE Returns the first value in the current window frame. Requires window ordering to be meaningful.
LAST_VALUE Returns the last value in the current window frame. Requires window ordering to be meaningful.
*/
SELECT *
,Amount-LAG(Amount,1,0) OVER (PARTITION BY AccountID ORDER BY TransactionDate, TransactionID) AS DIFF
,SUM(Amount) OVER (PARTITION BY AccountID) AS FinalBalance
,SUM(Amount) OVER(
PARTITION BY AccountID
ORDER BY TransactionDate, TransactonID
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWS -- default with order by
) AS CurrentBalance
FROM Transactions
WHERE AccountID=25
ORDER BY AccountID,TransactionDate,TransactionID;
/*
Pivoting includes three phases:
Grouping determines which element gets a row in the result set
Spreading provides the distinct values to be pivoted across
Aggregation performs an aggregation function (such as SUM)
*/
SELECT Category, [2006],[2007],[2008]
FROM ( SELECT Category, Qty, Orderyear
FROM Sales.CategoryQtyYear) AS D
PIVOT(SUM(QTY) FOR orderyear
IN([2006],[2007],[2008])
) AS pvt;
SELECT VendorID, [250] AS Emp1, [251] AS Emp2, [256] AS Emp3, [257] AS Emp4, [260] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [250], [251], [256], [257], [260] )
) AS pvt
ORDER BY pvt.VendorID;
-- Using UNPIVOT to normalize the table
CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
Emp3 int, Emp4 int, Emp5 int);
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4);
INSERT INTO pvt VALUES (2,4,1,5,5,5);
INSERT INTO pvt VALUES (3,4,3,5,4,4);
GO
SELECT VendorID, Employee, Orders
FROM
(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
FROM pvt) p
UNPIVOT
(Orders FOR Employee IN
(Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt;
GO
SELECT TerritoryID, CustomerID, SUM(TotalDue) AS TotalAmountDue
FROM Sales.SalesOrderHeader
GROUP BY
GROUPING SETS((TerritoryID),(CustomerID),());
--CUBE provides shortcut for defining grouping sets given a list of columns
-- All possible combinations of grouping sets are created
SELECT TerritoryID, CustomerID, SUM(TotalDue) AS TotalAmountDue
FROM Sales.SalesOrderHeader
GROUP BY CUBE(TerritoryID, CustomerID)
ORDER BY TerritoryID, CustomerID;
-- ROLLUP provides shortcut for defining grouping sets, creates combinations assuming input columns form a hierarchy
SELECT TerritoryID, CustomerID, SUM(TotalDue) AS TotalAmountDue
FROM Sales.SalesOrderHeader
GROUP BY ROLLUP(TerritoryID, CustomerID)
ORDER BY TerritoryID, CustomerID;
-- 06 | Modifying Data in SQL Server
INSERT INTO Production.UnitMeasure (Name, UnitMeasureCode, ModifiedDate)
VALUES (N'Square Yards', N'Y2', GETDATE());
GO
INSERT INTO Production.UnitMeasure (Name, UnitMeasureCode, ModifiedDate)
VALUES
(N'Square Feet', N‘F2', GETDATE()),
(N'Square Inches', N‘I2', GETDATE());
INSERT INTO Production.UnitMeasure (Name, UnitMeasureCode, ModifiedDate, Country)
VALUES ((N'Square Miles', N'M2', GETDATE()); DEFAULT);
-- INSERT...SELECT is used to insert the result set of a query into an existing table
INSERT INTO Production.UnitMeasure (Name, UnitMeasureCode, ModifiedDate)
SELECT Name, UnitMeasureCode, ModifiedDate
FROM Sales.TempUnitTable
WHERE ModifiedDate < '20080101';
-- INSERT...EXEC is used to insert the result of a stored procedure or dynamic SQL expression into an existing table
INSERT INTO Production.UnitMeasure (Name, UnitMeasureCode, ModifiedDate)
EXEC Production.Temp_UOM
@numrows = 5, @catid=1;
-- SELECT...INTO is similar to INSERT...SELECT but SELECT...INTO creates a new table each time the statement is executed
-- Copies column names, data types, and nullability Does not copy constraints or indexes
SELECT Name, UnitMeasureCode, ModifiedDate
INTO Production.TempUOMTable
FROM Production.UnitMeasure
WHERE orderdate < '20080101';
-- IDENTITY property with a starting number of 100 and incremented by 10 as each row is added
CREATE TABLE Production.IdentityProducts(
productid int IDENTITY(100,10) NOT NULL,
productname nvarchar(40) NOT NULL,
categoryid int NOT NULL,
unitprice money NOT NULL)
-- Define a sequence
CREATE SEQUENCE dbo.InvoiceSeq AS INT START WITH 5 INCREMENT BY 5;
-- Retrieve next available value from sequence
SELECT NEXT VALUE FOR dbo.InvoiceSeq;
UPDATE Production.UnitMeasure
SET ModifiedDate = (GETDATE())
WHERE UnitMeasureCode = 'M2';
MERGE INTO schema_name.table_name AS TargetTbl
USING (SELECT <select_list>) AS SourceTbl
ON (TargetTbl.col1 = SourceTbl.col1)
WHEN MATCHED THEN
UPDATE SET col2 = SourceTbl.col2
WHEN NOT MATCHED THEN
INSERT (<column_list>)
VALUES (<value_list>);
ALTER TABLE Production.TransactionHistoryArchive
ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID
PRIMARY KEY CLUSTERED (TransactionID);
ALTER TABLE Sales.SalesOrderHeaderSalesReason
ADD CONSTRAINT FK_SalesReason
FOREIGN KEY (SalesReasonID)
REFERENCES Sales.SalesReason (SalesReasonID)
ON DELETE CASCADE
ON UPDATE CASCADE ;
CREATE TABLE Production.TransactionHistoryArchive4
(TransactionID int NOT NULL,
CONSTRAINT AK_TransactionID UNIQUE(TransactionID) );
ALTER TABLE DBO.NewTable
ADD ZipCode int NULL
CONSTRAINT CHK_ZipCode
CHECK (ZipCode LIKE '[0-9][0-9][0-9][0-9][0-9]');
ALTER TABLE Sales.CountryRegionCurrency
ADD CONSTRAINT Default_Country
DEFAULT 'USA' FOR CountryRegionCode;
CREATE TRIGGER reminder1 ON Sales.Customer
AFTER INSERT, UPDATE
AS RAISERROR ('Notify Customer Relations', 16, 10);
DELETE Sales.ShoppingCartItem OUTPUT DELETED.* WHERE ShoppingCartID = 20621;
--Verify the rows in the table matching the WHERE clause have been deleted.
SELECT COUNT(*) AS [Rows in Table]
FROM Sales.ShoppingCartItem
WHERE ShoppingCartID = 20621;
--BEGIN TRAN
DECLARE @tmp TABLE (ProductID INT PRIMARY KEY);
UPDATE Production.Product SET Name=UPPER(Name)
OUTPUT INSERTED.ProductID INTO @tmp
WHERE ListPrice >10;
SELECT * from @tmp -- @tmp can be referenced to retrieve original modified rows
-- ROLLBACK TRAN;
-- 07 | Programming with T-SQL
--Declare,initialize, and use a variable
DECLARE @SalesPerson_id INT = 5;
SELECT OrderYear, COUNT(DISTINCT CustomerID) AS CustCount
FROM (
SELECT YEAR(OrderDate) AS OrderYear, CustomerID
FROM Sales.SalesOrderHeader
WHERE SalesPersonID = @SalesPerson_id
) AS DerivedYear
GROUP BY OrderYear;
-- Values can be assigned with a SET command or a SELECT statement
-- SET can only assign one variable at a time. SELECT can assign multiple variables at a time
-- When using SELECT to assign a value, make sure that exactly one row is returned by the query
--Declare and initialize variables
DECLARE @numrows INT = 3, @catid INT = 2;
--Use variables to pass parameters to procedure
EXEC Production.ProdsByCategory
@numrows = @numrows, @catid = @catid;
GO
-- Create a synonym for the Product table in AdventureWorks
CREATE SYNONYM dbo.MyProduct
FOR AdventureWorks.Production.Product;
GO
-- Query the Product table by using the synonym.
SELECT ProductID, Name
FROM MyProduct
WHERE ProductID < 5;
GO
IF OBJECT_ID (‘Production.Product', 'U') IS NOT NULL
PRINT 'I am here and contain data, so don’t delete me’;
IF OBJECT_ID (‘Production.Product', 'U') IS NOT NULL
PRINT 'I am here and contain data, so don’t delete me’
ELSE
PRINT ‘Table not found, so feel free to create one’
GO
DECLARE @BusinessEntID AS INT = 1, @Title AS NVARCHAR(50);
WHILE @BusinessEntID <=10
BEGIN
SELECT @Title = JobTitle FROM HumanResources.Employee
WHERE BusinessEntityID = @BusinessEntID;
PRINT @Title;
SET @BusinessEntID += 1;
END;
GO
BEGIN TRY
-- Generate a divide-by-zero error.
SELECT 1/0;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO
BEGIN TRY
-- Table does not exist; object name resolution error not caught.
SELECT * FROM IDontExist;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH
GO
BEGIN TRY
SELECT 100/0 AS 'Problem';
END TRY
BEGIN CATCH
PRINT 'Code inside CATCH is beginning'
PRINT 'MyError: ' + CAST(ERROR_NUMBER()
AS VARCHAR(255));
THROW;
END CATCH
-- Transactions extend batches
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO Sales.SalesOrderHeader... --Succeeds
INSERT INTO Sales.SalesOrderDetail... --Fails
COMMIT TRANSACTION -- If no errors, transaction completes
END TRY
BEGIN CATCH
--Inserted rows still exist in Sales.SalesOrderHeader SELECT ERROR_NUMBER()
ROLLBACK TRANSACTION --Any transaction work undone
END CATCH;
-- SQL Server does not automatically roll back transactions when errors occur
-- To roll back, either use ROLLBACK statements in error-handling logic or enable XACT_ABORT
-- XACT_ABORT specifies whether SQL Server automatically rolls back the current transaction when a runtime error occurs
-- When SET XACT_ABORT is ON, the entire transaction is terminated and rolled back on error, unless occurring in TRY block
-- SET XACT_ABORT OFF is the default setting
-- Change XACT_ABORT value with the SET command:
SET XACT_ABORT ON;
-- 08 | Retrieving SQL Server Metadata and Improving Query Performance
--Pre-filtered to exclude system objects
SELECT name, object_id, schema_id, type, type_desc
FROM sys.tables;
--Includes system and user objects
SELECT name, object_id, schema_id, type, type_desc
FROM sys.objects;
SELECT TABLE_CATALOG, TABLE_SCHEMA,
TABLE_NAME, TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES;
SELECT VIEW_CATALOG, VIEW_SCHEMA, VIEW_NAME, TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE;
SELECT VIEW_CATALOG, VIEW_SCHEMA, VIEW_NAME, TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE;
SELECT @@VERSION AS SQL_Version;
SELECT SERVERPROPERTY('ProductVersion') AS version;
SELECT SERVERPROPERTY('Collation') AS collation;
SELECT session_id, login_time, program_name
FROM sys.dm_exec_sessions
WHERE is_user_process = 1;
SELECT referencing_schema_name, referencing_entity_name, referencing_class_desc
FROM sys.dm_sql_referencing_entities('Sales.SalesOrderHeader', 'OBJECT');
GO
--no parameters so lists all database
EXEC sys.sp_databases;
--single parameter of name of table
EXEC sys.sp_help N'Sales.Customer';
--multiple named parameters
EXEC sys.sp_tables
@table_name = '%',
@table_owner = N'Sales';
CREATE PROCEDURE Production.ProdsByProductLine
(@numrows AS int, @ProdLine AS nchar)
AS
SELECT TOP(@numrows) ProductID,
Name, ListPrice
FROM Production.Product
WHERE ProductLine = @ProdLine;
--Retrieve top 50 products with product line = M
EXEC Production.ProdsByProductLine 50, ‘M’
