SchemaToDoc - Northwind

Note: The Type column includes the maximum size for character fields and the precision/scale (if they have been set) for Numeric and Decimal fields.

Table of Contents

All Tables (Tables)........................................................................................................................................ 3

Categories................................................................................................................................................. 3

CustomerCustomerDemo.......................................................................................................................... 3

CustomerDemographics............................................................................................................................ 4

Customers................................................................................................................................................. 4

Employees................................................................................................................................................ 5

EmployeeTerritories.................................................................................................................................. 6

Order Details............................................................................................................................................ 6

Orders...................................................................................................................................................... 7

Products................................................................................................................................................... 8

Region...................................................................................................................................................... 9

Shippers.................................................................................................................................................... 9

Suppliers................................................................................................................................................. 10

Territories............................................................................................................................................... 11

All Views (Views)....................................................................................................................................... 12

Alphabetical list of products..................................................................................................................... 12

Category Sales for 1997.......................................................................................................................... 12

Current Product List................................................................................................................................ 12

Customer and Suppliers by City............................................................................................................... 13

Invoices.................................................................................................................................................. 13

Order Details Extended........................................................................................................................... 14

Order Subtotals....................................................................................................................................... 15

Orders Qry............................................................................................................................................. 15

Product Sales for 1997............................................................................................................................ 16

Products Above Average Price................................................................................................................ 17

Products by Category.............................................................................................................................. 17

Quarterly Orders..................................................................................................................................... 17

Sales by Category................................................................................................................................... 18

Sales Totals by Amount........................................................................................................................... 18

Summary of Sales by Quarter.................................................................................................................. 19

Summary of Sales by Year...................................................................................................................... 19

All Stored Procedures (Stored Procedures)................................................................................................. 20

CustOrderHist......................................................................................................................................... 20

CustOrdersDetail.................................................................................................................................... 20

CustOrdersOrders.................................................................................................................................. 20

Employee Sales by Country..................................................................................................................... 21

Sales by Year.......................................................................................................................................... 21

SalesByCategory..................................................................................................................................... 21

Ten Most Expensive Products................................................................................................................. 22

All Tables (Tables)

Categories

Primary Key(s): CategoryID

Field	Type	Nulls?
CategoryID	int	No
CategoryName	nvarchar (15)	No
Description	ntext	Yes
Picture	image	Yes

Index Name	Unique	Clustered	Fields
PK_Categories	Yes	Yes	CategoryID
CategoryName	No	No	CategoryName

No Check Constraints

No Internal Foreign Keys

Primary Key as Foreign Key Constraint	Affected Table	Affected Field
FK_Products_Categories	Products	CategoryID

No Enabled Triggers

CustomerCustomerDemo

Primary Key(s): CustomerID, CustomerTypeID

Field	Type	Nulls?
CustomerID	nchar (5)	No
CustomerTypeID	nchar (10)	No

Index Name	Unique	Clustered	Fields
PK_CustomerCustomerDemo	Yes	No	CustomerID, CustomerTypeID

No Check Constraints

Internal Foreign Key Constraint	Affected Field	Source Table
FK_CustomerCustomerDemo	CustomerTypeID	CustomerDemographics
FK_CustomerCustomerDemo_Customers	CustomerID	Customers

No External Foreign Keys

No Enabled Triggers

CustomerDemographics

Primary Key(s): CustomerTypeID

Field	Type	Nulls?
CustomerTypeID	nchar (10)	No
CustomerDesc	ntext	Yes

Index Name	Unique	Clustered	Fields
PK_CustomerDemographics	Yes	No	CustomerTypeID

No Check Constraints

No Internal Foreign Keys

Primary Key as Foreign Key Constraint	Affected Table	Affected Field
FK_CustomerCustomerDemo	CustomerCustomerDemo	CustomerTypeID

No Enabled Triggers

Customers

Primary Key(s): CustomerID

