sub queries

Posted in Labels: | at 10:13 AM

-----------------------------------------------------------
-- Total Training for SQL Server 2005
--
-- Part 2 - Selecting Data
-- Lesson 3 - Working with Subqueries
--
-- Paul Nielsen
-----------------------------------------------------------

-----------------------------------------------------------
-- Simple Subqueries
-- Coorelated Subqueries

-----------------------------------------------------------
-- Using Subqueries as Expressions

SELECT (SELECT 'Paul') AS SubqueryValue

USE CHA2

SELECT BaseCampID
FROM dbo.BaseCamp
WHERE Name = 'Ashville'

-- Find all Tours that depart from Ashville

-- Join Version
SELECT Tour.Name
FROM dbo.Tour
JOIN dbo.BaseCamp
ON Tour.BaseCampID = BaseCamp.BaseCampID
WHERE BaseCamp.Name = 'Ashville';

-- Where = (Scalar Subquery) Version
SELECT Tour.Name
FROM dbo.Tour
WHERE BaseCampID =
(SELECT BaseCampID
FROM dbo.BaseCamp
WHERE Name = 'Ashville');

-- Common Table Expression Version
WITH BC (BaseCampID)
AS (SELECT BaseCampID
FROM dbo.BaseCamp
WHERE Name = 'Ashville')
SELECT Tour.Name
FROM dbo.Tour
WHERE BaseCampID =
(SELECT BaseCampID FROM BC)


-----------------------------------------------------------
-- Including Subqueries as Lists

-- Where IN (List Subquery) Version
SELECT Tour.Name
FROM dbo.Tour
WHERE BaseCampID IN
(SELECT BaseCampID
FROM dbo.BaseCamp
WHERE Region = 'NC');

-- Where NOT in (List Subquery)
USE OBXKites

SELECT ProductName, ProductID
FROM dbo.Product
WHERE ProductID NOT IN
(SELECT TOP 10 ProductID
FROM dbo.Product
ORDER BY ProductID)


----------------------------------------------
-- Adding Subqueries as Derived Tables

-- How many children has each mother born?
USE Family

SELECT PersonID, FirstName, LastName, Children
FROM dbo.Person
JOIN (SELECT MotherID, COUNT(*) AS Children
FROM dbo.Person
WHERE MotherID IS NOT NULL
GROUP BY MotherID) ChildCount
ON Person.PersonID = ChildCount.MotherID
ORDER BY Children DESC;


-----------------------------------------------------------
-- Working with Correlated Subqueries

USE CHA2

-- Who has gone on an event outside thier state?
SELECT * FROM dbo.BaseCamp

-- this code assumes the data has been converted only once and the base campes are ID 1-4

UPDATE dbo.BaseCamp SET Region = 'NC' Where BaseCampID = 1
UPDATE dbo.BaseCamp SET Region = 'NC' Where BaseCampID = 2
UPDATE dbo.BaseCamp SET Region = 'BA' Where BaseCampID = 3
UPDATE dbo.BaseCamp SET Region = 'FL' Where BaseCampID = 4
UPDATE dbo.BaseCamp SET Region = 'WV' Where BaseCampID = 5

UPDATE dbo.Customer SET Region = 'ND' WHERE CustomerID = 1
UPDATE dbo.Customer SET Region = 'NC' WHERE CustomerID = 2
UPDATE dbo.Customer SET Region = 'NJ' WHERE CustomerID = 3
UPDATE dbo.Customer SET Region = 'NE' WHERE CustomerID = 4
UPDATE dbo.Customer SET Region = 'ND' WHERE CustomerID = 5
UPDATE dbo.Customer SET Region = 'NC' WHERE CustomerID = 6
UPDATE dbo.Customer SET Region = 'NC' WHERE CustomerID = 7
UPDATE dbo.Customer SET Region = 'BA' WHERE CustomerID = 8
UPDATE dbo.Customer SET Region = 'NC' WHERE CustomerID = 9
UPDATE dbo. Customer SET Region = 'FL' WHERE CustomerID = 10

-- location matrix
SELECT DISTINCT Customer.Region, BaseCamp.Region
FROM dbo.Customer
JOIN dbo.Event_mm_Customer
ON Customer.CustomerID = Event_mm_Customer.CustomerID
JOIN dbo.Event
ON Event_mm_Customer.EventID = Event.EventID
JOIN dbo.Tour
ON Event.TourID = Tour.TourID
JOIN dbo.BaseCamp
ON Tour.BaseCampID = BaseCamp.BaseCampID
WHERE Customer.Region IS NOT NULL
GROUP BY Customer.Region, BaseCamp.Region
ORDER BY Customer.Region, BaseCamp.Region

-- who lives near a base camp?
USE CHA2
SELECT C.FirstName, C.LastName, C.Region
FROM dbo.Customer C
WHERE EXISTS
(SELECT *
FROM dbo.BaseCamp B
WHERE B.Region = C.Region)
ORDER BY LastName, FirstName

-- same query as a join
SELECT DISTINCT C.FirstName, C.LastName, C.Region, B.Region
FROM dbo.Customer C
JOIN dbo.BaseCamp B
ON C.Region = B.Region
ORDER BY LastName, FirstName

