SQL _Server2000 example database NorthWind stored procedure and View Analysis

Stored Procedure:
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
Function: name of the product ordered by the customer name {@ CustomerID nchar (5)}, product quantity
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
Function: product name, unit price, quantity, discount, and total price of the order {@ OrderID}
3. CustOrdersOrders
Create procedure CustOrdersOrders @ CustomerID nchar (5)
AS
SELECT OrderID,
OrderDate,
RequiredDate,
ShippedDate
FROM Orders
WHERE CustomerID = @ CustomerID
Order by OrderID
GO
Function: the customer name is {@ CustomerID nchar (5)}, the Order ID, the order date, the expected arrival date, and the shipping date.

View:
1. Alphabetical list of products
Create view "Alphabetical list of products"
SELECT Products. *, Categories. CategoryName
FROM Categories inner join Products ON Categories. CategoryID = Products. CategoryID
WHERE (Products. Discontinued) = 0 ))
Function: products. *, and the name of the product category (name)
2. CATEGORY sales for 1997
Create view & quot; Category sales for 1997 & quot;
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
Function: 1997 of the total price of the sold category
3. Current product list
Create View "current product list"
Select product_list.productid, product_list.productname
From products as product_list
Where (product_list.discontinued) = 0 ))
-- Order by product_list.productname
Function: {product ID and Product Name of the non-discounted item ;}
4. Customer and suppliers by city
Create View "customer and suppliers by city"
Select city, companyName, contactname, 'customer' as relationship
From MERs
Union
Select city, companyName, contactname, 'suppliers'
From suppliers
-- Order by city, companyName
Function: customer, and supplier's {city, company name, contact name, category}
5. Invoices
Create view invoices
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
Function: goods supplier table, product table, employee table, customer table, order list, and order table connection;
6. Order Details Extended
Create view "Order Details Extended"
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
Function: connects to the product table and order list. Options: Order ID, product id, product name, unit price, order quantity, discount, and total price;
7. Order Subtotals
Create view "Order Subtotals"
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
Function: Order List; Order id; order total price
8. Orders Qry
Create view "Orders Qry"
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
Function: connect a customer table to an order table. Select order id, customer id, employee id, order date, estimated date, and so on ,.......
9. Product Sales for 1997
Create view "Product Sales for 1997"
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 '000000' And '000000 '))
Group by Categories. CategoryName, Products. ProductName
Function: Category Table, product table, order table, And order list connection. The order date is between 19970101 And 19971231. Select the category name, product name, And total product price.
10. Products Above Average Price
Create view "Products Above Average Price"
SELECT Products. ProductName, Products. UnitPrice
FROM Products
WHERE Products. UnitPrice> (select avg (UnitPrice) From Products)
-- Order by Products. UnitPrice DESC
Function: Product table. Select {product name, unit price} whose unit price is greater than the average price}
11. Products by Category
Create view "Products by Category"
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
Function: category name, product name connection; select non-terminated {category name, product name, unit price, inventory, termination}
12. Quarterly Orders
Create view "Quarterly Orders"
Select distinct MERs. CustomerID, Customers. CompanyName, Customers. City, counmers. Country
FROM Customers right join Orders ON Customers. CustomerID = Orders. CustomerID
WHERE Orders. OrderDate BETWEEN '20160301' And '20160301'
Function: connects to the customer table And order table. Select {category customer id, customer company name, customer city name, And customer country on 'and' 19970101}
13. Sales by Category
Create view "Sales by Category"
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 '20160301' And '20160301'
Group by Categories. CategoryID, Categories. CategoryName, Products. ProductName
-- Order by Products. ProductName
Function: Category Table, product table, Order table, And Order Details Extended connection; select the Order date in 19970101 And 19971231; by category id, category name, product Name category {category id, category name, product name, {all orders (total price of an item in the order)} AS ProductSales}
14 .. Ales Totals by Amount
Create view "Sales Totals by Amount"
SELECT "Order Subtotals". Subtotal AS SaleAmount, Orders. OrderID, Customers. CompanyName, Orders. ShippedDate
FROM MERs 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 '000000' And '000000 ')
Functions: Customer table, Order table, and Order Subtotals connection; Order shipping date within 1997 and ("Order Subtotals ". for Subtotal (total price of an Order)> 2500), {"Order Subtotals ". subtotal, order id, customer company name, order shipping date}
15. Summary of Sales by Quarter
Create view "Summary of Sales by Quarter"
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
Function: Order, "Order Subtotals" (total Order price) connection; select {Order date, Order id, total Order price} with the Order date not blank}
16. Summary of Sales by Year
Create view "Summary of Sales by Year"
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
Function: Order, "Order Subtotals" connection; select {Order date, Order id, Order total price} where the Order date is not blank}
17. Sysconstraints
Create view sysconstraints
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
Function: No !!!!!!!
18. Syssegments
Create view syssegments (segment, name, status)
Select 0, 'system', 0 Union
Select 1, 'default', 1 Union
Select 2, 'logsegment', 0
Function: adds three pieces of data;