uY3q5VMc

1DROP TABLE IF EXISTS goal;
2DROP TABLE IF EXISTS game;
3DROP TABLE IF EXISTS eteam;
4
5CREATE TABLE eteam(
6   id VARCHAR (3) NOT NULL,
7   teamname VARCHAR(30),
8   coach VARCHAR(60), 			
9   PRIMARY KEY (id)
10   ) ENGINE=innodb;
11
12CREATE TABLE game(
13   id DECIMAL (4) NOT NULL,
14   mdate VARCHAR(20),
15   stadium VARCHAR(60), 		
16   team1 VARCHAR(3),
17   team2 VARCHAR(3),		
18   PRIMARY KEY (id),
19   FOREIGN KEY(team1) REFERENCES eteam(id),
20   FOREIGN KEY(team2) REFERENCES eteam(id)
21   ) ENGINE=innodb;
22
23CREATE TABLE goal(
24   matchid DECIMAL (4) NOT NULL,
25   teamid VARCHAR (3) NOT NULL,
26   player VARCHAR(40),
27   gtime INTEGER NOT NULL,
28   PRIMARY KEY (matchid, teamid, gtime),
29   FOREIGN KEY(matchid) REFERENCES game(id),
30   FOREIGN KEY(teamid) REFERENCES eteam(id)
31   ) ENGINE=innodb;
32
33
34
35INSERT INTO eteam VALUES ('POL','Poland','Franciszek Smuda');
36INSERT INTO eteam VALUES ('RUS','Russia','Dick Advocaat');
37INSERT INTO eteam VALUES ('CZE','Czech Republic','Michal Bilek');
38INSERT INTO eteam VALUES ('GRE','Greece','Fernando Santos');
39INSERT INTO eteam VALUES ('NED','Netherlands','Bert van Marwijk');
40INSERT INTO eteam VALUES ('DEN','Denmark','Morten Olsen');
41INSERT INTO eteam VALUES ('GER','Germany','Joachim Low');
42INSERT INTO eteam VALUES ('POR','Portugal','Paulo Bento');
43INSERT INTO eteam VALUES ('ESP','Spain','Vicente del Bosque');
44INSERT INTO eteam VALUES ('ITA','Italy','Cesare Prandelli');
45INSERT INTO eteam VALUES ('IRL','Republic of Ireland','Giovanni Trapattoni');
46INSERT INTO eteam VALUES ('CRO','Croatia','Slaven Bilic');
47INSERT INTO eteam VALUES ('UKR','Ukraine','Oleh Blokhin');
48INSERT INTO eteam VALUES ('SWE','Sweden','Erik Hamren');
49INSERT INTO eteam VALUES ('ENG','England','Roy Hodgson');
50INSERT INTO eteam VALUES ('FRA','France','Laurent Blanc');
51
52
53INSERT INTO game VALUES (1001,'8 June 2012','National Stadium Warsaw','POL','GRE');
54INSERT INTO game VALUES (1002,'8 June 2012','Stadion Miejski Wroclaw','RUS','CZE');
55INSERT INTO game VALUES (1003,'12 June 2012','Stadion Miejski Wroclaw','GRE','CZE');
56INSERT INTO game VALUES (1004,'12 June 2012','National Stadium Warsaw','POL','RUS');
57INSERT INTO game VALUES (1005,'16 June 2012','Stadion Miejski Wroclaw','CZE','POL');
58INSERT INTO game VALUES (1006,'16 June 2012','National Stadium Warsaw','GRE','RUS');
59INSERT INTO game VALUES (1007,'9 June 2012','Metalist Stadium','NED','DEN');
60INSERT INTO game VALUES (1008,'9 June 2012','Arena Lviv','GER','POR');
61INSERT INTO game VALUES (1009,'13 June 2012','Arena Lviv','DEN','POR');
62INSERT INTO game VALUES (1010,'13 June 2012','Metalist Stadium','NED','GER');
63INSERT INTO game VALUES (1011,'17 June 2012','Metalist Stadium','POR','NED');
64INSERT INTO game VALUES (1012,'17 June 2012','Arena Lviv','DEN','GER');
65INSERT INTO game VALUES (1013,'10 June 2012','PGE Arena Gdansk','ESP','ITA');
66INSERT INTO game VALUES (1014,'10 June 2012','Stadion Miejski Poznan','IRL','CRO');
67INSERT INTO game VALUES (1015,'14 June 2012','Stadion Miejski Poznan','ITA','CRO');
68INSERT INTO game VALUES (1016,'14 June 2012','PGE Arena Gdansk','ESP','IRL');
69INSERT INTO game VALUES (1017,'18 June 2012','PGE Arena Gdansk','CRO','ESP');
70INSERT INTO game VALUES (1018,'18 June 2012','Stadion Miejski Poznan','ITA','IRL');
71INSERT INTO game VALUES (1019,'11 June 2012','Donbass Arena','FRA','ENG');
72INSERT INTO game VALUES (1020,'11 June 2012','Olimpiyskiy National Sports Complex','UKR','SWE');
73INSERT INTO game VALUES (1021,'15 June 2012','Donbass Arena','UKR','FRA');
74INSERT INTO game VALUES (1022,'15 June 2012','Olimpiyskiy National Sports Complex','SWE','ENG');
75INSERT INTO game VALUES (1023,'19 June 2012','Donbass Arena','ENG','UKR');
76INSERT INTO game VALUES (1024,'19 June 2012','Olimpiyskiy National Sports Complex','SWE','FRA');
77INSERT INTO game VALUES (1025,'21 June 2012','National Stadium Warsaw','CZE','POR');
78INSERT INTO game VALUES (1026,'22 June 2012','PGE Arena Gdansk','GER','GRE');
79INSERT INTO game VALUES (1027,'23 June 2012','Donbass Arena','ESP','FRA');
80INSERT INTO game VALUES (1028,'24 June 2012','Olimpiyskiy National Sports Complex','ENG','ITA');
81INSERT INTO game VALUES (1029,'27 June 2012','Donbass Arena','POR','ESP');
82INSERT INTO game VALUES (1030,'28 June 2012','National Stadium Warsaw','GER','ITA');
83INSERT INTO game VALUES (1031,'1 July 2012','Olimpiyskiy National Sports Complex','ESP','ITA');
84
85INSERT INTO goal VALUES (1001,'POL','Robert Lewandowski',17);
86INSERT INTO goal VALUES (1001,'GRE','Dimitris Salpingidis',51);
87INSERT INTO goal VALUES (1002,'RUS','Alan Dzagoev',15);
88INSERT INTO goal VALUES (1002,'RUS','Alan Dzagoev',79);
89INSERT INTO goal VALUES (1002,'RUS','Roman Shirokov',24);
90INSERT INTO goal VALUES (1002,'RUS','Roman Pavlyuchenko',82);
91INSERT INTO goal VALUES (1002,'CZE','Vaclav Pilar',52);
92INSERT INTO goal VALUES (1003,'GRE','Theofanis Gekas',53);
93INSERT INTO goal VALUES (1003,'CZE','Petr Jiracek',3);
94INSERT INTO goal VALUES (1003,'CZE','Vaclav Pilar',6);
95INSERT INTO goal VALUES (1004,'POL','Jakub Blaszczykowski',57);
96INSERT INTO goal VALUES (1004,'RUS','Alan Dzagoev',37);
97INSERT INTO goal VALUES (1005,'CZE','Petr Jiracek',72);
98INSERT INTO goal VALUES (1006,'GRE','Giorgos Karagounis',45);
99INSERT INTO goal VALUES (1007,'DEN','Michael Krohn-Dehli',24);
100INSERT INTO goal VALUES (1008,'GER','Mario Gomez',72);
101INSERT INTO goal VALUES (1009,'DEN','Nicklas Bendtner',41);
102INSERT INTO goal VALUES (1009,'DEN','Nicklas Bendtner',80);
103INSERT INTO goal VALUES (1009,'POR','Pepe',24);
104INSERT INTO goal VALUES (1009,'POR','Helder Postiga',36);
105INSERT INTO goal VALUES (1009,'POR','Silvestre Varela',87);
106INSERT INTO goal VALUES (1010,'NED','Robin van Persie',73);
107INSERT INTO goal VALUES (1010,'GER','Mario Gomez',24);
108INSERT INTO goal VALUES (1010,'GER','Mario Gomez',38);
109INSERT INTO goal VALUES (1011,'POR','Cristiano Ronaldo',28);
110INSERT INTO goal VALUES (1011,'POR','Cristiano Ronaldo',74);
111INSERT INTO goal VALUES (1011,'NED','Rafael van der Vaart',11);
112INSERT INTO goal VALUES (1012,'DEN','Michael Krohn-Dehli',24);
113INSERT INTO goal VALUES (1012,'GER','Lukas Podolski',19);
114INSERT INTO goal VALUES (1012,'GER','Lars Bender',80);
115INSERT INTO goal VALUES (1013,'ESP','Cesc Fabregas',64);
116INSERT INTO goal VALUES (1013,'ITA','Antonio Di Natale',61);
117INSERT INTO goal VALUES (1014,'IRL','Sean St Ledger',19);
118INSERT INTO goal VALUES (1014,'CRO','Mario Mandzukic',3);
119INSERT INTO goal VALUES (1014,'CRO','Mario Mandzukic',49);
120INSERT INTO goal VALUES (1014,'CRO','Nikica Jelavic',43);
121INSERT INTO goal VALUES (1015,'ITA','Andrea Pirlo',39);
122INSERT INTO goal VALUES (1015,'CRO','Mario Mandzukic',72);
123INSERT INTO goal VALUES (1016,'ESP','Fernando Torres',4);
124INSERT INTO goal VALUES (1016,'ESP','Fernando Torres',70);
125INSERT INTO goal VALUES (1016,'ESP','David Silva',49);
126INSERT INTO goal VALUES (1016,'ESP','Cesc Fabregas',83);
127INSERT INTO goal VALUES (1017,'ESP','Jesus Navas',88);
128INSERT INTO goal VALUES (1018,'ITA','Antonio Cassano',35);
129INSERT INTO goal VALUES (1018,'ITA','Mario Balotelli',90);
130INSERT INTO goal VALUES (1019,'FRA','Samir Nasri',39);
131INSERT INTO goal VALUES (1019,'ENG','Joleon Lescott',30);
132INSERT INTO goal VALUES (1020,'UKR','Andriy Shevchenko',55);
133INSERT INTO goal VALUES (1020,'UKR','Andriy Shevchenko',62);
134INSERT INTO goal VALUES (1020,'SWE','Zlatan Ibrahimovic',52);
135INSERT INTO goal VALUES (1021,'FRA','Jeremy Menez',53);
136INSERT INTO goal VALUES (1021,'FRA','Yohan Cabaye',56);
137INSERT INTO goal VALUES (1022,'SWE','Glen Johnson',49);
138INSERT INTO goal VALUES (1022,'SWE','Olof Mellberg',59);
139INSERT INTO goal VALUES (1022,'ENG','Andy Carroll',23);
140INSERT INTO goal VALUES (1022,'ENG','Theo Walcott',64);
141INSERT INTO goal VALUES (1022,'ENG','Danny Welbeck',78);
142INSERT INTO goal VALUES (1023,'ENG','Wayne Rooney',48);
143INSERT INTO goal VALUES (1024,'SWE','Zlatan Ibrahimovic',54);
144INSERT INTO goal VALUES (1024,'SWE','Sebastian Larsson',90);
145INSERT INTO goal VALUES (1025,'POR','Cristiano Ronaldo',79);
146INSERT INTO goal VALUES (1026,'GER','Philipp Lahm',39);
147INSERT INTO goal VALUES (1026,'GER','Sami Khedira',61);
148INSERT INTO goal VALUES (1026,'GER','Miroslav Klose',68);
149INSERT INTO goal VALUES (1026,'GER','Marco Reus',74);
150INSERT INTO goal VALUES (1026,'GRE','Georgios Samaras',55);
151INSERT INTO goal VALUES (1026,'GRE','Dimitris Salpingidis',89);
152INSERT INTO goal VALUES (1027,'ESP','Xabi Alonso',19);
153INSERT INTO goal VALUES (1027,'ESP','Xabi Alonso',90);
154INSERT INTO goal VALUES (1030,'GER','Mesut Ozil',90);
155INSERT INTO goal VALUES (1030,'ITA','Mario Balotelli',20);
156INSERT INTO goal VALUES (1030,'ITA','Mario Balotelli',36);
157INSERT INTO goal VALUES (1031,'ESP','David Silva',14);
158INSERT INTO goal VALUES (1031,'ESP','Jordi Alba',41);
159INSERT INTO goal VALUES (1031,'ESP','Fernando Torres',84);
160INSERT INTO goal VALUES (1031,'ESP','Juan Mata',88);