【转】SQL_Server2000示例数据库NorthWind的存储过程和视图分析

存储过程：
1。CustOrderHist：
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
功能：客户名为{@CustomerID nchar(5)}所订购的产品名称，产品数量
2。CustOrdersDetail
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
功能：订单名为{@OrderID}的，产品名，单价，数量，打折，总价钱
3。CustOrdersOrders
CREATE PROCEDURE CustOrdersOrders @CustomerID nchar(5)
AS
SELECT OrderID,
 OrderDate,
 RequiredDate,
 ShippedDate
FROM Orders
WHERE CustomerID = @CustomerID
ORDER BY OrderID
GO
功能：客户名为{@CustomerID nchar(5)}的，定单ID，订单日期，预计到达日期，运送日期

视图：
1。Alphabetical list of products
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))
功能：产品Products.*，和加上所属种类的名字（name）
2。Category Sales for 1997
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
功能：1997卖出的种类总价钱
3。Current Product List
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
功能：不打折的物品的{产品id，产品名；}
4。Customer and Suppliers by City
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
功能：客户，和供应商的{城市，公司名，联系名，类别}
5。Invoices
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
功能：运货商表，产品表，雇员表，客户表，订单明细表，订单表连接；
6。Order Details Extended
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
功能：产品表，订单明细表连接；选择：订单ID，产品id，产品名，单价，订购数量，折扣，总价；
7。Order Subtotals
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
功能：订单明细表；订单id，订单总价
8。Orders Qry
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
功能：客户表和订单表连接；选择：订单id，客户id，雇员id，订单日期，预计日期，。。。。。。。
9。Product Sales for 1997
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
功能：种类表，产品表，订单表，订单明细表连接；订单日期在19970101' And '19971231；的；选择种类名，产品名，产品总价
10。Products Above Average Price
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
功能：产品表；选择单价大于平均价格的{产品名，单价}
11。Products by Category
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
功能：种类名，产品名连接；选择不终止的的{种类名，产品名，单价，库存量，终止}
12。Quarterly Orders
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'
功能：客户表，订单表连接；选择日期在19970101' And '19971231的{分类客户id，客户公司名，客户城市名客户国家}
13。Sales by Category
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
功 能：种类表，产品表，订单表，Order Details Extended连接；选择定单日期在19970101' And '19971231的；按种类id，种类名，产品名分类的{种类id，种类名，产品名，{所有订单（订单某物品总价）}AS ProductSales}
14.。ales Totals by Amount
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')
功 能：客户表，订单表，Order Subtotals连接；订单运送日期在1997年内且("Order Subtotals".Subtotal（某订单总价） >2500)的，{"Order Subtotals".Subtotal，订单id，客户公司名，订单运送日期}
15。Summary of Sales by Quarter
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
功能：订单，"Order Subtotals"（订单总价）连接；选择订单日期不为空的{订单日期，订单id，订单总价}
16。Summary of Sales by Year
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
功能：订单，"Order Subtotals"连接；选择订单日期不为空的{订单日期，订单id，订单总价}
17。sysconstraints
CREATE VIEW sysconstraints AS
SELECT
 constid = convert(int, id),
 id = convert(int, parent_obj),
 colid = convert(smallint, info),
 spare1 = convert(tinyint, 0),
 status = convert(int,
   CASE xtype
    WHEN 'PK' THEN 1 WHEN 'UQ' THEN 2 WHEN 'F' THEN 3
    WHEN 'C' THEN 4 WHEN 'D' THEN 5 ELSE 0 END
   + CASE WHEN info != 0   -- CNST_COLUMN / CNST_TABLE
     THEN (16) ELSE (32) END
   + CASE WHEN (status & 16)!=0 -- CNST_CLINDEX
     THEN (512) ELSE 0 END
   + CASE WHEN (status & 32)!=0 -- CNST_NCLINDEX
     THEN (1024) ELSE 0 END
   + (2048)      -- CNST_NOTDEFERRABLE
   + CASE WHEN (status & 256)!=0 -- CNST_DISABLE
     THEN (16384) ELSE 0 END
   + CASE WHEN (status & 512)!=0 -- CNST_ENABLE
     THEN (32767) ELSE 0 END
   + CASE WHEN (status & 4)!=0  -- CNST_NONAME
     THEN (131072) ELSE 0 END
   + CASE WHEN (status & 1)!=0  -- CNST_NEW
     THEN (1048576) ELSE 0 END
   + CASE WHEN (status & 1024)!=0 -- CNST_REPL
     THEN (2097152) ELSE 0 END),
 actions = convert(int,  4096),
 error = convert(int, 0)
FROM sysobjects WHERE xtype in ('C', 'F', 'PK', 'UQ', 'D')
     AND (status & 64) = 0
功能：不会！！！！！！！
18。syssegments
CREATE VIEW syssegments (segment, name, status) AS
 SELECT  0, 'system'     , 0  UNION
 SELECT 1, 'default'    , 1  UNION
 SELECT 2, 'logsegment' , 0
功能：增添三条数据；