connect 'jdbc:derby:clubstatistik;create=true';

drop table uefa;

drop table euroleague;

create table euroleague (

	team char(20),

	lwin int, ldraw int, llost int,

	uwin int, udraw int,

	country char(15));

create unique index team_idx on euroleague(team);

insert into euroleague values('FC Toulouse', 16, 16, 6, 0, 0, 0, 0, 'Frankreich');

insert into euroleague values('Rubin Kazan', 6, 3, 2, 0.25, 0, 0, 0, 'Russland');

insert into euroleague values('Paris St. Germain', 19, 7, 12, 0, 4, 4, 4, 'Frankreich');

insert into euroleague values('Stade Rennes', 15, 16, 7, 0, 0, 0, 0, 'Frankreich');

insert into euroleague values('Spartak Moskau', 6, 2, 4, 0, 1, 1, 4, 'Russland');

insert into euroleague values('AJ Auxerre', 16, 7, 15, 0, 0, 0, 0, 'Frankreich');

insert into euroleague values('AS Monaco', 11, 12, 15, 0, 0, 0, 0, 'Frankreich');

insert into euroleague values('ZSKA Moskau', 6, 1, 4, 0.5, 8, 1, 0, 'Russland');

insert into euroleague values('St. Petersburg', 5, 4, 3, 0, 4, 2, 3, 'Russland');

insert into euroleague values('Ajax Amsterdam', 21, 5, 8, 0, 4, 2, 4, 'Niederlande');

insert into euroleague values('PSV Eindhoven', 19, 8, 7, 0, 1, 0, 3, 'Niederlande');

insert into euroleague values('FC Villarreal', 18, 11, 9, 0, 3, 5, 5, 'Spanien');

insert into euroleague values('1899 Hoffenheim', 15, 10, 9, 0, 0, 0, 0, 'Deutschland');

insert into euroleague values('Schalke 04', 14, 8, 12, 0, 1, 1, 4, 'Deutschland');

insert into euroleague values('Bayer Leverkusen', 14, 7, 13, 0.25, 0, 0, 0, 'Deutschland');

insert into euroleague values('FC Groningen', 17, 5, 12, 0, 0, 0, 0, 'Niederlande');

insert into euroleague values('Werder Bremen', 12, 9, 13, 0.5, 4, 8, 6, 'Deutschland');

insert into euroleague values('Feyenoord Rotterdam', 12, 9, 13, 0, 0, 0, 2, 'Niederlande');

insert into euroleague values('Manchester United', 28, 6, 4, 0.125, 6, 6, 7, 'England');

insert into euroleague values('FC Liverpool', 25, 11, 2, 0, 6, 3, 6.5, 'England');

insert into euroleague values('FC Valencia', 18, 8, 12, 0, 1, 5, 4, 'Spanien');

insert into euroleague values('Deportivo La Coruna', 16, 10, 12, 0, 2, 1, 3.5, 'Spanien');

insert into euroleague values('VFL Wolfsburg', 21, 6, 7, 0, 3, 1, 3, 'Deutschland');

insert into euroleague values('Inter Mailand', 25, 9, 4, 0.125, 2, 3, 4, 'Italien');

insert into euroleague values('Juventus Turin', 21, 11, 6, 0.125, 3, 4, 5.5, 'Italien');

insert into euroleague values('FC Chelsea', 25, 8, 5, 0.5, 5, 6, 6, 'England');

insert into euroleague values('AC Mailand', 22, 8, 8, 0, 2, 4, 4, 'Italien');

insert into euroleague values('FC Porto', 21, 7, 2, 0.5, 4, 3, 5, 'Portugal');

insert into euroleague values('AC Florenz', 21, 5, 12, 0, 1, 4, 4.5, 'Italien');

insert into euroleague values('Sporting Lissabon', 20, 6, 4, 0, 3, 0, 4, 'Portugal');

insert into euroleague values('Benfica Lissabon', 17, 8, 5, 0, 0, 1, 0, 'Portugal');

insert into euroleague values('FC Arsenal', 20, 12, 6, 0.125, 5, 3, 8, 'England');

insert into euroleague values('NAC Breda', 13, 6, 15, 0, 0, 0, 0, 'Niederlande');

insert into euroleague values('FC Everton', 17, 12, 9, 0.25, 0, 0, 1, 'England');

