Author Name	City	State	Title
White, Johnson	Menlo Park	CA	Prolonged Data Deprivation: Four Case Studies
Green, Marjorie	Oakland	CA	The Busy Executive's Database Guide
Green, Marjorie	Oakland	CA	You Can Combat Computer Stress!
Carson, Cheryl	Berkeley	CA	But Is It User Friendly?
O'Leary, Michael	San Jose	CA	Cooking with Computers: Surreptitious Balance Sheets
O'Leary, Michael	San Jose	CA	Sushi, Anyone?
Straight, Dean	Oakland	CA	Straight Talk About Computers
Bennet, Abraham	Berkeley	CA	The Busy Executive's Database Guide
Dull, Ann	Palo Alto	CA	Secrets of Silicon Valley
Gringlesby, Burt	Covelo	CA	Sushi, Anyone?
Locksley, Charlene	San Francisco	CA	Net Etiquette
Locksley, Charlene	San Francisco	CA	Emotional Security: A New Algorithm
Yokomoto, Akiko	Walnut Creek	CA	Sushi, Anyone?
Stringer, Dirk	Oakland	CA	(null)
MacFeather, Stearns	Oakland	CA	Cooking with Computers: Surreptitious Balance Sheets
MacFeather, Stearns	Oakland	CA	Computer Phobic AND Non-Phobic Individuals
Karsen, Livia	Oakland	CA	Computer Phobic AND Non-Phobic Individuals
Hunter, Sheryl	Palo Alto	CA	Secrets of Silicon Valley
McBadden, Heather	Vacaville	CA	(null)
(null)	(null)	(null)	The Psychology of Computer Cooking

Figure 3   Cartesian Product

-- Using the employee table each will be setup with
-- all the default tax accumulators and a zero quantity

-- create and populate the temporary table using static data
-- in a real life situation this table may be populated externally
CREATE TABLE #tax (
    tax_type        VARCHAR(11)    NOT NULL
    )
GO

INSERT INTO #tax VALUES ('FEDERAL')
INSERT INTO #tax VALUES ('FICA')
INSERT INTO #tax VALUES ('MEDICARE')
INSERT INTO #tax VALUES ('STATE')
INSERT INTO #tax VALUES ('LOCAL')
INSERT INTO #tax VALUES ('CITY')
GO

-- create the new employee benefit table
CREATE TABLE emptaxaccum (
    emp_id       empid  NOT NULL REFERENCES employee(emp_id),
    tax_type     id     NOT NULL,
    tax_amount   MONEY  NOT NULL,
    PRIMARY KEY (emp_id, tax_type)
    )
GO

-- populate the employee tax tables with all the base options
INSERT INTO emptaxaccum
SELECT E.emp_id, B.tax_type, 0
FROM employee AS E CROSS JOIN #tax AS B
GO

-- drop the temporary table
DROP TABLE #tax
GO

Figure 4   Best-Selling Stores

CREATE PROCEDURE usp_best_selling_stores
    @percent        INTEGER
AS

    SET NOCOUNT ON
    
    -- create a temporary table to hold the sales summary information
    CREATE TABLE #beststores (
        rank_id      INTEGER        IDENTITY NOT NULL PRIMARY KEY,
        stor_id      VARCHAR(4),
        stor_name    VARCHAR(40),
        tot_sales    MONEY
    )
    
    -- insert into the temporary table the sale summary records
    -- in descending order to ensure the identity runs from 1 up
    INSERT INTO #beststores
    SELECT ST.stor_id, ST.stor_name, SUM(SA.qty * T.price) AS tot_sales
    FROM stores AS ST
    INNER JOIN sales AS SA ON ST.stor_id = SA.stor_id
    INNER JOIN titles AS T ON SA.title_id = T.title_id
    GROUP BY ST.stor_id, ST.stor_name
    ORDER BY tot_sales DESC
    
    -- calculate the number of records to return based on percentage
    DECLARE @rowstofetch INTEGER
    SELECT @rowstofetch = CEILING((@@ROWCOUNT * @percent) / 100.0)
    
    SET NOCOUNT OFF
    
    SET ROWCOUNT @rowstofetch
    SELECT * FROM #beststores
    ORDER BY rank_id
    SET ROWCOUNT 0

    DROP TABLE #beststores
    
