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'
