NorthWind db in MySQL
northwind database download for mysql
Microsoft's SQL Server 2000 comes with 2 sample databases, one of which is NORTHWIND a sample accounting database
Below are the table definitions needed to export NorthWind from MS SQL Server 2000 and import NorthWind into MySQL
I have exported out the main table definitions and their data and put them in the MySQL format and imported them into MySQL. (see the definitions below)
----------------------------------------------------------------------------
below are 7 table definitions, the create statements and, the data for the smaller data sets.
Customers
Employees
Orders
OrderDetails
Products
Shippers
Suppliers
the Orders and OrderDetails tables are the most massive in terms of sample data and are listed in separate files.
For your own experimental purposes, you can get a "developer's" copy of Microsoft's SQL Server 2000 (or 2003?) ... which MS has given away freely with no license needed. the installation includes the sample (accounting) database called NorthWind. (there is also a PUBS, publishing database) The 2 main tools for working with MS SQL Server are SQL Query Analyzer and Enterprise Manager
To get a table definition (like the ones below), in Query Analyzer, select the database, user tables, select the table you want to copy out, right-click on it, select "script object to new window as", "select"
run the query and rt-click in the data to "select all" then again to "save as" give it a file name and save it. This way, null-empty fields will say "NULL" instead of just having another comma separator with nothing in between.
go to the file and check for instances of ", " as opposed to "," because the 1st may not be a field comma separater but, a comma within a field. and check for single quotes and "addslashes" where found (as is the case in the Orders and Customers tables) then, change all "," to "','" to put single quotes around each field then, add (' to the begining and ') to the end of the lines.
then, add a few insert statements with field names and you will have something like what is below:
Conclusion: It is a bit of a hassle!
NorthWind
Table definition from MS SQL Server 2000:
CREATE TABLE [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 CONSTRAINT [DF_Orders_Freight] DEFAULT (0),
[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 ,
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
[OrderID]
) ON [PRIMARY] ,
CONSTRAINT [FK_Orders_Customers] FOREIGN KEY
(
[CustomerID]
) REFERENCES [Customers] (
[CustomerID]
),
CONSTRAINT [FK_Orders_Employees] FOREIGN KEY
(
[EmployeeID]
) REFERENCES [Employees] (
[EmployeeID]
),
CONSTRAINT [FK_Orders_Shippers] FOREIGN KEY
(
[ShipVia]
) REFERENCES [Shippers] (
[ShipperID]
)
) ON [PRIMARY]
SQL -- IIS vs. Apache:
I could not capitalize any letter in a MySQL Table Name under MS's IIS server -- Capitol letters in table names are ignored. under the Apache Server they are not.
I did not recreate the constraints - If and when I want to play with changing the data, then I should add them in.
I also added the prefix nw to the table names to identify them and to keep them together and not need to create a separate database to hold them.
DROP TABLE IF EXISTS nwOrders ;
CREATE TABLE nwOrders
(
OrderID int(11) NOT NULL auto_increment,
CustomerID varchar(5) default NULL,
EmployeeID int(11) default NULL,
OrderDate datetime default NULL,
RequiredDate datetime default NULL,
ShippedDate datetime default NULL,
ShipVia int(11) default NULL,
Freight decimal(9,2) default '0.00',
ShipName varchar(40) default NULL,
ShipAddress varchar(60) default NULL,
ShipCity varchar(15) default NULL,
ShipRegion varchar(15) default NULL,
ShipPostalCode varchar(10) default NULL,
ShipCountry varchar(15) default NULL,
PRIMARY KEY (OrderID)
) ;
( data: 830 rows )
7 rows:
INSERT INTO nwOrders (OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry) VALUES
(10248, 'VINET', 5, '1996-07-04', '1996-08-01', '1996-07-16', 3, 32.38, 'Vins et alcools Chevalier', '59 rue de l''Abbaye', 'Reims', 'NULL', '51100', 'France'),
(10249, 'TOMSP', 6, '1996-07-05', '1996-08-16', '1996-07-10', 1, 11.61, 'Toms Spezialitaten', 'Luisenstr. 48', 'Munster', 'NULL', '44087', 'Germany'),
(10250, 'HANAR', 4, '1996-07-08', '1996-08-05', '1996-07-12', 2, 65.83, 'Hanari Carnes', 'Rua do Paco 67', 'Rio de Janeiro', 'RJ', '05454-876', 'Brazil'),
(10251, 'VICTE', 3, '1996-07-08', '1996-08-05', '1996-07-15', 1, 41.34, 'Victuailles en stock', '2 rue du Commerce', 'Lyon', 'NULL', '69004', 'France'),
(10252, 'SUPRD', 4, '1996-07-09', '1996-08-06', '1996-07-11', 2, 51.30, 'Supremes delices', 'Boulevard Tirou 255', 'Charleroi', 'NULL', 'B-6000', 'Belgium'),
(10253, 'HANAR', 3, '1996-07-10', '1996-07-24', '1996-07-16', 2, 58.17, 'Hanari Carnes', 'Rua do Paco 67', 'Rio de Janeiro', 'RJ', '05454-876', 'Brazil'),
(10254, 'CHOPS', 5, '1996-07-11', '1996-08-08', '1996-07-23', 2, 22.98, 'Chop-suey Chinese', 'Hauptstr. 31', 'Bern', 'NULL', '3012', 'Switzerland')
-------------------------------------------------------------------------------
MS Sequel Server 2000:
CREATE TABLE [Order Details]
( [OrderID] [int] NOT NULL ,
[ProductID] [int] NOT NULL ,
[UnitPrice] [money] NOT NULL CONSTRAINT [DF_Order_Details_UnitPrice] DEFAULT (0),
[Quantity] [smallint] NOT NULL CONSTRAINT [DF_Order_Details_Quantity] DEFAULT (1),
[Discount] [real] NOT NULL CONSTRAINT [DF_Order_Details_Discount] DEFAULT (0),
CONSTRAINT [PK_Order_Details] PRIMARY KEY CLUSTERED
( [OrderID], [ProductID] ) ON [PRIMARY] ,
CONSTRAINT [FK_Order_Details_Orders] FOREIGN KEY
( [OrderID] ) REFERENCES [Orders]
( [OrderID] ),
CONSTRAINT [FK_Order_Details_Products] FOREIGN KEY
( [ProductID] ) REFERENCES [Products]
( [ProductID] ),
CONSTRAINT [CK_Discount] CHECK ([Discount] >= 0 and [Discount] <= 1),
CONSTRAINT [CK_Quantity] CHECK ([Quantity] > 0),
CONSTRAINT [CK_UnitPrice] CHECK ([UnitPrice] >= 0)
)
MySQL:
CREATE TABLE nwOrderDetails
(
OrderID int NOT NULL,
ProductID int NOT NULL,
UnitPrice decimal(9,2) unsigned NOT NULL DEFAULT '0',
Quantity smallint unsigned NOT NULL DEFAULT '1',
Discount real NOT NULL DEFAULT '0',
PRIMARY KEY ( OrderID, ProductID )
)
( data: 2155 rows )
6 rows:
INSERT INTO nwOrderDetails (OrderID, ProductID, UnitPrice, Quantity, Discount) VALUES
(10248, 11, 14.00, 12, 0),
(10248, 42, 9.80, 10, 0),
(10248, 72, 34.80, 5, 0),
(10249, 14, 18.60, 9, 0),
(10249, 51, 42.40, 40, 0),
(10250, 41, 7.70, 10, 0)
----------------------------------------------------------------------------
DROP TABLE IF EXISTS nwCustomers ;
CREATE TABLE nwCustomers
(
CustomerID char(5) unique NOT NULL ,
CompanyName varchar(40) NOT NULL ,
ContactName varchar(30) NULL ,
ContactTitle varchar(30) NULL ,
Address varchar(60) NULL ,
City varchar(15) NULL ,
Region varchar(15) NULL ,
PostalCode varchar(10) NULL ,
Country varchar(15) NULL ,
Phone varchar(24) NULL ,
Fax varchar(24) NULL ,
PRIMARY KEY (`CustomerID`)
)
( data: 3072 rows )
a few:
INSERT INTO nwCustomers (CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax) VALUES
('ALFKI', 'Alfreds Futterkiste', 'Maria Anders', 'Sales Representative', 'Obere Str. 57', 'Berlin', 'NULL', '12209', 'Germany', '030-0074321', '030-0076545'),
('ANATR', 'Ana Trujillo Emparedados y helados', 'Ana Trujillo', 'Owner', 'Avda. de la Constitucion 2222', 'Mexico D.F.', 'NULL', '05021', 'Mexico', '(5) 555-4729', '(5) 555-3745'),
('ANTON', 'Antonio Moreno Taqueria', 'Antonio Moreno', 'Owner', 'Mataderos 2312', 'Mexico D.F.', 'NULL', '05023', 'Mexico', '(5) 555-3932', 'NULL'),
('AROUT', 'Around the Horn', 'Thomas Hardy', 'Sales Representative', '120 Hanover Sq.', 'London', 'NULL', 'WA1 1DP', 'UK', '(171) 555-7788', '(171) 555-6750'),
('BERGS', 'Berglunds snabbkop', 'Christina Berglund', 'Order Administrator', 'Berguvsvagen 8', 'Lulea', 'NULL', 'S-958 22', 'Sweden', '0921-12 34 65', '0921-12 34 67'),
('BLAUS', 'Blauer See Delikatessen', 'Hanna Moos', 'Sales Representative', 'Forsterstr. 57', 'Mannheim', 'NULL', '68306', 'Germany', '0621-08460', '0621-08924'),
('BLONP', 'Blondesddsl pere et fils', 'Frederique Citeaux', 'Marketing Manager', '24. place Kleber', 'Strasbourg', 'NULL', '67000', 'France', '88.60.15.31', '88.60.15.32'),
('BOLID', 'Bolido Comidas preparadas', 'Martin Sommer', 'Owner', 'C/ Araquil. 67', 'Madrid', 'NULL', '28023', 'Spain', '(91) 555 22 82', '(91) 555 91 99'),
('BONAP', 'Bon app''', 'Laurence Lebihan', 'Owner', '12. rue des Bouchers', 'Marseille', 'NULL', '13008', 'France', '91.24.45.40', '91.24.45.41'),
-------------------------------------------------------------------------------
DROP TABLE IF EXISTS nwEmployees ;
CREATE TABLE nwEmployees
(
EmployeeID int unique NOT NULL ,
LastName varchar(20) NOT NULL ,
FirstName varchar(10) NOT NULL ,
Title varchar(30) NULL ,
TitleOfCourtesy varchar(25) NULL ,
BirthDate date NULL ,
HireDate date NULL ,
Address varchar(60) NULL ,
City varchar(15) NULL ,
Region varchar(15) NULL ,
PostalCode varchar(10) NULL ,
Country varchar(15) NULL ,
HomePhone varchar(24) NULL ,
Extension varchar (4) NULL ,
Notes text NULL ,
ReportsTo int NULL ,
PhotoFileName varchar(255) NULL ,
PRIMARY KEY (EmployeeID)
)
( data: 9 rows )
9 rows:
INSERT INTO nwEmployees (EmployeeID, LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address, City, Region, PostalCode, Country, HomePhone, Extension, Notes, ReportsTo, PhotoFileName) VALUES
(1, 'Davolio', 'Tom', 'Sales Representative', 'Ms.', '1948-12-08', '1992-05-01', '507 - 20th Ave. E. Apt. 2A', 'Seattle', 'WA', '98122', 'USA', '(206) 555-9857', '5467', 'Education includes a BA in psychology from Colorado State University in 1970. He also completed "The Art of the Cold Call." Tom is a member of Toastmasters International.', 2, 'http://fevj.org/energy-news/images/pages/Ogle-Tom-cu.jpg'),
(2, 'Fuller', 'Nick', 'Vice President of Sales', 'Dr.', '1952-02-19', '1992-08-14', '908 W. Capital Way', 'Tacoma', 'WA', '98401', 'USA', '(206) 555-9482', '3457', 'Nick received his BTS commercial in 1974 and a Ph.D. in international marketing from the University of Dallas in 1981. He is fluent in Hungarian, French, English, Latin and Italian and reads German. He joined the company as a sales representative; was promoted to sales manager i', 0, 'http://fevj.org/energy-news/images/pages/Tesla-portrait-1933-10-13.jpg'),
(3, 'Leverling', 'Janet', 'Sales Representative', 'Ms.', '1963-08-30', '1992-04-01', '722 Moss Bay Blvd.', 'Kirkland', 'WA', '98033', 'USA', '(206) 555-3412', '3355', 'Janet has a BS degree in chemistry from Boston College (1984). She has also completed a certificate program in food retailing management. Janet was hired as a sales associate in 1991 and promoted to sales representative in February 1992.', 2, 'http://accweb/emmployees/leverling.bmp'),
(4, 'Peacock', 'Margaret', 'Sales Representative', 'Mrs.', '1937-09-19', '1993-05-03', '4110 Old Redmond Rd.', 'Redmond', 'WA', '98052', 'USA', '(206) 555-8122', '5176', 'Margaret holds a BA in English literature from Concordia College (1958) and an MA from the American Institute of Culinary Arts (1966). She was assigned to the London office temporarily from July through November 1992.', 2, 'http://accweb/emmployees/peacock.bmp'),
(5, 'Buchanan', 'Steven', 'Sales Manager', 'Mr.', '1955-03-04', '1993-10-17', '14 Garrett Hill', 'London', 'NULL', 'SW1 8JR', 'UK', '(71) 555-4848', '3453', 'Steven Buchanan graduated from St. Andrews University (Scotland) with a BSC degree in 1976. Upon joining the company as a sales representative in 1992. He spent 6 months in an orientation program at the Seattle office and then returned to his permanent po', 2, 'http://fevj.org/energy-news/images/pages/Ogle-Tom-cu.jpg'),
(6, 'Suyama', 'Michael', 'Sales Representative', 'Mr.', '1963-07-02', '1993-10-17', 'Coventry House Miner Rd.', 'London', 'NULL', 'EC2 7JR', 'UK', '(71) 555-7773', '428', 'Michael is a graduate of Sussex University (MA economics 1983) and the University of California at Los Angeles (MBA marketing 1986). He has also taken the courses "Multi-Cultural Selling" and "Time Management for the Sales Professional." He is fluent', 5, 'http://fevj.org/energy-news/images/pages/Ogle-Tom-cu.jpg'),
(7, 'King', 'Robert', 'Sales Representative', 'Mr.', '1960-05-29', '1994-01-02', 'Edgeham Hollow Winchester Way', 'London', 'NULL', 'RG1 9SP', 'UK', '(71) 555-5598', '465', 'Robert King served in the Peace Corps and traveled extensively before completing his degree in English at the University of Michigan in 1992; the year he joined the company. After completing a course entitled "Selling in Europe" he was transferred to the', 5, 'http://fevj.org/energy-news/images/pages/Ogle-Tom-cu.jpg'),
(8, 'Callahan', 'Laura', 'Inside Sales Coordinator', 'Ms.', '1958-01-09', '1994-03-05', '4726 - 11th Ave. N.E.', 'Seattle', 'WA', '98105', 'USA', '(206) 555-1189', '2344', 'Laura received a BA in psychology from the University of Washington. She has also completed a course in business French. She reads and writes French.', 2, 'http://fevj.org/energy-news/images/pages/Ogle-Tom-cu.jpg'),
(9, 'Dodsworth', 'Anne', 'Sales Representative', 'Ms.', '1966-01-27', '1994-11-15', '7 Houndstooth Rd.', 'London', 'NULL', 'WG2 7LT', 'UK', '(71) 555-4444', '452', 'Anne has a BA degree in English from St. Lawrence College. She is fluent in French and German.', 5, 'http://fevj.org/energy-news/images/pages/Ogle-Tom-cu.jpg');
-------------------------------------------------------------------------------
CREATE TABLE nwProducts
(
ProductID int NOT NULL auto_increment,
ProductName varchar(40) NOT NULL ,
SupplierID int NULL ,
CategoryID int NULL ,
QuantityPerUnit varchar(20) NULL ,
UnitPrice decimal(9,2) DEFAULT '0',
UnitsInStock smallint DEFAULT '0',
UnitsOnOrder smallint DEFAULT '0',
ReorderLevel smallint DEFAULT '0',
Discontinued char(1) DEFAULT '0',
PRIMARY KEY (ProductID)
)
( data: 77 rows )
9 rows:
INSERT INTO nwProducts (ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued) VALUES
(1, 'Chai', 1, 1, '10 boxes x 20 bags', 18.00, 39, 0, 10, '0'),
(2, 'Chang', 1, 1, '24 - 12 oz bottles', 19.00, 17, 40, 25, '0'),
(3, 'Aniseed Syrup', 1, 2, '12 - 550 ml bottles', 10.00, 13, 70, 25, '0'),
(4, 'Chef Anton''s Cajun Seasoning', 2, 2, '48 - 6 oz jars', 22.00, 53, 0, 0, '0'),
(5, 'Chef Anton''s Gumbo Mix', 2, 2, '36 boxes', 21.35, 0, 0, 0, '1'),
(6, 'Grandma''s Boysenberry Spread', 3, 2, '12 - 8 oz jars', 25.00, 120, 0, 25, '0'),
(7, 'Uncle Bob''s Organic Dried Pears', 3, 7, '12 - 1 lb pkgs.', 30.00, 15, 0, 10, '0'),
(8, 'Northwoods Cranberry Sauce', 3, 2, '12 - 12 oz jars', 40.00, 6, 0, 0, '0'),
(9, 'Mishi Kobe Niku', 4, 6, '18 - 500 g pkgs.', 97.00, 29, 0, 0, '1')
-------------------------------------------------------------------------------
CREATE TABLE nwShippers ( ShipperID int NOT NULL auto_increment, CompanyName varchar(40) NOT NULL , Phone varchar(24) NULL , PRIMARY KEY (ShipperID) ) INSERT INTO nwshippers VALUES (1, 'Speedy Express', '(503) 555-9831'); INSERT INTO nwshippers VALUES (2, 'United Package', '(503) 555-3199'); INSERT INTO nwshippers VALUES (3, 'Federal Shipping', '(503) 555-9931'); ------------------------------------------------------------------------------- CREATE TABLE nwSuppliers ( SupplierID int NOT NULL auto_increment, CompanyName varchar(40) NOT NULL , ContactName varchar(30) NULL , ContactTitle varchar(30) NULL , Address varchar(60) NULL , City varchar(15) NULL , Region varchar(15) NULL , PostalCode varchar(10) NULL , Country varchar(15) NULL , Phone varchar(24) NULL , Fax varchar(24) NULL , HomePage text NULL , PRIMARY KEY (SupplierID) )
6 rows:
INSERT INTO nwSuppliers (SupplierID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax, HomePage) VALUES
(1, 'Exotic Liquids', 'Charlotte Cooper', 'Purchasing Manager', '49 Gilbert St.', 'London', 'NULL', 'EC1 4SD', 'UK', '(171) 555-2222', 'NULL', 'NULL'),
(2, 'New Orleans Cajun Delights', 'Shelley Burke', 'Order Administrator', 'P.O. Box 78934', 'New Orleans', 'LA', '70117', 'USA', '(100) 555-4822', 'NULL', '#CAJUN.HTM#'),
(3, 'Grandma Kelly''s Homestead', 'Regina Murphy', 'Sales Representative', '707 Oxford Rd.', 'Ann Arbor', 'MI', '48104', 'USA', '(313) 555-5735', '(313) 555-3349', 'NULL'),
(4, 'Tokyo Traders', 'Yoshi Nagase', 'Marketing Manager', '9-8 Sekimai Musashino-shi', 'Tokyo', 'NULL', '100', 'Japan', '(03) 3555-5011', 'NULL', 'NULL'),
(5, 'Cooperativa de Quesos ''Las Cabras''', 'Antonio del Valle Saavedra', 'Export Administrator', 'Calle del Rosal 4', 'Oviedo', 'Asturias', '33007', 'Spain', '(98) 598 76 54', 'NULL', 'NULL'),
(6, 'Nick''s', 'Nikola Tesla', 'Marketing Representative', '92 Setsuko Chuo-ku', 'Osaka', 'NULL', '545', 'Japan', '(06) 431-7877', 'NULL', 'Nick''s (on the World Wide Web)#http://www.fevj.org/energy-news/?page_id=980#')
here are the last 3 tables:
CREATE TABLE Region (
RegionID int NOT NULL,
RegionDescription varchar(50) NOT NULL
)
INSERT INTO Region VALUES
(1,'Eastern'),
(2,'Western'),
(3,'Northern'),
(4,'Southern');
CREATE TABLE Territories (
TerritoryID varchar(20) NOT NULL ,
TerritoryDescription varchar(50) NOT NULL ,
RegionID int NOT NULL
)
CREATE TABLE EmployeeTerritories (
EmployeeID int NOT NULL,
TerritoryID varchar(20) NOT NULL
)
download the zipped .sql format file with all MySQL table definitions and data that can be imported
straight into an empty MySQL database.
Northwind-MySQL-db.sql.zip
Also in this MySql category, see "a few NorthWind MySQL PHP scripts"
Thanks for the db!