Northwind database

Database server: server1
Database size: 4.25 MB
Documentation date: 30/03/2009

Tables (14)	Views (16 )	Stored procedures (7)
dbo.Categories dbo.CustomerCustomerDemo dbo.CustomerDemographics dbo.Customers dbo.Employees dbo.EmployeeTerritories dbo.Order Details dbo.Orders dbo.Products dbo.Region dbo.Shippers dbo.Suppliers dbo.Territories dbo.TestTable	dbo.Alphabetical list of products dbo.Category Sales for 1997 dbo.Current Product List dbo.Customer and Suppliers by City dbo.Invoices dbo.Order Details Extended dbo.Order Subtotals dbo.Orders Qry dbo.Product Sales for 1997 dbo.Products Above Average Price dbo.Products by Category dbo.Quarterly Orders dbo.Sales by Category dbo.Sales Totals by Amount dbo.Summary of Sales by Quarter dbo.Summary of Sales by Year	dbo.CustOrderHist dbo.CustOrdersDetail dbo.CustOrdersOrders dbo.Employee Sales by Country dbo.Sales by Year dbo.SalesByCategory dbo.Ten Most Expensive Products

User defined data types (1)	Users (2)	Roles (10)
TestType	dbo guest	db_accessadmin db_backupoperator db_datareader db_datawriter db_ddladmin db_denydatareader db_denydatawriter db_owner db_securityadmin public

(up)

Table: dbo.Categories

List of categories Second line

Field name

Data type

Nullable

Default value

Description

PK	CategoryID	int		Categories of Northwind products.
IX	CategoryName	nvarchar (15)		Name of food category.
	Description	ntext	Yes	Full description of the category. Provide samples.
	Picture	image	Yes	A picture representing the food category.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
CategoryName	CategoryName	ASC		NONCLUSTERED
PK_Categories	CategoryID	ASC	Yes	CLUSTERED

Object permissions

User / Role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI
public	grant	grant	grant	grant		grant

Extended properties

Author	John Smith
CustomProperty1	Sample value to test extended properties

Objects that depend on dbo.Categories

dbo.Alphabetical list of products
dbo.Product Sales for 1997
dbo.Products by Category
dbo.Sales by Category
dbo.SalesByCategory

Table definition

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

(up)

Table: dbo.CustomerCustomerDemo

Test description

Field name

Data type

Nullable

Default value

Description

PK FK	CustomerID	nchar (5)			234
PK FK	CustomerTypeID	nchar (10)			234

Foreign keys dependencies:

FK_CustomerCustomerDemo_Customers: CustomerID relies upon remote PK_Customers (dbo.Customers)
FK_CustomerCustomerDemo: CustomerTypeID relies upon remote PK_CustomerDemographics (dbo.CustomerDemographics)

Indexes

Index name	Column name	Sort direction	Is unique	Index type
PK_CustomerCustomerDemo	CustomerID	ASC	Yes	NONCLUSTERED
PK_CustomerCustomerDemo	CustomerTypeID	ASC	Yes	NONCLUSTERED

Object permissions

User / Role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI
public	grant	grant	grant	grant		grant

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[CustomerCustomerDemo](
    [CustomerID] [nchar](5) COLLATE Modern_Spanish_CI_AS NOT NULL,
    [CustomerTypeID] [nchar](10) COLLATE Modern_Spanish_CI_AS NOT NULL
) ON [PRIMARY]

(up)

Table: dbo.CustomerDemographics

Sample annotation for table CustomerDemographics

Field name

Data type

Nullable

Default value

Description

PK	CustomerTypeID	nchar (10)			123465
	CustomerDesc	ntext	Yes		sdfg

Indexes

Index name	Column name	Sort direction	Is unique	Index type
PK_CustomerDemographics	CustomerTypeID	ASC	Yes	NONCLUSTERED

Object permissions

