/*
** Copyright Microsoft, Inc. 1994 - 2000
** All Rights Reserved.
**
** Changes for PostgreSQL Copyright 2009 Studio Associato Di Nunzio e Di Gregorio
**
** This file is distributed under the MS-PL license.
** See LICENSE.MSPL for more information.
*/


CREATE TABLE "Employees" (
	"EmployeeID" serial NOT NULL ,
	"LastName" varchar (20) NOT NULL ,
	"FirstName" varchar (10) NOT NULL ,
	"Title" varchar (30) ,
	"TitleOfCourtesy" varchar (25) ,
	"BirthDate" "date" ,
	"HireDate" date ,
	"Address" varchar (60) ,
	"City" varchar (15) ,
	"Region" varchar (15) ,
	"PostalCode" varchar (10) ,
	"Country" varchar (15) ,
	"HomePhone" varchar (24) ,
	"Extension" varchar (4) ,
	"Photo" bytea ,
	"Notes" text ,
	"ReportsTo" int4 ,
	"PhotoPath" varchar (255) ,
	CONSTRAINT "PK_Employees" PRIMARY KEY (
		"EmployeeID"
	),
	CONSTRAINT "FK_Employees_Employees"  FOREIGN KEY
	(
		"ReportsTo"
	) REFERENCES "Employees" (
		"EmployeeID"
	) DEFERRABLE,
	CONSTRAINT "CK_Birthdate" CHECK ("BirthDate" < now())
);

CREATE  INDEX "Employees_LastName" ON "Employees"("LastName");
CREATE  INDEX "Employees_PostalCode" ON "Employees"("PostalCode");

CREATE TABLE "Categories" (
	"CategoryID" serial NOT NULL ,
	"CategoryName" varchar (15) NOT NULL ,
	"Description" text ,
	"Picture" bytea ,
	CONSTRAINT "PK_Categories" PRIMARY KEY 
	(
		"CategoryID"
	)
);

CREATE  INDEX "Categories_CategoryName" ON "Categories"("CategoryName");

CREATE TABLE "Customers" (
	"CustomerID" char (5) NOT NULL ,
	"CompanyName" varchar (40) NOT NULL ,
	"ContactName" varchar (30) ,
	"ContactTitle" varchar (30) ,
	"Address" varchar (60) ,
	"City" varchar (15) ,
	"Region" varchar (15) ,
	"PostalCode" varchar (10) ,
	"Country" varchar (15) ,
	"Phone" varchar (24) ,
	"Fax" varchar (24) ,
	CONSTRAINT "PK_Customers" PRIMARY KEY 
	(
		"CustomerID"
	)
);

CREATE  INDEX "Customers_City" ON "Customers"("City");
CREATE  INDEX "Customers_CompanyName" ON "Customers"("CompanyName");
CREATE  INDEX "Customers_PostalCode" ON "Customers"("PostalCode");
CREATE  INDEX "Customers_Region" ON "Customers"("Region");

CREATE TABLE "Shippers" (
	"ShipperID" serial NOT NULL ,
	"CompanyName" varchar (40) NOT NULL ,
	"Phone" varchar (24) NULL ,
	CONSTRAINT "PK_Shippers" PRIMARY KEY 
	(
		"ShipperID"
	)
);

CREATE TABLE "Suppliers" (
	"SupplierID" serial NOT NULL ,
	"CompanyName" varchar (40) NOT NULL ,
	"ContactName" varchar (30) ,
	"ContactTitle" varchar (30) ,
	"Address" varchar (60) ,
	"City" varchar (15) ,
	"Region" varchar (15) ,
	"PostalCode" varchar (10) ,
	"Country" varchar (15) ,
	"Phone" varchar (24) ,
	"Fax" varchar (24) ,
	"HomePage" text ,
	CONSTRAINT "PK_Suppliers" PRIMARY KEY 
	(
		"SupplierID"
	)
);

CREATE  INDEX "Suppliers_CompanyName" ON "Suppliers"("CompanyName");
CREATE  INDEX "Suppliers_PostalCode" ON "Suppliers"("PostalCode");

CREATE TABLE "Orders" (
	"OrderID" serial NOT NULL ,
	"CustomerID" char (5) ,
	"EmployeeID" int4 ,
	"OrderDate" date ,
	"RequiredDate" date ,
	"ShippedDate" date ,
	"ShipVia" int4 ,
	"Freight" decimal DEFAULT 0,
	"ShipName" varchar (40) ,
	"ShipAddress" varchar (60) ,
	"ShipCity" varchar (15) ,
	"ShipRegion" varchar (15) ,
	"ShipPostalCode" varchar (10) ,
	"ShipCountry" varchar (15) ,
	CONSTRAINT "PK_Orders" PRIMARY KEY 
	(
		"OrderID"
	),
	CONSTRAINT "FK_Orders_Customers" FOREIGN KEY 
	(
		"CustomerID"
	) REFERENCES "Customers" (
		"CustomerID"
	) DEFERRABLE,
	CONSTRAINT "FK_Orders_Employees" FOREIGN KEY 
	(
		"EmployeeID"
	) REFERENCES "Employees" (
		"EmployeeID"
	) DEFERRABLE,
	CONSTRAINT "FK_Orders_Shippers" FOREIGN KEY 
	(
		"ShipVia"
	) REFERENCES "Shippers" (
		"ShipperID"
	) DEFERRABLE
);