GO

Figure 5   Best-Selling Stores with Ties

CREATE PROCEDURE usp_best_selling_stores_ties
    @percent        INTEGER
AS

    SET NOCOUNT ON
    
    -- create a temporary table to hold the sales summary information
    CREATE TABLE #beststores (
        rank_id      INTEGER        IDENTITY NOT NULL PRIMARY KEY,
        stor_id      VARCHAR(4),
        stor_name    VARCHAR(40),
        tot_sales    MONEY
    )
    
    -- insert into the temporary table the sale summary records
    -- in descending order to ensure the identity runs from 1 up
    INSERT INTO #beststores
    SELECT ST.stor_id, ST.stor_name, SUM(SA.qty * T.price) AS tot_sales
    FROM stores AS ST
    INNER JOIN sales AS SA ON ST.stor_id = SA.stor_id
    INNER JOIN titles AS T ON SA.title_id = T.title_id
    GROUP BY ST.stor_id, ST.stor_name
    ORDER BY tot_sales DESC
    
    -- calculate the sales of the last row to return
    DECLARE @maxrowid INTEGER
    DECLARE @maxsales MONEY
    SELECT @maxrowid = CEILING((@@ROWCOUNT * @percent) / 100.0)
    SELECT @maxsales = (SELECT tot_sales FROM #beststores
                        WHERE rank_id = @maxrowid)
                            
    SET NOCOUNT OFF
    
    SELECT (SELECT MIN(B_IN.rank_id) FROM #beststores B_IN
            WHERE B_IN.tot_sales = B.tot_sales) AS tie_rank_id,
            B.stor_id, B.stor_name, B.tot_sales
    FROM #beststores B
    WHERE tot_sales >= @maxsales
    ORDER BY tie_rank_id

    DROP TABLE #beststores
    
GO

Figure 6   Revenues by Quarter

CREATE PROCEDURE usp_title_revenue_quarter
AS

SELECT     T.title_id, T.title, sale_year = DATEPART(yy, S.ord_date),
SUM(CASE DATEPART(qq, S.ord_date) WHEN 1 THEN (S.qty * T.price) ELSE 0 END) AS q1_sales,
SUM(CASE DATEPART(qq, S.ord_date) WHEN 2 THEN (S.qty * T.price) ELSE 0 END) AS q2_sales,
SUM(CASE DATEPART(qq, S.ord_date) WHEN 3 THEN (S.qty * T.price) ELSE 0 END) AS q3_sales,
SUM(CASE DATEPART(qq, S.ord_date) WHEN 4 THEN (S.qty * T.price) ELSE 0 END) AS q4_sales,
SUM(S.qty * T.price) AS yearly_sales
FROM titles AS T
INNER JOIN sales AS S ON T.title_id = S.title_id
GROUP BY T.title_id, T.title, DATEPART(yy, S.ord_date)
ORDER BY T.title_id, T.title, sale_year 

GO

Figure 7   Expanding Hierarchy Output

bom_level	prod_id	prod_name	bom_qty
1	10	Microsoft Press Visual C++ Team Publications	1
2	9	Programming Microsoft Visual C++	5
2	8	Microsoft Visual C++ Programmers Guide	5
2	1	Microsoft Visual C++ Reference Library	1
3	7	Microsoft Visual C++ Template Libraries Reference	1
3	4	Microsoft Visual C++ MFC Reference Library	1
4	6	Microsoft Visual C++ MFC Library Reference Part 2	1
4	5	Microsoft Visual C++ MFC Library Reference Part 1	1
3	3	Microsoft Visual C++ Language Reference	1
3	2	Microsoft Visual C++ Run-Time Library Reference	1

