SELECT

For queries spanning multiple tables, specify columns with fully-qualified names or aliases. There are three ways to specify an alias:

• alias = column_name
• column_or_table AS alias
• column_or_table alias

Return all data from table

SELECT * FROM Person.Contact

Use '+' to concatenate two fields into one

SELECT FirstName + ' ' + MiddleName + ' ' + LastName AS FullName FROM Person.Contact 

Concatenating a NULL field with another will always produce an NULL value. Use ISNULL function to return a non-null value. The second parameter can contain a default value.

SELECT FirstName + ' ' + ISNULL(MiddleName, '') + ' ' + LastName AS FullName FROM Person.Contact 

Concatenating a NULL field with another will always produce an NULL value. Use COALESCE function to return a non-null value

SELECT FirstName + ' ' + COALESCE(MiddleName, '') + ' ' + LastName AS FullName FROM Person.Contact 

Return count of rows in table

SELECT COUNT(FirstName) from Person.Contact

Return distinct values from column

SELECT DISTINCT FirstName from Person.Contact 

Return count of distinct values in column

SELECT COUNT(DISTINCT FirstName) from Person.Contact

Return ordered list of distinct values in column. Default sort is ascending (ASC) order. Use DESC for reverse sort.

SELECT DISTINCT FirstName FROM Person.Contact ORDER BY FirstName DESC 

CAST changes datatype of specified column

SELECT CAST(FirstName AS VARCHAR(30)) FROM Person.Contact 

CONVERT changes datatype of specified column

SELECT CONVERT(VARCHAR(30), FirstName) FROM Person.Contact 

CONVERT has predefined formats, the third parameter, for date datatypes

SELECT CONVERT(VARCHAR(30), ModifiedDate, 103) AS FullDate FROM Person.Contact

CONVERT has predefined formats, the third parameter, for date datatypes

SELECT CONVERT(VARCHAR(30), ModifiedDate, 101) FROM tablename

Returns ContactID and NameLength ordered by NameLength

SELECT ContactID, 
DATALENGTH(FirstName + ISNULL(MiddleName, '') + LastName) AS NameLength 
FROM Person.Contact ORDER BY NameLength

Returns the length of the largest Name in table

SELECT MAX(DATALENGTH(FirstName + ISNULL(MiddleName, '') + LastName)) AS MaxNameLength 
FROM Person.Contact

SELECT ... CASE allows switch statements to be generated

SELECT CASE Title
	WHEN 'Sr.' THEN 'MALE'
	WHEN 'Mr.' THEN 'MALE'
	WHEN 'Mrs.' THEN 'FEMALE'
	WHEN 'Ms.' THEN 'FEMALE'
	WHEN 'Sra.' THEN 'FEMALE'
	ELSE 'UNSUPPORTED'
	END
FROM Person.Contact

Returns first ten rows from table

SELECT TOP 10 FirstName from Person.Contact

Returns first ten percent of rows from table

SELECT TOP 10 PERCENT FirstName from Person.Contact

WHERE

The WHERE clause is used to filter results.

Return all values for column where first letter is greater than or equal to 'J'

SELECT FirstName FROM Person.Contact 
WHERE FirstName >= 'J' ORDER BY FirstName

Return all values from column that end in 'ack'. Filters using non-discrete values cannot use indexes for search, and are thus inefficient

SELECT DISTINCT FirstName FROM Person.Contact 
WHERE FirstName LIKE '%ack' ORDER BY FirstName

Return all values from column that contain 'ack'

SELECT DISTINCT FirstName FROM Person.Contact 
WHERE FirstName LIKE '%ack%' ORDER BY FirstName

Return all values from column where value is in the specified array

SELECT FirstName FROM Person.Contact 
WHERE FirstName in ('Jack', 'Jackie', 'Jackson')

select NULL values

SELECT FirstName, LastName FROM Person.Contact 
WHERE MiddleName IS NULL

select non-NULL values

SELECT FirstName, LastName FROM Person.Contact 
WHERE MiddleName IS NOT NULL

Select rows where FirstName does not equal 'Jack'

SELECT DISTINCT FirstName, LastName FROM Person.Contact 
WHERE FirstName <> 'Jack'

Select rows where FirstName does not equal 'Jack'