CREATE  INDEX "Orders_CustomerID" ON "Orders"("CustomerID");
CREATE  INDEX "Orders_CustomersOrders" ON "Orders"("CustomerID");
CREATE  INDEX "Orders_EmployeeID" ON "Orders"("EmployeeID");
CREATE  INDEX "Orders_EmployeesOrders" ON "Orders"("EmployeeID");
CREATE  INDEX "Orders_OrderDate" ON "Orders"("OrderDate");
CREATE  INDEX "Orders_ShippedDate" ON "Orders"("ShippedDate");
CREATE  INDEX "Orders_ShippersOrders" ON "Orders"("ShipVia");
CREATE  INDEX "Orders_ShipPostalCode" ON "Orders"("ShipPostalCode");

CREATE TABLE "Products" (
	"ProductID" serial NOT NULL ,
	"ProductName" varchar (40) NOT NULL ,
	"SupplierID" int4 ,
	"CategoryID" int4 ,
	"QuantityPerUnit" varchar (20) ,
	"UnitPrice" decimal DEFAULT 0,
	"UnitsInStock" int2 DEFAULT 0,
	"UnitsOnOrder" int2 DEFAULT 0,
	"ReorderLevel" int2 DEFAULT 0,
	"Discontinued" boolean DEFAULT false,
	CONSTRAINT "PK_Products" PRIMARY KEY 
	(
		"ProductID"
	),
	CONSTRAINT "FK_Products_Categories" FOREIGN KEY 
	(
		"CategoryID"
	) REFERENCES "Categories" (
		"CategoryID"
	),
	CONSTRAINT "FK_Products_Suppliers" FOREIGN KEY 
	(
		"SupplierID"
	) REFERENCES "Suppliers" (
		"SupplierID"
	) DEFERRABLE,
	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)
);

CREATE  INDEX "Products_CategoriesProducts" ON "Products"("CategoryID");
CREATE  INDEX "Products_CategoryID" ON "Products"("CategoryID");
CREATE  INDEX "Products_ProductName" ON "Products"("ProductName");
CREATE  INDEX "Products_SupplierID" ON "Products"("SupplierID");
CREATE  INDEX "Products_SuppliersProducts" ON "Products"("SupplierID");

CREATE TABLE "OrderDetails" (
	"OrderID" int4 NOT NULL ,
	"ProductID" int4 NOT NULL ,
	"UnitPrice" decimal DEFAULT 0,
	"Quantity" int2 DEFAULT 1,
	"Discount" float DEFAULT 0,
	CONSTRAINT "PK_Order_Details" PRIMARY KEY 
	(
		"OrderID",
		"ProductID"
	),
	CONSTRAINT "FK_Order_Details_Orders" FOREIGN KEY 
	(
		"OrderID"
	) REFERENCES "Orders" (
		"OrderID"
	) DEFERRABLE,
	CONSTRAINT "FK_Order_Details_Products" FOREIGN KEY 
	(
		"ProductID"
	) REFERENCES "Products" (
		"ProductID"
	) DEFERRABLE,
	CONSTRAINT "CK_Discount" CHECK ("Discount" >= 0 AND "Discount" <= 1),
	CONSTRAINT "CK_Quantity" CHECK ("Quantity" > 0),
	CONSTRAINT "CK_UnitPrice" CHECK ("UnitPrice" >= 0)
);

CREATE  INDEX "OrderDetails_OrderID" ON "OrderDetails"("OrderID");
CREATE  INDEX "OrderDetails_OrdersOrder_Details" ON "OrderDetails"("OrderID");
CREATE  INDEX "OrderDetails_ProductID" ON "OrderDetails"("ProductID");
CREATE  INDEX "OrderDetails_ProductsOrder_Details" ON "OrderDetails"("ProductID");

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"
;

CREATE VIEW "Alphabetical list of products" AS
SELECT "Products".*, "Categories"."CategoryName"
FROM "Categories" INNER JOIN "Products" ON ("Categories"."CategoryID" = "Products"."CategoryID")
WHERE ("Products"."Discontinued"=false)
;

CREATE VIEW "Current Product List" AS
SELECT "Product_List"."ProductID", "Product_List"."ProductName"
FROM "Products" AS "Product_List"
WHERE ("Product_List"."Discontinued"=false)
--ORDER BY "Product_List"."ProductName"
;

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"
;

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
;

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" != true
--ORDER BY Categories.CategoryName, Products.ProductName
;

CREATE VIEW "Quarterly Orders" AS
SELECT DISTINCT "Customers"."CustomerID", "Customers"."CompanyName", "Customers"."City", 
	"Customers"."Country"
