Wednesday, September 8, 2010

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'

2 comments:

  1. Some of the stuff up there is very relevant and useful... keep it up.http://sqlserver-guide.blogspot.com

    ReplyDelete
  2. I have read news on technical sites that GiveBIG is now dominating the spring fundraising season for most nonprofits in every sector in Seattle and King County. The nonprofit community and fundraising thought leaders are trying to make sense of the GiveLocalAmerica kerfuffle. Great!!friv jogos online
    jogos online 2019
    friv jogos 4 school online

    ReplyDelete