SELECT DISTINCT FirstName, LastName FROM Person.Contact 
WHERE FirstName != 'Jack'

WITH

One query can act as the source of data for another query.

Use a subquery in the filter. The subquery is executed first and used to filter the outer query results

SELECT a.ContactID, a.FirstName, a.LastName FROM Person.Contact a 
WHERE a.ContactID IN (SELECT b.ContactID FROM HumanResources.Employee b)

Use a correlated subquery as a filter. The outer query is executed, and for each row returned the inner query is executed to see if it returns true (1 or more rows) or false (0 rows). Rows from the outer query are displayed only if the correlated query returns true

SELECT a.FirstName, a.LastName FROM Person.Contact a 
WHERE EXISTS (SELECT * FROM HumanResources.Employee b WHERE b.ContactID = a.ContactID)

Use a 'WITH' statement to define the subquery

with albums
as (
	select distinct albumID, artistID
)
select artistID.artistID, t2.artistname, count(*) 
from albums left join artists t2 on albums.artistID = t2.artistID
group by artistID.artistID, t2.artistname

JOINS

Returns all rows from a and b that have matching values. Does not return rows from either table that have no match.

SELECT a.FirstName, a.LastName, b.LoginID FROM Person.Contact a 
INNER JOIN HumanResources.Employee b 
ON (a.ContactID = b.ContactID)

Returns a row for every row in a. Returns column value from b if a match is found otherwise returns NULL. The left-hand table is the table on the left-side of the JOIN clause not the JOIN condition

SELECT a.FirstName, a.LastName, b.LoginID FROM Person.Contact a 
LEFT OUTER JOIN HumanResources.Employee b 
ON (a.ContactID = b.ContactID)

Returns all rows from b. Returns values from a where a match is found or NULL

SELECT a.FirstName, a.LastName, b.LoginID FROM Person.Contact a 
RIGHT OUTER JOIN HumanResources.Employee b ON (b.ContactID = a.ContactID)

Returns all rows from a and b. Where a matching value is not found, sets value as NULL

SELECT a.FirstName, a.LastName, b.LoginID FROM Person.Contact a 
FULL OUTER JOIN HumanResources.Employee b ON (b.ContactID = a.ContactID)

Returns all rows from a combined with every row from b (cross product). No JOIN condition is specified in a cross join

SELECT a.FirstName, a.LastName, b.LoginID FROM Person.Contact a 
CROSS JOIN HumanResources.Employee b

join three tables

SELECT a.AccountNumber, c.AddressLine1, c.AddressLine2, c.City 
FROM Sales.Customer a 
INNER JOIN Sales.CustomerAddress b ON (a.CustomerID = b.CustomerID) 
INNER JOIN Person.Address c ON (b.AddressID = c.AddressID)

join four tables

SELECT a.AccountNumber, c.AddressLine1, c.AddressLine2, 
c.City, d.CountryRegionCode FROM Sales.Customer a 
INNER JOIN Sales.CustomerAddress b ON (a.CustomerID = b.CustomerID) 
INNER JOIN Person.Address c ON (b.AddressID = c.AddressID) 
INNER JOIN Sales.SalesTerritory d ON (d.TerritoryID = a.TerritoryID)

self-joining a table

SELECT a.EmployeeID AS Manager, b.EmployeeID AS Employee 
FROM HumanResources.Employee a 
INNER JOIN HumanResources.Employee b ON (a.EmployeeID = b.ManagerID) 
ORDER BY Manager

self-joining a table, with additional tables

SELECT c.FirstName + ' ' + c.LastName AS Manager, 
d.FirstName + ' ' + d.LastName AS Employee, b.SickLeaveHours  
FROM HumanResources.Employee a 
INNER JOIN HumanResources.Employee b ON (a.EmployeeID = b.ManagerID) 
INNER JOIN Person.Contact c ON (a.EmployeeID = c.ContactID) 
INNER JOIN Person.Contact d ON (b.EmployeeID = d.ContactID) 
ORDER BY Manager

UNION

UNION returns a combined resultset over multiple queries removing duplicate rows. UNION ALL preserves duplicate rows.

Return values from both queries with no duplicates

SELECT ModifiedDate FROM Person.Address 
UNION 
SELECT ModifiedDate FROM HumanResources.Employee

Return all values from both queries including any duplicates

