T-SQL Session, Promised Script

Several people asked that I post this script on T-SQL fundementals:

USE Northwind

SELECT *
FROM Customers;

SELECT CompanyName
, ContactName
, ContactTitle
--, Address
--, City
--, Region
--, PostalCode
FROM Customers;


SELECT CompanyName
, ContactName
, ContactTitle
, Address
, City
, Region
, PostalCode
FROM Customers
ORDER BY ContactName;




SELECT CompanyName
, ContactName
, ContactTitle
, Address
, City
, Region
, PostalCode
FROM Customers
ORDER BY CompanyName, ContactName DESC; --ASC is the default.

SELECT CompanyName
, ContactName
, ContactTitle
, Address
, City
, Region
, PostalCode
FROM Customers
WHERE CompanyName = 'Alfreds Futterkiste'
ORDER BY ContactName DESC; --ASC is the default.

--Comparison Operators
--=
--<>
--!=
--<
--<=
--!<
-->
-->=
--!>
--BETWEEN
--IS NULL

SELECT CustomerID
, OrderID
, Freight
, ShippedDate
FROM Orders
WHERE Freight > 100;

SELECT CustomerID
, OrderID
, Freight
, ShippedDate
FROM Orders
WHERE Freight !> '100';

SELECT CustomerID
, OrderID
, Freight
, ShippedDate
FROM Orders
WHERE Freight <= '100' ;

SELECT CustomerID
, Region
FROM Customers
WHERE Region <> NULL;

SELECT CustomerID
, Region
FROM Customers
WHERE Region <> 'CA';

SELECT CustomerID
, Region
FROM Customers
WHERE Region <> 'CA' OR Region IS NOT NULL;

SELECT CustomerID
, OrderID
, Freight
, ShippedDate
FROM Orders
WHERE Freight BETWEEN '10.14' AND '43.90'
ORDER BY Freight ;

--Logical Operators
--AND
--OR
--NOT

SELECT CustomerID
, OrderID
, Freight
, ShippedDate
FROM Orders
WHERE Freight BETWEEN '10.14' AND '43.90'
OR ShippedDate BETWEEN '1/1/1997' AND '12/1/1997'
ORDER BY ShippedDate, Freight ;

SELECT CustomerID
, OrderID
, Freight
, ShippedDate
FROM Orders
WHERE Freight BETWEEN '10.14' AND '43.90'
OR ShippedDate BETWEEN '1/1/1997' AND '12/1/1997'
ORDER BY ShippedDate, Freight ;


--I want all orders from CustomerID = VINET with a freight over $10 and all orders from CustomerID TOMSP with a freight over $10.
SELECT CustomerID
, OrderID
, Freight
FROM Orders
WHERE Freight > '10'
AND CustomerID = 'VINET'
OR CustomerID = 'TOMSP'
ORDER BY CustomerID;

--SQL is reading this from left to right. So technically we can do this:
SELECT CustomerID
, OrderID
, Freight
FROM Orders
WHERE CustomerID = 'VINET'
AND Freight > '10'
OR CustomerID = 'TOMSP'
AND Freight > '10'
ORDER BY CustomerID;

SELECT CustomerID
, OrderID
, Freight
FROM Orders
WHERE (CustomerID = 'VINET'
OR CustomerID = 'TOMSP')
AND Freight > '10'
ORDER BY CustomerID;

--IN Operator
SELECT CustomerID
, OrderID
FROM Orders
WHERE (CustomerID = 'VINET'
OR CustomerID = 'TOMSP'
OR CustomerID = 'HANAR'
OR CustomerID = 'SUPRD');


SELECT CustomerID
, OrderID
FROM Orders
WHERE CustomerID IN ('VINET', 'TOMSP', 'HANAR', 'SUPRD');

SELECT CustomerID
, OrderID
FROM Orders
WHERE CustomerID NOT IN ('VINET', 'TOMSP', 'HANAR', 'SUPRD');


