abs Posted March 5, 2009 Share Posted March 5, 2009 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 More sharing options...
Pollux Posted March 6, 2009 Share Posted March 6, 2009 I believe the NorthWind database is an example database that comes with MS Access. It don't know if it's available in SQL Server. Link to comment Share on other sites More sharing options...
justsomeguy Posted March 6, 2009 Share Posted March 6, 2009 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 More sharing options...
abs Posted March 6, 2009 Author Share Posted March 6, 2009 Thanks for replyingI found the database sample on Microsoft http://www.microsoft.com/downloads/details...;displaylang=enJustSomeGuy thanks for the script but I not familiar with sql server need to learn. I have worked with MySQL toad and SQL I Oracle Link to comment Share on other sites More sharing options...
Guest derryart Posted December 6, 2010 Share Posted December 6, 2010 Northwind is available to download from here:http://www.joeblogs.ie/blog/post/Northwind...05-Express.aspx Link to comment Share on other sites More sharing options...
Vladd Posted November 26, 2013 Share Posted November 26, 2013 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 More sharing options...
justsomeguy Posted November 26, 2013 Share Posted November 26, 2013 They might have changed the database structure for the tutorials, it doesn't look like they say where they got it. Link to comment Share on other sites More sharing options...
Vladd Posted November 27, 2013 Share Posted November 27, 2013 It looks like. But its confusing that thay in tutorial said "In this tutorial we will use the well-known Northwind sample database." Link to comment Share on other sites More sharing options...
daniel@okaj.se Posted April 1, 2014 Share Posted April 1, 2014 They got there own version but you will find the database used in the tutorial here: http://w3schools.invisionzone.com/index.php?showtopic=49065&hl=northwind Link to comment Share on other sites More sharing options...
Debasmita Nandi Posted August 5, 2020 Share Posted August 5, 2020 Open this. It will be able to solve your problem. https://github.com/debsmita99/SQLite/blob/master/Northwind.sql Link to comment Share on other sites More sharing options...
Funce Posted August 6, 2020 Share Posted August 6, 2020 Please do not reply to old topics. Link to comment Share on other sites More sharing options...
Recommended Posts