SELECT ModifiedDate FROM Person.Address 
UNION ALL 
SELECT ModifiedDate FROM HumanResources.Employee

Return any combination of values that exist in both tables

SELECT ModifiedDate FROM Person.Address 
INTERSECT 
SELECT ModifiedDate FROM HumanResources.Employee

Return any combination of values that exist in first query but not the second

SELECT ModifiedDate FROM Person.Address 
EXCEPT 
SELECT ModifiedDate FROM HumanResources.Employee

PIVOT

The PIVOT and UNPIVOT commands allow you to rotate columns to rows and rows to columns in your queries.

UNPIVOT can be useful when you wish to search multiple columns in a table for a single value. For instance consider the following table of recipes:

CREATE TABLE ingredients (
	recipe_id INT IDENTITY(1,1),
	recipe_name VARCHAR(50) NOT NULL,
	recipe_type VARCHAR(50) NOT NULL,
	preparation_time INT NULL,
	cooking_time INT NULL,
	ingredient1 VARCHAR(20) NOT NULL,
	ingredient2 VARCHAR(20) NULL,
	ingredient3 VARCHAR(20) NULL,
	ingredient4 VARCHAR(20) NULL,
	ingredient5 VARCHAR(20) NULL,
	ingredient6 VARCHAR(20) NULL,
	ingredient7 VARCHAR(20) NULL,
	ingredient8 VARCHAR(20) NULL,
	ingredient9 VARCHAR(20) NULL,
	ingredient10 VARCHAR(20) NULL,
	ingredient11 VARCHAR(20) NULL,
	ingredient12 VARCHAR(20) NULL,
	ingredient13 VARCHAR(20) NULL,
	ingredient14 VARCHAR(20) NULL,
	ingredient15 VARCHAR(20) NULL,
	ingredient16 VARCHAR(20) NULL,
CONSTRAINT pk_ingredients PRIMARY KEY (recipe_id)
)

An ingredient for a recipe could be stored in any of the 16 'ingredient' columns. To search for a recipe with a particular ingredient, you could search each column in turn for the ingredient. Or you could use the UNPIVOT command to treat ingredient1 to ingredient16 as a a single column:

SELECT recipe_name, recipe_type, preparation_time, cooking_time, ingredient 
FROM (SELECT recipe_name, recipe_type, preparation_time, cooking_time, 
	ingredient1, ingredient2, ingredient3, ingredient4, ingredient5, 
	ingredient6, ingredient7, ingredient8, ingredient9, ingredient10, 
	ingredient11, ingredient12, ingredient13, ingredient14, 
	ingredient15, ingredient16 FROM ingredients
) AS t 
UNPIVOT (ingredient FOR GROUPS IN (ingredient1, ingredient2, ingredient3, 
	ingredient4, ingredient5, ingredient6, ingredient7, ingredient8, 
	ingredient9, ingredient10, ingredient11, ingredient12, 
	ingredient13, ingredient14, ingredient15, ingredient16)
) AS upvt
WHERE ingredient = 'Onions'

The 'UNPIVOT (columnalias FOR GROUPS IN [list of columns to pivot])' allows us to treat the list of columns as a single column, and apply our filter to that column

PIVOT works in the opposite direction, turning multiple rows into a single column by rotating the column, and using the rotated column for column headers. The values in each field for the rotated rows is determined by the provided aggregate function. Consider the following recipe table:

CREATE TABLE recipe(
recipe_name VARCHAR(50) NOT NULL,
recipe_type VARCHAR(50) NOT NULL,
preparation_time INT NULL,
cooking_time INT NULL,
ingredient VARCHAR(20) NOT NULL, 
CONSTRAINT pk_normalised_recipe PRIMARY KEY(recipe_name, ingredient)
)

For each recipe there will be a row for each ingredient. We can instead turn this into one row per recipe, using the cooking times to name the new columns and counting the number of rows to represent the number of ingredients per recipe:

SELECT recipe_name, [30] AS 'quick', [60] AS 'medium', 
	[90] AS 'long', [120] AS 'very long', [150] AS 'super long'
FROM (SELECT recipe_name, cooking_time FROM recipe) AS t
PIVOT( COUNT(cooking_time) FOR cooking_time IN ([30], [60], [90], [120], [150]))
AS pvt

