USE master
go

IF NOT EXISTS
     (SELECT *
      FROM   sysdatabases
      WHERE  name = 'KungFuGifts')
  BEGIN
        CREATE DATABASE [KungFuGifts];
    PRINT 'KungFuGifts Database Created '
  END
Go

USE KungFuGifts
Go

IF EXISTS
     (SELECT name
      FROM   sys.tables
      WHERE  name = 'Customers')
  BEGIN
        DROP TABLE Customers
    PRINT 'Customers Table Dropped'
  END
Go

USE [KungFuGifts];
GO

CREATE TABLE [dbo].[Customers](
  [CustomerID] INT IDENTITY (1, 1) NOT NULL, [Title] VARCHAR(20), [CustFirstName] VARCHAR(20), [CustLastName] VARCHAR(20),
  [ExperienceLevelID] TINYINT NOT NULL)
GO

PRINT 'Customers Table Created'

INSERT INTO KungFuGifts.dbo.Customers(Title, CustFirstName, CustLastName, ExperienceLevelID)
  SELECT 'Mister', 'David', 'Stein', 4
  UNION ALL
  SELECT 'Master', 'Po', NULL, 1
  UNION ALL
  SELECT 'Master', 'Kan', NULL, 1
  UNION ALL
  SELECT 'Sifu', 'Kwai Chang', 'Caine', 2

IF EXISTS
     (SELECT name
      FROM   sys.tables
      WHERE  name = 'Products')
  BEGIN
        DROP TABLE Products
    PRINT 'Products Table Dropped'
  END
Go

CREATE TABLE [dbo].[Products](
  [ProductID]       INT IDENTITY (1, 1) NOT NULL PRIMARY KEY,
  [ProdName]        VARCHAR(25),
  [ProdDescription] VARCHAR(200),
  [ListUnitPrice]   NUMERIC(7, 2) NOT NULL,
  )
GO

INSERT INTO KungFuGifts.dbo.Products(ProdName, ProdDescription, ListUnitPrice)
  SELECT 'Training Ninja Star', 'Ninja Star for Training with blunt tips.', 7.79
  UNION ALL
  SELECT 'Katana Letter Opener', 'Sharp Novelty Katana blade to open your mail.', 10
  UNION ALL
  SELECT 'Kung Fu Snuggie', 'Snuggie looks like meditational robe. Fool Master Po!', 14.99
  UNION ALL
  SELECT 'Kung Fu Bag of Crap', NULL, $9.99

IF EXISTS
     (SELECT name
      FROM   sys.tables
      WHERE  name = 'OrderMaster')
  BEGIN
        DROP TABLE OrderMaster
    PRINT 'OrderMaster Table Dropped'
  END
Go

CREATE TABLE [dbo].[OrderMaster](
  [OrderID]      INT IDENTITY (1, 1) NOT NULL PRIMARY KEY,
  [CustomerID]   INT NOT NULL,
  [OrderDate]    DATETIME NOT NULL,
  [DueDate]      DATETIME NOT NULL,
  [PONo]         VARCHAR(25) NULL,
  [ShipToAddrID] INT NOT NULL,
  )
Go

INSERT INTO KungFuGifts.dbo.OrderMaster(CustomerID, OrderDate, DueDate, PONo, ShipToAddrID)
  SELECT 1, '1/1/2011', '1/1/1900', 'Weapons Purchase 2011', 1
  UNION ALL
  SELECT 1, '1/27/2011', '2/17/2011', 'P123', 1
  UNION ALL
  SELECT 2, '1/1/1900', '1/1/1900', NULL, 2
  UNION ALL
  SELECT 4, '2/1/2011', '3/24/2011', 'Stuff', 3

IF EXISTS
     (SELECT name
      FROM   sys.tables
      WHERE  name = 'OrderDetails')
  BEGIN
        DROP TABLE OrderDetails
    PRINT 'OrderDetails Table Dropped'
  END
Go

CREATE TABLE [dbo].[OrderDetails](
  [OrderID] INT NOT NULL, [LineNo] TINYINT NOT NULL, [ProductID] INT NULL, [ItemDescription] VARCHAR(200), [OrderQty] INT NULL,
  [Discount] TINYINT NULL, [ListPrice] NUMERIC(7, 2) NULL, PRIMARY KEY(OrderID, [LineNo]))
Go

INSERT INTO KungFuGifts.dbo.OrderDetails(OrderID, [LineNo], ProductID, ItemDescription, OrderQty, Discount, ListPrice)
  SELECT 1, 1, 2, 'Make mine red.', 1, 10, 10.00
  UNION ALL
  SELECT 1, 2, 1, NULL, NULL, NULL, NULL
  UNION ALL
  SELECT 1, 3, 3, 'Have Master Po Sign', 1, 10, 10.00
  UNION ALL
  SELECT 4, 1, 1, NULL, 1, 0, 7.99
  UNION ALL
  SELECT 4, 2, 4, NULL, 4, 0, 9.99
Go