My version
connect too ACS-DBA-TJAY\USERGROUP.Itzik


---------------------------------------------------------------------
-- Inside Microsoft SQL Server 2005: T-SQL Querying (MSPress, 2006)
-- Chapter 01 - Logical Query Processing
-- Copyright Itzik Ben-Gan, 2006
-- All Rights Reserved
---------------------------------------------------------------------

---------------------------------------------------------------------
-- Sample Query based on Customers/Orders Scenario
---------------------------------------------------------------------

-- Listing 1-2: Data Definition Language (DDL) and sample data for Customers and Orders
SET NOCOUNT ON;
USE tempdb;
GO
IF OBJECT_ID('dbo.Orders') IS NOT NULL
  DROP TABLE dbo.Orders;
GO
IF OBJECT_ID('dbo.Customers') IS NOT NULL
  DROP TABLE dbo.Customers;
GO
CREATE TABLE dbo.Customers
(
  CustomerID  CHAR(5)     NOT NULL PRIMARY KEY,
  city        VARCHAR(10) NOT NULL
);

INSERT INTO dbo.Customers(CustomerID, city) VALUES('FISSA', 'Madrid');
INSERT INTO dbo.Customers(CustomerID, city) VALUES('FRNDO', 'Madrid');
INSERT INTO dbo.Customers(CustomerID, city) VALUES('KRLOS', 'Madrid');
INSERT INTO dbo.Customers(CustomerID, city) VALUES('MRPHS', 'Zion');

CREATE TABLE dbo.Orders
(
  orderid    INT     NOT NULL PRIMARY KEY,
  CustomerID CHAR(5)     NULL REFERENCES Customers(CustomerID)
);

INSERT INTO dbo.Orders(orderid, CustomerID) VALUES(1, 'FRNDO');
INSERT INTO dbo.Orders(orderid, CustomerID) VALUES(2, 'FRNDO');
INSERT INTO dbo.Orders(orderid, CustomerID) VALUES(3, 'KRLOS');
INSERT INTO dbo.Orders(orderid, CustomerID) VALUES(4, 'KRLOS');
INSERT INTO dbo.Orders(orderid, CustomerID) VALUES(5, 'KRLOS');
INSERT INTO dbo.Orders(orderid, CustomerID) VALUES(6, 'MRPHS');
INSERT INTO dbo.Orders(orderid, CustomerID) VALUES(7, NULL);
GO

/*
select * from Customers
select * from Orders
*/


-- Listing 1-3: Query: Madrid customers with Fewer than three orders

-- The query returns customers from Madrid that made fewer than
-- three orders (including zero), and their order count.
-- The result is sorted by the order count.
SELECT C.CustomerID, COUNT(O.orderid) AS numorders
FROM dbo.Customers AS C
  LEFT OUTER JOIN dbo.Orders AS O
    ON C.CustomerID = O.CustomerID
WHERE C.city = 'Madrid'
GROUP BY C.CustomerID
HAVING COUNT(O.orderid) < 3
ORDER BY numorders;
GO

---------------------------------------------------
-- Query Logical Processing Phase Details
---------------------------------------------------

---------------------------------------------------------------------
-- Step 1 – Performing a cartesian Product (Cross Join)
---------------------------------------------------------------------

SELECT 
    C.CustomerID, 
    C.City, 
    O.OrderID, 
    O.CustomerID
  FROM dbo.Customers AS C, dbo.Orders AS O
  ORDER BY c.CustomerID;
GO


---------------------------------------------------------------------
-- Step 2 – Applying the ON Filter (Join Condition)
---------------------------------------------------------------------
--use this to generate the list of matches

SELECT 
    case
      when C.CustomerID = O.CustomerID then 'True'
      when C.CustomerID <> O.CustomerID then 'False'
      when C.CustomerID is Null then 'Unknown'
      when O.CustomerID is Null then 'Unknown'
    end as [Match?],
    C.CustomerID, 
    C.City, 
    O.OrderID, 
    O.CustomerID
  FROM dbo.Customers AS C, dbo.Orders AS O
  ORDER BY c.CustomerID;
GO

--use this to create VT2
SELECT 
    case
      when C.CustomerID = O.CustomerID then 'True'
      when C.CustomerID <> O.CustomerID then 'False'
      when C.CustomerID is Null then 'Unknown'
      when O.CustomerID is Null then 'Unknown'
    end as [Match?],
    C.CustomerID, 
    C.City, 
    O.OrderID, 
    O.CustomerID
  FROM dbo.Customers AS C, dbo.Orders AS O
  where C.CustomerID = O.CustomerID
  ORDER BY c.CustomerID;
GO

---------------------------------------------------------------------
-- Step 3 – Adding Outer Rows
---------------------------------------------------------------------
--I cheat here with sql to represent the resulting virtual table generated with a union and a where not in

SELECT 
    C.CustomerID, 
    C.City, 
    O.OrderID, 
    O.CustomerID
  FROM dbo.Customers AS C, dbo.Orders AS O
  where C.CustomerID = O.CustomerID
union all
Select distinct 
    C.CustomerID,
    C.City, 
    null as OrderID, 
    null as CustomerID
  FROM dbo.Customers AS C
  where C.CustomerID not in ( SELECT C.CustomerID FROM dbo.Customers AS C, dbo.Orders AS O where C.CustomerID = O.CustomerID)
--  ORDER BY OrderID
GO