FROM "Customers" RIGHT OUTER JOIN "Orders" ON "Customers"."CustomerID" = "Orders"."CustomerID"
WHERE "Orders"."OrderDate" BETWEEN '1997-01-01' AND '1997-12-31'
;

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",
	"OrderDetails"."ProductID", "Products"."ProductName", "OrderDetails"."UnitPrice",
	"OrderDetails"."Quantity", "OrderDetails"."Discount", 
	(("OrderDetails"."UnitPrice" * "Quantity" * (1-"Discount")/100)*100)::decimal AS "ExtendedPrice",
	"Orders"."Freight"
FROM 	"Shippers", 
	"Products", 
	"Employees",
	"Customers",
	"Orders",
	"OrderDetails"
WHERE   "Employees"."EmployeeID" = "Orders"."EmployeeID"
  AND   "Customers"."CustomerID" = "Orders"."CustomerID"
  AND   "Orders"."OrderID" = "OrderDetails"."OrderID"
  AND   "Products"."ProductID" = "OrderDetails"."ProductID"
  AND   "Shippers"."ShipperID" = "Orders"."ShipVia"
;

CREATE VIEW "Order Details Extended" AS
SELECT "OrderDetails"."OrderID", "OrderDetails"."ProductID", "Products"."ProductName", 
	"OrderDetails"."UnitPrice", "OrderDetails"."Quantity", "OrderDetails"."Discount", 
	(("OrderDetails"."UnitPrice" * "Quantity" * (1-"Discount")/100)::decimal * 100) AS "ExtendedPrice"
FROM "Products" INNER JOIN "OrderDetails" ON ("Products"."ProductID" = "OrderDetails"."ProductID")
--ORDER BY "OrderDetails".OrderID
;

CREATE VIEW "Order Subtotals" AS
SELECT "OrderDetails"."OrderID",
	sum(("OrderDetails"."UnitPrice" * "Quantity" * (1-"Discount")/100)::decimal * 100) AS "Subtotal"
FROM "OrderDetails"
GROUP BY "OrderDetails"."OrderID"
;

CREATE VIEW "Product Sales for 1997" AS
SELECT "Categories"."CategoryName", "Products"."ProductName",
	sum(("OrderDetails"."UnitPrice" * "Quantity" * (1-"Discount")/100)::DECIMAL * 100) AS "ProductSales"
FROM "Categories" INNER JOIN "Products" ON ("Categories"."CategoryID" = "Products"."CategoryID") 
	INNER JOIN ("Orders"
		INNER JOIN "OrderDetails" ON ("Orders"."OrderID" = "OrderDetails"."OrderID"))
	ON ("Products"."ProductID" = "OrderDetails"."ProductID")
WHERE "Orders"."ShippedDate" BETWEEN '1997-01-01' AND '1997-12-31'
GROUP BY "Categories"."CategoryName", "Products"."ProductName"
;

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"
;

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 '1997-01-01' AND '1997-12-31'
GROUP BY "Categories"."CategoryID", "Categories"."CategoryName", "Products"."ProductName"
--ORDER BY Products.ProductName
;

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 '1997-01-01' AND '1997-12-31')
;

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
;

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
;

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

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
GO

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
GO*/

BEGIN;
SET CONSTRAINTS ALL DEFERRED;
SET datestyle TO iso, mdy;

INSERT INTO "Categories"("CategoryID","CategoryName","Description","Picture") VALUES(1,'Beverages','Soft drinks, coffees, teas, beers, and  ales',E'[encoded data, 21492 characters]');
INSERT INTO "Categories"("CategoryID","CategoryName","Description","Picture") VALUES(2,'Condiments','Sweet and savory sauces, relishes, spreads, and seasonings',E'[encoded data, 21492 characters]');
INSERT INTO "Categories"("CategoryID","CategoryName","Description","Picture") VALUES(3,'Confections','Desserts, candies, and sweet breads',E'[encoded data, 21492 characters]');
INSERT INTO "Categories"("CategoryID","CategoryName","Description","Picture") VALUES(4,'Dairy Products','Cheeses',E'[encoded data, 21492 characters]');
INSERT INTO "Categories"("CategoryID","CategoryName","Description","Picture") VALUES(5,'Grains/Cereals','Breads, crackers, pasta, and cereal',E'[encoded data, 21492 characters]');
INSERT INTO "Categories"("CategoryID","CategoryName","Description","Picture") VALUES(6,'Meat/Poultry','Prepared meats',E'[encoded data, 21492 characters]');
INSERT INTO "Categories"("CategoryID","CategoryName","Description","Picture") VALUES(7,'Produce','Dried fruit and bean curd',E'[encoded data, 21492 characters]');
INSERT INTO "Categories"("CategoryID","CategoryName","Description","Picture") VALUES(8,'Seafood','Seaweed and fish',E'[encoded data, 21492 characters]');