User / Role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI
public	grant	grant	grant	grant		grant

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[CustomerDemographics](
    [CustomerTypeID] [nchar](10) COLLATE Modern_Spanish_CI_AS NOT NULL,
    [CustomerDesc] [ntext] COLLATE Modern_Spanish_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

(up)

Table: dbo.Customers

Customers' names and related data

Field name

Data type

Nullable

Default value

Description

PK	CustomerID	nchar (5)		Unique five-character code based on customer name.
IX	CompanyName	nvarchar (40)		sdfg
	ContactName	nvarchar (30)	Yes	sdg
	ContactTitle	nvarchar (30)	Yes	sdfg
	Address	nvarchar (60)	Yes	Street or post-office box.
IX	City	nvarchar (15)	Yes
IX	Region	nvarchar (15)	Yes	State or province.
IX	PostalCode	nvarchar (10)	Yes
	Country	nvarchar (15)	Yes
	Phone	nvarchar (24)	Yes	Phone number includes country code or area code.
	Fax	nvarchar (24)	Yes	Phone number includes country code or area code.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
City	City	ASC		NONCLUSTERED
CompanyName	CompanyName	ASC		NONCLUSTERED
IX_Customers	CompanyName	ASC		NONCLUSTERED
PK_Customers	CustomerID	ASC	Yes	CLUSTERED
PostalCode	PostalCode	ASC		NONCLUSTERED
Region	Region	ASC		NONCLUSTERED

Object permissions

User / Role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI
public	grant	grant	grant	grant		grant

Extended properties

Author	John Smith
CustomProperty1	Sample value to test extended properties

Objects that depend on dbo.Customers

dbo.Customer and Suppliers by City
dbo.CustOrderHist
dbo.Invoices
dbo.Orders Qry
dbo.Quarterly Orders
dbo.Sales Totals by Amount

Table definition

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

(up)

Table: dbo.Employees

Employees' names, titles, and personal information.

Field name

Data type

Nullable

Default value

Description

PK	EmployeeID	int		Number automatically assigned to new employee.
IX	LastName	nvarchar (20)
	FirstName	nvarchar (10)
	Title	nvarchar (30)	Yes	Employee's title.
	TitleOfCourtesy	nvarchar (25)	Yes
	BirthDate	datetime	Yes
	HireDate	datetime	Yes
	Address	nvarchar (60)	Yes	Street or post-office box.
	City	nvarchar (15)	Yes
	Region	nvarchar (15)	Yes	Street or post-office box.
IX	PostalCode	nvarchar (10)	Yes
	Country	nvarchar (15)	Yes
	HomePhone	nvarchar (24)	Yes	Phone number includes country code or area code.
	Extension	nvarchar (4)	Yes	Internal telephone extension number.
	Photo	image	Yes	Picture of employee.
	Notes	ntext	Yes	General information about employee's background.
FK	ReportsTo	int	Yes	Employee's supervisor.
	PhotoPath	nvarchar (255)	Yes

Foreign keys dependencies:

FK_Employees_Employees: ReportsTo relies upon remote PK_Employees (dbo.Employees)

Indexes

Index name	Column name	Sort direction	Is unique	Index type
LastName	LastName	ASC		NONCLUSTERED
PK_Employees	EmployeeID	ASC	Yes	CLUSTERED
PostalCode	PostalCode	ASC		NONCLUSTERED

Check constraints

Check name	Column name	Check expresion
CK_Birthdate	BirthDate	([BirthDate] < getdate())

Object permissions

User / Role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI
public	grant	grant	grant	grant		grant

Objects that depend on dbo.Employees

dbo.Employee Sales by Country
dbo.Invoices

Table definition

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

(up)

Table: dbo.EmployeeTerritories

Field name

Data type

Nullable

Default value

Description

PK FK	EmployeeID	int
PK FK	TerritoryID	nvarchar (20)

Foreign keys dependencies:

FK_EmployeeTerritories_Employees: EmployeeID relies upon remote PK_Employees (dbo.Employees)
FK_EmployeeTerritories_Territories: TerritoryID relies upon remote PK_Territories (dbo.Territories)

Indexes

Index name	Column name	Sort direction	Is unique	Index type
PK_EmployeeTerritories	EmployeeID	ASC	Yes	NONCLUSTERED
PK_EmployeeTerritories	TerritoryID	ASC	Yes	NONCLUSTERED

Object permissions

User / Role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI
public	grant	grant	grant	grant		grant

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[EmployeeTerritories](
    [EmployeeID] [int] NOT NULL,
    [TerritoryID] [nvarchar](20) COLLATE Modern_Spanish_CI_AS NOT NULL
) ON [PRIMARY]

(up)

Table: dbo.Order Details

Details on products, quantities, and prices for each order in the Orders table.

Field name

Data type

Nullable

Default value

Description

PK FK	OrderID	int		Same as Order ID in Orders table.
PK FK	ProductID	int		Same as Product ID in Products table.
	UnitPrice	money	(0)
	Quantity	smallint	(1)
	Discount	real	(0)

Foreign keys dependencies:

FK_Order_Details_Orders: OrderID relies upon remote PK_Orders (dbo.Orders)
FK_Order_Details_Products: ProductID relies upon remote PK_Products (dbo.Products)

Indexes

Index name	Column name	Sort direction	Is unique	Index type
OrderID	OrderID	ASC		NONCLUSTERED
OrdersOrder_Details	OrderID	ASC		NONCLUSTERED
PK_Order_Details	OrderID	ASC	Yes	CLUSTERED
PK_Order_Details	ProductID	ASC	Yes	CLUSTERED
ProductID	ProductID	ASC		NONCLUSTERED
ProductsOrder_Details	ProductID	ASC		NONCLUSTERED

Check constraints

Check name	Column name	Check expresion
CK_Discount	Discount	([Discount] >= 0 and [Discount] <= 1)
CK_Quantity	Quantity	([Quantity] > 0)
CK_UnitPrice	UnitPrice	([UnitPrice] >= 0)

Object permissions

User / Role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI
public	grant	grant	grant	grant		grant

Objects that depend on dbo.Order Details

dbo.CustOrderHist
dbo.CustOrdersDetail
dbo.Invoices
dbo.Order Details Extended
dbo.Order Subtotals
dbo.Product Sales for 1997
dbo.SalesByCategory

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
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]

