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


Product Sales for 1997 Top
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



Dependencies Top
Table Name Column Name
Orders OrderID
Orders ShippedDate
Products ProductID
Products ProductName
Products CategoryID
Order Details OrderID
Order Details ProductID
Order Details UnitPrice
Order Details Quantity
Order Details Discount
Categories CategoryID
Categories CategoryName
Tables Views Stored Procedures Indexes



Category Sales for 1997 Top
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



Dependencies Top
Table Name Column Name
Product Sales for 1997 CategoryName
Product Sales for 1997 ProductSales
Tables Views Stored Procedures Indexes



Sales by Category Top
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



Dependencies Top
Table Name Column Name
Orders OrderID
Orders OrderDate
Products ProductID
Products ProductName
Products CategoryID
Order Details Extended OrderID
Order Details Extended ProductID
Order Details Extended ExtendedPrice
Categories CategoryID
Categories CategoryName
Tables Views Stored Procedures Indexes



Sales Totals by Amount Top
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')



Dependencies Top
Table Name Column Name
Orders OrderID
Orders CustomerID
Orders ShippedDate
Order Subtotals OrderID
Order Subtotals Subtotal
Customers CustomerID
Customers CompanyName
Tables Views Stored Procedures Indexes



Summary of Sales by Quarter Top
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



Dependencies Top
Table Name Column Name
Orders OrderID
Orders ShippedDate
Order Subtotals OrderID
Order Subtotals Subtotal
Tables Views Stored Procedures Indexes