ch 07 - merging data with relational algebra.sql

《SQLServer2000宝典》—包括本书中的所有代码

-----------------------------------------------------------
-- SQL Server 2000 Bible 
-- Wiley Publishing 
-- Paul Nielsen

-- Chapter 7  - Merging Data using Relational Algebra

-----------------------------------------------------------
-----------------------------------------------------------

-------------------------------------------------
-- Using Joins and Unions

USE CHA2

SELECT Tour.Name, Tour.BaseCampID,
    BaseCamp.BaseCampID, BaseCamp.Name
  FROM dbo.Tour
    JOIN dbo.BaseCamp
      ON Tour.BaseCampID = BaseCamp.BaseCampID

-- the side by side queries:
USE OBXKites

SELECT ContactCode, LastName
  FROM dbo.Contact
  ORDER BY ContactCode

SELECT Contact.ContactCode, Contact.ContactID, 
    [Order].ContactID, [Order].OrderNumber
  FROM dbo.Contact
    JOIN dbo.[Order] 
      ON [Order].ContactID = Contact.ContactID
  ORDER BY ContactCode

-- another example inner 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

-- Legacy Join
SELECT Contact.ContactCode, Contact.ContactID, 
    [Order].ContactID, [Order].OrderNumber
  FROM dbo.Contact, dbo.[Order]
  WHERE [Order].ContactID = Contact.ContactID
  ORDER BY ContactCode

-- Left Outer Join
SELECT ContactCode, OrderNumber
  FROM dbo.Contact
    LEFT OUTER JOIN dbo.[Order] 
      ON [Order].ContactID = Contact.ContactID
  ORDER BY ContactCode


SELECT OrderNumber, OrderPriorityName
  FROM dbo.[Order]
    Left Outer Join dbo.OrderPriority
    ON [Order].OrderPriorityID = OrderPriority.OrderPriorityID


-- Full Outer Join
go
USE Tempdb

IF EXISTS(SELECT * FROM SysObjects Where [Name] = 'One')
  DROP TABLE dbo.One
IF EXISTS(SELECT * FROM SysObjects Where [Name] = 'Two')
  DROP TABLE dbo.Two

CREATE TABLE dbo.One (
  OnePK INT,
  Thing1 VARCHAR(15)
  )

CREATE TABLE dbo.Two (
  TwoPK INT,
  OnePK INT,
  Thing2 VARCHAR(15)
  )
go
INSERT dbo.One(OnePK, Thing1)
  VALUES (1, 'Old Thing')
INSERT dbo.One(OnePK, Thing1)
  VALUES (2, 'New Thing')
INSERT dbo.One(OnePK, Thing1)
  VALUES (3, 'Red Thing')
INSERT dbo.One(OnePK, Thing1)
  VALUES (4, 'Blue Thing')

INSERT dbo.Two(TwoPK, OnePK, Thing2)
  VALUES(1,0, 'Plane')
INSERT dbo.Two(TwoPK, OnePK, Thing2)
  VALUES(2,2, 'Train')
INSERT dbo.Two(TwoPK, OnePK, Thing2)
  VALUES(3,3, 'Car')
INSERT dbo.Two(TwoPK, OnePK, Thing2)
  VALUES(4,NULL, 'Cycle')

-- Inner Join
SELECT Thing1, Thing2 
  FROM dbo.One
    INNER JOIN dbo.Two 
      ON One.OnePK = Two.OnePK

-- Left Outer Join
SELECT Thing1, Thing2 
  FROM dbo.One
    LEFT OUTER JOIN dbo.Two 
      ON One.OnePK = Two.OnePK

-- Full Outer Join
SELECT Thing1, Thing2 
  FROM dbo.One
    FULL OUTER JOIN dbo.Two 
      ON One.OnePK = Two.OnePK


-- Condition Placement
SELECT Thing1, Thing2 
  FROM dbo.One
    LEFT OUTER JOIN dbo.Two 
      ON One.OnePK = Two.OnePK
        AND One.Thing1 = 'New Thing'
  