--WILDCARDS
SELECT CompanyName
FROM Customers
WHERE CompanyName LIKE 'A%';


SELECT CompanyName
FROM Customers
WHERE CompanyName LIKE '%A';


SELECT CompanyName
FROM Customers
WHERE CompanyName LIKE '%A%';


SELECT CompanyName
FROM Customers
WHERE CompanyName LIKE 'W%A';


SELECT CompanyName
FROM Customers
WHERE CompanyName LIKE 'F%A%';

SELECT CompanyName
FROM Customers
WHERE CompanyName LIKE '[FM]%';


SELECT CompanyName + ' - ' + ContactName
+ ' - ' + ContactTitle AS HeaderLine1
FROM Customers
WHERE CompanyName + ' - ' + ContactName
+ ' - ' + ContactTitle LIKE 'A%'
ORDER BY HeaderLine1;

--Order of Operation
--1.FROM clause
--2.WHERE clause
--3.GROUP BY clause
--4.HAVING clause
--5.SELECT clause
--6.ORDER BY clause

--Calculated fields - Math
SELECT
OrderID
, ProductID
, UnitPrice
, Quantity
, UnitPrice * Quantity AS TotalPrice
FROM [Order Details];

--Math operators
-- +, -, *, /


--FUN WITH FUNCTIONS!

--Figuring out envelope size for a mailing
SELECT CompanyName
, LEN(CompanyName) AS NumberOfChars
FROM Customers;

--Uppercase CompanyName
SELECT UPPER(CompanyName) AS UpperCompany
FROM Customers;

--STRING FUNCTIONS
--LTRIM, RTRIM
--SUBSTRING
--RIGHT
--LEFT
--SOUNDEX

SELECT ContactName
FROM Customers
WHERE SOUNDEX(ContactName) = SOUNDEX('paul');

--Date Functions
--DATEDIFF
--DATEADD
--MONTH
--YEAR
--DATEPART

--The difference between order date and ship date in days - T-SQL help is great with Functions - SHIFT-F1
SELECT
OrderDate
, ShippedDate
, DATEDIFF(d, OrderDate, ShippedDate) AS DaysToShip
FROM Orders;

--The orders that took longer than 15 days to ship
SELECT
OrderDate
, ShippedDate
, DATEDIFF(d, OrderDate, ShippedDate) AS DaysToShip
FROM Orders
WHERE DATEDIFF(d, OrderDate, ShippedDate) > 15;


--Aggregates
--MAX
--MIN
--SUM
--AVG
--COUNT
--COUNT DISTINCT

--Most recent Order Date
SELECT MAX(OrderDate) AS MaxOrderDate
FROM Orders;

--First Order Date
SELECT MIN(OrderDate) AS MaxOrderDate
FROM Orders;

--Let's combine this into a subquery
SELECT OrderID
FROM Orders
WHERE OrderDate =
(
SELECT MAX(OrderDate) AS MaxOrderDate
FROM Orders
);

--Max Days to Ship --Null values out of aggregation
SELECT MAX(DATEDIFF(d, OrderDate, ShippedDate)) AS MaxDaysToShip
FROM Orders;

--Average Days to Ship
SELECT AVG(DATEDIFF(d, OrderDate, ShippedDate)) AS MaxDaysToShip
FROM Orders;

--Orders above my average days to ship
SELECT OrderID
, CustomerID
, OrderDate
, ShippedDate
, DATEDIFF(d, OrderDate, ShippedDate) AS DaysToShip
FROM Orders
WHERE DATEDIFF(d, OrderDate, ShippedDate) >
(
SELECT AVG(DATEDIFF(d, OrderDate, ShippedDate)) AS MaxDaysToShip
FROM Orders
) ;


SELECT CustomerID
, AVG(DATEDIFF(d, OrderDate, ShippedDate)) AS MaxDaysToShip
FROM Orders
GROUP BY CustomerID;