INSERT INTO "Customers" VALUES('ALFKI','Alfreds Futterkiste','Maria Anders','Sales Representative','Obere Str. 57','Berlin',NULL,'12209','Germany','030-0074321','030-0076545');
INSERT INTO "Customers" VALUES('ANATR','Ana Trujillo Emparedados y helados','Ana Trujillo','Owner','Avda. de la Constitución 2222','México D.F.',NULL,'05021','Mexico','(5) 555-4729','(5) 555-3745');
INSERT INTO "Customers" VALUES('ANTON','Antonio Moreno Taquería','Antonio Moreno','Owner','Mataderos  2312','México D.F.',NULL,'05023','Mexico','(5) 555-3932',NULL);
INSERT INTO "Customers" VALUES('AROUT','Around the Horn','Thomas Hardy','Sales Representative','120 Hanover Sq.','London',NULL,'WA1 1DP','UK','(171) 555-7788','(171) 555-6750');
INSERT INTO "Customers" VALUES('BERGS','Berglunds snabbköp','Christina Berglund','Order Administrator','Berguvsvägen  8','Luleå',NULL,'S-958 22','Sweden','0921-12 34 65','0921-12 34 67');
INSERT INTO "Customers" VALUES('BLAUS','Blauer See Delikatessen','Hanna Moos','Sales Representative','Forsterstr. 57','Mannheim',NULL,'68306','Germany','0621-08460','0621-08924');
INSERT INTO "Customers" VALUES('BLONP','Blondesddsl père et fils','Frédérique Citeaux','Marketing Manager','24, place Kléber','Strasbourg',NULL,'67000','France','88.60.15.31','88.60.15.32');
INSERT INTO "Customers" VALUES('BOLID','Bólido Comidas preparadas','Martín Sommer','Owner','C/ Araquil, 67','Madrid',NULL,'28023','Spain','(91) 555 22 82','(91) 555 91 99');
INSERT INTO "Customers" VALUES('BONAP','Bon app''','Laurence Lebihan','Owner','12, rue des Bouchers','Marseille',NULL,'13008','France','91.24.45.40','91.24.45.41');
INSERT INTO "Customers" VALUES('BOTTM','Bottom-Dollar Markets','Elizabeth Lincoln','Accounting Manager','23 Tsawassen Blvd.','Tsawassen','BC','T2F 8M4','Canada','(604) 555-4729','(604) 555-3745');

INSERT INTO "Customers" VALUES('BSBEV','B''s Beverages','Victoria Ashworth','Sales Representative','Fauntleroy Circus','London',NULL,'EC2 5NT','UK','(171) 555-1212',NULL);
INSERT INTO "Customers" VALUES('CACTU','Cactus Comidas para llevar','Patricio Simpson','Sales Agent','Cerrito 333','Buenos Aires',NULL,'1010','Argentina','(1) 135-5555','(1) 135-4892');
INSERT INTO "Customers" VALUES('CENTC','Centro comercial Moctezuma','Francisco Chang','Marketing Manager','Sierras de Granada 9993','México D.F.',NULL,'05022','Mexico','(5) 555-3392','(5) 555-7293');
INSERT INTO "Customers" VALUES('CHOPS','Chop-suey Chinese','Yang Wang','Owner','Hauptstr. 29','Bern',NULL,'3012','Switzerland','0452-076545',NULL);
INSERT INTO "Customers" VALUES('COMMI','Comércio Mineiro','Pedro Afonso','Sales Associate','Av. dos Lusíadas, 23','Sao Paulo','SP','05432-043','Brazil','(11) 555-7647',NULL);
INSERT INTO "Customers" VALUES('CONSH','Consolidated Holdings','Elizabeth Brown','Sales Representative','Berkeley Gardens 12  Brewery','London',NULL,'WX1 6LT','UK','(171) 555-2282','(171) 555-9199');
INSERT INTO "Customers" VALUES('DRACD','Drachenblut Delikatessen','Sven Ottlieb','Order Administrator','Walserweg 21','Aachen',NULL,'52066','Germany','0241-039123','0241-059428');
INSERT INTO "Customers" VALUES('DUMON','Du monde entier','Janine Labrune','Owner','67, rue des Cinquante Otages','Nantes',NULL,'44000','France','40.67.88.88','40.67.89.89');
INSERT INTO "Customers" VALUES('EASTC','Eastern Connection','Ann Devon','Sales Agent','35 King George','London',NULL,'WX3 6FW','UK','(171) 555-0297','(171) 555-3373');
INSERT INTO "Customers" VALUES('ERNSH','Ernst Handel','Roland Mendel','Sales Manager','Kirchgasse 6','Graz',NULL,'8010','Austria','7675-3425','7675-3426');