recipe_name        quick       medium      long        very long   super long
------------------ ----------- ----------- ----------- ----------- -----------
Beef in Beer       0           0           0           9           0
Boeuf Bourguignon   0           0           0           0           9
Hungarian Goulash  0           0           0           0           8
Soda Bread         4           0           0           0           0

Alternatively, we can pivot the table on the ingredient column, using the ingredient names to name the new columns:

SELECT recipe_name, [Onions], [Parsley], [Bay Leaf], [Black Pepper], 
	[Braising Steak], [Flour], 
FROM ( SELECT recipe_name, ingredient FROM recipe) AS t
PIVOT (count(ingredient) FOR ingredient IN ([Onions], [Parsley], 
	[Bay Leaf], [Black Pepper], [Braising Steak], [Flour])) AS pvt

recipe_name       Onions      Parsley     Bay Leaf    Black Pepper Braising Steak Flour       
----------------- ----------- ----------- ----------- ------------ -------------- ----------- 
Beef in Beer      1           0           1           1            1              1           
Boeuf Bourguignon  1           0           0           0            1              1           
Hungarian Goulash 1           0           0           1            1              0           
Soda Bread        0           0           0           0            0              1           

GROUP BY

GROUP BY allows you define columns to be used to compute aggregate values. NULL values are aggregated as one value. Use 'HAVING' to filter aggregated values - WHERE is always tested prior to any aggregation

Return count of employees for each manager

SELECT ManagerID, COUNT(EmployeeID) FROM HumanResources.Employee 
GROUP BY ManagerID

Return ManagerID of manager with most subordinates

SELECT TOP 1 ManagerID FROM HumanResources.Employee 
GROUP BY ManagerID ORDER BY COUNT(EmployeeID) DESC

Return birthdate of youngest subordinate for each Manager

SELECT ManagerID, MAX(BirthDate) FROM HumanResources.Employee 
GROUP BY ManagerID

Return birthdate of oldest subordinate for each Manager

SELECT ManagerID, MIN(BirthDate) FROM HumanResources.Employee 
GROUP BY ManagerID

Return age range of employees for each manager with more than one subordinate

SELECT ManagerID, 
(CAST((MAX(BirthDate) - MIN(BirthDate)) AS INT)) / 365 AS AgeRange 
FROM HumanResources.Employee 
GROUP BY ManagerID HAVING COUNT(EmployeeID) > 1

Return top 10 percent of sales

SELECT TOP 10 PERCENT SalesOrderID, SUM(LineTotal) 
FROM Sales.SalesOrderDetail 
GROUP BY SalesOrderID ORDER BY SUM(LineTotal) DESC

Return daily sales per salesperson, total per salesperson and grand total

SELECT SalesPersonID, OrderDate, SUM(TotalDue) Sales 
FROM Sales.SalesOrderHeader 
WHERE SalesPersonID IS NOT NULL 
GROUP BY SalesPersonID, OrderDate 
WITH ROLLUP

Count monthly sales, per year, per salesperson with totals per grouping

SELECT SalesPersonID, YEAR(OrderDate), MONTH(OrderDate), SUM(TotalDue) Sales 
FROM Sales.SalesOrderHeader 
WHERE SalesPersonID IS NOT NULL 
GROUP BY SalesPersonID, YEAR(OrderDate), MONTH(OrderDate) 
WITH ROLLUP

Count for all possible combinations of aggregate columns

SELECT SalesPersonID, YEAR(OrderDate), MONTH(OrderDate), SUM(TotalDue) Sales 
FROM Sales.SalesOrderHeader 
WHERE SalesPersonID IS NOT NULL 
GROUP BY SalesPersonID, YEAR(OrderDate), MONTH(OrderDate) 
WITH CUBE

CUBE groupings will display NULL to indicate all values for a field. However if the field contains NULL values, then this will be confused with the all values grouping. Use GROUPING on columns that may have NULL values, and CUBE will display 1 to indicate NULL values in the data, and 0 to indicate all values.

SELECT GROUPING(country), town, street, COUNT(*) 
FROM orders 
GROUP BY country, town, street 
WITH CUBE

Specify an array of columns that will be used to produce multiple GROUP BY statements on the same query. Differs from CUBE in that it only produces the GROUP BYs specified

