CREATE TABLE [dbo].[ConditionQuestions] (
	[TypeId] [int] NOT NULL ,
	[Seq] [int] NOT NULL ,
	[Question] [varchar] (64) NULL ,
	[Weight] [int] NULL ,
	[Responses] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Customers] (
	[CustomerId] [int] NOT NULL ,
	[Name] [varchar] (64) NULL ,
	[Street] [varchar] (64) NULL ,
	[City] [varchar] (64) NULL ,
	[State] [char] (2) NULL ,
	[Zip] [int] NULL ,
	[Phone] [varchar] (32) NULL ,
	[EMailAddress] [varchar] (128) NOT NULL ,
	[DateAdded] [datetime] NULL ,
	[DateUpdated] [datetime] NULL ,
	[MailingList] [bit] NOT NULL ,
	[Comments] [varchar] (256) NULL 
)
GO

CREATE TABLE [dbo].[Images] (
	[InventoryId] [int] NOT NULL ,
	[Seq] [int] NOT NULL ,
	[Image] [image] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[Inventory] (
	[InventoryId] [int] NOT NULL ,
	[ToyTypeId] [int] NULL ,
	[ModelLine] [varchar] (64) NULL ,
	[Name] [varchar] (64) NULL ,
	[ManufacturerId] [int] NULL ,
	[YearIssued] [datetime] NULL ,
	[Description] [varchar] (1024) NULL ,
	[MintValue] [money] NULL ,
	[Condition] [int] NULL ,
	[ConditionMask] [varchar] (64) NULL ,
	[TrueValue] [money] NULL ,
	[DatePurchased] [datetime] NULL ,
	[PurchasePrice] [money] NULL ,
	[AskingPrice] [money] NULL ,
	[BuyingPrice] [money] NULL ,
	[Wanted] [bit] NOT NULL ,
	[ForSale] [bit] NOT NULL ,
	[Comments] [varchar] (256) NULL ,
	[DateUpdated] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Manufacturers] (
	[ManufacturerId] [int] NOT NULL ,
	[Name] [varchar] (64) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[OrderDetails] (
	[OrderId] [int] NOT NULL ,
	[Seq] [int] NOT NULL ,
	[InventoryId] [int] NULL ,
	[PurchasePrice] [money] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Orders] (
	[OrderId] [int] NOT NULL ,
	[CustomerId] [int] NULL ,
	[OrderType] [int] NULL ,
	[ShippingCost] [money] NULL ,
	[SalesTax] [money] NULL ,
	[HowPaid] [int] NULL ,
	[CreditCardNumber] [varchar] (32) NULL ,
	[ExpDate] [varchar] (16) NULL ,
	[OrderStatus] [int] NULL ,
	[DateOrdered] [datetime] NULL ,
	[DateShipped] [datetime] NULL ,
	[DateReceived] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[States] (
	[State] [char] (2) NOT NULL ,
	[StateName] [varchar] (64) NULL ,
	[SalesTaxRate] [decimal](9, 4) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ToyTypes] (
	[TypeId] [int] NOT NULL ,
	[Description] [varchar] (256) NULL 
) ON [PRIMARY]
GO

 CREATE  UNIQUE  CLUSTERED  INDEX [CustomerIndex] ON [dbo].[Customers]([CustomerId]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Images] WITH NOCHECK ADD 
	CONSTRAINT [PK_Images] PRIMARY KEY  NONCLUSTERED 
	(
		[InventoryId],
		[Seq]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Inventory] WITH NOCHECK ADD 
	CONSTRAINT [DF_Inventory_Wanted] DEFAULT (0) FOR [Wanted],
	CONSTRAINT [DF_Inventory_ForSale] DEFAULT (1) FOR [ForSale],
	CONSTRAINT [PK_Inventory] PRIMARY KEY  NONCLUSTERED 
	(
		[InventoryId]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Manufacturers] WITH NOCHECK ADD 
	CONSTRAINT [PK_Manufacturers] PRIMARY KEY  NONCLUSTERED 
	(
		[ManufacturerId]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[OrderDetails] WITH NOCHECK ADD 
	CONSTRAINT [PK_OrderDetails] PRIMARY KEY  NONCLUSTERED 
	(
		[OrderId],
		[Seq]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Orders] WITH NOCHECK ADD 
	CONSTRAINT [PK_Orders] PRIMARY KEY  NONCLUSTERED 
	(
		[OrderId]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[States] WITH NOCHECK ADD 
	CONSTRAINT [PK_States] PRIMARY KEY  NONCLUSTERED 
	(
		[State]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[ToyTypes] WITH NOCHECK ADD 
	CONSTRAINT [PK_Types] PRIMARY KEY  NONCLUSTERED 
	(
		[TypeId]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[ConditionQuestions] ADD 
	CONSTRAINT [FK_ConditionQuestions_Types] FOREIGN KEY 
	(
		[TypeId]
	) REFERENCES [dbo].[ToyTypes] (
		[TypeId]
	)
GO

ALTER TABLE [dbo].[Customers] ADD 
	CONSTRAINT [FK_Customers_States] FOREIGN KEY 
	(
		[State]
	) REFERENCES [dbo].[States] (
		[State]
	)
GO

ALTER TABLE [dbo].[Images] ADD 
	CONSTRAINT [FK_Images_Inventory] FOREIGN KEY 
	(
		[InventoryId]
	) REFERENCES [dbo].[Inventory] (
		[InventoryId]
	)
GO

ALTER TABLE [dbo].[Inventory] ADD 
	CONSTRAINT [FK_Inventory_Manufacturers] FOREIGN KEY 
	(
		[ManufacturerId]
	) REFERENCES [dbo].[Manufacturers] (
		[ManufacturerId]
	),
	CONSTRAINT [FK_Inventory_Types] FOREIGN KEY 
	(
		[ToyTypeId]
	) REFERENCES [dbo].[ToyTypes] (
		[TypeId]
	)
GO

ALTER TABLE [dbo].[OrderDetails] ADD 
	CONSTRAINT [FK_OrderDetails_Inventory] FOREIGN KEY 
	(
		[InventoryId]
	) REFERENCES [dbo].[Inventory] (
		[InventoryId]
	),
	CONSTRAINT [FK_OrderDetails_Orders] FOREIGN KEY 
	(
		[OrderId]
	) REFERENCES [dbo].[Orders] (
		[OrderId]
	)
GO

ALTER TABLE [dbo].[Orders] ADD 
	CONSTRAINT [FK_Orders_Customers] FOREIGN KEY 
	(
		[CustomerId]
	) REFERENCES [dbo].[Customers] (
		[CustomerId]
	)
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

Create View CustomerNames As 
   Select Name, CustomerId 
   From Customers


GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

Create View MdCustomers As 
   Select * 
   From Customers
   Where State = "MD"



GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

SET QUOTED_IDENTIFIER  ON    SET ANSI_NULLS  ON 
GO

CREATE Procedure CountByState (@State Char(2))   As

Select Count(*)
From Customers
Where State = @State
	
Return 

GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

CREATE PROCEDURE GetCustomer (@CustId Int)
AS
Select * 
From Customers
Where CustomerId = @CustId

GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

CREATE PROCEDURE GetCustomerByName (@Name Varchar(64),  @CustId Int)
AS
Select * 
From Customers
Where CustomerId = @CustId or  Name = @Name 



GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

CREATE Procedure TwoStates (@State1 Char(2),  @State2 Char(2) ) As

Select * 
From Customers
Where State = @State1

Select *
From Customers
Where State = @State2

Return

GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