---------------------------------------------------------------------
-- Step 4 – Applying the WHERE Filter
---------------------------------------------------------------------
--copying the above example for resulting data, lets simply add in a where on City = Madrid
--the rows for customer MRPHS is removed, because they are not from Madrid

SELECT 
      C.CustomerID, 
      C.City, 
      O.OrderID, 
      O.CustomerID
    FROM dbo.Customers AS C, dbo.Orders AS O
    where C.CustomerID = O.CustomerID
      and c.City = 'Madrid'
  union all
  Select distinct 
      C.CustomerID,
      C.City, 
      null as OrderID, 
      null as CustomerID
    FROM dbo.Customers AS C
    where C.CustomerID not in ( SELECT C.CustomerID FROM dbo.Customers AS C, dbo.Orders AS O where C.CustomerID = O.CustomerID)
      and c.City = 'Madrid'
GO


---------------------------------------------------------------------
-- Step 5 – Grouping
---------------------------------------------------------------------
--cant really show this in a query... two sections... groups and raw data

--resulting query
SELECT 
      C.CustomerID, 
      C.City, 
      count( O.OrderID) as [OrderID], 
      O.CustomerID
    FROM dbo.Customers AS C, dbo.Orders AS O
    where C.CustomerID = O.CustomerID
      and c.City = 'Madrid'
    group by c.CustomerID, C.City, o.CustomerID
  union all
  Select distinct 
      C.CustomerID,
      C.City, 
      null as OrderID, 
      null as CustomerID
    FROM dbo.Customers AS C
    where C.CustomerID not in ( SELECT C.CustomerID FROM dbo.Customers AS C, dbo.Orders AS O where C.CustomerID = O.CustomerID)
      and c.City = 'Madrid'
    group by c.CustomerID, C.City  --silly because those are the only 2 fields returned
GO

---------------------------------------------------------------------
-- Step 6 – Applying the CUBE or ROLLUP Option
---------------------------------------------------------------------
--we did not have this step in this particular query


---------------------------------------------------------------------
-- Step 7 – Applying the HAVING filter
---------------------------------------------------------------------

SELECT 
      C.CustomerID, 
      C.City, 
      O.OrderID, 
      O.CustomerID
    FROM dbo.Customers AS C, dbo.Orders AS O
    where C.CustomerID = O.CustomerID
      and c.City = 'Madrid'
      and ( SELECT count(*) FROM dbo.Orders AS O where O.CustomerID = C.CustomerID) < 3
  union all
  Select distinct 
      C.CustomerID,
      C.City, 
      null as OrderID, 
      null as CustomerID
    FROM dbo.Customers AS C
    where C.CustomerID not in ( SELECT C.CustomerID FROM dbo.Customers AS C, dbo.Orders AS O where C.CustomerID = O.CustomerID)
      and c.City = 'Madrid'
GO


---------------------------------------------------------------------
-- Step 8 – Processing the SELECT list
---------------------------------------------------------------------

SELECT 
      C.CustomerID, 
      count( O.OrderID) as [numorders]
    FROM dbo.Customers AS C, dbo.Orders AS O
    where C.CustomerID = O.CustomerID
      and c.City = 'Madrid'
      and ( SELECT count(*) FROM dbo.Orders AS O where O.CustomerID = C.CustomerID) < 3
    group by C.CustomerID
  union all
  Select distinct 
      C.CustomerID,
      0 as [numorders]
    FROM dbo.Customers AS C
    where C.CustomerID not in ( SELECT C.CustomerID FROM dbo.Customers AS C, dbo.Orders AS O where C.CustomerID = O.CustomerID)
      and c.City = 'Madrid'
GO

---------------------------------------------------------------------
-- Step 9 – Applying the DISTNCT clause
---------------------------------------------------------------------

--this step is skipped in this example

---------------------------------------------------------------------
-- Step 10 – Applying ORDER BY clause
---------------------------------------------------------------------

-- Sorting by Ordinal Positions
SELECT 
    orderid, 
    CustomerID 
  FROM dbo.Orders 
  ORDER BY 2, 1;
GO

-- ORDER BY in Derived Table is not Allowed
SELECT *
  FROM (
          SELECT 
              orderid, 
              CustomerID
            FROM dbo.Orders
            ORDER BY orderid
        ) AS D;
GO

-- ORDER BY in View is not Allowed
IF OBJECT_ID('dbo.VSortedOrders') IS NOT NULL
  DROP VIEW dbo.VSortedOrders;
GO

CREATE VIEW dbo.VSortedOrders
AS

SELECT 
    orderid, 
    CustomerID
  FROM dbo.Orders
  ORDER BY orderid
GO

---------------------------------------------------------------------
-- Step 11 – Applying TOP Option
---------------------------------------------------------------------

-- Circumventing ORDER BY Limitation with TOP
SELECT *
  FROM (
          SELECT TOP 100 PERCENT 
              orderid, 
              CustomerID
            FROM dbo.Orders
            ORDER BY orderid
        ) AS D;
GO

IF OBJECT_ID('dbo.VSortedOrders') IS NOT NULL
  DROP VIEW dbo.VSortedOrders;
GO

-- Note: This does not create a “sorted view”!
CREATE VIEW dbo.VSortedOrders
AS

SELECT TOP 100 PERCENT 
    orderid, 
    CustomerID
  FROM dbo.Orders
  ORDER BY orderid
GO