(up)

Table: dbo.Orders

Customer name, order date, and freight charge for each order.

Field name

Data type

Nullable

Default value

Description

PK	OrderID	int
FK	CustomerID	nchar (5)	Yes
FK	EmployeeID	int	Yes		Same entry as in Employees table.
IX	OrderDate	datetime	Yes
	RequiredDate	datetime	Yes
IX	ShippedDate	datetime	Yes
FK	ShipVia	int	Yes		Same as Shipper ID in Shippers table.
	Freight	money	Yes	(0)
	ShipName	nvarchar (40)	Yes		Name of person or company to receive the shipment.
	ShipAddress	nvarchar (60)	Yes		Street address only -- no post-office box allowed.
	ShipCity	nvarchar (15)	Yes
	ShipRegion	nvarchar (15)	Yes		State or province.
IX	ShipPostalCode	nvarchar (10)	Yes
	ShipCountry	nvarchar (15)	Yes

Foreign keys dependencies:

FK_Orders_Customers: CustomerID relies upon remote PK_Customers (dbo.Customers)
FK_Orders_Employees: EmployeeID relies upon remote PK_Employees (dbo.Employees)
FK_Orders_Shippers: ShipVia relies upon remote PK_Shippers (dbo.Shippers)

Indexes

Index name	Column name	Sort direction	Is unique	Index type
CustomerID	CustomerID	ASC		NONCLUSTERED
CustomersOrders	CustomerID	ASC		NONCLUSTERED
EmployeeID	EmployeeID	ASC		NONCLUSTERED
EmployeesOrders	EmployeeID	ASC		NONCLUSTERED
OrderDate	OrderDate	ASC		NONCLUSTERED
PK_Orders	OrderID	ASC	Yes	CLUSTERED
ShippedDate	ShippedDate	ASC		NONCLUSTERED
ShippersOrders	ShipVia	ASC		NONCLUSTERED
ShipPostalCode	ShipPostalCode	ASC		NONCLUSTERED

Object permissions

User / Role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI
public	grant	grant	grant	grant		grant

Objects that depend on dbo.Orders

dbo.CustOrderHist
dbo.CustOrdersOrders
dbo.Employee Sales by Country
dbo.Invoices
dbo.Orders Qry
dbo.Product Sales for 1997
dbo.Quarterly Orders
dbo.Sales by Category
dbo.Sales by Year
dbo.Sales Totals by Amount
dbo.SalesByCategory
dbo.Summary of Sales by Quarter
dbo.Summary of Sales by Year

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[Orders](
    [OrderID] [int] IDENTITY(1,1) NOT NULL,
    [CustomerID] [nchar](5) COLLATE Modern_Spanish_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 Modern_Spanish_CI_AS NULL,
    [ShipAddress] [nvarchar](60) COLLATE Modern_Spanish_CI_AS NULL,
    [ShipCity] [nvarchar](15) COLLATE Modern_Spanish_CI_AS NULL,
    [ShipRegion] [nvarchar](15) COLLATE Modern_Spanish_CI_AS NULL,
    [ShipPostalCode] [nvarchar](10) COLLATE Modern_Spanish_CI_AS NULL,
    [ShipCountry] [nvarchar](15) COLLATE Modern_Spanish_CI_AS NULL
) ON [PRIMARY]

(up)

Table: dbo.Products