INSERT INTO "Customers" VALUES('FAMIA','Familia Arquibaldo','Aria Cruz','Marketing Assistant','Rua Orós, 92','Sao Paulo','SP','05442-030','Brazil','(11) 555-9857',NULL);
INSERT INTO "Customers" VALUES('FISSA','FISSA Fabrica Inter. Salchichas S.A.','Diego Roel','Accounting Manager','C/ Moralzarzal, 86','Madrid',NULL,'28034','Spain','(91) 555 94 44','(91) 555 55 93');
INSERT INTO "Customers" VALUES('FOLIG','Folies gourmandes','Martine Rancé','Assistant Sales Agent','184, chaussée de Tournai','Lille',NULL,'59000','France','20.16.10.16','20.16.10.17');
INSERT INTO "Customers" VALUES('FOLKO','Folk och fä HB','Maria Larsson','Owner','Åkergatan 24','Bräcke',NULL,'S-844 67','Sweden','0695-34 67 21',NULL);
INSERT INTO "Customers" VALUES('FRANK','Frankenversand','Peter Franken','Marketing Manager','Berliner Platz 43','München',NULL,'80805','Germany','089-0877310','089-0877451');
INSERT INTO "Customers" VALUES('FRANR','France restauration','Carine Schmitt','Marketing Manager','54, rue Royale','Nantes',NULL,'44000','France','40.32.21.21','40.32.21.20');
INSERT INTO "Customers" VALUES('FRANS','Franchi S.p.A.','Paolo Accorti','Sales Representative','Via Monte Bianco 34','Torino',NULL,'10100','Italy','011-4988260','011-4988261');
INSERT INTO "Customers" VALUES('FURIB','Furia Bacalhau e Frutos do Mar','Lino Rodriguez','Sales Manager','Jardim das rosas n. 32','Lisboa',NULL,'1675','Portugal','(1) 354-2534','(1) 354-2535');
INSERT INTO "Customers" VALUES('GALED','Galería del gastrónomo','Eduardo Saavedra','Marketing Manager','Rambla de Cataluña, 23','Barcelona',NULL,'08022','Spain','(93) 203 4560','(93) 203 4561');
INSERT INTO "Customers" VALUES('GODOS','Godos Cocina Típica','José Pedro Freyre','Sales Manager','C/ Romero, 33','Sevilla',NULL,'41101','Spain','(95) 555 82 82',NULL);

INSERT INTO "Customers" VALUES('GOURL','Gourmet Lanchonetes','André Fonseca','Sales Associate','Av. Brasil, 442','Campinas','SP','04876-786','Brazil','(11) 555-9482',NULL);
INSERT INTO "Customers" VALUES('GREAL','Great Lakes Food Market','Howard Snyder','Marketing Manager','2732 Baker Blvd.','Eugene','OR','97403','USA','(503) 555-7555',NULL);
INSERT INTO "Customers" VALUES('GROSR','GROSELLA-Restaurante','Manuel Pereira','Owner','5ª Ave. Los Palos Grandes','Caracas','DF','1081','Venezuela','(2) 283-2951','(2) 283-3397');
INSERT INTO "Customers" VALUES('HANAR','Hanari Carnes','Mario Pontes','Accounting Manager','Rua do Paço, 67','Rio de Janeiro','RJ','05454-876','Brazil','(21) 555-0091','(21) 555-8765');
INSERT INTO "Customers" VALUES('HILAA','HILARION-Abastos','Carlos Hernández','Sales Representative','Carrera 22 con Ave. Carlos Soublette #8-35','San Cristóbal','Táchira','5022','Venezuela','(5) 555-1340','(5) 555-1948');
INSERT INTO "Customers" VALUES('HUNGC','Hungry Coyote Import Store','Yoshi Latimer','Sales Representative','City Center Plaza 516 Main St.','Elgin','OR','97827','USA','(503) 555-6874','(503) 555-2376');
INSERT INTO "Customers" VALUES('HUNGO','Hungry Owl All-Night Grocers','Patricia McKenna','Sales Associate','8 Johnstown Road','Cork','Co. Cork',NULL,'Ireland','2967 542','2967 3333');
INSERT INTO "Customers" VALUES('ISLAT','Island Trading','Helen Bennett','Marketing Manager','Garden House Crowther Way','Cowes','Isle of Wight','PO31 7PJ','UK','(198) 555-8888',NULL);
INSERT INTO "Customers" VALUES('KOENE','Königlich Essen','Philip Cramer','Sales Associate','Maubelstr. 90','Brandenburg',NULL,'14776','Germany','0555-09876',NULL);
INSERT INTO "Customers" VALUES('LACOR','La corne d''abondance','Daniel Tonini','Sales Representative','67, avenue de l''Europe','Versailles',NULL,'78000','France','30.59.84.10','30.59.85.11');

