mono/mcs/class/System.Data/Test/ProviderTests/System.Data.OleDb.jvm/GHTDB.MSSQL2005.sql

CREATE DATABASE [GHTDB]  ON (NAME = N'GHTDB_dat', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\GHTDB.mdf' , SIZE = 6, FILEGROWTH = 10%) LOG ON (NAME = N'GHTDB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\GHTDB.ldf' , SIZE = 2, FILEGROWTH = 10%)
 COLLATE SQL_Latin1_General_CP1_CI_AS
GO

exec sp_dboption N'GHTDB', N'autoclose', N'false'
GO

exec sp_dboption N'GHTDB', N'bulkcopy', N'true'
GO

exec sp_dboption N'GHTDB', N'trunc. log', N'true'
GO

exec sp_dboption N'GHTDB', N'torn page detection', N'true'
GO

exec sp_dboption N'GHTDB', N'read only', N'false'
GO

exec sp_dboption N'GHTDB', N'dbo use', N'false'
GO

exec sp_dboption N'GHTDB', N'single', N'false'
GO

exec sp_dboption N'GHTDB', N'autoshrink', N'false'
GO

exec sp_dboption N'GHTDB', N'ANSI null default', N'false'
GO

exec sp_dboption N'GHTDB', N'recursive triggers', N'false'
GO

exec sp_dboption N'GHTDB', N'ANSI nulls', N'false'
GO

exec sp_dboption N'GHTDB', N'concat null yields null', N'false'
GO

exec sp_dboption N'GHTDB', N'cursor close on commit', N'false'
GO

exec sp_dboption N'GHTDB', N'default to local cursor', N'false'
GO

exec sp_dboption N'GHTDB', N'quoted identifier', N'false'
GO

exec sp_dboption N'GHTDB', N'ANSI warnings', N'false'
GO

exec sp_dboption N'GHTDB', N'auto create statistics', N'true'
GO

exec sp_dboption N'GHTDB', N'auto update statistics', N'true'
GO

if( ( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion & 0xffff >= 724) ) or ( (@@microsoftversion / power(2, 24) = 7) and (@@microsoftversion & 0xffff >= 1082) ) )
	exec sp_dboption N'GHTDB', N'db chaining', N'false'
GO

