SQLize Online / PHPize Online  /  SQLtest Online

/*Table structure for table `offices` */ DROP TABLE IF EXISTS `offices`; CREATE TABLE `offices` ( `officeCode` varchar(10) NOT NULL, `city` varchar(50) NOT NULL, `phone` varchar(50) NOT NULL, `addressLine1` varchar(50) NOT NULL, `addressLine2` varchar(50) DEFAULT NULL, `state` varchar(50) DEFAULT NULL, `country` varchar(50) NOT NULL, `postalCode` varchar(15) NOT NULL, `territory` varchar(10) NOT NULL, PRIMARY KEY (`officeCode`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*Data for the table `offices` */ insert into `offices`(`officeCode`,`city`,`phone`,`addressLine1`,`addressLine2`,`state`,`country`,`postalCode`,`territory`) values ('1','San Francisco','+1 650 219 4782','100 Market Street','Suite 300','CA','USA','94080','NA'), ('2','Boston','+1 215 837 0825','1550 Court Place','Suite 102','MA','USA','02107','NA'), ('3','NYC','+1 212 555 3000','523 East 53rd Street','apt. 5A','NY','USA','10022','NA'), ('4','Paris','+33 14 723 4404','43 Rue Jouffroy D\'abbans',NULL,NULL,'France','75017','EMEA'), ('5','Tokyo','+81 33 224 5000','4-1 Kioicho',NULL,'Chiyoda-Ku','Japan','102-8578','Japan'), ('6','Sydney','+61 2 9264 2451','5-11 Wentworth Avenue','Floor #2',NULL,'Australia','NSW 2010','APAC'), ('7','London','+44 20 7877 2041','25 Old Broad Street','Level 7',NULL,'UK','EC2N 1HN','EMEA'); /*Table structure for table `employees` */ DROP TABLE IF EXISTS `employees`; CREATE TABLE `employees` ( `employeeNumber` int(11) NOT NULL, `lastName` varchar(50) NOT NULL, `firstName` varchar(50) NOT NULL, `extension` varchar(10) NOT NULL, `email` varchar(100) NOT NULL, `officeCode` varchar(10) NOT NULL, `reportsTo` int(11) DEFAULT NULL, `jobTitle` varchar(50) NOT NULL, PRIMARY KEY (`employeeNumber`), KEY `reportsTo` (`reportsTo`), KEY `officeCode` (`officeCode`), CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`reportsTo`) REFERENCES `employees` (`employeeNumber`), CONSTRAINT `employees_ibfk_2` FOREIGN KEY (`officeCode`) REFERENCES `offices` (`officeCode`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*Data for the table `employees` */ insert into `employees`(`employeeNumber`,`lastName`,`firstName`,`extension`,`email`,`officeCode`,`reportsTo`,`jobTitle`) values (1002,'Murphy','Diane','x5800','dmurphy@classicmodelcars.com','1',NULL,'President'), (1056,'Patterson','Mary','x4611','mpatterso@classicmodelcars.com','1',1002,'VP Sales'), (1076,'Firrelli','Jeff','x9273','jfirrelli@classicmodelcars.com','1',1002,'VP Marketing'), (1088,'Patterson','William','x4871','wpatterson@classicmodelcars.com','6',1056,'Sales Manager (APAC)'), (1102,'Bondur','Gerard','x5408','gbondur@classicmodelcars.com','4',1056,'Sale Manager (EMEA)'), (1143,'Bow','Anthony','x5428','abow@classicmodelcars.com','1',1056,'Sales Manager (NA)'), (1165,'Jennings','Leslie','x3291','ljennings@classicmodelcars.com','1',1143,'Sales Rep'), (1166,'Thompson','Leslie','x4065','lthompson@classicmodelcars.com','1',1143,'Sales Rep'), (1188,'Firrelli','Julie','x2173','jfirrelli@classicmodelcars.com','2',1143,'Sales Rep'), (1216,'Patterson','Steve','x4334','spatterson@classicmodelcars.com','2',1143,'Sales Rep'), (1286,'Tseng','Foon Yue','x2248','ftseng@classicmodelcars.com','3',1143,'Sales Rep'), (1323,'Vanauf','George','x4102','gvanauf@classicmodelcars.com','3',1143,'Sales Rep'), (1337,'Bondur','Loui','x6493','lbondur@classicmodelcars.com','4',1102,'Sales Rep'), (1370,'Hernandez','Gerard','x2028','ghernande@classicmodelcars.com','4',1102,'Sales Rep'), (1401,'Castillo','Pamela','x2759','pcastillo@classicmodelcars.com','4',1102,'Sales Rep'), (1501,'Bott','Larry','x2311','lbott@classicmodelcars.com','7',1102,'Sales Rep'), (1504,'Jones','Barry','x102','bjones@classicmodelcars.com','7',1102,'Sales Rep'), (1611,'Fixter','Andy','x101','afixter@classicmodelcars.com','6',1088,'Sales Rep'), (1612,'Marsh','Peter','x102','pmarsh@classicmodelcars.com','6',1088,'Sales Rep'), (1619,'King','Tom','x103','tking@classicmodelcars.com','6',1088,'Sales Rep'), (1621,'Nishi','Mami','x101','mnishi@classicmodelcars.com','5',1056,'Sales Rep'), (1625,'Kato','Yoshimi','x102','ykato@classicmodelcars.com','5',1621,'Sales Rep'), (1702,'Gerard','Martin','x2312','mgerard@classicmodelcars.com','4',1102,'Sales Rep'); CREATE TABLE `customers` ( `customerNumber` int(11) NOT NULL, `customerName` varchar(50) NOT NULL, `contactLastName` varchar(50) NOT NULL, `contactFirstName` varchar(50) NOT NULL, `phone` varchar(50) NOT NULL, `addressLine1` varchar(50) NOT NULL, `addressLine2` varchar(50) DEFAULT NULL, `city` varchar(50) NOT NULL, `state` varchar(50) DEFAULT NULL, `postalCode` varchar(15) DEFAULT NULL, `country` varchar(50) NOT NULL, `salesRepEmployeeNumber` int(11) DEFAULT NULL, `creditLimit` decimal(10,2) DEFAULT NULL, PRIMARY KEY (`customerNumber`), KEY `salesRepEmployeeNumber` (`salesRepEmployeeNumber`), CONSTRAINT `customers_ibfk_1` FOREIGN KEY (`salesRepEmployeeNumber`) REFERENCES `employees` (`employeeNumber`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*Data for the table `customers` */ insert into `customers`(`customerNumber`,`customerName`,`contactLastName`,`contactFirstName`,`phone`,`addressLine1`,`addressLine2`,`city`,`state`,`postalCode`,`country`,`salesRepEmployeeNumber`,`creditLimit`) values (103,'Atelier graphique','Schmitt','Carine ','40.32.2555','54, rue Royale',NULL,'Nantes',NULL,'44000','France',1370,'21000.00'), (112,'Signal Gift Stores','King','Jean','7025551838','8489 Strong St.',NULL,'Las Vegas','NV','83030','USA',1166,'71800.00'), (114,'Australian Collectors, Co.','Ferguson','Peter','03 9520 4555','636 St Kilda Road','Level 3','Melbourne','Victoria','3004','Australia',1611,'117300.00'), (119,'La Rochelle Gifts','Labrune','Janine ','40.67.8555','67, rue des Cinquante Otages',NULL,'Nantes',NULL,'44000','France',1370,'118200.00'), (121,'Baane Mini Imports','Bergulfsen','Jonas ','07-98 9555','Erling Skakkes gate 78',NULL,'Stavern',NULL,'4110','Norway',1504,'81700.00'), (124,'Mini Gifts Distributors Ltd.','Nelson','Susan','4155551450','5677 Strong St.',NULL,'San Rafael','CA','97562','USA',1165,'210500.00'), (125,'Havel & Zbyszek Co','Piestrzeniewicz','Zbyszek ','(26) 642-7555','ul. Filtrowa 68',NULL,'Warszawa',NULL,'01-012','Poland',NULL,'0.00'), (128,'Blauer See Auto, Co.','Keitel','Roland','+49 69 66 90 2555','Lyonerstr. 34',NULL,'Frankfurt',NULL,'60528','Germany',1504,'59700.00'), (129,'Mini Wheels Co.','Murphy','Julie','6505555787','5557 North Pendale Street',NULL,'San Francisco','CA','94217','USA',1165,'64600.00'), (131,'Land of Toys Inc.','Lee','Kwai','2125557818','897 Long Airport Avenue',NULL,'NYC','NY','10022','USA',1323,'114900.00'), (141,'Euro+ Shopping Channel','Freyre','Diego ','(91) 555 94 44','C/ Moralzarzal, 86',NULL,'Madrid',NULL,'28034','Spain',1370,'227600.00'), (144,'Volvo Model Replicas, Co','Berglund','Christina ','0921-12 3555','Berguvsvägen 8',NULL,'Luleå',NULL,'S-958 22','Sweden',1504,'53100.00'), (145,'Danish Wholesale Imports','Petersen','Jytte ','31 12 3555','Vinbæltet 34',NULL,'Kobenhavn',NULL,'1734','Denmark',1401,'83400.00'), (146,'Saveley & Henriot, Co.','Saveley','Mary ','78.32.5555','2, rue du Commerce',NULL,'Lyon',NULL,'69004','France',1337,'123900.00'), (148,'Dragon Souveniers, Ltd.','Natividad','Eric','+65 221 7555','Bronz Sok.','Bronz Apt. 3/6 Tesvikiye','Singapore',NULL,'079903','Singapore',1621,'103800.00'), (151,'Muscle Machine Inc','Young','Jeff','2125557413','4092 Furth Circle','Suite 400','NYC','NY','10022','USA',1286,'138500.00'), (157,'Diecast Classics Inc.','Leong','Kelvin','2155551555','7586 Pompton St.',NULL,'Allentown','PA','70267','USA',1216,'100600.00'), (161,'Technics Stores Inc.','Hashimoto','Juri','6505556809','9408 Furth Circle',NULL,'Burlingame','CA','94217','USA',1165,'84600.00'), (166,'Handji Gifts& Co','Victorino','Wendy','+65 224 1555','106 Linden Road Sandown','2nd Floor','Singapore',NULL,'069045','Singapore',1612,'97900.00'), (167,'Herkku Gifts','Oeztan','Veysel','+47 2267 3215','Brehmen St. 121','PR 334 Sentrum','Bergen',NULL,'N 5804','Norway ',1504,'96800.00'), (168,'American Souvenirs Inc','Franco','Keith','2035557845','149 Spinnaker Dr.','Suite 101','New Haven','CT','97823','USA',1286,'0.00'), (169,'Porto Imports Co.','de Castro','Isabel ','(1) 356-5555','Estrada da saúde n. 58',NULL,'Lisboa',NULL,'1756','Portugal',NULL,'0.00'), (171,'Daedalus Designs Imports','Rancé','Martine ','20.16.1555','184, chaussée de Tournai',NULL,'Lille',NULL,'59000','France',1370,'82900.00'), (172,'La Corne D\'abondance, Co.','Bertrand','Marie','(1) 42.34.2555','265, boulevard Charonne',NULL,'Paris',NULL,'75012','France',1337,'84300.00'), (173,'Cambridge Collectables Co.','Tseng','Jerry','6175555555','4658 Baden Av.',NULL,'Cambridge','MA','51247','USA',1188,'43400.00'), (175,'Gift Depot Inc.','King','Julie','2035552570','25593 South Bay Ln.',NULL,'Bridgewater','CT','97562','USA',1323,'84300.00'), (177,'Osaka Souveniers Co.','Kentary','Mory','+81 06 6342 5555','1-6-20 Dojima',NULL,'Kita-ku','Osaka',' 530-0003','Japan',1621,'81200.00'), (181,'Vitachrome Inc.','Frick','Michael','2125551500','2678 Kingston Rd.','Suite 101','NYC','NY','10022','USA',1286,'76400.00'), (186,'Toys of Finland, Co.','Karttunen','Matti','90-224 8555','Keskuskatu 45',NULL,'Helsinki',NULL,'21240','Finland',1501,'96500.00'), (187,'AV Stores, Co.','Ashworth','Rachel','(171) 555-1555','Fauntleroy Circus',NULL,'Manchester',NULL,'EC2 5NT','UK',1501,'136800.00'), (189,'Clover Collections, Co.','Cassidy','Dean','+353 1862 1555','25 Maiden Lane','Floor No. 4','Dublin',NULL,'2','Ireland',1504,'69400.00'), (198,'Auto-Moto Classics Inc.','Taylor','Leslie','6175558428','16780 Pompton St.',NULL,'Brickhaven','MA','58339','USA',1216,'23000.00'), (201,'UK Collectables, Ltd.','Devon','Elizabeth','(171) 555-2282','12, Berkeley Gardens Blvd',NULL,'Liverpool',NULL,'WX1 6LT','UK',1501,'92700.00'), (202,'Canadian Gift Exchange Network','Tamuri','Yoshi ','(604) 555-3392','1900 Oak St.',NULL,'Vancouver','BC','V3F 2K1','Canada',1323,'90300.00'), (204,'Online Mini Collectables','Barajas','Miguel','6175557555','7635 Spinnaker Dr.',NULL,'Brickhaven','MA','58339','USA',1188,'68700.00'), (205,'Toys4GrownUps.com','Young','Julie','6265557265','78934 Hillside Dr.',NULL,'Pasadena','CA','90003','USA',1166,'90700.00'), (206,'Asian Shopping Network, Co','Walker','Brydey','+612 9411 1555','Suntec Tower Three','8 Temasek','Singapore',NULL,'038988','Singapore',NULL,'0.00'), (209,'Mini Caravy','Citeaux','Frédérique ','88.60.1555','24, place Kléber',NULL,'Strasbourg',NULL,'67000','France',1370,'53800.00'), (211,'King Kong Collectables, Co.','Gao','Mike','+852 2251 1555','Bank of China Tower','1 Garden Road','Central Hong Kong',NULL,NULL,'Hong Kong',1621,'58600.00'), (216,'Enaco Distributors','Saavedra','Eduardo ','(93) 203 4555','Rambla de Cataluña, 23',NULL,'Barcelona',NULL,'08022','Spain',1702,'60300.00'), (219,'Boards & Toys Co.','Young','Mary','3105552373','4097 Douglas Av.',NULL,'Glendale','CA','92561','USA',1166,'11000.00'), (223,'Natürlich Autos','Kloss','Horst ','0372-555188','Taucherstraße 10',NULL,'Cunewalde',NULL,'01307','Germany',NULL,'0.00'), (227,'Heintze Collectables','Ibsen','Palle','86 21 3555','Smagsloget 45',NULL,'Århus',NULL,'8200','Denmark',1401,'120800.00'), (233,'Québec Home Shopping Network','Fresnière','Jean ','(514) 555-8054','43 rue St. Laurent',NULL,'Montréal','Québec','H1J 1C3','Canada',1286,'48700.00'), (237,'ANG Resellers','Camino','Alejandra ','(91) 745 6555','Gran Vía, 1',NULL,'Madrid',NULL,'28001','Spain',NULL,'0.00'), (239,'Collectable Mini Designs Co.','Thompson','Valarie','7605558146','361 Furth Circle',NULL,'San Diego','CA','91217','USA',1166,'105000.00'), (240,'giftsbymail.co.uk','Bennett','Helen ','(198) 555-8888','Garden House','Crowther Way 23','Cowes','Isle of Wight','PO31 7PJ','UK',1501,'93900.00'), (242,'Alpha Cognac','Roulet','Annette ','61.77.6555','1 rue Alsace-Lorraine',NULL,'Toulouse',NULL,'31000','France',1370,'61100.00'), (247,'Messner Shopping Network','Messner','Renate ','069-0555984','Magazinweg 7',NULL,'Frankfurt',NULL,'60528','Germany',NULL,'0.00'), (249,'Amica Models & Co.','Accorti','Paolo ','011-4988555','Via Monte Bianco 34',NULL,'Torino',NULL,'10100','Italy',1401,'113000.00'), (250,'Lyon Souveniers','Da Silva','Daniel','+33 1 46 62 7555','27 rue du Colonel Pierre Avia',NULL,'Paris',NULL,'75508','France',1337,'68100.00'), (256,'Auto Associés & Cie.','Tonini','Daniel ','30.59.8555','67, avenue de l\'Europe',NULL,'Versailles',NULL,'78000','France',1370,'77900.00'), (259,'Toms Spezialitäten, Ltd','Pfalzheim','Henriette ','0221-5554327','Mehrheimerstr. 369',NULL,'Köln',NULL,'50739','Germany',1504,'120400.00'), (260,'Royal Canadian Collectables, Ltd.','Lincoln','Elizabeth ','(604) 555-4555','23 Tsawassen Blvd.',NULL,'Tsawassen','BC','T2F 8M4','Canada',1323,'89600.00'), (273,'Franken Gifts, Co','Franken','Peter ','089-0877555','Berliner Platz 43',NULL,'München',NULL,'80805','Germany',NULL,'0.00'), (276,'Anna\'s Decorations, Ltd','O\'Hara','Anna','02 9936 8555','201 Miller Street','Level 15','North Sydney','NSW','2060','Australia',1611,'107800.00'), (278,'Rovelli Gifts','Rovelli','Giovanni ','035-640555','Via Ludovico il Moro 22',NULL,'Bergamo',NULL,'24100','Italy',1401,'119600.00'), (282,'Souveniers And Things Co.','Huxley','Adrian','+61 2 9495 8555','Monitor Money Building','815 Pacific Hwy','Chatswood','NSW','2067','Australia',1611,'93300.00'), (286,'Marta\'s Replicas Co.','Hernandez','Marta','6175558555','39323 Spinnaker Dr.',NULL,'Cambridge','MA','51247','USA',1216,'123700.00'), (293,'BG&E Collectables','Harrison','Ed','+41 26 425 50 01','Rte des Arsenaux 41 ',NULL,'Fribourg',NULL,'1700','Switzerland',NULL,'0.00'), (298,'Vida Sport, Ltd','Holz','Mihael','0897-034555','Grenzacherweg 237',NULL,'Genève',NULL,'1203','Switzerland',1702,'141300.00'), (299,'Norway Gifts By Mail, Co.','Klaeboe','Jan','+47 2212 1555','Drammensveien 126A','PB 211 Sentrum','Oslo',NULL,'N 0106','Norway ',1504,'95100.00'), (303,'Schuyler Imports','Schuyler','Bradley','+31 20 491 9555','Kingsfordweg 151',NULL,'Amsterdam',NULL,'1043 GR','Netherlands',NULL,'0.00'), (307,'Der Hund Imports','Andersen','Mel','030-0074555','Obere Str. 57',NULL,'Berlin',NULL,'12209','Germany',NULL,'0.00'), (311,'Oulu Toy Supplies, Inc.','Koskitalo','Pirkko','981-443655','Torikatu 38',NULL,'Oulu',NULL,'90110','Finland',1501,'90500.00'), (314,'Petit Auto','Dewey','Catherine ','(02) 5554 67','Rue Joseph-Bens 532',NULL,'Bruxelles',NULL,'B-1180','Belgium',1401,'79900.00'), (319,'Mini Classics','Frick','Steve','9145554562','3758 North Pendale Street',NULL,'White Plains','NY','24067','USA',1323,'102700.00'), (320,'Mini Creations Ltd.','Huang','Wing','5085559555','4575 Hillside Dr.',NULL,'New Bedford','MA','50553','USA',1188,'94500.00'), (321,'Corporate Gift Ideas Co.','Brown','Julie','6505551386','7734 Strong St.',NULL,'San Francisco','CA','94217','USA',1165,'105000.00'), (323,'Down Under Souveniers, Inc','Graham','Mike','+64 9 312 5555','162-164 Grafton Road','Level 2','Auckland ',NULL,NULL,'New Zealand',1612,'88000.00'), (324,'Stylish Desk Decors, Co.','Brown','Ann ','(171) 555-0297','35 King George',NULL,'London',NULL,'WX3 6FW','UK',1501,'77000.00'), (328,'Tekni Collectables Inc.','Brown','William','2015559350','7476 Moss Rd.',NULL,'Newark','NJ','94019','USA',1323,'43000.00'), (333,'Australian Gift Network, Co','Calaghan','Ben','61-7-3844-6555','31 Duncan St. West End',NULL,'South Brisbane','Queensland','4101','Australia',1611,'51600.00'), (334,'Suominen Souveniers','Suominen','Kalle','+358 9 8045 555','Software Engineering Center','SEC Oy','Espoo',NULL,'FIN-02271','Finland',1501,'98800.00'), (335,'Cramer Spezialitäten, Ltd','Cramer','Philip ','0555-09555','Maubelstr. 90',NULL,'Brandenburg',NULL,'14776','Germany',NULL,'0.00'), (339,'Classic Gift Ideas, Inc','Cervantes','Francisca','2155554695','782 First Street',NULL,'Philadelphia','PA','71270','USA',1188,'81100.00'), (344,'CAF Imports','Fernandez','Jesus','+34 913 728 555','Merchants House','27-30 Merchant\'s Quay','Madrid',NULL,'28023','Spain',1702,'59600.00'), (347,'Men \'R\' US Retailers, Ltd.','Chandler','Brian','2155554369','6047 Douglas Av.',NULL,'Los Angeles','CA','91003','USA',1166,'57700.00'), (348,'Asian Treasures, Inc.','McKenna','Patricia ','2967 555','8 Johnstown Road',NULL,'Cork','Co. Cork',NULL,'Ireland',NULL,'0.00'), (350,'Marseille Mini Autos','Lebihan','Laurence ','91.24.4555','12, rue des Bouchers',NULL,'Marseille',NULL,'13008','France',1337,'65000.00'), (353,'Reims Collectables','Henriot','Paul ','26.47.1555','59 rue de l\'Abbaye',NULL,'Reims',NULL,'51100','France',1337,'81100.00'), (356,'SAR Distributors, Co','Kuger','Armand','+27 21 550 3555','1250 Pretorius Street',NULL,'Hatfield','Pretoria','0028','South Africa',NULL,'0.00'), (357,'GiftsForHim.com','MacKinlay','Wales','64-9-3763555','199 Great North Road',NULL,'Auckland',NULL,NULL,'New Zealand',1612,'77700.00'), (361,'Kommission Auto','Josephs','Karin','0251-555259','Luisenstr. 48',NULL,'Münster',NULL,'44087','Germany',NULL,'0.00'), (362,'Gifts4AllAges.com','Yoshido','Juri','6175559555','8616 Spinnaker Dr.',NULL,'Boston','MA','51003','USA',1216,'41900.00'), (363,'Online Diecast Creations Co.','Young','Dorothy','6035558647','2304 Long Airport Avenue',NULL,'Nashua','NH','62005','USA',1216,'114200.00'), (369,'Lisboa Souveniers, Inc','Rodriguez','Lino ','(1) 354-2555','Jardim das rosas n. 32',NULL,'Lisboa',NULL,'1675','Portugal',NULL,'0.00'), (376,'Precious Collectables','Urs','Braun','0452-076555','Hauptstr. 29',NULL,'Bern',NULL,'3012','Switzerland',1702,'0.00'), (379,'Collectables For Less Inc.','Nelson','Allen','6175558555','7825 Douglas Av.',NULL,'Brickhaven','MA','58339','USA',1188,'70700.00'), (381,'Royale Belge','Cartrain','Pascale ','(071) 23 67 2555','Boulevard Tirou, 255',NULL,'Charleroi',NULL,'B-6000','Belgium',1401,'23500.00'), (382,'Salzburg Collectables','Pipps','Georg ','6562-9555','Geislweg 14',NULL,'Salzburg',NULL,'5020','Austria',1401,'71700.00'), (385,'Cruz & Sons Co.','Cruz','Arnold','+63 2 555 3587','15 McCallum Street','NatWest Center #13-03','Makati City',NULL,'1227 MM','Philippines',1621,'81500.00'), (386,'L\'ordine Souveniers','Moroni','Maurizio ','0522-556555','Strada Provinciale 124',NULL,'Reggio Emilia',NULL,'42100','Italy',1401,'121400.00'), (398,'Tokyo Collectables, Ltd','Shimamura','Akiko','+81 3 3584 0555','2-2-8 Roppongi',NULL,'Minato-ku','Tokyo','106-0032','Japan',1621,'94400.00'), (406,'Auto Canal+ Petit','Perrier','Dominique','(1) 47.55.6555','25, rue Lauriston',NULL,'Paris',NULL,'75016','France',1337,'95000.00'), (409,'Stuttgart Collectable Exchange','Müller','Rita ','0711-555361','Adenauerallee 900',NULL,'Stuttgart',NULL,'70563','Germany',NULL,'0.00'), (412,'Extreme Desk Decorations, Ltd','McRoy','Sarah','04 499 9555','101 Lambton Quay','Level 11','Wellington',NULL,NULL,'New Zealand',1612,'86800.00'), (415,'Bavarian Collectables Imports, Co.','Donnermeyer','Michael',' +49 89 61 08 9555','Hansastr. 15',NULL,'Munich',NULL,'80686','Germany',1504,'77000.00'), (424,'Classic Legends Inc.','Hernandez','Maria','2125558493','5905 Pompton St.','Suite 750','NYC','NY','10022','USA',1286,'67500.00'), (443,'Feuer Online Stores, Inc','Feuer','Alexander ','0342-555176','Heerstr. 22',NULL,'Leipzig',NULL,'04179','Germany',NULL,'0.00'), (447,'Gift Ideas Corp.','Lewis','Dan','2035554407','2440 Pompton St.',NULL,'Glendale','CT','97561','USA',1323,'49700.00'), (448,'Scandinavian Gift Ideas','Larsson','Martha','0695-34 6555','Åkergatan 24',NULL,'Bräcke',NULL,'S-844 67','Sweden',1504,'116400.00'), (450,'The Sharp Gifts Warehouse','Frick','Sue','4085553659','3086 Ingle Ln.',NULL,'San Jose','CA','94217','USA',1165,'77600.00'), (452,'Mini Auto Werke','Mendel','Roland ','7675-3555','Kirchgasse 6',NULL,'Graz',NULL,'8010','Austria',1401,'45300.00'), (455,'Super Scale Inc.','Murphy','Leslie','2035559545','567 North Pendale Street',NULL,'New Haven','CT','97823','USA',1286,'95400.00'), (456,'Microscale Inc.','Choi','Yu','2125551957','5290 North Pendale Street','Suite 200','NYC','NY','10022','USA',1286,'39800.00'), (458,'Corrida Auto Replicas, Ltd','Sommer','Martín ','(91) 555 22 82','C/ Araquil, 67',NULL,'Madrid',NULL,'28023','Spain',1702,'104600.00'), (459,'Warburg Exchange','Ottlieb','Sven ','0241-039123','Walserweg 21',NULL,'Aachen',NULL,'52066','Germany',NULL,'0.00'), (462,'FunGiftIdeas.com','Benitez','Violeta','5085552555','1785 First Street',NULL,'New Bedford','MA','50553','USA',1216,'85800.00'), (465,'Anton Designs, Ltd.','Anton','Carmen','+34 913 728555','c/ Gobelas, 19-1 Urb. La Florida',NULL,'Madrid',NULL,'28023','Spain',NULL,'0.00'), (471,'Australian Collectables, Ltd','Clenahan','Sean','61-9-3844-6555','7 Allen Street',NULL,'Glen Waverly','Victoria','3150','Australia',1611,'60300.00'), (473,'Frau da Collezione','Ricotti','Franco','+39 022515555','20093 Cologno Monzese','Alessandro Volta 16','Milan',NULL,NULL,'Italy',1401,'34800.00'), (475,'West Coast Collectables Co.','Thompson','Steve','3105553722','3675 Furth Circle',NULL,'Burbank','CA','94019','USA',1166,'55400.00'), (477,'Mit Vergnügen & Co.','Moos','Hanna ','0621-08555','Forsterstr. 57',NULL,'Mannheim',NULL,'68306','Germany',NULL,'0.00'), (480,'Kremlin Collectables, Co.','Semenov','Alexander ','+7 812 293 0521','2 Pobedy Square',NULL,'Saint Petersburg',NULL,'196143','Russia',NULL,'0.00'), (481,'Raanan Stores, Inc','Altagar,G M','Raanan','+ 972 9 959 8555','3 Hagalim Blv.',NULL,'Herzlia',NULL,'47625','Israel',NULL,'0.00'), (484,'Iberia Gift Imports, Corp.','Roel','José Pedro ','(95) 555 82 82','C/ Romero, 33',NULL,'Sevilla',NULL,'41101','Spain',1702,'65700.00'), (486,'Motor Mint Distributors Inc.','Salazar','Rosa','2155559857','11328 Douglas Av.',NULL,'Philadelphia','PA','71270','USA',1323,'72600.00'), (487,'Signal Collectibles Ltd.','Taylor','Sue','4155554312','2793 Furth Circle',NULL,'Brisbane','CA','94217','USA',1165,'60300.00'), (489,'Double Decker Gift Stores, Ltd','Smith','Thomas ','(171) 555-7555','120 Hanover Sq.',NULL,'London',NULL,'WA1 1DP','UK',1501,'43300.00'), (495,'Diecast Collectables','Franco','Valarie','6175552555','6251 Ingle Ln.',NULL,'Boston','MA','51003','USA',1188,'85100.00'), (496,'Kelly\'s Gift Shop','Snowden','Tony','+64 9 5555500','Arenales 1938 3\'A\'',NULL,'Auckland ',NULL,NULL,'New Zealand',1612,'110000.00'); /*Table structure for table `payments` */ DROP TABLE IF EXISTS `payments`; CREATE TABLE `payments` ( `customerNumber` int(11) NOT NULL, `checkNumber` varchar(50) NOT NULL, `paymentDate` date NOT NULL, `amount` decimal(10,2) NOT NULL, PRIMARY KEY (`customerNumber`,`checkNumber`), CONSTRAINT `payments_ibfk_1` FOREIGN KEY (`customerNumber`) REFERENCES `customers` (`customerNumber`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*Data for the table `payments` */ insert into `payments`(`customerNumber`,`checkNumber`,`paymentDate`,`amount`) values (103,'HQ336336','2004-10-19','6066.78'), (103,'JM555205','2003-06-05','14571.44'), (103,'OM314933','2004-12-18','1676.14'), (112,'BO864823','2004-12-17','14191.12'), (112,'HQ55022','2003-06-06','32641.98'), (112,'ND748579','2004-08-20','33347.88'), (114,'GG31455','2003-05-20','45864.03'), (114,'MA765515','2004-12-15','82261.22'), (114,'NP603840','2003-05-31','7565.08'), (114,'NR27552','2004-03-10','44894.74'), (119,'DB933704','2004-11-14','19501.82'), (119,'LN373447','2004-08-08','47924.19'), (119,'NG94694','2005-02-22','49523.67'), (121,'DB889831','2003-02-16','50218.95'), (121,'FD317790','2003-10-28','1491.38'), (121,'KI831359','2004-11-04','17876.32'), (121,'MA302151','2004-11-28','34638.14'), (124,'AE215433','2005-03-05','101244.59'), (124,'BG255406','2004-08-28','85410.87'), (124,'CQ287967','2003-04-11','11044.30'), (124,'ET64396','2005-04-16','83598.04'), (124,'HI366474','2004-12-27','47142.70'), (124,'HR86578','2004-11-02','55639.66'), (124,'KI131716','2003-08-15','111654.40'), (124,'LF217299','2004-03-26','43369.30'), (124,'NT141748','2003-11-25','45084.38'), (128,'DI925118','2003-01-28','10549.01'), (128,'FA465482','2003-10-18','24101.81'), (128,'FH668230','2004-03-24','33820.62'), (128,'IP383901','2004-11-18','7466.32'), (129,'DM826140','2004-12-08','26248.78'), (129,'ID449593','2003-12-11','23923.93'), (129,'PI42991','2003-04-09','16537.85'), (131,'CL442705','2003-03-12','22292.62'), (131,'MA724562','2004-12-02','50025.35'), (131,'NB445135','2004-09-11','35321.97'), (141,'AU364101','2003-07-19','36251.03'), (141,'DB583216','2004-11-01','36140.38'), (141,'DL460618','2005-05-19','46895.48'), (141,'HJ32686','2004-01-30','59830.55'), (141,'ID10962','2004-12-31','116208.40'), (141,'IN446258','2005-03-25','65071.26'), (141,'JE105477','2005-03-18','120166.58'), (141,'JN355280','2003-10-26','49539.37'), (141,'JN722010','2003-02-25','40206.20'), (141,'KT52578','2003-12-09','63843.55'), (141,'MC46946','2004-07-09','35420.74'), (141,'MF629602','2004-08-16','20009.53'), (141,'NU627706','2004-05-17','26155.91'), (144,'IR846303','2004-12-12','36005.71'), (144,'LA685678','2003-04-09','7674.94'), (145,'CN328545','2004-07-03','4710.73'), (145,'ED39322','2004-04-26','28211.70'), (145,'HR182688','2004-12-01','20564.86'), (145,'JJ246391','2003-02-20','53959.21'), (146,'FP549817','2004-03-18','40978.53'), (146,'FU793410','2004-01-16','49614.72'), (146,'LJ160635','2003-12-10','39712.10'), (148,'BI507030','2003-04-22','44380.15'), (148,'DD635282','2004-08-11','2611.84'), (148,'KM172879','2003-12-26','105743.00'), (148,'ME497970','2005-03-27','3516.04'), (151,'BF686658','2003-12-22','58793.53'), (151,'GB852215','2004-07-26','20314.44'), (151,'IP568906','2003-06-18','58841.35'), (151,'KI884577','2004-12-14','39964.63'), (157,'HI618861','2004-11-19','35152.12'), (157,'NN711988','2004-09-07','63357.13'), (161,'BR352384','2004-11-14','2434.25'), (161,'BR478494','2003-11-18','50743.65'), (161,'KG644125','2005-02-02','12692.19'), (161,'NI908214','2003-08-05','38675.13'), (166,'BQ327613','2004-09-16','38785.48'), (166,'DC979307','2004-07-07','44160.92'), (166,'LA318629','2004-02-28','22474.17'), (167,'ED743615','2004-09-19','12538.01'), (167,'GN228846','2003-12-03','85024.46'), (171,'GB878038','2004-03-15','18997.89'), (171,'IL104425','2003-11-22','42783.81'), (172,'AD832091','2004-09-09','1960.80'), (172,'CE51751','2004-12-04','51209.58'), (172,'EH208589','2003-04-20','33383.14'), (173,'GP545698','2004-05-13','11843.45'), (173,'IG462397','2004-03-29','20355.24'), (175,'CITI3434344','2005-05-19','28500.78'), (175,'IO448913','2003-11-19','24879.08'), (175,'PI15215','2004-07-10','42044.77'), (177,'AU750837','2004-04-17','15183.63'), (177,'CI381435','2004-01-19','47177.59'), (181,'CM564612','2004-04-25','22602.36'), (181,'GQ132144','2003-01-30','5494.78'), (181,'OH367219','2004-11-16','44400.50'), (186,'AE192287','2005-03-10','23602.90'), (186,'AK412714','2003-10-27','37602.48'), (186,'KA602407','2004-10-21','34341.08'), (187,'AM968797','2004-11-03','52825.29'), (187,'BQ39062','2004-12-08','47159.11'), (187,'KL124726','2003-03-27','48425.69'), (189,'BO711618','2004-10-03','17359.53'), (189,'NM916675','2004-03-01','32538.74'), (198,'FI192930','2004-12-06','9658.74'), (198,'HQ920205','2003-07-06','6036.96'), (198,'IS946883','2004-09-21','5858.56'), (201,'DP677013','2003-10-20','23908.24'), (201,'OO846801','2004-06-15','37258.94'), (202,'HI358554','2003-12-18','36527.61'), (202,'IQ627690','2004-11-08','33594.58'), (204,'GC697638','2004-08-13','51152.86'), (204,'IS150005','2004-09-24','4424.40'), (205,'GL756480','2003-12-04','3879.96'), (205,'LL562733','2003-09-05','50342.74'), (205,'NM739638','2005-02-06','39580.60'), (209,'BOAF82044','2005-05-03','35157.75'), (209,'ED520529','2004-06-21','4632.31'), (209,'PH785937','2004-05-04','36069.26'), (211,'BJ535230','2003-12-09','45480.79'), (216,'BG407567','2003-05-09','3101.40'), (216,'ML780814','2004-12-06','24945.21'), (216,'MM342086','2003-12-14','40473.86'), (219,'BN17870','2005-03-02','3452.75'), (219,'BR941480','2003-10-18','4465.85'), (227,'MQ413968','2003-10-31','36164.46'), (227,'NU21326','2004-11-02','53745.34'), (233,'BOFA23232','2005-05-20','29070.38'), (233,'II180006','2004-07-01','22997.45'), (233,'JG981190','2003-11-18','16909.84'), (239,'NQ865547','2004-03-15','80375.24'), (240,'IF245157','2004-11-16','46788.14'), (240,'JO719695','2004-03-28','24995.61'), (242,'AF40894','2003-11-22','33818.34'), (242,'HR224331','2005-06-03','12432.32'), (242,'KI744716','2003-07-21','14232.70'), (249,'IJ399820','2004-09-19','33924.24'), (249,'NE404084','2004-09-04','48298.99'), (250,'EQ12267','2005-05-17','17928.09'), (250,'HD284647','2004-12-30','26311.63'), (250,'HN114306','2003-07-18','23419.47'), (256,'EP227123','2004-02-10','5759.42'), (256,'HE84936','2004-10-22','53116.99'), (259,'EU280955','2004-11-06','61234.67'), (259,'GB361972','2003-12-07','27988.47'), (260,'IO164641','2004-08-30','37527.58'), (260,'NH776924','2004-04-24','29284.42'), (276,'EM979878','2005-02-09','27083.78'), (276,'KM841847','2003-11-13','38547.19'), (276,'LE432182','2003-09-28','41554.73'), (276,'OJ819725','2005-04-30','29848.52'), (278,'BJ483870','2004-12-05','37654.09'), (278,'GP636783','2003-03-02','52151.81'), (278,'NI983021','2003-11-24','37723.79'), (282,'IA793562','2003-08-03','24013.52'), (282,'JT819493','2004-08-02','35806.73'), (282,'OD327378','2005-01-03','31835.36'), (286,'DR578578','2004-10-28','47411.33'), (286,'KH910279','2004-09-05','43134.04'), (298,'AJ574927','2004-03-13','47375.92'), (298,'LF501133','2004-09-18','61402.00'), (299,'AD304085','2003-10-24','36798.88'), (299,'NR157385','2004-09-05','32260.16'), (311,'DG336041','2005-02-15','46770.52'), (311,'FA728475','2003-10-06','32723.04'), (311,'NQ966143','2004-04-25','16212.59'), (314,'LQ244073','2004-08-09','45352.47'), (314,'MD809704','2004-03-03','16901.38'), (319,'HL685576','2004-11-06','42339.76'), (319,'OM548174','2003-12-07','36092.40'), (320,'GJ597719','2005-01-18','8307.28'), (320,'HO576374','2003-08-20','41016.75'), (320,'MU817160','2003-11-24','52548.49'), (321,'DJ15149','2003-11-03','85559.12'), (321,'LA556321','2005-03-15','46781.66'), (323,'AL493079','2005-05-23','75020.13'), (323,'ES347491','2004-06-24','37281.36'), (323,'HG738664','2003-07-05','2880.00'), (323,'PQ803830','2004-12-24','39440.59'), (324,'DQ409197','2004-12-13','13671.82'), (324,'FP443161','2003-07-07','29429.14'), (324,'HB150714','2003-11-23','37455.77'), (328,'EN930356','2004-04-16','7178.66'), (328,'NR631421','2004-05-30','31102.85'), (333,'HL209210','2003-11-15','23936.53'), (333,'JK479662','2003-10-17','9821.32'), (333,'NF959653','2005-03-01','21432.31'), (334,'CS435306','2005-01-27','45785.34'), (334,'HH517378','2003-08-16','29716.86'), (334,'LF737277','2004-05-22','28394.54'), (339,'AP286625','2004-10-24','23333.06'), (339,'DA98827','2003-11-28','34606.28'), (344,'AF246722','2003-11-24','31428.21'), (344,'NJ906924','2004-04-02','15322.93'), (347,'DG700707','2004-01-18','21053.69'), (347,'LG808674','2003-10-24','20452.50'), (350,'BQ602907','2004-12-11','18888.31'), (350,'CI471510','2003-05-25','50824.66'), (350,'OB648482','2005-01-29','1834.56'), (353,'CO351193','2005-01-10','49705.52'), (353,'ED878227','2003-07-21','13920.26'), (353,'GT878649','2003-05-21','16700.47'), (353,'HJ618252','2005-06-09','46656.94'), (357,'AG240323','2003-12-16','20220.04'), (357,'NB291497','2004-05-15','36442.34'), (362,'FP170292','2004-07-11','18473.71'), (362,'OG208861','2004-09-21','15059.76'), (363,'HL575273','2004-11-17','50799.69'), (363,'IS232033','2003-01-16','10223.83'), (363,'PN238558','2003-12-05','55425.77'), (379,'CA762595','2005-02-12','28322.83'), (379,'FR499138','2003-09-16','32680.31'), (379,'GB890854','2004-08-02','12530.51'), (381,'BC726082','2004-12-03','12081.52'), (381,'CC475233','2003-04-19','1627.56'), (381,'GB117430','2005-02-03','14379.90'), (381,'MS154481','2003-08-22','1128.20'), (382,'CC871084','2003-05-12','35826.33'), (382,'CT821147','2004-08-01','6419.84'), (382,'PH29054','2004-11-27','42813.83'), (385,'BN347084','2003-12-02','20644.24'), (385,'CP804873','2004-11-19','15822.84'), (385,'EK785462','2003-03-09','51001.22'), (386,'DO106109','2003-11-18','38524.29'), (386,'HG438769','2004-07-18','51619.02'), (398,'AJ478695','2005-02-14','33967.73'), (398,'DO787644','2004-06-21','22037.91'), (398,'JPMR4544','2005-05-18','615.45'), (398,'KB54275','2004-11-29','48927.64'), (406,'BJMPR4545','2005-04-23','12190.85'), (406,'HJ217687','2004-01-28','49165.16'), (406,'NA197101','2004-06-17','25080.96'), (412,'GH197075','2004-07-25','35034.57'), (412,'PJ434867','2004-04-14','31670.37'), (415,'ER54537','2004-09-28','31310.09'), (424,'KF480160','2004-12-07','25505.98'), (424,'LM271923','2003-04-16','21665.98'), (424,'OA595449','2003-10-31','22042.37'), (447,'AO757239','2003-09-15','6631.36'), (447,'ER615123','2003-06-25','17032.29'), (447,'OU516561','2004-12-17','26304.13'), (448,'FS299615','2005-04-18','27966.54'), (448,'KR822727','2004-09-30','48809.90'), (450,'EF485824','2004-06-21','59551.38'), (452,'ED473873','2003-11-15','27121.90'), (452,'FN640986','2003-11-20','15130.97'), (452,'HG635467','2005-05-03','8807.12'), (455,'HA777606','2003-12-05','38139.18'), (455,'IR662429','2004-05-12','32239.47'), (456,'GJ715659','2004-11-13','27550.51'), (456,'MO743231','2004-04-30','1679.92'), (458,'DD995006','2004-11-15','33145.56'), (458,'NA377824','2004-02-06','22162.61'), (458,'OO606861','2003-06-13','57131.92'), (462,'ED203908','2005-04-15','30293.77'), (462,'GC60330','2003-11-08','9977.85'), (462,'PE176846','2004-11-27','48355.87'), (471,'AB661578','2004-07-28','9415.13'), (471,'CO645196','2003-12-10','35505.63'), (473,'LL427009','2004-02-17','7612.06'), (473,'PC688499','2003-10-27','17746.26'), (475,'JP113227','2003-12-09','7678.25'), (475,'PB951268','2004-02-13','36070.47'), (484,'GK294076','2004-10-26','3474.66'), (484,'JH546765','2003-11-29','47513.19'), (486,'BL66528','2004-04-14','5899.38'), (486,'HS86661','2004-11-23','45994.07'), (486,'JB117768','2003-03-20','25833.14'), (487,'AH612904','2003-09-28','29997.09'), (487,'PT550181','2004-02-29','12573.28'), (489,'OC773849','2003-12-04','22275.73'), (489,'PO860906','2004-01-31','7310.42'), (495,'BH167026','2003-12-26','59265.14'), (495,'FN155234','2004-05-14','6276.60'), (496,'EU531600','2005-05-25','30253.75'), (496,'MB342426','2003-07-16','32077.44'), (496,'MN89921','2004-12-31','52166.00'); /*select c.customerNumber, c.customerName, c.phone, SUM(p.amount) amount, FORMAT(SUM(p.amount), 2) amount_formatted from customers c, payments p where p.customerNumber=c.customerNumber group by c.customerNumber order by amount desc;*/ /*SELECT YEAR(paymentDate) AS Year, SUM(amount) AS TotalSales, FORMAT(SUM(amount), 2) amount_formatted FROM payments GROUP BY YEAR(paymentDate) ORDER BY Year desc;*/ SELECT YEAR(p.paymentDate) AS Year, c.customerName, SUM(p.amount) AS TotalSpent FROM payments p INNER JOIN customers c ON p.customerNumber = c.customerNumber GROUP BY YEAR(p.paymentDate), c.customerNumber ORDER BY Year;