Field	Type	Nulls?
CustomerID	nchar (5)	No
CompanyName	nvarchar (40)	No
ContactName	nvarchar (30)	Yes
ContactTitle	nvarchar (30)	Yes
Address	nvarchar (60)	Yes
City	nvarchar (15)	Yes
Region	nvarchar (15)	Yes
PostalCode	nvarchar (10)	Yes
Country	nvarchar (15)	Yes
Phone	nvarchar (24)	Yes
Fax	nvarchar (24)	Yes

Index Name	Unique	Clustered	Fields
PK_Customers	Yes	Yes	CustomerID
City	No	No	City
CompanyName	No	No	CompanyName
PostalCode	No	No	PostalCode
Region	No	No	Region

No Check Constraints

No Internal Foreign Keys

Primary Key as Foreign Key Constraint	Affected Table	Affected Field
FK_CustomerCustomerDemo_Customers	CustomerCustomerDemo	CustomerID
FK_Orders_Customers	Orders	CustomerID

No Enabled Triggers

Employees

Primary Key(s): EmployeeID

Field	Type	Nulls?
EmployeeID	int	No
LastName	nvarchar (20)	No
FirstName	nvarchar (10)	No
Title	nvarchar (30)	Yes
TitleOfCourtesy	nvarchar (25)	Yes
BirthDate	datetime	Yes
HireDate	datetime	Yes
Address	nvarchar (60)	Yes
City	nvarchar (15)	Yes
Region	nvarchar (15)	Yes
PostalCode	nvarchar (10)	Yes
Country	nvarchar (15)	Yes
HomePhone	nvarchar (24)	Yes
Extension	nvarchar (4)	Yes
Photo	image	Yes
Notes	ntext	Yes
ReportsTo	int	Yes
PhotoPath	nvarchar (255)	Yes

Index Name	Unique	Clustered	Fields
PK_Employees	Yes	Yes	EmployeeID
LastName	No	No	LastName
PostalCode	No	No	PostalCode

Check Constraint Name	Text
CK_Birthdate	([BirthDate] < getdate())

Internal Foreign Key Constraint	Affected Field	Source Table
FK_Employees_Employees	ReportsTo	Employees

Primary Key as Foreign Key Constraint	Affected Table	Affected Field
FK_Employees_Employees	Employees	ReportsTo
FK_EmployeeTerritories_Employees	EmployeeTerritories	EmployeeID
FK_Orders_Employees	Orders	EmployeeID

No Enabled Triggers

EmployeeTerritories

Primary Key(s): EmployeeID, TerritoryID

Field	Type	Nulls?
EmployeeID	int	No
TerritoryID	nvarchar (20)	No

Index Name	Unique	Clustered	Fields
PK_EmployeeTerritories	Yes	No	EmployeeID, TerritoryID

No Check Constraints

Internal Foreign Key Constraint	Affected Field	Source Table
FK_EmployeeTerritories_Employees	EmployeeID	Employees
FK_EmployeeTerritories_Territories	TerritoryID	Territories

No External Foreign Keys

No Enabled Triggers

Order Details

Primary Key(s): OrderID, ProductID

Field	Type	Default	Nulls?
OrderID	int		No
ProductID	int		No
UnitPrice	money	(0)	No
Quantity	smallint	(1)	No
Discount	real	(0)	No

Index Name	Unique	Clustered	Fields
PK_Order_Details	Yes	Yes	OrderID, ProductID
OrderID	No	No	OrderID
OrdersOrder_Details	No	No	OrderID
ProductID	No	No	ProductID
ProductsOrder_Details	No	No	ProductID

Check Constraint Name	Text
CK_Discount	([Discount] >= 0 and [Discount] <= 1)
CK_Quantity	([Quantity] > 0)
CK_UnitPrice	([UnitPrice] >= 0)

Internal Foreign Key Constraint	Affected Field	Source Table
FK_Order_Details_Orders	OrderID	Orders
FK_Order_Details_Products	ProductID	Products