Product names, suppliers, prices, and units in stock.

Field name

Data type

Nullable

Default value

Description

PK	ProductID	int			Number automatically assigned to new product.
IX	ProductName	nvarchar (40)
FK	SupplierID	int	Yes		Same entry as in Suppliers table.
FK	CategoryID	int	Yes		Same entry as in Categories table.
	QuantityPerUnit	nvarchar (20)	Yes		(e.g., 24-count case, 1-liter bottle).
	UnitPrice	money	Yes	(0)
	UnitsInStock	smallint	Yes	(0)
	UnitsOnOrder	smallint	Yes	(0)
	ReorderLevel	smallint	Yes	(0)	Minimum units to maintain in stock.
	Discontinued	bit		(0)	Yes means item is no longer available.

Foreign keys dependencies:

FK_Products_Suppliers: SupplierID relies upon remote PK_Suppliers (dbo.Suppliers)
FK_Products_Categories: CategoryID relies upon remote PK_Categories (dbo.Categories)

Indexes

Index name	Column name	Sort direction	Is unique	Index type
CategoriesProducts	CategoryID	ASC		NONCLUSTERED
CategoryID	CategoryID	ASC		NONCLUSTERED
PK_Products	ProductID	ASC	Yes	CLUSTERED
ProductName	ProductName	ASC		NONCLUSTERED
SupplierID	SupplierID	ASC		NONCLUSTERED
SuppliersProducts	SupplierID	ASC		NONCLUSTERED

Check constraints

Check name	Column name	Check expresion
CK_Products_UnitPrice	UnitPrice	([UnitPrice] >= 0)
CK_ReorderLevel	ReorderLevel	([ReorderLevel] >= 0)
CK_UnitsInStock	UnitsInStock	([UnitsInStock] >= 0)
CK_UnitsOnOrder	UnitsOnOrder	([UnitsOnOrder] >= 0)

Object permissions

User / Role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI
public	grant	grant	grant	grant		grant

Objects that depend on dbo.Products

dbo.Alphabetical list of products
dbo.Current Product List
dbo.CustOrderHist
dbo.CustOrdersDetail
dbo.Invoices
dbo.Order Details Extended
dbo.Product Sales for 1997
dbo.Products Above Average Price
dbo.Products by Category
dbo.Sales by Category
dbo.SalesByCategory
dbo.Ten Most Expensive Products

Table definition

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

(up)

Table: dbo.Region

Sample comment add to test dbdesc This is the second line

Field name

Data type

Nullable

Default value

Description

PK	RegionID	int
	RegionDescription	nchar (50)

Indexes

Index name	Column name	Sort direction	Is unique	Index type
PK_Region	RegionID	ASC	Yes	NONCLUSTERED

Object permissions

User / Role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI
public	grant	grant	grant	grant		grant

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[Region](
    [RegionID] [int] NOT NULL,
    [RegionDescription] [nchar](50) COLLATE Modern_Spanish_CI_AS NOT NULL
) ON [PRIMARY]

(up)

Table: dbo.Shippers

Shippers' names and phone numbers.

Field name

Data type

Nullable

Default value

Description

PK	ShipperID	int		Number automatically assigned to new shipper.
	CompanyName	nvarchar (40)		Name of shipping company.
	Phone	nvarchar (24)	Yes	Phone number includes country code or area code.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
PK_Shippers	ShipperID	ASC	Yes	CLUSTERED

Objects that depend on dbo.Shippers

dbo.Invoices

Table definition

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

(up)

Table: dbo.Suppliers

Suppliers' names, addresses, phone numbers, and hyperlinks to home pages.

Field name

Data type

Nullable

Default value

Description

PK	SupplierID	int		Number automatically assigned to new supplier.
IX	CompanyName	nvarchar (40)
	ContactName	nvarchar (30)	Yes
	ContactTitle	nvarchar (30)	Yes
	Address	nvarchar (60)	Yes	Street or post-office box.
	City	nvarchar (15)	Yes
	Region	nvarchar (15)	Yes	State or province.
IX	PostalCode	nvarchar (10)	Yes
	Country	nvarchar (15)	Yes
	Phone	nvarchar (24)	Yes	Phone number includes country code or area code.
	Fax	nvarchar (24)	Yes	Phone number includes country code or area code.
	HomePage	ntext	Yes	Supplier's home page on World Wide Web.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
CompanyName	CompanyName	ASC		NONCLUSTERED
PK_Suppliers	SupplierID	ASC	Yes	CLUSTERED
PostalCode	PostalCode	ASC		NONCLUSTERED