GROUP BY GROUPING SETS(a.col1, b.col2, c.col1)

Other useful aggregate functions:

• ROW_NUMBER
• PARTITION BY
• RANK
• DENSE_RANK
• NTILE

INSERT

When inserting data, it is better to explicitly specify the column list, to avoid problems when changes are made to the table definintion. To insert the default value in a particular field, use the keyword 'DEFAULT' for the value.

insert given values to named columns in one row

INSERT INTO Robot(RobotID, ManufactureDate, OrderID, Model, UnitPrice) 
VALUES (1, '2010-10-16', '3', 'Z1 X1', '100.99')

insert given values to named columns in three rows

INSERT INTO Robot(RobotID, ManufactureDate, OrderID, Model, UnitPrice) 
VALUES (2, '2010-01-14', '4', 'Z23 5', '89.99'),
(3, '2009-12-01', '5', 'A1-XX', '235.01'),
(4, '2010-10-23', '6', 'P21', '179.89')

inserts values into tablename. Because column names are not specified, values must match ordering and number of columns in table definition

INSERT INTO Robot
VALUES (5, 7, 'P45', '179.99', '2010-09-09')

SELECT...INTO combines CREATE TABLE and INSERT in one statement:

SELECT a.EmployeeID, a.AddressID, a.rowguid, a.ModifiedDate
INTO HumanResources.ManagerAddress
FROM HumanResources.EmployeeAddress a
WHERE a.EmployeeID IN 
(SELECT DISTINCT b.ManagerID FROM HumanResources.Employee b)

Use a resultset from a SELECT statement to populate columns

INSERT INTO 
HumanResources.ManagerAddress(EmployeeID, AddressID, rowguid, ModifiedDate)
SELECT a.EmployeeID, a.AddressID, a.rowguid, a.ModifiedDate 
FROM HumanResources.EmployeeAddress a
WHERE a.EmployeeID IN (3, 6, 7)

UPDATE

Update value of UnitPrice field for all rows with specified Model value

UPDATE Robot
SET UnitPrice = '199.99' WHERE Model = 'UB40'

Update value of UnitPrice field for all rows. Probably not what you really wanted to do

UPDATE Robot
SET UnitPrice = '199.99'

adds seven percent to UnitPrice

UPDATE Robot
SET UnitPrice = UnitPrice * 1.07

Update value for AddressID with with value from another table

UPDATE a
SET a.AddressID = b.AddressID, a.ModifiedDate = GETDATE()
FROM HumanResources.ManagerAddress a,  HumanResources.EmployeeAddress b
WHERE a.EmployeeID = 3 
AND b.EmployeeID = 4

DELETE

Delete all rows with OrderID = 5

DELETE FROM Robot
WHERE OrderID = 5

Delete all rows from Robot

DELETE FROM Robot

Truncate is a more efficient way to delete all rows from a table

TRUNCATE TABLE Robot

Delete all rows from EmployeeAddress with matching record in ManagerAddress

DELETE FROM a
FROM HumanResources.EmployeeAddress a 
INNER JOIN HumanResources.ManagerAddress b
ON (a.EmployeeID = b.EmployeeID)

MERGE

The MERGE command combines allows you to combine INSERT, UPDATE and DELETE statements.

 MERGE Robot2 AS target
USING (SELECT RobotID, OrderID, Model, UnitPrice, ManufactureDate FROM Robot) 
AS source ON target.RobotID = source.RobotID
WHEN MATCHED
   THEN UPDATE SET target.UnitPrice = source.UnitPrice * 1.07
WHEN NOT MATCHED BY TARGET
   THEN INSERT (RobotID, OrderID, Model, UnitPrice, ManufactureDate)
   VALUES(source.RobotID, source.OrderID, source.Model, 
	source.UnitPrice, source.ManufactureDate)
WHEN NOT MATCHED BY SOURCE
	THEN DELETE;

OUTPUT

During DML operations, two special tables are available called inserted and deleted. Both tables mirror the structure of the table affected by the DML and contain values for rows that have been modified. 'deleted' contains the row values prior to any modification and 'inserted' contains the values after the modification

DML statements have an optional 'OUTPUT' clause which allows access to the deleted and inserted tables to be either stored in a table or table variable or returned as a resultset