No External Foreign Keys

No Enabled Triggers

Orders

Primary Key(s): OrderID

Field	Type	Default	Nulls?
OrderID	int		No
CustomerID	nchar (5)		Yes
EmployeeID	int		Yes
OrderDate	datetime		Yes
RequiredDate	datetime		Yes
ShippedDate	datetime		Yes
ShipVia	int		Yes
Freight	money	(0)	Yes
ShipName	nvarchar (40)		Yes
ShipAddress	nvarchar (60)		Yes
ShipCity	nvarchar (15)		Yes
ShipRegion	nvarchar (15)		Yes
ShipPostalCode	nvarchar (10)		Yes
ShipCountry	nvarchar (15)		Yes

Index Name	Unique	Clustered	Fields
PK_Orders	Yes	Yes	OrderID
CustomerID	No	No	CustomerID
CustomersOrders	No	No	CustomerID
EmployeeID	No	No	EmployeeID
EmployeesOrders	No	No	EmployeeID
OrderDate	No	No	OrderDate
ShippedDate	No	No	ShippedDate
ShippersOrders	No	No	ShipVia
ShipPostalCode	No	No	ShipPostalCode

No Check Constraints

Internal Foreign Key Constraint	Affected Field	Source Table
FK_Orders_Customers	CustomerID	Customers
FK_Orders_Employees	EmployeeID	Employees
FK_Orders_Shippers	ShipVia	Shippers

Primary Key as Foreign Key Constraint	Affected Table	Affected Field
FK_Order_Details_Orders	Order Details	OrderID

No Enabled Triggers

Products

Primary Key(s): ProductID

Field	Type	Default	Nulls?
ProductID	int		No
ProductName	nvarchar (40)		No
SupplierID	int		Yes
CategoryID	int		Yes
QuantityPerUnit	nvarchar (20)		Yes
UnitPrice	money	(0)	Yes
UnitsInStock	smallint	(0)	Yes
UnitsOnOrder	smallint	(0)	Yes
ReorderLevel	smallint	(0)	Yes
Discontinued	bit	(0)	No

Index Name	Unique	Clustered	Fields
PK_Products	Yes	Yes	ProductID
CategoriesProducts	No	No	CategoryID
CategoryID	No	No	CategoryID
ProductName	No	No	ProductName
SupplierID	No	No	SupplierID
SuppliersProducts	No	No	SupplierID

Check Constraint Name	Text
CK_Products_UnitPrice	([UnitPrice] >= 0)
CK_ReorderLevel	([ReorderLevel] >= 0)
CK_UnitsInStock	([UnitsInStock] >= 0)
CK_UnitsOnOrder	([UnitsOnOrder] >= 0)

Internal Foreign Key Constraint	Affected Field	Source Table
FK_Products_Categories	CategoryID	Categories
FK_Products_Suppliers	SupplierID	Suppliers

Primary Key as Foreign Key Constraint	Affected Table	Affected Field
FK_Order_Details_Products	Order Details	ProductID

No Enabled Triggers

Region

Primary Key(s): RegionID

Field	Type	Nulls?
RegionID	int	No
RegionDescription	nchar (50)	No

Index Name	Unique	Clustered	Fields
PK_Region	Yes	No	RegionID

No Check Constraints

No Internal Foreign Keys

Primary Key as Foreign Key Constraint	Affected Table	Affected Field
FK_Territories_Region	Territories	RegionID

No Enabled Triggers

Shippers

Primary Key(s): ShipperID

Field	Type	Nulls?
ShipperID	int	No
CompanyName	nvarchar (40)	No
Phone	nvarchar (24)	Yes

Index Name	Unique	Clustered	Fields
PK_Shippers	Yes	Yes	ShipperID

No Check Constraints

No Internal Foreign Keys

Primary Key as Foreign Key Constraint	Affected Table	Affected Field
FK_Orders_Shippers	Orders	ShipVia

No Enabled Triggers

Suppliers