--Customers with an average greater than the average
SELECT CustomerID
, AVG(DATEDIFF(d, OrderDate, ShippedDate)) AS MaxDaysToShip
FROM Orders
GROUP BY CustomerID
HAVING AVG(DATEDIFF(d, OrderDate, ShippedDate)) >= 15;

--JOINS
--INNER
--RIGHT OUTER
--LEFT OUTER

--Inner Join
SELECT c.CompanyName
, c.CustomerID
, o.OrderDate
, o.ShippedDate
FROM Customers c
JOIN Orders o
ON c.CustomerID = o.CustomerID
ORDER BY c.CompanyName

--LEFT OUTER JOIN
SELECT c.CompanyName
, c.CustomerID
, o.OrderDate
, o.ShippedDate
FROM Customers c
LEFT OUTER JOIN Orders o
ON c.CustomerID = o.CustomerID
WHERE o.CustomerID IS NULL
ORDER BY c.CompanyName


SELECT *
FROM Customers

--CRUD
INSERT INTO Customers
(CustomerID, CompanyName)
VALUES
('ELLIS', 'EllisTeam')

DELETE FROM Customers
WHERE CustomerID = 'ELLIS'

UPDATE Customers
SET ContactName = 'Ike Ellis'

SELECT * FROM Customers
WHERE CustomerID = 'Ellis'

DELETE FROM Customers
WHERE CustomerID = 'ELLIS'


UPDATE o
SET Freight = Freight + 1
FROM Customers AS c
JOIN Orders AS o
ON c.CustomerID = o.CustomerID
WHERE Region = 'CA'


--Tips
--================
--Sometimes T-SQL doesn't read as intuitively as we like. Use white space and comments.

--Limit record results to as little as possible. (Good for network, disk, memory, users, and performance.)

--Nulls

--Connection Tips - Coloring and highlighting

--Always use Linenumbers

--Copy and paste results into EXCEL for adhoc stuff

--Show an error message (RAISEERROR) with severity
RAISERROR (N'This is message.', 18, 1);

--Learn about Query Plans
SELECT *
FROM Customers c
JOIN Orders o
ON c.CustomerID = o.CustomerID
JOIN [Order Details] od
ON o.OrderID = od.OrderID
JOIN Products p
ON od.ProductID = p.ProductID
ORDER BY c.CompanyName

--Object Explorer Drag & Drop

--Avoid Cursors

--Be careful updating and deleting (WHERE clauses, backup, SELECT using the WHERE clause, then Delete with the same one.)

--Never change production without changing dev and beta first

--Don't bother coding for portability or language independence.

--Consider using synonyms for long table names
CREATE SYNONYM Cust FOR dbo.Customers
SELECT * FROM Cust
DROP SYNONYM Cust

--TRUNCATE VS DELETE
--Logged and WHERE
DELETE FROM Temp1

--Non-logged and no WHERE, but fast
TRUNCATE TABLE Temp1


--Common question - How to delete duplicates
CREATE TABLE dbo.duplicateTest
(
[ID] [int] ,
[FirstName] [varchar](25),
[LastName] [varchar](25)
) ON [PRIMARY]

INSERT INTO dbo.duplicateTest VALUES(1, 'Bob','Smith')
INSERT INTO dbo.duplicateTest VALUES(2, 'Dave','Jones')
INSERT INTO dbo.duplicateTest VALUES(3, 'Karen','White')
INSERT INTO dbo.duplicateTest VALUES(1, 'Bob','Smith')

SELECT *
FROM dbo.duplicateTest

SELECT *
FROM dbo.duplicateTest
WHERE ID = 1 AND FirstName = 'Bob' AND LastName = 'Smith'

DELETE TOP(1) FROM dbo.duplicateTest WHERE ID = 1

DROP TABLE duplicateTest

UPDATE Customers
SET ContactName = 'Ike Ellis'


WHERE CustomerID = 'ELLIS'


DELETE FROM Customers
WHERE CustomerID = 'ELLIS'