Jump to content

Northwind Database


abs

Recommended Posts

HiI’m really enjoying learning SQL and the W3 Schools website has been really helpful. They frequently use the Northwind Database in their examples and I tried searching for the database but couldn’t find it. I really would like to download the database or if there are any scripts available that can create the table in SQL.If you guys have the Northwind Database or any other database I could use please post a link or a script.As for SQL software I’m planning to use the Microsoft SQL Server Express. If you guys know any other advance tutorials on SQL it would be helpful.

Link to comment
Share on other sites

Here's a script to set up the tables, views, and procedures from SQL Server 2000. I can't find a way to export the data to a SQL script.

CREATE TABLE [dbo].[Categories] (	[CategoryID] [int] IDENTITY (1, 1) NOT NULL ,	[CategoryName] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,	[Description] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,	[Picture] [image] NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOCREATE TABLE [dbo].[CustomerCustomerDemo] (	[CustomerID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,	[CustomerTypeID] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY]GOCREATE TABLE [dbo].[CustomerDemographics] (	[CustomerTypeID] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,	[CustomerDesc] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOCREATE TABLE [dbo].[Customers] (	[CustomerID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,	[CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,	[ContactName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,	[ContactTitle] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,	[Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,	[City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,	[Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,	[PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,	[Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,	[Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,	[Fax] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY]GOCREATE TABLE [dbo].[EmployeeTerritories] (	[EmployeeID] [int] NOT NULL ,	[TerritoryID] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY]GOCREATE TABLE [dbo].[Employees] (	[EmployeeID] [int] IDENTITY (1, 1) NOT NULL ,	[LastName] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,	[FirstName] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,	[Title] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,	[TitleOfCourtesy] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,	[birthDate] [datetime] NULL ,	[HireDate] [datetime] NULL ,	[Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,	[City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,	[Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,	[PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,	[Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,	[HomePhone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,	[Extension] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,	[Photo] [image] NULL ,	[Notes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,	[ReportsTo] [int] NULL ,	[PhotoPath] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOCREATE TABLE [dbo].[Order Details] (	[OrderID] [int] NOT NULL ,	[ProductID] [int] NOT NULL ,	[unitPrice] [money] NOT NULL ,	[Quantity] [smallint] NOT NULL ,	[Discount] [real] NOT NULL ) ON [PRIMARY]GOCREATE TABLE [dbo].[Orders] (	[OrderID] [int] IDENTITY (1, 1) NOT NULL ,	[CustomerID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,	[EmployeeID] [int] NULL ,	[OrderDate] [datetime] NULL ,	[RequiredDate] [datetime] NULL ,	[shippedDate] [datetime] NULL ,	[shipVia] [int] NULL ,	[Freight] [money] NULL ,	[shipName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,	[shipAddress] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,	[shipCity] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,	[shipRegion] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,	[shipPostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,	[shipCountry] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY]GOCREATE TABLE [dbo].[Products] (	[ProductID] [int] IDENTITY (1, 1) NOT NULL ,	[ProductName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,	[supplierID] [int] NULL ,	[CategoryID] [int] NULL ,	[QuantityPerUnit] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,	[unitPrice] [money] NULL ,	[unitsInStock] [smallint] NULL ,	[unitsOnOrder] [smallint] NULL ,	[ReorderLevel] [smallint] NULL ,	[Discontinued] [bit] NOT NULL ) ON [PRIMARY]GOCREATE TABLE [dbo].[Region] (	[RegionID] [int] NOT NULL ,	[RegionDescription] [nchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY]GOCREATE TABLE [dbo].[shippers] (	[shipperID] [int] IDENTITY (1, 1) NOT NULL ,	[CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,	[Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY]GOCREATE TABLE [dbo].[suppliers] (	[supplierID] [int] IDENTITY (1, 1) NOT NULL ,	[CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,	[ContactName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,	[ContactTitle] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,	[Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,	[City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,	[Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,	[PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,	[Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,	[Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,	[Fax] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,	[HomePage] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOCREATE TABLE [dbo].[Territories] (	[TerritoryID] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,	[TerritoryDescription] [nchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,	[RegionID] [int] NOT NULL ) ON [PRIMARY]GOALTER TABLE [dbo].[Categories] WITH NOCHECK ADD 	CONSTRAINT [PK_Categories] PRIMARY KEY  CLUSTERED 	(		[CategoryID]	)  ON [PRIMARY] GOALTER TABLE [dbo].[Customers] WITH NOCHECK ADD 	CONSTRAINT [PK_Customers] PRIMARY KEY  CLUSTERED 	(		[CustomerID]	)  ON [PRIMARY] GOALTER TABLE [dbo].[Employees] WITH NOCHECK ADD 	CONSTRAINT [PK_Employees] PRIMARY KEY  CLUSTERED 	(		[EmployeeID]	)  ON [PRIMARY] GOALTER TABLE [dbo].[Order Details] WITH NOCHECK ADD 	CONSTRAINT [PK_Order_Details] PRIMARY KEY  CLUSTERED 	(		[OrderID],		[ProductID]	)  ON [PRIMARY] GOALTER TABLE [dbo].[Orders] WITH NOCHECK ADD 	CONSTRAINT [PK_Orders] PRIMARY KEY  CLUSTERED 	(		[OrderID]	)  ON [PRIMARY] GOALTER TABLE [dbo].[Products] WITH NOCHECK ADD 	CONSTRAINT [PK_Products] PRIMARY KEY  CLUSTERED 	(		[ProductID]	)  ON [PRIMARY] GOALTER TABLE [dbo].[shippers] WITH NOCHECK ADD 	CONSTRAINT [PK_Shippers] PRIMARY KEY  CLUSTERED 	(		[shipperID]	)  ON [PRIMARY] GOALTER TABLE [dbo].[suppliers] WITH NOCHECK ADD 	CONSTRAINT [PK_Suppliers] PRIMARY KEY  CLUSTERED 	(		[supplierID]	)  ON [PRIMARY] GO CREATE  INDEX [CategoryName] ON [dbo].[Categories]([CategoryName]) ON [PRIMARY]GOALTER TABLE [dbo].[CustomerCustomerDemo] ADD 	CONSTRAINT [PK_CustomerCustomerDemo] PRIMARY KEY  NONCLUSTERED 	(		[CustomerID],		[CustomerTypeID]	)  ON [PRIMARY] GOALTER TABLE [dbo].[CustomerDemographics] ADD 	CONSTRAINT [PK_CustomerDemographics] PRIMARY KEY  NONCLUSTERED 	(		[CustomerTypeID]	)  ON [PRIMARY] GO CREATE  INDEX [City] ON [dbo].[Customers]([City]) ON [PRIMARY]GO CREATE  INDEX [CompanyName] ON [dbo].[Customers]([CompanyName]) ON [PRIMARY]GO CREATE  INDEX [PostalCode] ON [dbo].[Customers]([PostalCode]) ON [PRIMARY]GO CREATE  INDEX [Region] ON [dbo].[Customers]([Region]) ON [PRIMARY]GOALTER TABLE [dbo].[EmployeeTerritories] ADD 	CONSTRAINT [PK_EmployeeTerritories] PRIMARY KEY  NONCLUSTERED 	(		[EmployeeID],		[TerritoryID]	)  ON [PRIMARY] GOALTER TABLE [dbo].[Employees] ADD 	CONSTRAINT [CK_Birthdate] CHECK ([birthDate] < getdate())GO CREATE  INDEX [LastName] ON [dbo].[Employees]([LastName]) ON [PRIMARY]GO CREATE  INDEX [PostalCode] ON [dbo].[Employees]([PostalCode]) ON [PRIMARY]GOALTER TABLE [dbo].[Order Details] ADD 	CONSTRAINT [DF_Order_Details_UnitPrice] DEFAULT (0) FOR [unitPrice],	CONSTRAINT [DF_Order_Details_Quantity] DEFAULT (1) FOR [Quantity],	CONSTRAINT [DF_Order_Details_Discount] DEFAULT (0) FOR [Discount],	CONSTRAINT [CK_Discount] CHECK ([Discount] >= 0 and [Discount] <= 1),	CONSTRAINT [CK_Quantity] CHECK ([Quantity] > 0),	CONSTRAINT [CK_UnitPrice] CHECK ([unitPrice] >= 0)GO CREATE  INDEX [OrderID] ON [dbo].[Order Details]([OrderID]) ON [PRIMARY]GO CREATE  INDEX [OrdersOrder_Details] ON [dbo].[Order Details]([OrderID]) ON [PRIMARY]GO CREATE  INDEX [ProductID] ON [dbo].[Order Details]([ProductID]) ON [PRIMARY]GO CREATE  INDEX [ProductsOrder_Details] ON [dbo].[Order Details]([ProductID]) ON [PRIMARY]GOALTER TABLE [dbo].[Orders] ADD 	CONSTRAINT [DF_Orders_Freight] DEFAULT (0) FOR [Freight]GO CREATE  INDEX [CustomerID] ON [dbo].[Orders]([CustomerID]) ON [PRIMARY]GO CREATE  INDEX [CustomersOrders] ON [dbo].[Orders]([CustomerID]) ON [PRIMARY]GO CREATE  INDEX [EmployeeID] ON [dbo].[Orders]([EmployeeID]) ON [PRIMARY]GO CREATE  INDEX [EmployeesOrders] ON [dbo].[Orders]([EmployeeID]) ON [PRIMARY]GO CREATE  INDEX [OrderDate] ON [dbo].[Orders]([OrderDate]) ON [PRIMARY]GO CREATE  INDEX [shippedDate] ON [dbo].[Orders]([shippedDate]) ON [PRIMARY]GO CREATE  INDEX [shippersOrders] ON [dbo].[Orders]([shipVia]) ON [PRIMARY]GO CREATE  INDEX [shipPostalCode] ON [dbo].[Orders]([shipPostalCode]) ON [PRIMARY]GOALTER TABLE [dbo].[Products] ADD 	CONSTRAINT [DF_Products_UnitPrice] DEFAULT (0) FOR [unitPrice],	CONSTRAINT [DF_Products_UnitsInStock] DEFAULT (0) FOR [unitsInStock],	CONSTRAINT [DF_Products_UnitsOnOrder] DEFAULT (0) FOR [unitsOnOrder],	CONSTRAINT [DF_Products_ReorderLevel] DEFAULT (0) FOR [ReorderLevel],	CONSTRAINT [DF_Products_Discontinued] DEFAULT (0) FOR [Discontinued],	CONSTRAINT [CK_Products_UnitPrice] CHECK ([unitPrice] >= 0),	CONSTRAINT [CK_ReorderLevel] CHECK ([ReorderLevel] >= 0),	CONSTRAINT [CK_UnitsInStock] CHECK ([unitsInStock] >= 0),	CONSTRAINT [CK_UnitsOnOrder] CHECK ([unitsOnOrder] >= 0)GO CREATE  INDEX [CategoriesProducts] ON [dbo].[Products]([CategoryID]) ON [PRIMARY]GO CREATE  INDEX [CategoryID] ON [dbo].[Products]([CategoryID]) ON [PRIMARY]GO CREATE  INDEX [ProductName] ON [dbo].[Products]([ProductName]) ON [PRIMARY]GO CREATE  INDEX [supplierID] ON [dbo].[Products]([supplierID]) ON [PRIMARY]GO CREATE  INDEX [suppliersProducts] ON [dbo].[Products]([supplierID]) ON [PRIMARY]GOALTER TABLE [dbo].[Region] ADD 	CONSTRAINT [PK_Region] PRIMARY KEY  NONCLUSTERED 	(		[RegionID]	)  ON [PRIMARY] GO CREATE  INDEX [CompanyName] ON [dbo].[suppliers]([CompanyName]) ON [PRIMARY]GO CREATE  INDEX [PostalCode] ON [dbo].[suppliers]([PostalCode]) ON [PRIMARY]GOALTER TABLE [dbo].[Territories] ADD 	CONSTRAINT [PK_Territories] PRIMARY KEY  NONCLUSTERED 	(		[TerritoryID]	)  ON [PRIMARY] GOALTER TABLE [dbo].[CustomerCustomerDemo] ADD 	CONSTRAINT [FK_CustomerCustomerDemo] FOREIGN KEY 	(		[CustomerTypeID]	) REFERENCES [dbo].[CustomerDemographics] (		[CustomerTypeID]	),	CONSTRAINT [FK_CustomerCustomerDemo_Customers] FOREIGN KEY 	(		[CustomerID]	) REFERENCES [dbo].[Customers] (		[CustomerID]	)GOALTER TABLE [dbo].[EmployeeTerritories] ADD 	CONSTRAINT [FK_EmployeeTerritories_Employees] FOREIGN KEY 	(		[EmployeeID]	) REFERENCES [dbo].[Employees] (		[EmployeeID]	),	CONSTRAINT [FK_EmployeeTerritories_Territories] FOREIGN KEY 	(		[TerritoryID]	) REFERENCES [dbo].[Territories] (		[TerritoryID]	)GOALTER TABLE [dbo].[Employees] ADD 	CONSTRAINT [FK_Employees_Employees] FOREIGN KEY 	(		[ReportsTo]	) REFERENCES [dbo].[Employees] (		[EmployeeID]	)GOALTER TABLE [dbo].[Order Details] ADD 	CONSTRAINT [FK_Order_Details_Orders] FOREIGN KEY 	(		[OrderID]	) REFERENCES [dbo].[Orders] (		[OrderID]	),	CONSTRAINT [FK_Order_Details_Products] FOREIGN KEY 	(		[ProductID]	) REFERENCES [dbo].[Products] (		[ProductID]	)GOALTER TABLE [dbo].[Orders] ADD 	CONSTRAINT [FK_Orders_Customers] FOREIGN KEY 	(		[CustomerID]	) REFERENCES [dbo].[Customers] (		[CustomerID]	),	CONSTRAINT [FK_Orders_Employees] FOREIGN KEY 	(		[EmployeeID]	) REFERENCES [dbo].[Employees] (		[EmployeeID]	),	CONSTRAINT [FK_Orders_Shippers] FOREIGN KEY 	(		[shipVia]	) REFERENCES [dbo].[shippers] (		[shipperID]	)GOALTER TABLE [dbo].[Products] ADD 	CONSTRAINT [FK_Products_Categories] FOREIGN KEY 	(		[CategoryID]	) REFERENCES [dbo].[Categories] (		[CategoryID]	),	CONSTRAINT [FK_Products_Suppliers] FOREIGN KEY 	(		[supplierID]	) REFERENCES [dbo].[suppliers] (		[supplierID]	)GOALTER TABLE [dbo].[Territories] ADD 	CONSTRAINT [FK_Territories_Region] FOREIGN KEY 	(		[RegionID]	) REFERENCES [dbo].[Region] (		[RegionID]	)GOSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOcreate view "Customer and Suppliers by City" ASSELECT City, CompanyName, ContactName, 'Customers' AS Relationship FROM CustomersUNION SELECT City, CompanyName, ContactName, 'Suppliers'FROM Suppliers--ORDER BY City, CompanyNameGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOcreate view "Alphabetical list of products" ASSELECT Products.*, Categories.CategoryNameFROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryIDWHERE (((Products.Discontinued)=0))GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOcreate view "Current Product List" ASSELECT Product_List.ProductID, Product_List.ProductNameFROM Products AS Product_ListWHERE (((Product_List.Discontinued)=0))--ORDER BY Product_List.ProductNameGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOcreate view "Orders Qry" ASSELECT 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.CountryFROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerIDGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOcreate view "Products Above Average Price" ASSELECT Products.ProductName, Products.UnitPriceFROM ProductsWHERE Products.UnitPrice>(SELECT AVG(UnitPrice) From Products)--ORDER BY Products.UnitPrice DESCGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOcreate view "Products by Category" ASSELECT Categories.CategoryName, Products.ProductName, Products.QuantityPerUnit, Products.UnitsInStock, Products.DiscontinuedFROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryIDWHERE Products.Discontinued <> 1--ORDER BY Categories.CategoryName, Products.ProductNameGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOcreate view "Quarterly Orders" ASSELECT DISTINCT Customers.CustomerID, Customers.CompanyName, Customers.City, Customers.CountryFROM Customers RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerIDWHERE Orders.OrderDate BETWEEN '19970101' And '19971231'GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOcreate view Invoices ASSELECT 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.FreightFROM 	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.ShipViaGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOcreate view "Order Details Extended" ASSELECT "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 ExtendedPriceFROM Products INNER JOIN "Order Details" ON Products.ProductID = "Order Details".ProductID--ORDER BY "Order Details".OrderIDGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOcreate view "Order Subtotals" ASSELECT "Order Details".OrderID, Sum(CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100) AS SubtotalFROM "Order Details"GROUP BY "Order Details".OrderIDGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOcreate view "Product Sales for 1997" ASSELECT Categories.CategoryName, Products.ProductName, Sum(CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100) AS ProductSalesFROM (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".ProductIDWHERE (((Orders.ShippedDate) Between '19970101' And '19971231'))GROUP BY Categories.CategoryName, Products.ProductNameGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOcreate view "Category Sales for 1997" ASSELECT "Product Sales for 1997".CategoryName, Sum("Product Sales for 1997".ProductSales) AS CategorySalesFROM "Product Sales for 1997"GROUP BY "Product Sales for 1997".CategoryNameGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOcreate view "Sales Totals by Amount" ASSELECT "Order Subtotals".Subtotal AS SaleAmount, Orders.OrderID, Customers.CompanyName, Orders.ShippedDateFROM 	Customers INNER JOIN 		(Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID) 	ON Customers.CustomerID = Orders.CustomerIDWHERE ("Order Subtotals".Subtotal >2500) AND (Orders.ShippedDate BETWEEN '19970101' And '19971231')GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOcreate view "Sales by Category" ASSELECT Categories.CategoryID, Categories.CategoryName, Products.ProductName, 	Sum("Order Details Extended".ExtendedPrice) AS ProductSalesFROM 	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.CategoryIDWHERE Orders.OrderDate BETWEEN '19970101' And '19971231'GROUP BY Categories.CategoryID, Categories.CategoryName, Products.ProductName--ORDER BY Products.ProductNameGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOcreate view "Summary of Sales by Quarter" ASSELECT Orders.ShippedDate, Orders.OrderID, "Order Subtotals".SubtotalFROM Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderIDWHERE Orders.ShippedDate IS NOT NULL--ORDER BY Orders.ShippedDateGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOcreate view "Summary of Sales by Year" ASSELECT Orders.ShippedDate, Orders.OrderID, "Order Subtotals".SubtotalFROM Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderIDWHERE Orders.ShippedDate IS NOT NULL--ORDER BY Orders.ShippedDateGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOCREATE PROCEDURE CustOrderHist @CustomerID nchar(5)ASSELECT ProductName, Total=SUM(Quantity)FROM Products P, [Order Details] OD, Orders O, Customers CWHERE C.CustomerID = @CustomerIDAND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductIDGROUP BY ProductNameGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOCREATE PROCEDURE CustOrdersDetail @OrderID intASSELECT 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] OdWHERE Od.ProductID = P.ProductID and Od.OrderID = @OrderIDGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOCREATE PROCEDURE CustOrdersOrders @CustomerID nchar(5)ASSELECT OrderID, 	OrderDate,	RequiredDate,	ShippedDateFROM OrdersWHERE CustomerID = @CustomerIDORDER BY OrderIDGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOcreate procedure "Employee Sales by Country" @Beginning_Date DateTime, @Ending_Date DateTime ASSELECT Employees.Country, Employees.LastName, Employees.FirstName, Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal AS SaleAmountFROM Employees INNER JOIN 	(Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID) 	ON Employees.EmployeeID = Orders.EmployeeIDWHERE Orders.ShippedDate Between @Beginning_Date And @Ending_DateGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOcreate procedure "Sales by Year" 	@Beginning_Date DateTime, @Ending_Date DateTime ASSELECT Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal, DATENAME(yy,ShippedDate) AS YearFROM Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderIDWHERE Orders.ShippedDate Between @Beginning_Date And @Ending_DateGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOCREATE PROCEDURE SalesByCategory    @CategoryName nvarchar(15), @OrdYear nvarchar(4) = '1998'ASIF @OrdYear != '1996' AND @OrdYear != '1997' AND @OrdYear != '1998' BEGIN	SELECT @OrdYear = '1998'ENDSELECT 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 CWHERE 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) = @OrdYearGROUP BY ProductNameORDER BY ProductNameGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOcreate procedure "Ten Most Expensive Products" ASSET ROWCOUNT 10SELECT Products.ProductName AS TenMostExpensiveProducts, Products.UnitPriceFROM ProductsORDER BY Products.UnitPrice DESCGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO

Link to comment
Share on other sites

  • 1 year later...
  • 2 years later...

Can someone to told me wich version of Northwind database is used for w3school SQL Tutorial, because every version of Northwind database that I find is not same as database in tutorial?

 

For example. Diference is in table Customers, where in Tutorial table have columns CustomerID, CustomerName, ContactName, Address, City, PostalCode and Country. And table Customer in almost all Northwind database wich I find have columns CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax. Also, in tutorial table Customers CustomerID is number, and in my database, in table Customers CustomerID is string of five letters.

Link to comment
Share on other sites

  • 4 months later...
  • 6 years later...
  • Funce locked this topic
Guest
This topic is now closed to further replies.
×
×
  • Create New...