Primary Key(s): SupplierID

Field	Type	Nulls?
SupplierID	int	No
CompanyName	nvarchar (40)	No
ContactName	nvarchar (30)	Yes
ContactTitle	nvarchar (30)	Yes
Address	nvarchar (60)	Yes
City	nvarchar (15)	Yes
Region	nvarchar (15)	Yes
PostalCode	nvarchar (10)	Yes
Country	nvarchar (15)	Yes
Phone	nvarchar (24)	Yes
Fax	nvarchar (24)	Yes
HomePage	ntext	Yes

Index Name	Unique	Clustered	Fields
PK_Suppliers	Yes	Yes	SupplierID
CompanyName	No	No	CompanyName
PostalCode	No	No	PostalCode

No Check Constraints

No Internal Foreign Keys

Primary Key as Foreign Key Constraint	Affected Table	Affected Field
FK_Products_Suppliers	Products	SupplierID

No Enabled Triggers

Territories

Primary Key(s): TerritoryID

Field	Type	Nulls?
TerritoryID	nvarchar (20)	No
TerritoryDescription	nchar (50)	No
RegionID	int	No

Index Name	Unique	Clustered	Fields
PK_Territories	Yes	No	TerritoryID

No Check Constraints

Internal Foreign Key Constraint	Affected Field	Source Table
FK_Territories_Region	RegionID	Region

Primary Key as Foreign Key Constraint	Affected Table	Affected Field
FK_EmployeeTerritories_Territories	EmployeeTerritories	TerritoryID

No Enabled Triggers

All Views (Views)

Alphabetical list of products

Field	Type	Nulls?
ProductID	int	No
ProductName	nvarchar (40)	No
SupplierID	int	Yes
CategoryID	int	Yes
QuantityPerUnit	nvarchar (20)	Yes
UnitPrice	money	Yes
UnitsInStock	smallint	Yes
UnitsOnOrder	smallint	Yes
ReorderLevel	smallint	Yes
Discontinued	bit	No
CategoryName	nvarchar (15)	No

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

Category Sales for 1997

Field	Type	Nulls?
CategoryName	nvarchar (15)	No
CategorySales	money	Yes

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

Current Product List

Field	Type	Nulls?
ProductID	int	No
ProductName	nvarchar (40)	No

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

Customer and Suppliers by City

Field	Type	Nulls?
City	nvarchar (15)	Yes
CompanyName	nvarchar (40)	No
ContactName	nvarchar (30)	Yes
Relationship	varchar (9)	No

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

Invoices

Field	Type	Nulls?
ShipName	nvarchar (40)	Yes
ShipAddress	nvarchar (60)	Yes
ShipCity	nvarchar (15)	Yes
ShipRegion	nvarchar (15)	Yes
ShipPostalCode	nvarchar (10)	Yes
ShipCountry	nvarchar (15)	Yes
CustomerID	nchar (5)	Yes
CustomerName	nvarchar (40)	No
Address	nvarchar (60)	Yes
City	nvarchar (15)	Yes
Region	nvarchar (15)	Yes
PostalCode	nvarchar (10)	Yes
Country	nvarchar (15)	Yes
Salesperson	nvarchar (31)	No
OrderID	int	No
OrderDate	datetime	Yes
RequiredDate	datetime	Yes
ShippedDate	datetime	Yes
ShipperName	nvarchar (40)	No
ProductID	int	No
ProductName	nvarchar (40)	No
UnitPrice	money	No
Quantity	smallint	No
Discount	real	No
ExtendedPrice	money	Yes
Freight	money	Yes

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

Order Details Extended

Field	Type	Nulls?
OrderID	int	No
ProductID	int	No
ProductName	nvarchar (40)	No
UnitPrice	money	No
Quantity	smallint	No
Discount	real	No
ExtendedPrice	money	Yes

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

Order Subtotals

Field	Type	Nulls?
OrderID	int	No
Subtotal	money	Yes

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

Orders Qry