INSERT INTO "Customers" VALUES('LAMAI','La maison d''Asie','Annette Roulet','Sales Manager','1 rue Alsace-Lorraine','Toulouse',NULL,'31000','France','61.77.61.10','61.77.61.11');
INSERT INTO "Customers" VALUES('LAUGB','Laughing Bacchus Wine Cellars','Yoshi Tannamuri','Marketing Assistant','1900 Oak St.','Vancouver','BC','V3F 2K1','Canada','(604) 555-3392','(604) 555-7293');
INSERT INTO "Customers" VALUES('LAZYK','Lazy K Kountry Store','John Steel','Marketing Manager','12 Orchestra Terrace','Walla Walla','WA','99362','USA','(509) 555-7969','(509) 555-6221');
INSERT INTO "Customers" VALUES('LEHMS','Lehmanns Marktstand','Renate Messner','Sales Representative','Magazinweg 7','Frankfurt a.M.',NULL,'60528','Germany','069-0245984','069-0245874');
INSERT INTO "Customers" VALUES('LETSS','Let''s Stop N Shop','Jaime Yorres','Owner','87 Polk St. Suite 5','San Francisco','CA','94117','USA','(415) 555-5938',NULL);
INSERT INTO "Customers" VALUES('LILAS','LILA-Supermercado','Carlos González','Accounting Manager','Carrera 52 con Ave. Bolívar #65-98 Llano Largo','Barquisimeto','Lara','3508','Venezuela','(9) 331-6954','(9) 331-7256');
INSERT INTO "Customers" VALUES('LINOD','LINO-Delicateses','Felipe Izquierdo','Owner','Ave. 5 de Mayo Porlamar','I. de Margarita','Nueva Esparta','4980','Venezuela','(8) 34-56-12','(8) 34-93-93');
INSERT INTO "Customers" VALUES('LONEP','Lonesome Pine Restaurant','Fran Wilson','Sales Manager','89 Chiaroscuro Rd.','Portland','OR','97219','USA','(503) 555-9573','(503) 555-9646');
INSERT INTO "Customers" VALUES('MAGAA','Magazzini Alimentari Riuniti','Giovanni Rovelli','Marketing Manager','Via Ludovico il Moro 22','Bergamo',NULL,'24100','Italy','035-640230','035-640231');
INSERT INTO "Customers" VALUES('MAISD','Maison Dewey','Catherine Dewey','Sales Agent','Rue Joseph-Bens 532','Bruxelles',NULL,'B-1180','Belgium','(02) 201 24 67','(02) 201 24 68');

INSERT INTO "Customers" VALUES('MEREP','Mère Paillarde','Jean Fresnière','Marketing Assistant','43 rue St. Laurent','Montréal','Québec','H1J 1C3','Canada','(514) 555-8054','(514) 555-8055');
INSERT INTO "Customers" VALUES('MORGK','Morgenstern Gesundkost','Alexander Feuer','Marketing Assistant','Heerstr. 22','Leipzig',NULL,'04179','Germany','0342-023176',NULL);
INSERT INTO "Customers" VALUES('NORTS','North/South','Simon Crowther','Sales Associate','South House 300 Queensbridge','London',NULL,'SW7 1RZ','UK','(171) 555-7733','(171) 555-2530');
INSERT INTO "Customers" VALUES('OCEAN','Océano Atlántico Ltda.','Yvonne Moncada','Sales Agent','Ing. Gustavo Moncada 8585 Piso 20-A','Buenos Aires',NULL,'1010','Argentina','(1) 135-5333','(1) 135-5535');
INSERT INTO "Customers" VALUES('OLDWO','Old World Delicatessen','Rene Phillips','Sales Representative','2743 Bering St.','Anchorage','AK','99508','USA','(907) 555-7584','(907) 555-2880');
INSERT INTO "Customers" VALUES('OTTIK','Ottilies Käseladen','Henriette Pfalzheim','Owner','Mehrheimerstr. 369','Köln',NULL,'50739','Germany','0221-0644327','0221-0765721');
INSERT INTO "Customers" VALUES('PARIS','Paris spécialités','Marie Bertrand','Owner','265, boulevard Charonne','Paris',NULL,'75012','France','(1) 42.34.22.66','(1) 42.34.22.77');
INSERT INTO "Customers" VALUES('PERIC','Pericles Comidas clásicas','Guillermo Fernández','Sales Representative','Calle Dr. Jorge Cash 321','México D.F.',NULL,'05033','Mexico','(5) 552-3745','(5) 545-3745');
INSERT INTO "Customers" VALUES('PICCO','Piccolo und mehr','Georg Pipps','Sales Manager','Geislweg 14','Salzburg',NULL,'5020','Austria','6562-9722','6562-9723');
INSERT INTO "Customers" VALUES('PRINI','Princesa Isabel Vinhos','Isabel de Castro','Sales Representative','Estrada da saúde n. 58','Lisboa',NULL,'1756','Portugal','(1) 356-5634',NULL);

INSERT INTO "Customers" VALUES('QUEDE','Que Delícia','Bernardo Batista','Accounting Manager','Rua da Panificadora, 12','Rio de Janeiro','RJ','02389-673','Brazil','(21) 555-4252','(21) 555-4545');
INSERT INTO "Customers" VALUES('QUEEN','Queen Cozinha','Lúcia Carvalho','Marketing Assistant','Alameda dos Canàrios, 891','Sao Paulo','SP','05487-020','Brazil','(11) 555-1189',NULL);
INSERT INTO "Customers" VALUES('QUICK','QUICK-Stop','Horst Kloss','Accounting Manager','Taucherstraße 10','Cunewalde',NULL,'01307','Germany','0372-035188',NULL);
INSERT INTO "Customers" VALUES('RANCH','Rancho grande','Sergio Gutiérrez','Sales Representative','Av. del Libertador 900','Buenos Aires',NULL,'1010','Argentina','(1) 123-5555','(1) 123-5556');
INSERT INTO "Customers" VALUES('RATTC','Rattlesnake Canyon Grocery','Paula Wilson','Assistant Sales Representative','2817 Milton Dr.','Albuquerque','NM','87110','USA','(505) 555-5939','(505) 555-3620');
INSERT INTO "Customers" VALUES('REGGC','Reggiani Caseifici','Maurizio Moroni','Sales Associate','Strada Provinciale 124','Reggio Emilia',NULL,'42100','Italy','0522-556721','0522-556722');
INSERT INTO "Customers" VALUES('RICAR','Ricardo Adocicados','Janete Limeira','Assistant Sales Agent','Av. Copacabana, 267','Rio de Janeiro','RJ','02389-890','Brazil','(21) 555-3412',NULL);
INSERT INTO "Customers" VALUES('RICSU','Richter Supermarkt','Michael Holz','Sales Manager','Grenzacherweg 237','Genève',NULL,'1203','Switzerland','0897-034214',NULL);
INSERT INTO "Customers" VALUES('ROMEY','Romero y tomillo','Alejandra Camino','Accounting Manager','Gran Vía, 1','Madrid',NULL,'28001','Spain','(91) 745 6200','(91) 745 6210');
INSERT INTO "Customers" VALUES('SANTG','Santé Gourmet','Jonas Bergulfsen','Owner','Erling Skakkes gate 78','Stavern',NULL,'4110','Norway','07-98 92 35','07-98 92 47');

INSERT INTO "Customers" VALUES('SAVEA','Save-a-lot Markets','Jose Pavarotti','Sales Representative','187 Suffolk Ln.','Boise','ID','83720','USA','(208) 555-8097',NULL);
INSERT INTO "Customers" VALUES('SEVES','Seven Seas Imports','Hari Kumar','Sales Manager','90 Wadhurst Rd.','London',NULL,'OX15 4NB','UK','(171) 555-1717','(171) 555-5646');
INSERT INTO "Customers" VALUES('SIMOB','Simons bistro','Jytte Petersen','Owner','Vinbæltet 34','Kobenhavn',NULL,'1734','Denmark','31 12 34 56','31 13 35 57');
INSERT INTO "Customers" VALUES('SPECD','Spécialités du monde','Dominique Perrier','Marketing Manager','25, rue Lauriston','Paris',NULL,'75016','France','(1) 47.55.60.10','(1) 47.55.60.20');
INSERT INTO "Customers" VALUES('SPLIR','Split Rail Beer & Ale','Art Braunschweiger','Sales Manager','P.O. Box 555','Lander','WY','82520','USA','(307) 555-4680','(307) 555-6525');
INSERT INTO "Customers" VALUES('SUPRD','Suprêmes délices','Pascale Cartrain','Accounting Manager','Boulevard Tirou, 255','Charleroi',NULL,'B-6000','Belgium','(071) 23 67 22 20','(071) 23 67 22 21');
INSERT INTO "Customers" VALUES('THEBI','The Big Cheese','Liz Nixon','Marketing Manager','89 Jefferson Way Suite 2','Portland','OR','97201','USA','(503) 555-3612',NULL);
INSERT INTO "Customers" VALUES('THECR','The Cracker Box','Liu Wong','Marketing Assistant','55 Grizzly Peak Rd.','Butte','MT','59801','USA','(406) 555-5834','(406) 555-8083');
INSERT INTO "Customers" VALUES('TOMSP','Toms Spezialitäten','Karin Josephs','Marketing Manager','Luisenstr. 48','Münster',NULL,'44087','Germany','0251-031259','0251-035695');
INSERT INTO "Customers" VALUES('TORTU','Tortuga Restaurante','Miguel Angel Paolino','Owner','Avda. Azteca 123','México D.F.',NULL,'05033','Mexico','(5) 555-2933',NULL);