-- Who attended an event in their home region?
USE CHA2
SELECT DISTINCT C.FirstName, C.LastName, C.Region AS Home
FROM dbo.Customer C
JOIN dbo.Event_mm_Customer E
ON C.CustomerID = E.CustomerID
WHERE C.Region IS NOT NULL
AND EXISTS
(SELECT *
FROM dbo.Event
JOIN dbo.Tour
ON Event.TourID = Tour.TourID
JOIN dbo.BaseCamp
ON Tour.BaseCampID = BaseCamp.BaseCampID
WHERE BaseCamp.Region = C.Region
AND Event.EventID = E.EventID)

-- Same query as a join
SELECT Distinct C.FirstName, C.LastName, C.Region AS Home,
Tour.Name, BaseCamp.Region
FROM dbo.Customer C
JOIN dbo.Event_mm_Customer
ON C.CustomerID = Event_mm_Customer.CustomerID
JOIN dbo.Event
ON Event_mm_Customer.EventID = Event.EventID
JOIN dbo.Tour
ON Event.TourID = Tour.TourID
JOIN dbo.BaseCamp
ON Tour.BaseCampID = BaseCamp.BaseCampID
AND C.Region = BaseCamp.Region
AND C.Region IS NOT NULL
ORDER BY C.LastName

Go


-----------------------------------------------------------
-- Solving for X with Relational Division

-- Exact Relational Division
go
USE OBXKites
go
DECLARE @OrderNumber INT

-- First Person orders exactly all toys
EXEC pOrder_AddNew
@ContactCode = '110',
@EmployeeCode = '120',
@LocationCode = 'CH',
@OrderDate='6/1/2002',
@OrderNumber = @OrderNumber output

EXEC pOrder_AddItem
@OrderNumber = @OrderNumber, -- must be a valid, open order. Get OrderNumber from pOrder_AddNew
@Code = '1049', -- if NULL then non-stock Product text description
@NonStockProduct = NULL,
@Quantity = 1, -- required
@UnitPrice = NULL, -- if Null then the sproc will lookup the correct current price for the customer
@ShipRequestDate = NULL, -- if NULL then today
@ShipComment = NULL

EXEC pOrder_AddItem @OrderNumber, '1050', NULL, 1, NULL, NULL, NULL

-- Second Person - has ordered exactly all toys - 1050 twice
EXEC pOrder_AddNew '111', '119', 'JR', '6/1/2002', @OrderNumber output
EXEC pOrder_AddItem @OrderNumber, '1049', NULL, 1, NULL, NULL, NULL
EXEC pOrder_AddItem @OrderNumber, '1050', NULL, 1, NULL, NULL, NULL

EXEC pOrder_AddNew '111', '119', 'JR', '6/1/2002', @OrderNumber output
EXEC pOrder_AddItem @OrderNumber, '1050', NULL, 1, NULL, NULL, NULL

-- Third Person - has order all toys plus some others
EXEC pOrder_AddNew '112', '119', 'JR', '6/1/2002', @OrderNumber output
EXEC pOrder_AddItem @OrderNumber, '1049', NULL, 1, NULL, NULL, NULL
EXEC pOrder_AddItem @OrderNumber, '1050', NULL, 1, NULL, NULL, NULL
EXEC pOrder_AddItem @OrderNumber, '1001', NULL, 1, NULL, NULL, NULL
EXEC pOrder_AddItem @OrderNumber, '1002', NULL, 1, NULL, NULL, NULL

-- Fourth Person - has order one toy
EXEC pOrder_AddNew '113', '119', 'JR', '6/1/2002', @OrderNumber output
EXEC pOrder_AddItem @OrderNumber, '1049', NULL, 1, NULL, NULL, NULL

SELECT *
FROM dbo.[order]
WHERE OrderDate = '6/1/2002'

--Relational Division with remainder
-- Is number of toys ordered...
SELECT Contact.ContactCode
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 ProductCategory.ProductCategoryName = 'Toy'
GROUP BY Contact.ContactCode
HAVING COUNT(DISTINCT Product.Code) =
-- equal to number of toys available?
(SELECT Count(Code)
FROM dbo.Product
JOIN dbo.ProductCategory
ON Product.ProductCategoryID
= ProductCategory.ProductCategoryID
WHERE ProductCategory.ProductCategoryName = 'Toy')

-- Exact Relational Division
-- Is number of all products ordered...
SELECT Contact.ContactCode
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 P1
ON Product.ProductCategoryID = P1.ProductCategoryID
JOIN
-- and number of toys ordered
(SELECT Contact.ContactCode, Product.Code
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 ProductCategory.ProductCategoryName = 'Toy') ToysOrdered
ON Contact.ContactCode = ToysOrdered.ContactCode
GROUP BY Contact.ContactCode
HAVING COUNT(DISTINCT Product.Code) =
-- equal to number of toys available?
(SELECT Count(Code)
FROM dbo.Product
JOIN dbo.ProductCategory
ON Product.ProductCategoryID
= ProductCategory.ProductCategoryID
WHERE ProductCategory.ProductCategoryName = 'Toy')
-- AND equal to the total number of any product ordered?
AND COUNT(DISTINCT ToysOrdered.Code) =
(SELECT Count(Code)
FROM dbo.Product
JOIN dbo.ProductCategory
ON Product.ProductCategoryID
= ProductCategory.ProductCategoryID
WHERE ProductCategory.ProductCategoryName = 'Toy')


Read Users' Comments (0)

0 Response to "sub queries"

Post a Comment

Subscribe to: Post Comments (Atom)