Field	Type	Nulls?
OrderID	int	No
CustomerID	nchar (5)	Yes
EmployeeID	int	Yes
OrderDate	datetime	Yes
RequiredDate	datetime	Yes
ShippedDate	datetime	Yes
ShipVia	int	Yes
Freight	money	Yes
ShipName	nvarchar (40)	Yes
ShipAddress	nvarchar (60)	Yes
ShipCity	nvarchar (15)	Yes
ShipRegion	nvarchar (15)	Yes
ShipPostalCode	nvarchar (10)	Yes
ShipCountry	nvarchar (15)	Yes
CompanyName	nvarchar (40)	No
Address	nvarchar (60)	Yes
City	nvarchar (15)	Yes
Region	nvarchar (15)	Yes
PostalCode	nvarchar (10)	Yes
Country	nvarchar (15)	Yes

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

Product Sales for 1997

Field	Type	Nulls?
CategoryName	nvarchar (15)	No
ProductName	nvarchar (40)	No
ProductSales	money	Yes

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

Products Above Average Price

Field	Type	Nulls?
ProductName	nvarchar (40)	No
UnitPrice	money	Yes

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

Products by Category

Field	Type	Nulls?
CategoryName	nvarchar (15)	No
ProductName	nvarchar (40)	No
QuantityPerUnit	nvarchar (20)	Yes
UnitsInStock	smallint	Yes
Discontinued	bit	No

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

Quarterly Orders

Field	Type	Nulls?
CustomerID	nchar (5)	Yes
CompanyName	nvarchar (40)	Yes
City	nvarchar (15)	Yes
Country	nvarchar (15)	Yes

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'

Sales by Category

Field	Type	Nulls?
CategoryID	int	No
CategoryName	nvarchar (15)	No
ProductName	nvarchar (40)	No
ProductSales	money	Yes

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

Sales Totals by Amount

Field	Type	Nulls?
SaleAmount	money	Yes
OrderID	int	No
CompanyName	nvarchar (40)	No
ShippedDate	datetime	Yes

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

Summary of Sales by Quarter

Field	Type	Nulls?
ShippedDate	datetime	Yes
OrderID	int	No
Subtotal	money	Yes

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

Summary of Sales by Year

Field	Type	Nulls?
ShippedDate	datetime	Yes
OrderID	int	No
Subtotal	money	Yes

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

All Stored Procedures (Stored Procedures)

CustOrderHist

Parameter	Type	In/Out
@CustomerID	nchar (5)	IN

CREATE PROCEDURE CustOrderHist @CustomerID nchar(5)

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

CustOrdersDetail

Parameter	Type	In/Out
@OrderID	int	IN

CREATE PROCEDURE CustOrdersDetail @OrderID int

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

CustOrdersOrders

Parameter	Type	In/Out
@CustomerID	nchar (5)	IN

CREATE PROCEDURE CustOrdersOrders @CustomerID nchar(5)

SELECT OrderID,

OrderDate,

RequiredDate,

ShippedDate

FROM Orders

WHERE CustomerID = @CustomerID

ORDER BY OrderID

Employee Sales by Country

Parameter	Type	In/Out
@Beginning_Date	datetime	IN
@Ending_Date	datetime	IN

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

Sales by Year

Parameter	Type	In/Out
@Beginning_Date	datetime	IN
@Ending_Date	datetime	IN

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

SalesByCategory

Parameter	Type	In/Out
@CategoryName	nvarchar (15)	IN
@OrdYear	nvarchar (4)	IN

CREATE PROCEDURE SalesByCategory

@CategoryName nvarchar(15), @OrdYear nvarchar(4) = '1998'

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

Ten Most Expensive Products

No Parameters

create procedure "Ten Most Expensive Products" AS

SET ROWCOUNT 10

SELECT Products.ProductName AS TenMostExpensiveProducts, Products.UnitPrice

FROM Products

ORDER BY Products.UnitPrice DESC