SELECT Thing1, Thing2 
  FROM dbo.One
    LEFT OUTER JOIN dbo.Two 
      ON One.OnePK = Two.OnePK
  WHERE One.Thing1 = 'New Thing'

-- Self-Joins
USE Family
SELECT Person.PersonID, Person.FirstName, 
    Person.MotherID, Mother.PersonID
  FROM dbo.Person 
    JOIN dbo.Person Mother
      ON Person.MotherID = Mother.PersonID
  WHERE Mother.LastName = 'Halloway'
    AND Mother.FirstName = 'Audry'

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
    Left Outer JOIN dbo.Person F
      ON Person.FatherID = F.PersonID
    INNER JOIN dbo.Person M
      ON Person.MotherID = M.PersonID
  ORDER BY Person.DateOfBirth 

-- Cross Joins

USE Tempdb

SELECT Thing1, Thing2 
  FROM dbo.One
    CROSS JOIN dbo.Two 

-- Theta Join 
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

SELECT Person.FirstName + ' ' + Person.LastName AS Person, 
    Twin.FirstName + ' ' + Twin.LastName as Twin,
    Person.DateOfBirth
  FROM dbo.Person
    JOIN dbo.Person Twin
      ON Person.MotherID = Twin.MotherID
        AND Person.DateOfBirth = Twin.DateOfBirth
  WHERE Person.DateOfBirth IS NOT NULL
     AND Person.PersonID != Twin.PersonID

-------------------------------------------------
-- SubQueries

SELECT (SELECT 3) AS SubqueryValue

USE OBXKites

SELECT ProductName 
  FROM dbo.Product
  WHERE ProductCategoryID
    = (Select ProductCategoryID 
          FROM dbo.ProductCategory 
          Where ProductCategoryName = 'Kite')

SELECT ProductName
  FROM dbo.Product
    JOIN dbo.ProductCategory
       ON Product.ProductCategoryID = ProductCategory.ProductCategoryID
  Where ProductCategoryName = 'Kite'

SELECT ProductName 
  FROM dbo.Product
  WHERE ProductCategoryID
    = 'C38D8113-2BED-4E2B-9ABF-A589E0818069' -- Note: Your GUID will be different  -pn

-- Subqueries as expressions

-- Column Expression
SELECT ProductCategoryName, SUM(Quantity * UnitPrice) AS Sales, 
     Cast(SUM(Quantity * UnitPrice) /
         (SELECT SUM(Quantity * UnitPrice) FROM OrderDetail) *100 AS NUMERIC(4,2)) 
         AS PercentOfSales
  FROM dbo.OrderDetail
    JOIN dbo.Product
      ON OrderDetail.ProductID = Product.ProductID
    JOIN dbo.ProductCategory
      ON Product.ProductCategoryID = ProductCategory.ProductCategoryID
  GROUP BY ProductCategoryName
  ORDER BY Count(*) DESC

-- Subqueries as Lists

SELECT ProductName 
  FROM dbo.Product
  WHERE ProductID IN
    -- 4. Find all the products sold in orders with kites
    (SELECT ProductID 
      FROM dbo.OrderDetail
      WHERE OrderID IN 
      -- 3. Find the Kite Orders
      (SELECT OrderID  -- Find the Orders with Kites
        FROM dbo.OrderDetail 
        WHERE ProductID IN 
          -- 2. Find the Kite Products
          (SELECT ProductID  
            FROM dbo.Product       
            WHERE ProductCategoryID = 
               -- 1. Find the Kite category
               (Select ProductCategoryID 
                 FROM dbo.ProductCategory 
                 Where ProductCategoryName 
                    = 'Kite' ) ) ) )

SELECT ProductName 
  FROM dbo.Product
  WHERE ProductID IN
    -- 4. Find all the products sold in orders with kites
    (SELECT ProductID 
      FROM dbo.OrderDetail
      WHERE OrderID IN 
      -- 3. Find the Kite Orders
      (SELECT OrderID  -- Find the Orders with Kites
        FROM dbo.OrderDetail 
        WHERE ProductID IN 
          -- 2. Find the Kite Products
          (SELECT ProductID  
            FROM dbo.Product       
            WHERE ProductCategoryID = 
               -- 1. Find the Kite category
               (Select ProductCategoryID 
                 FROM dbo.ProductCategory 
                 Where ProductCategoryName 
                    = 'Kite' ) ) ) )
        AND ProductID NOT IN 
          (SELECT ProductID  
            FROM dbo.Product       
            WHERE ProductCategoryID = 
               (Select ProductCategoryID 
                 FROM dbo.ProductCategory 
                 Where ProductCategoryName 
                    = 'Kite' ) ) 

