· 7 years ago · Dec 09, 2018, 08:56 PM
1/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
3/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
4/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
5
6DROP TABLE IF EXISTS `CountryLookup`;
7CREATE TABLE CountryLookup (
8 CountryID int(10) NOT NULL AUTO_INCREMENT,
9 Name varchar(100) DEFAULT '',
10 primary key (CountryID));
11
12DROP TABLE IF EXISTS Warehouse;
13create table Warehouse(
14 WarehouseID int(10) NOT NULL AUTO_INCREMENT,
15 Name varchar(40) default '',
16 primary key (WarehouseID));
17
18
19/*Data for the table `suppliers` */
20
21insert into `suppliers`(`SupplierID`,`CompanyName`,`ContactName`,`ContactTitle`,`Address`,`City`,`Region`,`PostalCode`,`Country`,`Phone`,`Fax`,`HomePage`) values (1,'Exotic Liquids','Charlotte Cooper','Purchasing Manager','49 Gilbert St.','London','','EC1 4SD','UK','(171) 555-2222','',''),(2,'New Orleans Cajun Delights','Shelley Burke','Order Administrator','P.O. Box 78934','New Orleans','LA','70117','USA','(100) 555-4822','','#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',''),(4,'Tokyo Traders','Yoshi Nagase','Marketing Manager','9-8 Sekimai\r\nMusashino-shi','Tokyo','','100','Japan','(03) 3555-5011','',''),(5,'Cooperativa de Quesos \'Las Cabras\'','Antonio del Valle Saavedra ','Export Administrator','Calle del Rosal 4','Oviedo','Asturias','33007','Spain','(98) 598 76 54','',''),(6,'Mayumi\'s','Mayumi Ohno','Marketing Representative','92 Setsuko\r\nChuo-ku','Osaka','','545','Japan','(06) 431-7877','','Mayumi\'s (on the World Wide Web)#http://www.microsoft.com/accessdev/sampleapps/mayumi.htm#'),(7,'Pavlova, Ltd.','Ian Devling','Marketing Manager','74 Rose St.\r\nMoonie Ponds','Melbourne','Victoria','3058','Australia','(03) 444-2343','(03) 444-6588',''),(8,'Specialty Biscuits, Ltd.','Peter Wilson','Sales Representative','29 King\'s Way','Manchester','','M14 GSD','UK','(161) 555-4448','',''),(9,'PB Knäckebröd AB','Lars Peterson','Sales Agent','Kaloadagatan 13','Göteborg','','S-345 67','Sweden ','031-987 65 43','031-987 65 91',''),(10,'Refrescos Americanas LTDA','Carlos Diaz','Marketing Manager','Av. das Americanas 12.890','São Paulo','','5442','Brazil','(11) 555 4640','',''),(11,'Heli Süßwaren GmbH & Co. KG','Petra Winkler','Sales Manager','Tiergartenstraße 5','Berlin','','10785','Germany','(010) 9984510','',''),(12,'Plutzer Lebensmittelgroßmärkte AG','Martin Bein','International Marketing Mgr.','Bogenallee 51','Frankfurt','','60439','Germany','(069) 992755','','Plutzer (on the World Wide Web)#http://www.microsoft.com/accessdev/sampleapps/plutzer.htm#'),(13,'Nord-Ost-Fisch Handelsgesellschaft mbH','Sven Petersen','Coordinator Foreign Markets','Frahmredder 112a','Cuxhaven','','27478','Germany','(04721) 8713','(04721) 8714',''),(14,'Formaggi Fortini s.r.l.','Elio Rossi','Sales Representative','Viale Dante, 75','Ravenna','','48100','Italy','(0544) 60323','(0544) 60603','#FORMAGGI.HTM#'),(15,'Norske Meierier','Beate Vileid','Marketing Manager','Hatlevegen 5','Sandvika','','1320','Norway','(0)2-953010','',''),(16,'Bigfoot Breweries','Cheryl Saylor','Regional Account Rep.','3400 - 8th Avenue\r\nSuite 210','Bend','OR','97101','USA','(503) 555-9931','',''),(17,'Svensk Sjöföda AB','Michael Björn','Sales Representative','Brovallavägen 231','Stockholm','','S-123 45','Sweden','08-123 45 67','',''),(18,'Aux joyeux ecclésiastiques','Guylène Nodier','Sales Manager','203, Rue des Francs-Bourgeois','Paris','','75004','France','(1) 03.83.00.68','(1) 03.83.00.62',''),(19,'New England Seafood Cannery','Robb Merchant','Wholesale Account Agent','Order Processing Dept.\r\n2100 Paul Revere Blvd.','Boston','MA','02134','USA','(617) 555-3267','(617) 555-3389',''),(20,'Leka Trading','Chandra Leka','Owner','471 Serangoon Loop, Suite #402','Singapore','','0512','Singapore','555-8787','',''),(21,'Lyngbysild','Niels Petersen','Sales Manager','Lyngbysild\r\nFiskebakken 10','Lyngby','','2800','Denmark','43844108','43844115',''),(22,'Zaanse Snoepfabriek','Dirk Luchte','Accounting Manager','Verkoop\r\nRijnweg 22','Zaandam','','9999 ZZ','Netherlands','(12345) 1212','(12345) 1210',''),(23,'Karkki Oy','Anne Heikkonen','Product Manager','Valtakatu 12','Lappeenranta','','53120','Finland','(953) 10956','',''),(24,'G\'day, Mate','Wendy Mackenzie','Sales Representative','170 Prince Edward Parade\r\nHunter\'s Hill','Sydney','NSW','2042','Australia','(02) 555-5914','(02) 555-4873','G\'day Mate (on the World Wide Web)#http://www.microsoft.com/accessdev/sampleapps/gdaymate.htm#'),(25,'Ma Maison','Jean-Guy Lauzon','Marketing Manager','2960 Rue St. Laurent','Montréal','Québec','H1J 1C3','Canada','(514) 555-9022','',''),(26,'Pasta Buttini s.r.l.','Giovanni Giudici','Order Administrator','Via dei Gelsomini, 153','Salerno','','84100','Italy','(089) 6547665','(089) 6547667',''),(27,'Escargots Nouveaux','Marie Delamare','Sales Manager','22, rue H. Voiron','Montceau','','71300','France','85.57.00.07','',''),(28,'Gai pâturage','Eliane Noz','Sales Representative','Bat. B\r\n3, rue des Alpes','Annecy','','74000','France','38.76.98.06','38.76.98.58',''),(29,'Forêts d\'érables','Chantal Goulet','Accounting Manager','148 rue Chasseur','Ste-Hyacinthe','Québec','J2S 7S8','Canada','(514) 555-2955','(514) 555-2921','');
22
23/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
24/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
25/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
26/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
27
28ALTER TABLE employees
29 ADD FullCourtesyName varchar(65),
30 ADD CountryID int(10),
31 ADD CONSTRAINT FK_CountryID foreign key (CountryID) REFERENCES CountryLookup(countryID);
32
33ALTER TABLE products
34 ADD CONSTRAINT WarehouseID_NotNull CHECK (WarehouseID IS NOT NULL),
35 ADD CONSTRAINT FK_product_WarehouseID FOREIGN KEY (WarehouseID) REFERENCES Warehouse(WarehouseID);
36
37ALTER TABLE customers
38 ADD CountryID integer(10),
39 ADD CONSTRAINT FK_CountryID foreign key (CountryID) REFERENCES CountryLookup(countryID);
40
41ALTER TABLE orders
42 ADD CountryID integer(10),
43 ADD CONSTRAINT FK_CountryID foreign key (CountryID) REFERENCES CountryLookup(countryID);
44
45ALTER TABLE Suppliers
46 ADD CountryID integer(10),
47 ADD CONSTRAINT FK_CountryID foreign key (CountryID) REFERENCES CountryLookup(countryID);