Figure 9   Expanding Hierarchy Stored Procedure

CREATE PROCEDURE usp_bill_of_material
    @prod_id        INTEGER
AS

    SET NOCOUNT ON
    
    -- define the required temporary variables
    DECLARE @prev_prod_id    INTEGER
    DECLARE @curr_prod_id    INTEGER
    DECLARE @curr_bom_level  INTEGER
    DECLARE @quantity    NUMERIC(7,2)

    CREATE TABLE #stack (
        stack_nbr    INTEGER IDENTITY NOT NULL PRIMARY KEY,
        depth_level  INTEGER NOT NULL,
        prod_id      INTEGER NOT NULL,
        parent_id    INTEGER NULL
    )
    
    CREATE TABLE #bom (
        seq_nbr      INTEGER IDENTITY NOT NULL PRIMARY KEY,
        bom_level    INTEGER NOT NULL,
        prod_id      INTEGER NOT NULL,
        bom_qty      NUMERIC(7,2) NULL
    )

    -- define the initail values of the variables
    SELECT @prev_prod_id = NULL
    SELECT @curr_prod_id = NULL
    SELECT @curr_bom_level = 1
    INSERT INTO #stack VALUES (1, @prod_id, NULL)

    -- process items in the stack table
    -- as long as there is still a processing level
    WHILE @curr_bom_level > 0
    BEGIN
        IF EXISTS (SELECT * FROM #stack WHERE depth_level = @curr_bom_level)
        BEGIN

            -- select highest level item from the stack
            SET ROWCOUNT 1
            SELECT @curr_prod_id = prod_id, @prev_prod_id = parent_id
            FROM #stack
            ORDER BY stack_nbr DESC
            SET ROWCOUNT 0
            
            -- calculate the bom quantity for the new item

            IF @prev_prod_id IS NULL
                SELECT @quantity = 1
            ELSE
                SELECT @quantity = ISNULL((SELECT prod_bom_qty FROM materials    
                            WHERE prod_id = @prev_prod_id
                            AND prod_bom_id = @curr_prod_id), 0)
                                    
            -- write the item into the bom table
            INSERT INTO #bom 
            VALUES (@curr_bom_level, @curr_prod_id, @quantity)
            
            -- removed the process item from the stack
            DELETE FROM #stack
            WHERE depth_level = @curr_bom_level
            AND   prod_id = @curr_prod_id
            
            -- insert into the stack any sub items for the current item
            INSERT INTO #stack
            SELECT @curr_bom_level + 1, prod_bom_id, prod_id
            FROM materials
            WHERE prod_id = @curr_prod_id
            
            IF @@ROWCOUNT > 0
                SELECT @curr_bom_level = @curr_bom_level + 1
        END
        ELSE
            SELECT @curr_bom_level = @curr_bom_level - 1
    END
    
    SET NOCOUNT OFF
    
    -- finally select the bom listing from the temp table
    SELECT B.bom_level, B.prod_id, P.prod_name, B.bom_qty
    FROM #bom AS B
    INNER JOIN products AS P ON B.prod_id = P.prod_id
    ORDER BY B.seq_nbr

    DROP TABLE #stack
    DROP TABLE #bom

GO

Figure 10   Distributed Authors Report

CREATE Procedure usp_authors_report
AS

    SET NOCOUNT ON

    -- create a temporary table to hold the state names
    CREATE TABLE #statecodes (
        state        CHAR(2)        NOT NULL PRIMARY KEY,
        state_name   VARCHAR(40)
    )

    -- populate the temporary table from the remote server
    INSERT INTO #statecodes
    EXEC myserver.common.dbo.sp_sqlexec 'SELECT * FROM statecodes'

    SET NOCOUNT OFF

    SELECT author = A.au_lname + ', ' + A.au_fname,
        A.address, A.city, state = S.state_name, A.zip
    FROM authors AS A
    INNER JOIN #statecodes AS S ON A.state = S.state
    ORDER BY author

    DROP TABLE #statecodes

GO