Source Codes - SQL
-----------------------------------------------------------
-- Total Training for SQL Server 2005
--
-- Part 2 - Selecting Data
-- Lesson 2 - Merging Data with Joins and Unions
--
-- Paul Nielsen
-----------------------------------------------------------
USE CHA2
-----------------------------------------------------------
-- Working with Inner Joins
-- Two Table Join
SELECT Tour.Name, Tour.BaseCampID,
BaseCamp.BaseCampID, BaseCamp.Name
FROM dbo.Tour
JOIN dbo.BaseCamp
ON Tour.BaseCampID = BaseCamp.BaseCampID
-- Legacy Style Join
SELECT Tour.Name, Tour.BaseCampID,
BaseCamp.BaseCampID, BaseCamp.Name
FROM dbo.Tour, dbo.BaseCamp
WHERE Tour.BaseCampID = BaseCamp.BaseCampID
-- Multiple Table Join
USE OBXKites
SELECT LastName, FirstName, ProductName
FROM dbo.Contact
JOIN dbo.[Order]
ON Contact.ContactID = [Order].ContactID
JOIN dbo.OrderDetail
ON [Order].OrderID = OrderDetail.OrderID
JOIN dbo.Product
ON OrderDetail.ProductID = Product.ProductID
JOIN dbo.ProductCategory
ON Product.ProductCategoryID = ProductCategory.ProductCategoryID
WHERE ProductCategoryName = 'Kite'
ORDER BY LastName, FirstName
---------------------------------------------------------------
-- Using Left Outer Joins
USE OBXKites
-- All Primary table - some secondary table
SELECT ContactCode, OrderNumber
FROM dbo.Contact
LEFT OUTER JOIN dbo.[Order]
ON [Order].ContactID = Contact.ContactID
ORDER BY ContactCode
-- Optional FK to look-up
SELECT OrderNumber, OrderPriorityName
FROM dbo.[Order]
Left Outer Join dbo.OrderPriority
ON [Order].OrderPriorityID = OrderPriority.OrderPriorityID
---------------------------------------------------------------
-- Multiplying Data with Cross Joins
USE CHA2
SELECT GuideID, CustomerID
FROM dbo.Guide
CROSS JOIN dbo.Customer
---------------------------------------------------------------
-- Referencing Self-Joins
USE Family
-- Check the PersonID of Audry
SELECT PersonID
FROM dbo.Person
WHERE LastName = 'Halloway'
AND FirstName = 'Audry'
-- Select all of Audry's children
SELECT Person.PersonID, Person.FirstName,
Person.MotherID
FROM dbo.Person
JOIN dbo.Person AS Mother -- second reference with Alias
ON Person.MotherID = Mother.PersonID
WHERE Mother.LastName = 'Halloway'
AND Mother.FirstName = 'Audry'
-- Select all birth w/ child, mother, & father
SELECT CONVERT(NVARCHAR(15),Person.DateofBirth,1) AS Date,
Person.FirstName AS Name, Person.Gender AS G,
ISNULL(F.FirstName + ' ' + F.LastName, ' * unknown *')
as Father,
M.FirstName + ' ' + M.LastName as Mother
FROM dbo.Person -- Person Reference
Left Outer JOIN dbo.Person F -- Father Reference
ON Person.FatherID = F.PersonID
INNER JOIN dbo.Person M -- Mother Reference
ON Person.MotherID = M.PersonID
ORDER BY Person.DateOfBirth
---------------------------------------------------------------
-- Working with Complex Joins
USE Family
SELECT Person.FirstName + ' ' + Person.LastName,
Twin.FirstName + ' ' + Twin.LastName as Twin,
Person.DateOfBirth
FROM dbo.Person
JOIN dbo.Person Twin
ON Person.PersonID <> Twin.PersonID
AND Person.MotherID = Twin.MotherID
AND Person.DateOfBirth = Twin.DateOfBirth
WHERE Person.DateOfBirth IS NOT NULL
---------------------------------------------------------------
-- Finding the Set-Difference
-- Find all Customers w/o orders
USE OBXKites
SELECT ContactCode, FirstName, LastName, OrderNumber
FROM dbo.Contact
LEFT OUTER JOIN dbo.[Order]
ON [Order].ContactID = Contact.ContactID
WHERE [Order].OrderID IS NULL
ORDER BY ContactCode
-- Find all products w/o orders
SELECT ProductName
FROM dbo.Product P
LEFT OUTER JOIN dbo.OrderDetail OD
ON P.ProductID = OD.ProductID
WHERE OD.ProductID IS NULL
---------------------------------------------------------------
-- Stacking Data with Unions
SELECT FirstName as [First], LastName as [Last], 'CHA2' as Source
FROM CHA2.dbo.Customer
UNION
SELECT FirstName, LastName, 'OBXKites'
FROM OBXKites.dbo.Contact
ORDER BY LastName, FirstName
-- Union ALL / Distinct
SELECT LastName
FROM CHA2.dbo.Customer
UNION --DISTINCT by default
SELECT LastName
FROM OBXKites.dbo.Contact
ORDER BY LastName
SELECT LastName
FROM CHA2.dbo.Customer
UNION ALL
SELECT LastName
FROM OBXKites.dbo.Contact
ORDER BY LastName
-- Union Intersect
SELECT LastName
FROM CHA2.dbo.Customer
INTERSECT
SELECT LastName
FROM OBXKites.dbo.Contact
ORDER BY LastName
-- Union Except (Difference Union)
SELECT LastName
FROM CHA2.dbo.Customer
EXCEPT
SELECT LastName
FROM OBXKites.dbo.Contact
ORDER BY LastName
0 Response to "Source Codes - SQL"
Post a Comment