Object permissions

User / Role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI
public	grant	grant	grant	grant		grant

Objects that depend on dbo.Suppliers

dbo.Customer and Suppliers by City

Table definition

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

(up)

Table: dbo.Territories

sdfsf

Field name

Data type

Nullable

Default value

Description

PK	TerritoryID	nvarchar (20)	sdfg
	TerritoryDescription	nchar (50)	sdfg
FK	RegionID	int	sdfg

Foreign keys dependencies:

FK_Territories_Region: RegionID relies upon remote PK_Region (dbo.Region)

Indexes

Index name	Column name	Sort direction	Is unique	Index type
PK_Territories	TerritoryID	ASC	Yes	NONCLUSTERED

Object permissions

User / Role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI
public	grant	grant	grant	grant		grant

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[Territories](
    [TerritoryID] [nvarchar](20) COLLATE Modern_Spanish_CI_AS NOT NULL,
    [TerritoryDescription] [nchar](50) COLLATE Modern_Spanish_CI_AS NOT NULL,
    [RegionID] [int] NOT NULL
) ON [PRIMARY]

(up)

Table: dbo.TestTable

This is a test table 2

Field name

Data type

Nullable

Default value

Description

TestCustomField

TestType (20)

Yes

sdcs

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[TestTable](
    [TestCustomField] [TestType] NULL
) ON [PRIMARY]

(up)

View: dbo.Alphabetical list of products ( Created: 06 Ago 2000 Last altered: 06 Ago 2000 )

Resultset

Source	Field name	Data type	Nullable	Field description
dbo.Products (TABLE)	ProductID	int
dbo.Products (TABLE)	ProductName	nvarchar (40)
dbo.Products (TABLE)	SupplierID	int	Yes
dbo.Products (TABLE)	CategoryID	int	Yes
dbo.Products (TABLE)	QuantityPerUnit	nvarchar (20)	Yes
dbo.Products (TABLE)	UnitPrice	money	Yes
dbo.Products (TABLE)	UnitsInStock	smallint	Yes
dbo.Products (TABLE)	UnitsOnOrder	smallint	Yes
dbo.Products (TABLE)	ReorderLevel	smallint	Yes
dbo.Products (TABLE)	Discontinued	bit
dbo.Categories (TABLE)	CategoryName	nvarchar (15)

View definition

create view "Alphabetical list of products" AS
SELECT Products.*, Categories.CategoryName
FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
WHERE (((Products.Discontinued)=0))

Object permissions

User / Role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI
public	grant	grant	grant	grant

Related objects

dbo.Products
dbo.Categories

(up)

View: dbo.Category Sales for 1997 ( Created: 06 Ago 2000 Last altered: 06 Ago 2000 )

Totals product sales by category based on values returned by the Product Sales for 1997 query.

Resultset

Source	Field name	Data type	Nullable	Field description
dbo.Product Sales for 1997 (VIEW)	CategoryName	nvarchar (15)		CatName
	CategorySales	money	Yes	CatSales

View definition

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

Object permissions

User / Role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI
public	grant	grant	grant	grant

Related objects

dbo.Product Sales for 1997

(up)

View: dbo.Current Product List ( Created: 06 Ago 2000 Last altered: 06 Ago 2000 )

Filters records in Products table; query returns only products that are not discontinued.

Resultset

Source	Field name	Data type	Nullable	Field description
dbo.Products (TABLE)	ProductID	int
dbo.Products (TABLE)	ProductName	nvarchar (40)

View definition

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

Object permissions

User / Role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI
public	grant	grant	grant	grant

Related objects

dbo.Products

(up)

View: dbo.Customer and Suppliers by City ( Created: 06 Ago 2000 Last altered: 06 Ago 2000 )

Resultset

Source	Field name	Data type	Nullable	Field description
dbo.Customers (TABLE)	City	nvarchar (15)	Yes
dbo.Customers (TABLE)	CompanyName	nvarchar (40)
dbo.Customers (TABLE)	ContactName	nvarchar (30)	Yes
	Relationship	varchar (9)

View definition

create view "Customer and Suppliers by City" AS
SELECT City, CompanyName, ContactName, 'Customers' AS Relationship 
FROM Customers
UNION SELECT City, CompanyName, ContactName, 'Suppliers'
FROM Suppliers
--ORDER BY City, CompanyName

Object permissions

User / Role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI
public	grant	grant	grant	grant

Related objects

dbo.Suppliers
dbo.Customers

(up)

