SCHEMA Northwind, 19 TABLES, 9 PROCEDURES, 16 VIEWS
INFO

TABLE dbo Categories
  CategoryID int not null
  CategoryName nvarchar(15) not null
  Description ntext null
  Picture image null
INDEX PK_Categories UNIQUE CLUSTERED COLUMNS ( CategoryID )
INDEX CategoryName COLUMNS ( CategoryName )
TABLE INFO


TABLE dbo CustomerCustomerDemo
  CustomerID nchar(5) not null
  CustomerTypeID nchar(10) not null
INDEX PK_CustomerCustomerDemo UNIQUE COLUMNS ( CustomerID CustomerTypeID )
TABLE INFO


TABLE dbo CustomerDemographics
  CustomerTypeID nchar(10) not null
  CustomerDesc ntext null
INDEX PK_CustomerDemographics UNIQUE COLUMNS ( CustomerTypeID )
TABLE INFO


TABLE dbo Customers
  CustomerID nchar(5) not null
  CompanyName nvarchar(40) not null
  ContactName nvarchar(30) null
  ContactTitle nvarchar(30) null
  Address nvarchar(60) null
  City nvarchar(15) null
  Region nvarchar(15) null
  PostalCode nvarchar(10) null
  Country nvarchar(15) null
  Phone nvarchar(24) null
  Fax nvarchar(24) null
INDEX PK_Customers UNIQUE CLUSTERED COLUMNS ( CustomerID )
INDEX City COLUMNS ( City )
INDEX CompanyName COLUMNS ( CompanyName )
INDEX PostalCode COLUMNS ( PostalCode )
INDEX Region COLUMNS ( Region )
TABLE INFO


TABLE dbo decimal
  a decimal(5,2) null
TABLE INFO


TABLE dbo dtproperties
  id int not null
  objectid int null
  property varchar(64) not null
  value varchar(255) null
  uvalue nvarchar(255) null
  lvalue image null
  version int not null
INDEX pk_dtproperties UNIQUE CLUSTERED COLUMNS ( id property )
TABLE INFO


TABLE dbo Employees
  EmployeeID int not null
  LastName nvarchar(20) not null
  FirstName nvarchar(10) not null
  Title nvarchar(30) null
  TitleOfCourtesy nvarchar(25) null
  BirthDate datetime null
  HireDate datetime null
  Address nvarchar(60) null
  City nvarchar(15) null
  Region nvarchar(15) null
  PostalCode nvarchar(10) null
  Country nvarchar(15) null
  HomePhone nvarchar(24) null
  Extension nvarchar(4) null
  Photo image null
  Notes ntext null
  ReportsTo int null
  PhotoPath nvarchar(255) null
INDEX PK_Employees UNIQUE CLUSTERED COLUMNS ( EmployeeID )
INDEX LastName COLUMNS ( LastName )
INDEX PostalCode COLUMNS ( PostalCode )
TABLE INFO


TABLE dbo EmployeeTerritories
  EmployeeID int not null
  TerritoryID nvarchar(20) not null
INDEX PK_EmployeeTerritories UNIQUE COLUMNS ( EmployeeID TerritoryID )
TABLE INFO


TABLE dbo jello
  a int null
  b int null
  c int null
TRIGGER abcdef FOR insert
TABLE INFO


TABLE dbo jellonum
  a float(48) null
TABLE INFO


TABLE dbo one_two
  c int null
TABLE INFO


TABLE dbo Order_Details
  OrderID int not null
  ProductID int not null
  UnitPrice money not null
  Quantity smallint not null
  Discount real not null
INDEX PK_Order_Details UNIQUE CLUSTERED COLUMNS ( OrderID ProductID )
INDEX OrderID COLUMNS ( OrderID )
INDEX OrdersOrder_Details COLUMNS ( OrderID )
INDEX ProductID COLUMNS ( ProductID )
INDEX ProductsOrder_Details COLUMNS ( ProductID )
TABLE INFO


TABLE dbo Orders
  OrderID int not null
  CustomerID nchar(5) null
  EmployeeID int null
  OrderDate datetime null
  RequiredDate datetime null
  ShippedDate datetime null
  ShipVia int null
  Freight money null
  ShipName nvarchar(40) null
  ShipAddress nvarchar(60) null
  ShipCity nvarchar(15) null
  ShipRegion nvarchar(15) null
  ShipPostalCode nvarchar(10) null
  ShipCountry nvarchar(15) null