SELECT Distinct Product.ProductName
  FROM dbo.Product
    JOIN dbo.OrderDetail OrderRow
      ON Product.ProductID = OrderRow.ProductID
    JOIN dbo.OrderDetail KiteRow
      ON OrderRow.OrderID = KiteRow.OrderID
    JOIN dbo.Product Kite
      ON KiteRow.ProductID = Kite.ProductID
    JOIN dbo.ProductCategory
      ON Kite.ProductCategoryID = ProductCategory.ProductCategoryID
  Where ProductCategoryName  = 'Kite'

SELECT Distinct Product.ProductName
  FROM dbo.Product
    JOIN dbo.OrderDetail OrderRow
      ON Product.ProductID = OrderRow.ProductID
    JOIN dbo.OrderDetail KiteRow
      ON OrderRow.OrderID = KiteRow.OrderID
    JOIN dbo.Product Kite
      ON KiteRow.ProductID = Kite.ProductID
    JOIN dbo.ProductCategory
      ON Kite.ProductCategoryID = ProductCategory.ProductCategoryID
      AND Product.ProductCategoryID != Kite.ProductCategoryID
Where ProductCategoryName  = 'Kite'    

-- Where = ALL (subquery)

Select 'Yes'
where 
1 = ALL (select 1 where 1=0) -- empty set all is true

Select 'Yes'
where 
1 < ALL (select 1 where 1=0) -- empty set all is true

Select 'Yes'
where 
1 = ALL (select 1) -- works as expected

Select 'Yes'
where 
1 = ALL (select 2) -- works as expected


-- Derived Tables
-- Subquery solution to aggregate problem
-- How many of each product (include product code) have been sold?

SELECT Code, SUM(Quantity) AS QuantitySold
  FROM dbo.OrderDetail
    JOIN dbo.Product
      ON OrderDetail.ProductID = Product.ProductID
  GROUP BY Code

SELECT Product.Code, Product.ProductName, 
    Sales.QuantitySold 
  FROM dbo.Product
  JOIN (SELECT ProductID, SUM(Quantity) AS QuantitySold
             FROM dbo.OrderDetail
             GROUP BY ProductID) Sales
    ON Product.ProductID = Sales.ProductID
  ORDER BY Code

-- 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

-- Correlated Subqueries

USE CHA2

-- Who has gone on an event outside thier state? 
SELECT * FROM dbo.BaseCamp
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.LastName, 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.LastName, Customer.Region, BaseCamp.Region
  ORDER BY Customer.LastName, 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)

SELECT DISTINCT C.FirstName, C.LastName, C.Region, B.Region
  FROM dbo.Customer C
    JOIN dbo.BaseCamp B
      ON C.Region = B.Region

-- 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

-- CrossTab Query using Correlated Subqueries

USE TempDB

CREATE TABLE RawData (
  X VARCHAR(2),
  Y VARCHAR(2),
  Data INT )

INSERT RawData (X,Y,Data)
  VALUES( 'A', 'X', 1)
INSERT RawData (X,Y,Data)
  VALUES( 'B', 'X', 2)
INSERT RawData (X,Y,Data)
  VALUES( 'C', 'X', 3)
INSERT RawData (X,Y,Data)
  VALUES( 'A', 'Y', 4)
INSERT RawData (X,Y,Data)
  VALUES( 'B', 'Y', 5)
INSERT RawData (X,Y,Data)
  VALUES( 'D', 'Y', 6)
INSERT RawData (X,Y,Data)
  VALUES( 'A', 'Z', 7)
INSERT RawData (X,Y,Data)
  VALUES( 'B', 'Z', 8)
INSERT RawData (X,Y,Data)