use [GHTDB]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Products_Categories]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Products] DROP CONSTRAINT FK_Products_Categories
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_CustomerCustomerDemo]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[CustomerCustomerDemo] DROP CONSTRAINT FK_CustomerCustomerDemo
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_CustomerCustomerDemo_Customers]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[CustomerCustomerDemo] DROP CONSTRAINT FK_CustomerCustomerDemo_Customers
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Orders_Customers]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Orders] DROP CONSTRAINT FK_Orders_Customers
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Employees_Employees]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Employees] DROP CONSTRAINT FK_Employees_Employees
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_EmployeeTerritories_Employees]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[EmployeeTerritories] DROP CONSTRAINT FK_EmployeeTerritories_Employees
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Orders_Employees]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Orders] DROP CONSTRAINT FK_Orders_Employees
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Order_Details_Orders]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Order Details] DROP CONSTRAINT FK_Order_Details_Orders
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Order_Details_Products]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Order Details] DROP CONSTRAINT FK_Order_Details_Products
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Territories_Region]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Territories] DROP CONSTRAINT FK_Territories_Region
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Orders_Shippers]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Orders] DROP CONSTRAINT FK_Orders_Shippers
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Products_Suppliers]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Products] DROP CONSTRAINT FK_Products_Suppliers
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_EmployeeTerritories_Territories]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[EmployeeTerritories] DROP CONSTRAINT FK_EmployeeTerritories_Territories
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CustOrderHist]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CustOrderHist]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CustOrdersDetail]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CustOrdersDetail]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CustOrdersOrders]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CustOrdersOrders]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Employee Sales by Country]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Employee Sales by Country]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GH_CreateTable]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GH_CreateTable]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GH_MultiRecordSets]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GH_MultiRecordSets]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GH_INOUT1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GH_INOUT1]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GH_REFCURSOR1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GH_REFCURSOR1]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GH_REFCURSOR2]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GH_REFCURSOR2]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GH_REFCURSOR3]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GH_REFCURSOR3]
GO


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sales by Year]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sales by Year]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SalesByCategory]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SalesByCategory]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Ten Most Expensive Products]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Ten Most Expensive Products]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Category Sales for 1997]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[Category Sales for 1997]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sales Totals by Amount]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[Sales Totals by Amount]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sales by Category]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[Sales by Category]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Summary of Sales by Quarter]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[Summary of Sales by Quarter]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Summary of Sales by Year]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[Summary of Sales by Year]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Invoices]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[Invoices]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Order Details Extended]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[Order Details Extended]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Order Subtotals]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[Order Subtotals]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Product Sales for 1997]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[Product Sales for 1997]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Alphabetical list of products]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[Alphabetical list of products]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Current Product List]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[Current Product List]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Orders Qry]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[Orders Qry]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Products Above Average Price]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[Products Above Average Price]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Products by Category]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[Products by Category]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Quarterly Orders]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[Quarterly Orders]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Categories]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Categories]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CustomerCustomerDemo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CustomerCustomerDemo]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CustomerDemographics]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CustomerDemographics]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Customers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Customers]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EmployeeTerritories]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[EmployeeTerritories]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Employees]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Employees]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GH_EMPTYTABLE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[GH_EMPTYTABLE]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Order Details]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Order Details]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Orders]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Orders]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Products]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Products]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Region]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Region]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Shippers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Shippers]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Suppliers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Suppliers]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Territories]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Territories]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Types_Simple]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Types_Simple]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Types_Extended]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Types_Extended]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Types_Specific]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Types_Specific]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GHSP_TYPES_SIMPLE_1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GHSP_TYPES_SIMPLE_1]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GHSP_TYPES_SIMPLE_2]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GHSP_TYPES_SIMPLE_2]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GHSP_TYPES_SIMPLE_3]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GHSP_TYPES_SIMPLE_3]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GHSP_TYPES_SIMPLE_4]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GHSP_TYPES_SIMPLE_4]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GHSP_TYPES_SIMPLE_5]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GHSP_TYPES_SIMPLE_5]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE TABLE [dbo].[TYPES_SIMPLE] (
-- ID
	[ID] char(10) NULL,
	[T_BIT] [bit] NULL ,
-- integer
	[T_TINYINT] [tinyint] NULL ,
	[T_SMALLINT] [smallint] NULL ,
	[T_INT] [int] NULL ,
	[T_BIGINT] [bigint] NULL ,
-- float
	[T_DECIMAL] [decimal](18, 0) NULL ,
	[T_NUMERIC] [numeric](18, 0) NULL ,
	[T_FLOAT] [float] NULL ,
	[T_REAL] [real] NULL ,
-- text
	[T_CHAR] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[T_NCHAR] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[T_VARCHAR] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[T_NVARCHAR] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TYPES_EXTENDED] (
-- ID
	[ID] char(10) NULL,
-- Text
	[T_TEXT] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[T_NTEXT] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
-- Binary
	[T_BINARY] [binary] (50) NULL ,
	[T_VARBINARY] [varbinary] (50) NULL ,
--Time
	[T_DATETIME] [datetime] NULL ,
	[T_SMALLDATETIME] [smalldatetime] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE TABLE [dbo].[TYPES_SPECIFIC] (
-- ID
	[ID] char(10) NULL,
	[T_SMALLMONEY] [smallmoney] NULL ,
	[T_MONEY] [money] NULL ,
	[T_IMAGE] [image] NULL ,
	[T_UNIQUEIDENTIFIER] [uniqueidentifier] NULL ,
	[T_SQL_VARIANT] [sql_variant] NULL ,
	[T_TIMESTAMP] [timestamp] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[Categories] (
	[CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
	[CategoryName] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Description] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Picture] [image] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[CustomerCustomerDemo] (
	[CustomerID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[CustomerTypeID] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[CustomerDemographics] (
	[CustomerTypeID] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[CustomerDesc] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[Customers] (
	[CustomerID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ContactName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ContactTitle] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Fax] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[EmployeeTerritories] (
	[EmployeeID] [int] NOT NULL ,
	[TerritoryID] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Employees] (
	[EmployeeID] [int] NOT NULL ,
	[LastName] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[FirstName] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Title] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TitleOfCourtesy] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[BirthDate] [datetime] NULL ,
	[HireDate] [datetime] NULL ,
	[Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[HomePhone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Extension] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Photo] [image] NULL ,
	[Notes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ReportsTo] [int] NULL ,
	[PhotoPath] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[GH_EMPTYTABLE] (
	[Col1] [int] NULL ,
	[Col2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Order Details] (
	[OrderID] [int] NOT NULL ,
	[ProductID] [int] NOT NULL ,
	[UnitPrice] [money] NOT NULL ,
	[Quantity] [smallint] NOT NULL ,
	[Discount] [real] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Orders] (
	[OrderID] [int] IDENTITY (1, 1) NOT NULL ,
	[CustomerID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[EmployeeID] [int] NULL ,
	[OrderDate] [datetime] NULL ,
	[RequiredDate] [datetime] NULL ,
	[ShippedDate] [datetime] NULL ,
	[ShipVia] [int] NULL ,
	[Freight] [money] NULL ,
	[ShipName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ShipAddress] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ShipCity] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ShipRegion] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ShipPostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ShipCountry] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Products] (
	[ProductID] [int] IDENTITY (1, 1) NOT NULL ,
	[ProductName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[SupplierID] [int] NULL ,
	[CategoryID] [int] NULL ,
	[QuantityPerUnit] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[UnitPrice] [money] NULL ,
	[UnitsInStock] [smallint] NULL ,
	[UnitsOnOrder] [smallint] NULL ,
	[ReorderLevel] [smallint] NULL ,
	[Discontinued] [bit] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Region] (
	[RegionID] [int] NOT NULL ,
	[RegionDescription] [nchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Shippers] (
	[ShipperID] [int] IDENTITY (1, 1) NOT NULL ,
	[CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Suppliers] (
	[SupplierID] [int] IDENTITY (1, 1) NOT NULL ,
	[CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ContactName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ContactTitle] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Fax] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[HomePage] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[Territories] (
	[TerritoryID] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[TerritoryDescription] [nchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[RegionID] [int] NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Categories] WITH NOCHECK ADD 
	CONSTRAINT [PK_Categories] PRIMARY KEY  CLUSTERED 
	(
		[CategoryID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Customers] WITH NOCHECK ADD 
	CONSTRAINT [PK_Customers] PRIMARY KEY  CLUSTERED 
	(
		[CustomerID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Employees] WITH NOCHECK ADD 
	CONSTRAINT [PK_Employees] PRIMARY KEY  CLUSTERED 
	(
		[EmployeeID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Order Details] WITH NOCHECK ADD 
	CONSTRAINT [PK_Order_Details] PRIMARY KEY  CLUSTERED 
	(
		[OrderID],
		[ProductID]
	)  ON [PRIMARY] 
GO

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

ALTER TABLE [dbo].[Products] WITH NOCHECK ADD 
	CONSTRAINT [PK_Products] PRIMARY KEY  CLUSTERED 
	(
		[ProductID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Shippers] WITH NOCHECK ADD 
	CONSTRAINT [PK_Shippers] PRIMARY KEY  CLUSTERED 
	(
		[ShipperID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Suppliers] WITH NOCHECK ADD 
	CONSTRAINT [PK_Suppliers] PRIMARY KEY  CLUSTERED 
	(
		[SupplierID]
	)  ON [PRIMARY] 
GO

 CREATE  INDEX [CategoryName] ON [dbo].[Categories]([CategoryName]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[CustomerCustomerDemo] ADD 
	CONSTRAINT [PK_CustomerCustomerDemo] PRIMARY KEY  NONCLUSTERED 
	(
		[CustomerID],
		[CustomerTypeID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[CustomerDemographics] ADD 
	CONSTRAINT [PK_CustomerDemographics] PRIMARY KEY  NONCLUSTERED 
	(
		[CustomerTypeID]
	)  ON [PRIMARY] 
GO

 CREATE  INDEX [City] ON [dbo].[Customers]([City]) ON [PRIMARY]
GO

 CREATE  INDEX [CompanyName] ON [dbo].[Customers]([CompanyName]) ON [PRIMARY]
GO

 CREATE  INDEX [PostalCode] ON [dbo].[Customers]([PostalCode]) ON [PRIMARY]
GO

 CREATE  INDEX [Region] ON [dbo].[Customers]([Region]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[EmployeeTerritories] ADD 
	CONSTRAINT [PK_EmployeeTerritories] PRIMARY KEY  NONCLUSTERED 
	(
		[EmployeeID],
		[TerritoryID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Employees] ADD 
	CONSTRAINT [CK_Birthdate] CHECK ([BirthDate] < getdate())
GO

 CREATE  INDEX [LastName] ON [dbo].[Employees]([LastName]) ON [PRIMARY]
GO

 CREATE  INDEX [PostalCode] ON [dbo].[Employees]([PostalCode]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Order Details] ADD 
	CONSTRAINT [DF_Order_Details_UnitPrice] DEFAULT (0) FOR [UnitPrice],
	CONSTRAINT [DF_Order_Details_Quantity] DEFAULT (1) FOR [Quantity],
	CONSTRAINT [DF_Order_Details_Discount] DEFAULT (0) FOR [Discount],
	CONSTRAINT [CK_Discount] CHECK ([Discount] >= 0 and [Discount] <= 1),
	CONSTRAINT [CK_Quantity] CHECK ([Quantity] > 0),
	CONSTRAINT [CK_UnitPrice] CHECK ([UnitPrice] >= 0)
GO

 CREATE  INDEX [OrderID] ON [dbo].[Order Details]([OrderID]) ON [PRIMARY]
GO

 CREATE  INDEX [OrdersOrder_Details] ON [dbo].[Order Details]([OrderID]) ON [PRIMARY]
GO

 CREATE  INDEX [ProductID] ON [dbo].[Order Details]([ProductID]) ON [PRIMARY]
GO

 CREATE  INDEX [ProductsOrder_Details] ON [dbo].[Order Details]([ProductID]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Orders] ADD 
	CONSTRAINT [DF_Orders_Freight] DEFAULT (0) FOR [Freight]
GO

 CREATE  INDEX [CustomerID] ON [dbo].[Orders]([CustomerID]) ON [PRIMARY]
GO

 CREATE  INDEX [CustomersOrders] ON [dbo].[Orders]([CustomerID]) ON [PRIMARY]
GO

 CREATE  INDEX [EmployeeID] ON [dbo].[Orders]([EmployeeID]) ON [PRIMARY]
GO

 CREATE  INDEX [EmployeesOrders] ON [dbo].[Orders]([EmployeeID]) ON [PRIMARY]
GO

 CREATE  INDEX [OrderDate] ON [dbo].[Orders]([OrderDate]) ON [PRIMARY]
GO

 CREATE  INDEX [ShippedDate] ON [dbo].[Orders]([ShippedDate]) ON [PRIMARY]
GO

 CREATE  INDEX [ShippersOrders] ON [dbo].[Orders]([ShipVia]) ON [PRIMARY]
GO

 CREATE  INDEX [ShipPostalCode] ON [dbo].[Orders]([ShipPostalCode]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Products] ADD 
	CONSTRAINT [DF_Products_UnitPrice] DEFAULT (0) FOR [UnitPrice],
	CONSTRAINT [DF_Products_UnitsInStock] DEFAULT (0) FOR [UnitsInStock],
	CONSTRAINT [DF_Products_UnitsOnOrder] DEFAULT (0) FOR [UnitsOnOrder],
	CONSTRAINT [DF_Products_ReorderLevel] DEFAULT (0) FOR [ReorderLevel],
	CONSTRAINT [DF_Products_Discontinued] DEFAULT (0) FOR [Discontinued],
	CONSTRAINT [CK_Products_UnitPrice] CHECK ([UnitPrice] >= 0),
	CONSTRAINT [CK_ReorderLevel] CHECK ([ReorderLevel] >= 0),
	CONSTRAINT [CK_UnitsInStock] CHECK ([UnitsInStock] >= 0),
	CONSTRAINT [CK_UnitsOnOrder] CHECK ([UnitsOnOrder] >= 0)
GO

 CREATE  INDEX [CategoriesProducts] ON [dbo].[Products]([CategoryID]) ON [PRIMARY]
GO

 CREATE  INDEX [CategoryID] ON [dbo].[Products]([CategoryID]) ON [PRIMARY]
GO

 CREATE  INDEX [ProductName] ON [dbo].[Products]([ProductName]) ON [PRIMARY]
GO

 CREATE  INDEX [SupplierID] ON [dbo].[Products]([SupplierID]) ON [PRIMARY]
GO

 CREATE  INDEX [SuppliersProducts] ON [dbo].[Products]([SupplierID]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Region] ADD 
	CONSTRAINT [PK_Region] PRIMARY KEY  NONCLUSTERED 
	(
		[RegionID]
	)  ON [PRIMARY] 
GO

 CREATE  INDEX [CompanyName] ON [dbo].[Suppliers]([CompanyName]) ON [PRIMARY]
GO

 CREATE  INDEX [PostalCode] ON [dbo].[Suppliers]([PostalCode]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Territories] ADD 
	CONSTRAINT [PK_Territories] PRIMARY KEY  NONCLUSTERED 
	(
		[TerritoryID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[CustomerCustomerDemo] ADD 
	CONSTRAINT [FK_CustomerCustomerDemo] FOREIGN KEY 
	(
		[CustomerTypeID]
	) REFERENCES [dbo].[CustomerDemographics] (
		[CustomerTypeID]
	),
	CONSTRAINT [FK_CustomerCustomerDemo_Customers] FOREIGN KEY 
	(
		[CustomerID]
	) REFERENCES [dbo].[Customers] (
		[CustomerID]
	)
GO

ALTER TABLE [dbo].[EmployeeTerritories] ADD 
	CONSTRAINT [FK_EmployeeTerritories_Employees] FOREIGN KEY 
	(
		[EmployeeID]
	) REFERENCES [dbo].[Employees] (
		[EmployeeID]
	),
	CONSTRAINT [FK_EmployeeTerritories_Territories] FOREIGN KEY 
	(
		[TerritoryID]
	) REFERENCES [dbo].[Territories] (
		[TerritoryID]
	)
GO

ALTER TABLE [dbo].[Employees] ADD 
	CONSTRAINT [FK_Employees_Employees] FOREIGN KEY 
	(
		[ReportsTo]
	) REFERENCES [dbo].[Employees] (
		[EmployeeID]
	)
GO

ALTER TABLE [dbo].[Order Details] ADD 
	CONSTRAINT [FK_Order_Details_Orders] FOREIGN KEY 
	(
		[OrderID]
	) REFERENCES [dbo].[Orders] (
		[OrderID]
	),
	CONSTRAINT [FK_Order_Details_Products] FOREIGN KEY 
	(
		[ProductID]
	) REFERENCES [dbo].[Products] (
		[ProductID]
	)
GO

ALTER TABLE [dbo].[Orders] ADD 
	CONSTRAINT [FK_Orders_Customers] FOREIGN KEY 
	(
		[CustomerID]
	) REFERENCES [dbo].[Customers] (
		[CustomerID]
	),
	CONSTRAINT [FK_Orders_Employees] FOREIGN KEY 
	(
		[EmployeeID]
	) REFERENCES [dbo].[Employees] (
		[EmployeeID]
	),
	CONSTRAINT [FK_Orders_Shippers] FOREIGN KEY 
	(
		[ShipVia]
	) REFERENCES [dbo].[Shippers] (
		[ShipperID]
	)
GO

ALTER TABLE [dbo].[Products] ADD 
	CONSTRAINT [FK_Products_Categories] FOREIGN KEY 
	(
		[CategoryID]
	) REFERENCES [dbo].[Categories] (
		[CategoryID]
	),
	CONSTRAINT [FK_Products_Suppliers] FOREIGN KEY 
	(
		[SupplierID]
	) REFERENCES [dbo].[Suppliers] (
		[SupplierID]
	)
GO

ALTER TABLE [dbo].[Territories] ADD 
	CONSTRAINT [FK_Territories_Region] FOREIGN KEY 
	(
		[RegionID]
	) REFERENCES [dbo].[Region] (
		[RegionID]
	)
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create view "Current Product List" AS
SELECT Product_List.ProductID, Product_List.ProductName
FROM Products AS Product_List
WHERE (((Product_List.Discontinued)=0))
--ORDER BY Product_List.ProductName

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create view "Orders Qry" AS
SELECT Orders.OrderID, Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate, Orders.RequiredDate, 
	Orders.ShippedDate, Orders.ShipVia, Orders.Freight, Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, 
	Orders.ShipRegion, Orders.ShipPostalCode, Orders.ShipCountry, 
	Customers.CompanyName, Customers.Address, Customers.City, Customers.Region, Customers.PostalCode, Customers.Country
FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create view "Products Above Average Price" AS
SELECT Products.ProductName, Products.UnitPrice
FROM Products
WHERE Products.UnitPrice>(SELECT AVG(UnitPrice) From Products)
--ORDER BY Products.UnitPrice DESC

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create view "Products by Category" AS
SELECT Categories.CategoryName, Products.ProductName, Products.QuantityPerUnit, Products.UnitsInStock, Products.Discontinued
FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
WHERE Products.Discontinued <> 1
--ORDER BY Categories.CategoryName, Products.ProductName

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create view "Quarterly Orders" AS
SELECT DISTINCT Customers.CustomerID, Customers.CompanyName, Customers.City, Customers.Country
FROM Customers RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE Orders.OrderDate BETWEEN '19970101' And '19971231'

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create view Invoices AS
SELECT Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode, 
	Orders.ShipCountry, Orders.CustomerID, Customers.CompanyName AS CustomerName, Customers.Address, Customers.City, 
	Customers.Region, Customers.PostalCode, Customers.Country, 
	(FirstName + ' ' + LastName) AS Salesperson, 
	Orders.OrderID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Shippers.CompanyName As ShipperName, 
	"Order Details".ProductID, Products.ProductName, "Order Details".UnitPrice, "Order Details".Quantity, 
	"Order Details".Discount, 
	(CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100) AS ExtendedPrice, Orders.Freight
FROM 	Shippers INNER JOIN 
		(Products INNER JOIN 
			(
				(Employees INNER JOIN 
					(Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) 
				ON Employees.EmployeeID = Orders.EmployeeID) 
			INNER JOIN "Order Details" ON Orders.OrderID = "Order Details".OrderID) 
		ON Products.ProductID = "Order Details".ProductID) 
	ON Shippers.ShipperID = Orders.ShipVia

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create view "Order Details Extended" AS
SELECT "Order Details".OrderID, "Order Details".ProductID, Products.ProductName, 
	"Order Details".UnitPrice, "Order Details".Quantity, "Order Details".Discount, 
	(CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100) AS ExtendedPrice
FROM Products INNER JOIN "Order Details" ON Products.ProductID = "Order Details".ProductID
--ORDER BY "Order Details".OrderID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create view "Order Subtotals" AS
SELECT "Order Details".OrderID, Sum(CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100) AS Subtotal
FROM "Order Details"
GROUP BY "Order Details".OrderID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create view "Product Sales for 1997" AS
SELECT Categories.CategoryName, Products.ProductName, 
Sum(CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100) AS ProductSales
FROM (Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID) 
	INNER JOIN (Orders 
		INNER JOIN "Order Details" ON Orders.OrderID = "Order Details".OrderID) 
	ON Products.ProductID = "Order Details".ProductID
WHERE (((Orders.ShippedDate) Between '19970101' And '19971231'))
GROUP BY Categories.CategoryName, Products.ProductName

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create view "Category Sales for 1997" AS
SELECT "Product Sales for 1997".CategoryName, Sum("Product Sales for 1997".ProductSales) AS CategorySales
FROM "Product Sales for 1997"
GROUP BY "Product Sales for 1997".CategoryName

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create view "Sales Totals by Amount" AS
SELECT "Order Subtotals".Subtotal AS SaleAmount, Orders.OrderID, Customers.CompanyName, Orders.ShippedDate
FROM 	Customers INNER JOIN 
		(Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID) 
	ON Customers.CustomerID = Orders.CustomerID
WHERE ("Order Subtotals".Subtotal >2500) AND (Orders.ShippedDate BETWEEN '19970101' And '19971231')

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create view "Sales by Category" AS
SELECT Categories.CategoryID, Categories.CategoryName, Products.ProductName, 
	Sum("Order Details Extended".ExtendedPrice) AS ProductSales
FROM 	Categories INNER JOIN 
		(Products INNER JOIN 
			(Orders INNER JOIN "Order Details Extended" ON Orders.OrderID = "Order Details Extended".OrderID) 
		ON Products.ProductID = "Order Details Extended".ProductID) 
	ON Categories.CategoryID = Products.CategoryID
WHERE Orders.OrderDate BETWEEN '19970101' And '19971231'
GROUP BY Categories.CategoryID, Categories.CategoryName, Products.ProductName
--ORDER BY Products.ProductName

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create view "Summary of Sales by Quarter" AS
SELECT Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal
FROM Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID
WHERE Orders.ShippedDate IS NOT NULL
--ORDER BY Orders.ShippedDate

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create view "Summary of Sales by Year" AS
SELECT Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal
FROM Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID
WHERE Orders.ShippedDate IS NOT NULL
--ORDER BY Orders.ShippedDate

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE CustOrderHist @CustomerID nchar(5)
AS
SELECT ProductName, Total=SUM(Quantity)
FROM Products P, [Order Details] OD, Orders O, Customers C
WHERE C.CustomerID = @CustomerID
AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID
GROUP BY ProductName

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE CustOrdersDetail @OrderID int
AS
SELECT ProductName,
    UnitPrice=ROUND(Od.UnitPrice, 2),
    Quantity,
    Discount=CONVERT(int, Discount * 100), 
    ExtendedPrice=ROUND(CONVERT(money, Quantity * (1 - Discount) * Od.UnitPrice), 2)
FROM Products P, [Order Details] Od
WHERE Od.ProductID = P.ProductID and Od.OrderID = @OrderID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE CustOrdersOrders @CustomerID nchar(5)
AS
SELECT OrderID, 
	OrderDate,
	RequiredDate,
	ShippedDate
FROM Orders
WHERE CustomerID = @CustomerID
ORDER BY OrderID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create procedure "Employee Sales by Country" 
@Beginning_Date DateTime, @Ending_Date DateTime AS
SELECT Employees.Country, Employees.LastName, Employees.FirstName, Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal AS SaleAmount
FROM Employees INNER JOIN 
	(Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID) 
	ON Employees.EmployeeID = Orders.EmployeeID
WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE GH_CREATETABLE
AS
Begin
    --craete a temporary table
     Create Table #temp_tbl (
                            Col1 int,
                            Col2 int
                            )
          --insert values to the table
         insert into #temp_tbl values (11,12)
         insert into #temp_tbl values (21,22)
         insert into #temp_tbl values (31,32)
         --execute select on the created table
         select col1 as Value1, col2 as Value2 from #temp_tbl;
         --Update Return code
         end
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE GH_MultiRecordSets 
as BEGIN
    -- Declare cursor
          SELECT EmployeeId, LastName FROM Employees where EmployeeId in (1,2) order by EmployeeId asc;
          SELECT CustomerId, CompanyName,ContactName FROM Customers  where CustomerId in ('MORGK','NORTS') order by customerid asc;
            -- return empty result set
          SELECT OrderId, ShipAddress,ShipVia, ShipCity FROM Orders where OrderId=-1  ;
END
GO

CREATE procedure GH_INOUT1
@INPARAM varchar(20) ,
@OUTPARAM  int output
AS
declare @L_INPARAM varchar(30) 
select L_INPARAM = @INPARAM
select @OUTPARAM = 100
GO


CREATE procedure GH_REFCURSOR1
AS
SELECT EmployeeId, LastName FROM Employees
WHERE EmployeeId=1;
GO

CREATE procedure GH_REFCURSOR2
@IN_EMPLOYEEID int
AS
SELECT EmployeeId, LastName FROM Employees 
where EmployeeId = @IN_EMPLOYEEID
GO


CREATE procedure GH_REFCURSOR3
@IN_LASTNAME varchar(20) AS
SELECT EmployeeId, LastName FROM Employees  
where LastName = @IN_LASTNAME
GO



SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create procedure "Sales by Year" 
	@Beginning_Date DateTime, @Ending_Date DateTime AS
SELECT Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal, DATENAME(yy,ShippedDate) AS Year
FROM Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID
WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE SalesByCategory
    @CategoryName nvarchar(15), @OrdYear nvarchar(4) = '1998'
AS
IF @OrdYear != '1996' AND @OrdYear != '1997' AND @OrdYear != '1998' 
BEGIN
	SELECT @OrdYear = '1998'
END
SELECT ProductName,
	TotalPurchase=ROUND(SUM(CONVERT(decimal(14,2), OD.Quantity * (1-OD.Discount) * OD.UnitPrice)), 0)
FROM [Order Details] OD, Orders O, Products P, Categories C
WHERE OD.OrderID = O.OrderID 
	AND OD.ProductID = P.ProductID 
	AND P.CategoryID = C.CategoryID
	AND C.CategoryName = @CategoryName
	AND SUBSTRING(CONVERT(nvarchar(22), O.OrderDate, 111), 1, 4) = @OrdYear
GROUP BY ProductName
ORDER BY ProductName

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create procedure "Ten Most Expensive Products" AS
SET ROWCOUNT 10
SELECT Products.ProductName AS TenMostExpensiveProducts, Products.UnitPrice
FROM Products
ORDER BY Products.UnitPrice DESC

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE GHSP_TYPES_SIMPLE_1
@T_BIT bit ,
@T_TINYINT tinyint,
@T_SMALLINT smallint ,
@T_INT int,
@T_BIGINT bigint,
@T_DECIMAL decimal(18, 0),
@T_NUMERIC numeric(18, 0) ,
@T_FLOAT float  ,
@T_REAL  real  ,
@T_CHAR char (10),
@T_NCHAR nchar (10),
@T_VARCHAR varchar (50) ,
@T_NVARCHAR nvarchar (50)
 AS
SELECT @T_BIT as 'T_BIT', @T_TINYINT as 'T_TINYINT', @T_SMALLINT as 'T_SMALLINT' , @T_INT as 'T_INT', @T_BIGINT as 'T_BIGINT', @T_DECIMAL as 'T_DECIMAL',
@T_NUMERIC as 'T_NUMERIC' , @T_FLOAT as 'T_FLOAT'  , @T_REAL  as 'T_REAL'  , @T_CHAR as 'T_CHAR', @T_NCHAR as 'T_NCHAR', @T_VARCHAR as 'T_VARCHAR' , @T_NVARCHAR as 'T_NVARCHAR'
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE GHSP_TYPES_SIMPLE_2
@T_BIT bit output,
@T_TINYINT tinyint output,
@T_SMALLINT smallint output,
@T_INT int output,
@T_BIGINT bigint output,
@T_DECIMAL decimal(18, 0) output,
@T_NUMERIC numeric(18, 0)  output,
@T_FLOAT float output,
@T_REAL  real output,
@T_CHAR char (10) output,
@T_NCHAR nchar (10) output,
@T_VARCHAR varchar (50) output,
@T_NVARCHAR nvarchar (50) output
 AS
IF @T_BIT=0
	SELECT @T_BIT=1
ELSE IF (@T_BIT=1)
	SELECT @T_BIT=0
SELECT @T_TINYINT = @T_TINYINT*2
SELECT @T_SMALLINT = @T_SMALLINT*2 
SELECT @T_INT = @T_INT*2
SELECT @T_BIGINT = @T_BIGINT*2
SELECT @T_DECIMAL = @T_DECIMAL*2
SELECT @T_NUMERIC = @T_NUMERIC*2 
SELECT @T_FLOAT = @T_FLOAT*2  
SELECT @T_REAL = @T_REAL*2  
SELECT @T_CHAR = UPPER(@T_CHAR)
SELECT @T_NCHAR =UPPER(@T_NCHAR)
SELECT @T_VARCHAR = UPPER(@T_VARCHAR)
SELECT @T_NVARCHAR = UPPER(@T_NVARCHAR)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE GHSP_TYPES_SIMPLE_3
@ID char,
@T_BIT bit output,
@T_TINYINT tinyint output,
@T_SMALLINT smallint output,
@T_INT int output,
@T_BIGINT bigint output,
@T_DECIMAL decimal(18, 0) output,
@T_NUMERIC numeric(18, 0)  output,
@T_FLOAT float output,
@T_REAL  real output,
@T_CHAR char (10) output,
@T_NCHAR nchar (10) output,
@T_VARCHAR varchar (50) output,
@T_NVARCHAR nvarchar (50) output
AS
SELECT @T_BIT = T_BIT, @T_TINYINT = T_TINYINT, @T_SMALLINT = T_SMALLINT , @T_INT = T_INT, @T_BIGINT = T_BIGINT, @T_DECIMAL = T_DECIMAL ,
@T_NUMERIC = T_NUMERIC , @T_FLOAT = T_FLOAT  , @T_REAL = T_REAL  , @T_CHAR = T_CHAR, @T_NCHAR = T_NCHAR, 
@T_VARCHAR = T_VARCHAR, @T_NVARCHAR = T_NVARCHAR FROM TYPES_SIMPLE WHERE ID = @ID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE GHSP_TYPES_SIMPLE_4
@ID char
AS
/*Insert*/
insert into TYPES_SIMPLE(ID,T_INT) values (@ID,50)
SELECT * FROM TYPES_SIMPLE WHERE ID = @ID
/*Update*/
update TYPES_SIMPLE set T_INT=60 where Id = @ID
SELECT * FROM TYPES_SIMPLE WHERE ID = @ID
/*Delete*/
delete from TYPES_SIMPLE WHERE ID = @ID
SELECT * FROM TYPES_SIMPLE WHERE ID = @ID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE GHSP_TYPES_SIMPLE_5
AS
DECLARE @T_BIT bit
DECLARE @T_TINYINT tinyint
DECLARE @T_SMALLINT smallint
DECLARE @T_INT int
DECLARE @T_BIGINT bigint
DECLARE @T_DECIMAL decimal(18,0)
DECLARE @T_NUMERIC numeric(18,0)
DECLARE @T_FLOAT float
DECLARE @T_REAL real
DECLARE @T_CHAR char(10)
DECLARE @T_NCHAR nchar(10)
DECLARE @T_VARCHAR varchar(50)
DECLARE @T_NVARCHAR nvarchar(50)

SELECT @T_BIT = 1
SELECT @T_TINYINT = 25
SELECT @T_SMALLINT = 77
SELECT @T_INT = 2525
SELECT @T_BIGINT = 25251414
SELECT @T_DECIMAL = 10
SELECT @T_NUMERIC = 123123
SELECT @T_FLOAT = 17.1414257
SELECT @T_REAL = 0.71425
SELECT @T_CHAR = 'abcdefghij'
SELECT @T_NCHAR = N'klmnopqrst'
SELECT @T_VARCHAR = 'qwertasdfg'
SELECT @T_NVARCHAR = N'qwertasdfg'
 
SELECT @T_BIT as 'T_BIT', @T_TINYINT as 'T_TINYINT', @T_SMALLINT as 'T_SMALLINT' , @T_INT as 'T_INT', @T_BIGINT as 'T_BIGINT', @T_DECIMAL as 'T_DECIMAL', @T_NUMERIC as 'T_NUMERIC' , @T_FLOAT as 'T_FLOAT'  , @T_REAL  as 'T_REAL'  , @T_CHAR as 'T_CHAR', @T_NCHAR as 'T_NCHAR', @T_VARCHAR as 'T_VARCHAR' , @T_NVARCHAR as 'T_NVARCHAR'

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


if not exists (select * from master.dbo.syslogins where loginname = N'mainsoft')
BEGIN
	declare @logindb nvarchar(132), @loginlang nvarchar(132) select @logindb = N'GHTDB', @loginlang = N'us_english'
	if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
		select @logindb = N'master'
	if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N'us_english')
		select @loginlang = @@language
	exec sp_addlogin N'mainsoft', N'Sql123', @logindb, @loginlang
END
GO

exec sp_addsrvrolemember N'mainsoft', sysadmin
GO


if not exists (select * from dbo.sysusers where name = N'mainsoft' and uid < 16382)
	EXEC sp_grantdbaccess N'mainsoft', N'mainsoft'
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[mainsoft].[CategoriesNew]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [mainsoft].[CategoriesNew]
GO

CREATE TABLE [mainsoft].[CategoriesNew] (
	[CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
	[CategoryName] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Description] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Picture] [image] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [mainsoft].[Categories] (
	[CategoryID] [nvarchar] (15) NOT NULL ,
	[CategoryName] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Description] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Picture] [int] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE  procedure [mainsoft].[GH_DUMMY]
@EmployeeIdPrm  char (10)
AS
SELECT * FROM Employees where EmployeeID > @EmployeeIdPrm
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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


CREATE DATABASE [GHTDB_EX]  ON (NAME = N'GHTDB_dat_EX', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\GHTDB_EX.mdf' , SIZE = 6, FILEGROWTH = 10%) LOG ON (NAME = N'GHTDB_EX_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\GHTDB_EX.ldf' , SIZE = 2, FILEGROWTH = 10%)
 COLLATE SQL_Latin1_General_CP1_CI_AS
GO

exec sp_dboption N'GHTDB_EX', N'autoclose', N'false'
GO

exec sp_dboption N'GHTDB_EX', N'bulkcopy', N'true'
GO

exec sp_dboption N'GHTDB_EX', N'trunc. log', N'true'
GO

exec sp_dboption N'GHTDB_EX', N'torn page detection', N'true'
GO

exec sp_dboption N'GHTDB_EX', N'read only', N'false'
GO

exec sp_dboption N'GHTDB_EX', N'dbo use', N'false'
GO

exec sp_dboption N'GHTDB_EX', N'single', N'false'
GO

exec sp_dboption N'GHTDB_EX', N'autoshrink', N'false'
GO

exec sp_dboption N'GHTDB_EX', N'ANSI null default', N'false'
GO

exec sp_dboption N'GHTDB_EX', N'recursive triggers', N'false'
GO

exec sp_dboption N'GHTDB_EX', N'ANSI nulls', N'false'
GO

exec sp_dboption N'GHTDB_EX', N'concat null yields null', N'false'
GO

exec sp_dboption N'GHTDB_EX', N'cursor close on commit', N'false'
GO

exec sp_dboption N'GHTDB_EX', N'default to local cursor', N'false'
GO

exec sp_dboption N'GHTDB_EX', N'quoted identifier', N'false'
GO

exec sp_dboption N'GHTDB_EX', N'ANSI warnings', N'false'
GO

exec sp_dboption N'GHTDB_EX', N'auto create statistics', N'true'
GO

exec sp_dboption N'GHTDB_EX', N'auto update statistics', N'true'
GO

if( ( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion & 0xffff >= 724) ) or ( (@@microsoftversion / power(2, 24) = 7) and (@@microsoftversion & 0xffff >= 1082) ) )
	exec sp_dboption N'GHTDB_EX', N'db chaining', N'false'
GO

use [GHTDB_EX]
GO


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GH_DUMMY]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GH_DUMMY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Customers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Customers]
GO

CREATE TABLE [dbo].[Customers] (
	[CustomerID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ContactName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ContactTitle] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Fax] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

print '------------------------------'
print 'create another GH_DUMMY which select from a different table'
print 'customers instead of employees'
print '------------------------------'
go

CREATE  procedure GH_DUMMY
@CustomerIdPrm  char (10)
AS
SELECT * FROM Customers where CustomerID = @CustomerIdPrm
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO