Database : Northwind
Report created by EasyStruct 4.5 on 8/16/2004
  Tables Views Stored Procedures  Indexes
 Views


Products by CategoryTop
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


DependenciesTop
Table NameColumn Name
ProductsProductName
ProductsCategoryID
ProductsQuantityPerUnit
ProductsUnitsInStock
ProductsDiscontinued
CategoriesCategoryID
CategoriesCategoryName
TablesViewsStored ProceduresIndexes


Quarterly OrdersTop
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'


DependenciesTop
Table NameColumn Name
OrdersCustomerID
OrdersOrderDate
CustomersCustomerID
CustomersCompanyName
CustomersCity
CustomersCountry
TablesViewsStored ProceduresIndexes


InvoicesTop
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


DependenciesTop
Table NameColumn Name
OrdersOrderID
OrdersCustomerID
OrdersEmployeeID
OrdersOrderDate
OrdersRequiredDate
OrdersShippedDate
OrdersShipVia
OrdersFreight
OrdersShipName
OrdersShipAddress
OrdersShipCity
OrdersShipRegion
OrdersShipPostalCode
OrdersShipCountry
ProductsProductID
ProductsProductName
Order DetailsOrderID
Order DetailsProductID
Order DetailsUnitPrice
Order DetailsQuantity
Order DetailsDiscount
EmployeesEmployeeID
EmployeesLastName
EmployeesFirstName
CustomersCustomerID
CustomersCompanyName
CustomersAddress
CustomersCity
CustomersRegion
CustomersPostalCode
CustomersCountry
ShippersShipperID
ShippersCompanyName
TablesViewsStored ProceduresIndexes


Order Details ExtendedTop
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


DependenciesTop
Table NameColumn Name
ProductsProductID
ProductsProductName
Order DetailsOrderID
Order DetailsProductID
Order DetailsUnitPrice
Order DetailsQuantity
Order DetailsDiscount
TablesViewsStored ProceduresIndexes


Order SubtotalsTop
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


DependenciesTop
Table NameColumn Name
Order DetailsOrderID
Order DetailsUnitPrice
Order DetailsQuantity
Order DetailsDiscount
TablesViewsStored ProceduresIndexes