INDEX PK_Orders UNIQUE CLUSTERED COLUMNS ( OrderID )
INDEX CustomerID COLUMNS ( CustomerID )
INDEX CustomersOrders COLUMNS ( CustomerID )
INDEX EmployeeID COLUMNS ( EmployeeID )
INDEX EmployeesOrders COLUMNS ( EmployeeID )
INDEX OrderDate COLUMNS ( OrderDate )
INDEX ShippedDate COLUMNS ( ShippedDate )
INDEX ShippersOrders COLUMNS ( ShipVia )
INDEX ShipPostalCode COLUMNS ( ShipPostalCode )
TABLE INFO


TABLE dbo Products
  ProductID int not null
  ProductName nvarchar(40) not null
  SupplierID int null
  CategoryID int null
  QuantityPerUnit nvarchar(20) null
  UnitPrice money null
  UnitsInStock smallint null
  UnitsOnOrder smallint null
  ReorderLevel smallint null
  Discontinued bit not null
INDEX PK_Products UNIQUE CLUSTERED COLUMNS ( ProductID )
INDEX CategoriesProducts COLUMNS ( CategoryID )
INDEX CategoryID COLUMNS ( CategoryID )
INDEX ProductName COLUMNS ( ProductName )
INDEX SupplierID COLUMNS ( SupplierID )
INDEX SuppliersProducts COLUMNS ( SupplierID )
TABLE INFO


TABLE dbo Region
  RegionID int not null
  RegionDescription nchar(50) not null
INDEX PK_Region UNIQUE COLUMNS ( RegionID )
TABLE INFO


TABLE dbo Shippers
  ShipperID int not null
  CompanyName nvarchar(40) not null
  Phone nvarchar(24) null
INDEX PK_Shippers UNIQUE CLUSTERED COLUMNS ( ShipperID )
TABLE INFO


TABLE dbo Suppliers
  SupplierID int not null
  CompanyName nvarchar(40) not null
  ContactName nvarchar(30) null
  ContactTitle nvarchar(30) null
  Address nvarchar(60) null
  City nvarchar(15) null
  Region nvarchar(15) null
  PostalCode nvarchar(10) null
  Country nvarchar(15) null
  Phone nvarchar(24) null
  Fax nvarchar(24) null
  HomePage ntext null
INDEX PK_Suppliers UNIQUE CLUSTERED COLUMNS ( SupplierID )
INDEX CompanyName COLUMNS ( CompanyName )
INDEX PostalCode COLUMNS ( PostalCode )
TABLE INFO


TABLE dbo Territories
  TerritoryID nvarchar(20) not null
  TerritoryDescription nchar(50) not null
  RegionID int not null
INDEX PK_Territories UNIQUE COLUMNS ( TerritoryID )
TABLE INFO


TABLE dbo typetest
  a decimal(7,2) null
  b numeric(7,2) null
  c real null
  d money null
  e smallmoney null
  f int null
  g smallint null
  h tinyint null
  i bit not null
  j real null
  k varchar(7) null
  l nvarchar(7) null
  m char(7) null
  n nchar(7) null
  o float(48) null
TABLE INFO


PROCEDURES (9)

PROCEDURE 'aaa'

create procedure aaa as
begin
   select 'in procedure aaa'
   exec bbb
end


PROCEDURE 'bbb'

create procedure bbb as
begin
   select 'in procedure bbb'
end

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

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

PROCEDURE 'CustOrdersOrders'


CREATE PROCEDURE CustOrdersOrders @CustomerID nchar(5)
AS
SELECT OrderID, 
	OrderDate,
	RequiredDate,
	ShippedDate
FROM Orders
WHERE CustomerID = @CustomerID
ORDER BY OrderID

PROCEDURE 'Employee Sales by Country'

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

PROCEDURE 'Sales by Year'

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

PROCEDURE 'SalesByCategory'

CREATE PROCEDURE SalesByCategory
    @CategoryName nvarchar(15), @OrdYear nvarchar(4) = '1998'
AS
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

PROCEDURE 'Ten Most Expensive Products'

create procedure "Ten Most Expensive Products" AS
SET ROWCOUNT 10
SELECT Products.ProductName AS TenMostExpensiveProducts, Products.UnitPrice
FROM Products
ORDER BY Products.UnitPrice DESC


VIEWS (16)
VIEW '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))

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


END OF SCHEMA