View: dbo.Invoices ( Created: 06 Ago 2000 Last altered: 06 Ago 2000 )

(Criteria) Record source for Invoice report. Based on six tables. Includes expressions that concatenate first and last employee name and that use the CCur function to calculate extended price.

Resultset

Source	Field name	Data type	Nullable	Field description
dbo.Orders (TABLE)	ShipName	nvarchar (40)	Yes	Name of the shipper
dbo.Orders (TABLE)	ShipAddress	nvarchar (60)	Yes
dbo.Orders (TABLE)	ShipCity	nvarchar (15)	Yes
dbo.Orders (TABLE)	ShipRegion	nvarchar (15)	Yes
dbo.Orders (TABLE)	ShipPostalCode	nvarchar (10)	Yes
dbo.Orders (TABLE)	ShipCountry	nvarchar (15)	Yes
dbo.Orders (TABLE)	CustomerID	nchar (5)	Yes
	CustomerName	nvarchar (40)
dbo.Customers (TABLE)	Address	nvarchar (60)	Yes
dbo.Customers (TABLE)	City	nvarchar (15)	Yes
dbo.Customers (TABLE)	Region	nvarchar (15)	Yes
dbo.Customers (TABLE)	PostalCode	nvarchar (10)	Yes
dbo.Customers (TABLE)	Country	nvarchar (15)	Yes
	Salesperson	nvarchar (31)
dbo.Orders (TABLE)	OrderID	int
dbo.Orders (TABLE)	OrderDate	datetime	Yes
dbo.Orders (TABLE)	RequiredDate	datetime	Yes
dbo.Orders (TABLE)	ShippedDate	datetime	Yes
	ShipperName	nvarchar (40)
dbo.Products (TABLE)	ProductID	int
dbo.Products (TABLE)	ProductName	nvarchar (40)
dbo.Order Details (TABLE)	UnitPrice	money
dbo.Order Details (TABLE)	Quantity	smallint
dbo.Order Details (TABLE)	Discount	real
	ExtendedPrice	money	Yes
dbo.Orders (TABLE)	Freight	money	Yes

View definition

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

Object permissions

User / Role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI
public	grant	grant	grant	grant

View: dbo.Order Details Extended ( Created: 06 Ago 2000 Last altered: 06 Ago 2000 )

Record source for several forms and reports. Uses CCur function to compute the ExtendedPrice for each item ordered.

Resultset

Source	Field name	Data type	Nullable	Field description
dbo.Order Details (TABLE)	OrderID	int
dbo.Products (TABLE)	ProductID	int
dbo.Products (TABLE)	ProductName	nvarchar (40)
dbo.Order Details (TABLE)	UnitPrice	money
dbo.Order Details (TABLE)	Quantity	smallint
dbo.Order Details (TABLE)	Discount	real
	ExtendedPrice	money	Yes

View definition

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

Object permissions

User / Role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI
public	grant	grant	grant	grant

Related objects

dbo.Order Details
dbo.Products

Objects that depend on dbo.Order Details Extended

dbo.Sales by Category

(up)

View: dbo.Order Subtotals ( Created: 06 Ago 2000 Last altered: 06 Ago 2000 )

Record source for other queries. Uses Sum and CCur functions to compute subtotal for each order.

Resultset

Source	Field name	Data type	Nullable	Field description
dbo.Order Details (TABLE)	OrderID	int
	Subtotal	money	Yes

View definition

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

Object permissions

User / Role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI
public	grant	grant	grant	grant

Related objects

dbo.Order Details

Objects that depend on dbo.Order Subtotals

dbo.Employee Sales by Country
dbo.Sales by Year
dbo.Sales Totals by Amount
dbo.Summary of Sales by Quarter
dbo.Summary of Sales by Year

(up)

View: dbo.Orders Qry ( Created: 06 Ago 2000 Last altered: 06 Ago 2000 )

Resultset