insert into euroleague values('AS Rom', 18, 9, 11, 0, 5, 0, 4, 'Italien');

insert into euroleague values('Udinese Calcio', 16, 10, 12, 0, 5, 2, 3, 'Italien');

insert into euroleague values('Nacional Funchal', 15, 7, 8, 0, 0, 0, 0, 'Portugal');

insert into euroleague values('Lazio Rom', 15, 5, 18, 0.5, 0, 0, 0, 'Italien');

insert into euroleague values('SSC Neapel', 12, 10, 16, 0, 0, 0, 4, 'Italien');

insert into euroleague values('SC Braga', 13, 11, 6, 0, 3, 2, 4, 'Portugal');

insert into euroleague values('Bayern München', 20, 7, 7, 0, 6, 3, 5, 'Deutschland');

insert into euroleague values('VFB Stuttgart', 19, 7, 8, 0, 2, 1, 5, 'Deutschland');

insert into euroleague values('Hertha BSC', 19, 6, 9, 0, 0, 2, 6.5, 'Deutschland');

insert into euroleague values('Aston Villa', 17, 11, 10, 0, 2, 1, 4.5, 'England');

insert into euroleague values('Manchester City', 15, 5, 18, 0, 7, 2, 4, 'England');

insert into euroleague values('FC Barcelona', 27, 6, 5, 0.5, 7, 5, 8, 'Spanien');

insert into euroleague values('Real Madrid', 25, 3, 10, 0, 4, 0, 4, 'Spanien');

insert into euroleague values('Glasgow Rangers', 26, 8, 4, 0.5, 0, 0, 0.5, 'Schottland');

insert into euroleague values('Celtic Glasgow', 24, 10, 4, 0, 1, 2, 3, 'Schottland');

insert into euroleague values('Heart of Midlothian', 16, 11, 11, 0, 0, 0, 0, 'Schottland');

insert into euroleague values('Sampdoria Genua', 11, 13, 14, 0.25, 2, 1, 4, 'Italien');

insert into euroleague values('Girondins Bordeaux', 24, 8, 6, 0, 0, 2, 3, 'Frankreich');

insert into euroleague values('Hamburger SV', 19, 4, 11, 0.125, 8, 1, 5, 'Deutschland');

insert into euroleague values('Olympique Marseille', 22, 11, 5, 0, 4, 1, 6, 'Frankreich');

insert into euroleague values('Besiktas Istanbul', 21, 8, 5, 0.5, 0, 0, 0, 'Türkei');

insert into euroleague values('FC Sevilla', 21, 7, 10, 0.125, 2, 0, 4, 'Spanien');

insert into euroleague values('Fenerbahce Istanbul', 18, 7, 9, 0.25, 0, 2, 6.5, 'Türkei');

insert into euroleague values('FC Aberdeen', 14, 11, 13, 0, 0, 0, 0, 'Schottland');

insert into euroleague values('Olympique Lyon', 20, 11, 7, 0, 3, 3, 4, 'Frankreich');

insert into euroleague values('Galatasaray Istanbul', 18, 7, 9, 0, 4, 2, 5, 'Türkei');

insert into euroleague values('Borussia Dortmund', 15, 14, 5, 0, 0, 0, 2, 'Deutschland');

insert into euroleague values('Atletico Madrid', 20, 7, 11, 0, 3, 5, 5, 'Spanien');

insert into euroleague values('FK Moskau', 6, 5, 1, 0.125, 0, 0, 3.5, 'Russland');

insert into euroleague values('Schachtjor Donezk', 19, 7, 4, 0.25, 8, 4, 8, 'Ukraine');

select * from euroleague order by lwin desc, ldraw desc;

create table uefa (

	country char(20), 

create unique index uefa_idx on uefa(country);

insert into uefa values('Deutschland', 56.695);

insert into uefa values('England', 79.498999999999995);

insert into uefa values('Spanien', 74.266000000000005);

insert into uefa values('Schottland', 27.875);

insert into uefa values('Portugal', 36.462000000000003);

insert into uefa values('Ukraine', 41.847999999999999);

insert into uefa values('Italien', 62.784999999999997);

insert into uefa values('Frankreich', 50.167999999999999);

insert into uefa values('Russland', 47.125);

insert into uefa values('Niederlande', 38.963000000000001);

insert into uefa values('Türkei', 32.225000000000001);

quit;