javadb/sql/euroleague.sql
author Markus Brökers <mbroeker@largo.homelinux.org>
Sun, 29 Aug 2010 15:29:22 +0200
changeset 144 f064cd793f8d
parent 109 3d84eda3f16f
permissions -rw-r--r--
sts: show time stamps added
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
100
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
     1
connect 'jdbc:derby:clubstatistik;create=true';
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
     2
drop table uefa;
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
     3
drop table euroleague;
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
     4
create table euroleague (
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
     5
	team char(20),
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
     6
	lwin int, ldraw int, llost int,
109
3d84eda3f16f Derby uses float values here
Markus Bröker <mbroeker@largo.dyndns.tv>
parents: 100
diff changeset
     7
	cup float,
100
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
     8
	uwin int, udraw int,
109
3d84eda3f16f Derby uses float values here
Markus Bröker <mbroeker@largo.dyndns.tv>
parents: 100
diff changeset
     9
	bonus float,
100
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    10
	country char(15));
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    11
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    12
create unique index team_idx on euroleague(team);
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    13
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    14
insert into euroleague values('FC Toulouse', 16, 16, 6, 0, 0, 0, 0, 'Frankreich');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    15
insert into euroleague values('Rubin Kazan', 6, 3, 2, 0.25, 0, 0, 0, 'Russland');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    16
insert into euroleague values('Paris St. Germain', 19, 7, 12, 0, 4, 4, 4, 'Frankreich');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    17
insert into euroleague values('Stade Rennes', 15, 16, 7, 0, 0, 0, 0, 'Frankreich');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    18
insert into euroleague values('Spartak Moskau', 6, 2, 4, 0, 1, 1, 4, 'Russland');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    19
insert into euroleague values('AJ Auxerre', 16, 7, 15, 0, 0, 0, 0, 'Frankreich');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    20
insert into euroleague values('AS Monaco', 11, 12, 15, 0, 0, 0, 0, 'Frankreich');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    21
insert into euroleague values('ZSKA Moskau', 6, 1, 4, 0.5, 8, 1, 0, 'Russland');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    22
insert into euroleague values('St. Petersburg', 5, 4, 3, 0, 4, 2, 3, 'Russland');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    23
insert into euroleague values('Ajax Amsterdam', 21, 5, 8, 0, 4, 2, 4, 'Niederlande');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    24
insert into euroleague values('PSV Eindhoven', 19, 8, 7, 0, 1, 0, 3, 'Niederlande');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    25
insert into euroleague values('FC Villarreal', 18, 11, 9, 0, 3, 5, 5, 'Spanien');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    26
insert into euroleague values('1899 Hoffenheim', 15, 10, 9, 0, 0, 0, 0, 'Deutschland');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    27
insert into euroleague values('Schalke 04', 14, 8, 12, 0, 1, 1, 4, 'Deutschland');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    28
insert into euroleague values('Bayer Leverkusen', 14, 7, 13, 0.25, 0, 0, 0, 'Deutschland');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    29
insert into euroleague values('FC Groningen', 17, 5, 12, 0, 0, 0, 0, 'Niederlande');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    30
insert into euroleague values('Werder Bremen', 12, 9, 13, 0.5, 4, 8, 6, 'Deutschland');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    31
insert into euroleague values('Feyenoord Rotterdam', 12, 9, 13, 0, 0, 0, 2, 'Niederlande');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    32
insert into euroleague values('Manchester United', 28, 6, 4, 0.125, 6, 6, 7, 'England');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    33
insert into euroleague values('FC Liverpool', 25, 11, 2, 0, 6, 3, 6.5, 'England');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    34
insert into euroleague values('FC Valencia', 18, 8, 12, 0, 1, 5, 4, 'Spanien');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    35
insert into euroleague values('Deportivo La Coruna', 16, 10, 12, 0, 2, 1, 3.5, 'Spanien');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    36
insert into euroleague values('VFL Wolfsburg', 21, 6, 7, 0, 3, 1, 3, 'Deutschland');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    37
insert into euroleague values('Inter Mailand', 25, 9, 4, 0.125, 2, 3, 4, 'Italien');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    38
insert into euroleague values('Juventus Turin', 21, 11, 6, 0.125, 3, 4, 5.5, 'Italien');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    39
insert into euroleague values('FC Chelsea', 25, 8, 5, 0.5, 5, 6, 6, 'England');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    40
insert into euroleague values('AC Mailand', 22, 8, 8, 0, 2, 4, 4, 'Italien');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    41
insert into euroleague values('FC Porto', 21, 7, 2, 0.5, 4, 3, 5, 'Portugal');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    42
insert into euroleague values('AC Florenz', 21, 5, 12, 0, 1, 4, 4.5, 'Italien');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    43
insert into euroleague values('Sporting Lissabon', 20, 6, 4, 0, 3, 0, 4, 'Portugal');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    44
insert into euroleague values('Benfica Lissabon', 17, 8, 5, 0, 0, 1, 0, 'Portugal');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    45
insert into euroleague values('FC Arsenal', 20, 12, 6, 0.125, 5, 3, 8, 'England');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    46
insert into euroleague values('NAC Breda', 13, 6, 15, 0, 0, 0, 0, 'Niederlande');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    47
insert into euroleague values('FC Everton', 17, 12, 9, 0.25, 0, 0, 1, 'England');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    48
insert into euroleague values('AS Rom', 18, 9, 11, 0, 5, 0, 4, 'Italien');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    49
insert into euroleague values('Udinese Calcio', 16, 10, 12, 0, 5, 2, 3, 'Italien');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    50
insert into euroleague values('Nacional Funchal', 15, 7, 8, 0, 0, 0, 0, 'Portugal');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    51
insert into euroleague values('Lazio Rom', 15, 5, 18, 0.5, 0, 0, 0, 'Italien');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    52
insert into euroleague values('SSC Neapel', 12, 10, 16, 0, 0, 0, 4, 'Italien');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    53
insert into euroleague values('SC Braga', 13, 11, 6, 0, 3, 2, 4, 'Portugal');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    54
insert into euroleague values('Bayern München', 20, 7, 7, 0, 6, 3, 5, 'Deutschland');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    55
insert into euroleague values('VFB Stuttgart', 19, 7, 8, 0, 2, 1, 5, 'Deutschland');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    56
insert into euroleague values('Hertha BSC', 19, 6, 9, 0, 0, 2, 6.5, 'Deutschland');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    57
insert into euroleague values('Aston Villa', 17, 11, 10, 0, 2, 1, 4.5, 'England');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    58
insert into euroleague values('Manchester City', 15, 5, 18, 0, 7, 2, 4, 'England');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    59
insert into euroleague values('FC Barcelona', 27, 6, 5, 0.5, 7, 5, 8, 'Spanien');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    60
insert into euroleague values('Real Madrid', 25, 3, 10, 0, 4, 0, 4, 'Spanien');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    61
insert into euroleague values('Glasgow Rangers', 26, 8, 4, 0.5, 0, 0, 0.5, 'Schottland');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    62
insert into euroleague values('Celtic Glasgow', 24, 10, 4, 0, 1, 2, 3, 'Schottland');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    63
insert into euroleague values('Heart of Midlothian', 16, 11, 11, 0, 0, 0, 0, 'Schottland');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    64
insert into euroleague values('Sampdoria Genua', 11, 13, 14, 0.25, 2, 1, 4, 'Italien');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    65
insert into euroleague values('Girondins Bordeaux', 24, 8, 6, 0, 0, 2, 3, 'Frankreich');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    66
insert into euroleague values('Hamburger SV', 19, 4, 11, 0.125, 8, 1, 5, 'Deutschland');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    67
insert into euroleague values('Olympique Marseille', 22, 11, 5, 0, 4, 1, 6, 'Frankreich');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    68
insert into euroleague values('Besiktas Istanbul', 21, 8, 5, 0.5, 0, 0, 0, 'Türkei');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    69
insert into euroleague values('FC Sevilla', 21, 7, 10, 0.125, 2, 0, 4, 'Spanien');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    70
insert into euroleague values('Fenerbahce Istanbul', 18, 7, 9, 0.25, 0, 2, 6.5, 'Türkei');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    71
insert into euroleague values('FC Aberdeen', 14, 11, 13, 0, 0, 0, 0, 'Schottland');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    72
insert into euroleague values('Olympique Lyon', 20, 11, 7, 0, 3, 3, 4, 'Frankreich');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    73
insert into euroleague values('Galatasaray Istanbul', 18, 7, 9, 0, 4, 2, 5, 'Türkei');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    74
insert into euroleague values('Borussia Dortmund', 15, 14, 5, 0, 0, 0, 2, 'Deutschland');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    75
insert into euroleague values('Atletico Madrid', 20, 7, 11, 0, 3, 5, 5, 'Spanien');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    76
insert into euroleague values('FK Moskau', 6, 5, 1, 0.125, 0, 0, 3.5, 'Russland');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    77
insert into euroleague values('Schachtjor Donezk', 19, 7, 4, 0.25, 8, 4, 8, 'Ukraine');
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    78
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    79
select * from euroleague order by lwin desc, ldraw desc;
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    80
create table uefa (
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    81
	country char(20), 
109
3d84eda3f16f Derby uses float values here
Markus Bröker <mbroeker@largo.dyndns.tv>
parents: 100
diff changeset
    82
	coeffizient float);
100
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    83
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    84
create unique index uefa_idx on uefa(country);
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    85
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    86
insert into uefa values('Deutschland', 56.695);
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    87
insert into uefa values('England', 79.498999999999995);
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    88
insert into uefa values('Spanien', 74.266000000000005);
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    89
insert into uefa values('Schottland', 27.875);
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    90
insert into uefa values('Portugal', 36.462000000000003);
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    91
insert into uefa values('Ukraine', 41.847999999999999);
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    92
insert into uefa values('Italien', 62.784999999999997);
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    93
insert into uefa values('Frankreich', 50.167999999999999);
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    94
insert into uefa values('Russland', 47.125);
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    95
insert into uefa values('Niederlande', 38.963000000000001);
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    96
insert into uefa values('Türkei', 32.225000000000001);
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    97
a6d2add085d7 Apache Derby Examples...
Markus Bröker <mbroeker@largo.dyndns.tv>
parents:
diff changeset
    98
quit;