Source	Field name	Data type	Nullable	Field description
dbo.Orders (TABLE)	OrderID	int
dbo.Orders (TABLE)	CustomerID	nchar (5)	Yes
dbo.Orders (TABLE)	EmployeeID	int	Yes
dbo.Orders (TABLE)	OrderDate	datetime	Yes
dbo.Orders (TABLE)	RequiredDate	datetime	Yes
dbo.Orders (TABLE)	ShippedDate	datetime	Yes
dbo.Orders (TABLE)	ShipVia	int	Yes
dbo.Orders (TABLE)	Freight	money	Yes
dbo.Orders (TABLE)	ShipName	nvarchar (40)	Yes
dbo.Orders (TABLE)	ShipAddress	nvarchar (60)	Yes
dbo.Orders (TABLE)	ShipCity	nvarchar (15)	Yes
dbo.Orders (TABLE)	ShipRegion	nvarchar (15)	Yes
dbo.Orders (TABLE)	ShipPostalCode	nvarchar (10)	Yes
dbo.Orders (TABLE)	ShipCountry	nvarchar (15)	Yes
dbo.Customers (TABLE)	CompanyName	nvarchar (40)
dbo.Customers (TABLE)	Address	nvarchar (60)	Yes
dbo.Customers (TABLE)	City	nvarchar (15)	Yes
dbo.Customers (TABLE)	Region	nvarchar (15)	Yes
dbo.Customers (TABLE)	PostalCode	nvarchar (10)	Yes
dbo.Customers (TABLE)	Country	nvarchar (15)	Yes

View definition

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

Object permissions

User / Role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI
public	grant	grant	grant	grant

Related objects

dbo.Customers
dbo.Orders

(up)

View: dbo.Product Sales for 1997 ( Created: 06 Ago 2000 Last altered: 06 Ago 2000 )

Record source for Category Sales for 1997 query. Uses Sum and CCur functions.

Resultset

Source	Field name	Data type	Nullable	Field description
dbo.Categories (TABLE)	CategoryName	nvarchar (15)
dbo.Products (TABLE)	ProductName	nvarchar (40)
	ProductSales	money	Yes

View definition

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

Object permissions

User / Role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI
public	grant	grant	grant	grant

Objects that depend on dbo.Product Sales for 1997

dbo.Category Sales for 1997

(up)

View: dbo.Products Above Average Price ( Created: 06 Ago 2000 Last altered: 06 Ago 2000 )

(Subquery) Returns products that have a unit price higher than the average.

Resultset

Source	Field name	Data type	Nullable	Field description
dbo.Products (TABLE)	ProductName	nvarchar (40)
dbo.Products (TABLE)	UnitPrice	money	Yes

View definition

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

Object permissions

User / Role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI
public	grant	grant	grant	grant

Related objects

dbo.Products

(up)

View: dbo.Products by Category ( Created: 06 Ago 2000 Last altered: 06 Ago 2000 )

Resultset

Source	Field name	Data type	Nullable	Field description
dbo.Categories (TABLE)	CategoryName	nvarchar (15)
dbo.Products (TABLE)	ProductName	nvarchar (40)
dbo.Products (TABLE)	QuantityPerUnit	nvarchar (20)	Yes
dbo.Products (TABLE)	UnitsInStock	smallint	Yes
dbo.Products (TABLE)	Discontinued	bit

View definition

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

Object permissions

User / Role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI
public	grant	grant	grant	grant

Related objects

dbo.Categories
dbo.Products

(up)

View: dbo.Quarterly Orders ( Created: 06 Ago 2000 Last altered: 06 Ago 2000 )

Record source for Quarterly Orders form. Lists only customers who had orders in 1995.

Resultset

Source	Field name	Data type	Nullable	Field description
dbo.Orders (TABLE)	CustomerID	nchar (5)	Yes
dbo.Customers (TABLE)	CompanyName	nvarchar (40)	Yes
dbo.Customers (TABLE)	City	nvarchar (15)	Yes
dbo.Customers (TABLE)	Country	nvarchar (15)	Yes

View definition

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'

Object permissions

User / Role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI
public	grant	grant	grant	grant

Related objects

dbo.Customers
dbo.Orders

(up)

View: dbo.Sales by Category ( Created: 06 Ago 2000 Last altered: 06 Ago 2000 )

Record source for Sales by Category report.

Resultset

Source	Field name	Data type	Nullable	Field description
dbo.Products (TABLE)	CategoryID	int
dbo.Categories (TABLE)	CategoryName	nvarchar (15)
dbo.Products (TABLE)	ProductName	nvarchar (40)
	ProductSales	money	Yes

View definition

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

Object permissions

User / Role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI
public	grant	grant	grant	grant

View: dbo.Sales Totals by Amount ( Created: 06 Ago 2000 Last altered: 06 Ago 2000 )

Resultset

Source	Field name	Data type	Nullable	Field description
	SaleAmount	money	Yes
dbo.Orders (TABLE)	OrderID	int
dbo.Customers (TABLE)	CompanyName	nvarchar (40)
dbo.Orders (TABLE)	ShippedDate	datetime	Yes

View definition

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')