INSERT INTO "Customers" VALUES('TRADH','Tradição Hipermercados','Anabela Domingues','Sales Representative','Av. Inês de Castro, 414','Sao Paulo','SP','05634-030','Brazil','(11) 555-2167','(11) 555-2168');
INSERT INTO "Customers" VALUES('TRAIH','Trail''s Head Gourmet Provisioners','Helvetius Nagy','Sales Associate','722 DaVinci Blvd.','Kirkland','WA','98034','USA','(206) 555-8257','(206) 555-2174');
INSERT INTO "Customers" VALUES('VAFFE','Vaffeljernet','Palle Ibsen','Sales Manager','Smagsloget 45','Århus',NULL,'8200','Denmark','86 21 32 43','86 22 33 44');
INSERT INTO "Customers" VALUES('VICTE','Victuailles en stock','Mary Saveley','Sales Agent','2, rue du Commerce','Lyon',NULL,'69004','France','78.32.54.86','78.32.54.87');
INSERT INTO "Customers" VALUES('VINET','Vins et alcools Chevalier','Paul Henriot','Accounting Manager','59 rue de l''Abbaye','Reims',NULL,'51100','France','26.47.15.10','26.47.15.11');
INSERT INTO "Customers" VALUES('WANDK','Die Wandernde Kuh','Rita Müller','Sales Representative','Adenauerallee 900','Stuttgart',NULL,'70563','Germany','0711-020361','0711-035428');
INSERT INTO "Customers" VALUES('WARTH','Wartian Herkku','Pirkko Koskitalo','Accounting Manager','Torikatu 38','Oulu',NULL,'90110','Finland','981-443655','981-443655');
INSERT INTO "Customers" VALUES('WELLI','Wellington Importadora','Paula Parente','Sales Manager','Rua do Mercado, 12','Resende','SP','08737-363','Brazil','(14) 555-8122',NULL);
INSERT INTO "Customers" VALUES('WHITC','White Clover Markets','Karl Jablonski','Owner','305 - 14th Ave. S. Suite 3B','Seattle','WA','98128','USA','(206) 555-4112','(206) 555-4115');
INSERT INTO "Customers" VALUES('WILMK','Wilman Kala','Matti Karttunen','Owner/Marketing Assistant','Keskuskatu 45','Helsinki',NULL,'21240','Finland','90-224 8858','90-224 8858');
INSERT INTO "Customers" VALUES('WOLZA','Wolski  Zajazd','Zbyszek Piestrzeniewicz','Owner','ul. Filtrowa 68','Warszawa',NULL,'01-012','Poland','(26) 642-7012','(26) 642-7012');

INSERT INTO "Employees"("EmployeeID","LastName","FirstName","Title","TitleOfCourtesy","BirthDate","HireDate","Address","City","Region","PostalCode","Country","HomePhone","Extension","Photo","Notes","ReportsTo","PhotoPath") VALUES(1,'Davolio','Nancy','Sales Representative','Ms.','12/08/1948','05/01/1992','507 - 20th Ave. E.
Apt. 2A','Seattle','WA','98122','USA','(206) 555-9857','5467',E'[encoded data, 43252 characters]','Education includes a BA in psychology from Colorado State University in 1970.  She also completed "The Art of the Cold Call."  Nancy is a member of Toastmasters International.',2,'http://accweb/emmployees/davolio.bmp');

INSERT INTO "Employees"("EmployeeID","LastName","FirstName","Title","TitleOfCourtesy","BirthDate","HireDate","Address","City","Region","PostalCode","Country","HomePhone","Extension","Photo","Notes","ReportsTo","PhotoPath") VALUES(2,'Fuller','Andrew','Vice President, Sales','Dr.','02/19/1952','08/14/1992','908 W. Capital Way','Tacoma','WA','98401','USA','(206) 555-9482','3457',E'[encoded data, 43252 characters]','Andrew received his BTS commercial in 1974 and a Ph.D. in international marketing from the University of Dallas in 1981.  He is fluent in French and Italian and reads German.  He joined the company as a sales representative, was promoted to sales manager in January 1992 and to vice president of sales in March 1993.  Andrew is a member of the Sales Management Roundtable, the Seattle Chamber of Commerce, and the Pacific Rim Importers Association.',NULL,'http://accweb/emmployees/fuller.bmp');

INSERT INTO "Employees"("EmployeeID","LastName","FirstName","Title","TitleOfCourtesy","BirthDate","HireDate","Address","City","Region","PostalCode","Country","HomePhone","Extension","Photo","Notes","ReportsTo","PhotoPath") VALUES(3,'Leverling','Janet','Sales Representative','Ms.','08/30/1963','04/01/1992','722 Moss Bay Blvd.','Kirkland','WA','98033','USA','(206) 555-3412','3355',E'[encoded data, 43444 characters]','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');

INSERT INTO "Employees"("EmployeeID","LastName","FirstName","Title","TitleOfCourtesy","BirthDate","HireDate","Address","City","Region","PostalCode","Country","HomePhone","Extension","Photo","Notes","ReportsTo","PhotoPath") VALUES(4,'Peacock','Margaret','Sales Representative','Mrs.','09/19/1937','05/03/1993','4110 Old Redmond Rd.','Redmond','WA','98052','USA','(206) 555-8122','5176',E'[encoded data, 43252 characters]','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');

INSERT INTO "Employees"("EmployeeID","LastName","FirstName","Title","TitleOfCourtesy","BirthDate","HireDate","Address","City","Region","PostalCode","Country","HomePhone","Extension","Photo","Notes","ReportsTo","PhotoPath") VALUES(5,'Buchanan','Steven','Sales Manager','Mr.','03/04/1955','10/17/1993','14 Garrett Hill','London',NULL,'SW1 8JR','UK','(71) 555-4848','3453',E'[encoded data, 43252 characters]','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 post in London.  He was promoted to sales manager in March 1993.  Mr. Buchanan has completed the courses "Successful Telemarketing" and "International Sales Management."  He is fluent in French.',2,'http://accweb/emmployees/buchanan.bmp');