Object permissions

User / Role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI
public	grant	grant	grant	grant

View: dbo.Summary of Sales by Quarter ( Created: 06 Ago 2000 Last altered: 06 Ago 2000 )

Resultset

Source	Field name	Data type	Nullable	Field description
dbo.Orders (TABLE)	ShippedDate	datetime	Yes
dbo.Orders (TABLE)	OrderID	int
dbo.Order Subtotals (VIEW)	Subtotal	money	Yes

View definition

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

Object permissions

User / Role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI
public	grant	grant	grant	grant

Related objects

dbo.Orders
dbo.Order Subtotals

(up)

View: dbo.Summary of Sales by Year ( Created: 06 Ago 2000 Last altered: 06 Ago 2000 )

Resultset

Source	Field name	Data type	Nullable	Field description
dbo.Orders (TABLE)	ShippedDate	datetime	Yes
dbo.Orders (TABLE)	OrderID	int
dbo.Order Subtotals (VIEW)	Subtotal	money	Yes

View definition

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

Object permissions

User / Role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI
public	grant	grant	grant	grant

Related objects

dbo.Orders
dbo.Order Subtotals

(up)

Stored procedure: dbo.CustOrderHist ( Created: 06 Ago 2000 Last altered: 06 Ago 2000 )

This description has been extracted from the procedure definition

Parameters

Direction	Parameter name	Data type	Description
IN	@CustomerID	nchar (5)	This is stored procedure parameter description

Stored procedure definition

/* This description has been extracted from the procedure definition */
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

Object permissions

User / Role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI
public					grant

Stored procedure: dbo.CustOrdersDetail ( Created: 06 Ago 2000 Last altered: 06 Ago 2000 )

Record source for several forms and reports. Uses CCur function to compute the ExtendedPrice for each item ordered.

Parameters

Direction	Parameter name	Data type	Description
IN	@OrderID	int

Stored procedure definition


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

Object permissions

User / Role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI
public					grant

Related objects

dbo.Order Details
dbo.Products

(up)

Stored procedure: dbo.CustOrdersOrders ( Created: 06 Ago 2000 Last altered: 06 Ago 2000 )

Parameters

Direction	Parameter name	Data type	Description
IN	@CustomerID	nchar (5)

Stored procedure definition


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

Object permissions

User / Role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI
public					grant

Related objects

dbo.Orders

(up)

Stored procedure: dbo.Employee Sales by Country ( Created: 06 Ago 2000 Last altered: 06 Ago 2000 )

Record source for Employee Sales by Country report. Prompts for beginning and ending dates.

Parameters

Direction	Parameter name	Data type	Description
IN	@Beginning_Date	datetime
IN	@Ending_Date	datetime

Stored procedure definition

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

Object permissions

User / Role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI
public					grant

Stored procedure: dbo.Sales by Year ( Created: 06 Ago 2000 Last altered: 06 Ago 2000 )

Record source for Sales by Year report and its subreport. Refers to controls on Sales by Year Dialog for parameter values. Uses Format function to return the year value of a shipped date.

Parameters

Direction	Parameter name	Data type	Description
IN	@Beginning_Date	datetime
IN	@Ending_Date	datetime

Stored procedure definition

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

Object permissions

User / Role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI
public					grant

Related objects

dbo.Orders
dbo.Order Subtotals

(up)

Stored procedure: dbo.SalesByCategory ( Created: 06 Ago 2000 Last altered: 06 Ago 2000 )

Record source for Sales by Category report.

Parameters

Direction	Parameter name	Data type	Description
IN	@CategoryName	nvarchar (15)
IN	@OrdYear	nvarchar (4)

Stored procedure definition

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

Object permissions

User / Role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI
public					grant

Stored procedure: dbo.Ten Most Expensive Products ( Created: 06 Ago 2000 Last altered: 06 Ago 2000 )

Returns 10 most expensive products. Specifies the number of highest values to return using the Top Values box on the toolbar.

Stored procedure definition

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

Object permissions

User / Role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI
public					grant

Related objects

dbo.Products

USER-DEFINED DATA TYPES

TestType ( varchar (20), not null ) ,

USERS

User name	Login name	Created	Roles	User description
dbo	sa	06 Ago 2000	db_owner
guest		06 Ago 2000

ROLES

Role name	Is app role	Users with this role	Role description
db_accessadmin
db_backupoperator
db_datareader
db_datawriter
db_ddladmin
db_denydatareader
db_denydatawriter
db_owner		dbo
db_securityadmin
public