You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
89 lines
3.9 KiB
89 lines
3.9 KiB
DROP TABLE JOUE;
|
|
DROP TABLE GAME;
|
|
DROP TABLE APPARTIENT cascade constraints;
|
|
DROP TABLE JOUEUR cascade constraints;
|
|
DROP TABLE EQUIPE cascade constraints;
|
|
|
|
CREATE TABLE JOUEUR(id_joueur CHAR(6) PRIMARY KEY,
|
|
Nom VARCHAR2(20),
|
|
Prenom VARCHAR2(20),
|
|
Date_de_naissance DATE,
|
|
Taille NUMBER,
|
|
Poste char(2) constraint c_poste CHECK (Poste IN('PG','SG','SF','PF','C')));
|
|
|
|
CREATE TABLE EQUIPE(id_equipe CHAR(6) PRIMARY KEY,
|
|
Nom VARCHAR2(20),
|
|
Ville VARCHAR2(20),
|
|
Conference VARCHAR2(5) constraint c_conf check (Conference in('Est','Ouest')),
|
|
Date_creation DATE);
|
|
|
|
CREATE TABLE GAME(id_game CHAR(6) PRIMARY KEY,
|
|
Date_game DATE,
|
|
id_equipe_domicile CHAR(6) references EQUIPE,
|
|
id_equipe_exterieur CHAR(6) references EQUIPE,
|
|
Ville VARCHAR2(20),
|
|
Categorie char(7) constraint c_type check (Categorie in('Amical','Saison','Playoff','Allstar')),
|
|
Score_domicile NUMBER,
|
|
Score_exterieur NUMBER,
|
|
Prolongation NUMBER);
|
|
|
|
CREATE TABLE JOUE(id_joueur CHAR(6) references JOUEUR,
|
|
id_game CHAR(6) references GAME,
|
|
Points NUMBER,
|
|
Rebonds NUMBER,
|
|
Interceptions NUMBER,
|
|
Contres NUMBER,
|
|
Passes NUMBER,
|
|
Balles_perdues NUMBER,
|
|
Fautes NUMBER,
|
|
PRIMARY KEY(id_joueur,id_game));
|
|
|
|
|
|
CREATE TABLE APPARTIENT(id_contrat CHAR(6) PRIMARY KEY,
|
|
id_joueur CHAR(6) references JOUEUR,
|
|
id_equipe CHAR(6) references EQUIPE,
|
|
Date_debut DATE,
|
|
Date_fin DATE,
|
|
Salaire_jour NUMBER);
|
|
|
|
insert into JOUEUR values ('J00001','Jordan','Michael','01-Jan-1980','193','SF');
|
|
insert into JOUEUR values ('J00002','Bird','Larry','02-Jan-1980','194','SF');
|
|
insert into JOUEUR values ('J00003','Johnson','Magic','03-Jan-1980','201','PG');
|
|
insert into JOUEUR values ('J00004','Thomas','Isiah','04-Jan-1980','185','PG');
|
|
insert into JOUEUR values ('J00005','Oneil','Shaquille','04-Jan-1992','185','C');
|
|
insert into JOUEUR values ('J00006','Parker','Tony','04-Jan-1996','185','PG');
|
|
|
|
|
|
insert into EQUIPE values ('E00001','Bulls','Chicago','Est','04-Jan-1970');
|
|
insert into EQUIPE values ('E00002','Lakers','LA','Ouest','06-Jan-1970');
|
|
insert into EQUIPE values ('E00003','Pistons','Detroit','Est','07-Jan-1970');
|
|
insert into EQUIPE values ('E00004','Celtics','Boston','Est','09-Jan-1970');
|
|
insert into EQUIPE values ('E00005','Spurs','San Antonio','Ouest','06-Jan-1971');
|
|
|
|
insert into APPARTIENT values ('C00001','J00001','E00001','04-Jan-1980',NULL,10000);
|
|
insert into APPARTIENT values ('C00002','J00002','E00004','04-Jan-1981',NULL,1000);
|
|
insert into APPARTIENT values ('C00003','J00003','E00002','04-Jan-1981','04-Jan-2010',3000);
|
|
insert into APPARTIENT values ('C00004','J00004','E00003','04-Jan-1982',NULL,5000);
|
|
insert into APPARTIENT values ('C00005','J00005','E00002','04-Jan-1990',NULL,5000);
|
|
insert into APPARTIENT values ('C00006','J00006','E00005','04-Jan-1990',NULL,5000);
|
|
|
|
insert into GAME values ('G00001','04-Jan-2001','E00002','E00001','Londres','Saison',100,101,0);
|
|
insert into GAME values ('G00002','04-Jan-2001','E00003','E00004','Boston','Saison',102,101,0);
|
|
insert into GAME values ('G00003','04-May-2001','E00004','E00003','Detroit','Playoff',107,101,0);
|
|
insert into GAME values ('G00004','04-Apr-2001','E00001','E00002','Miami','Allstar',105,101,0);
|
|
insert into GAME values ('G00005','04-Apr-2001','E00001','E00003','Miami','Allstar',105,101,0);
|
|
insert into GAME values ('G00006','04-Apr-2001','E00001','E00004','Miami','Allstar',105,101,0);
|
|
|
|
insert into JOUE values ('J00006','G00004',17,18,1,0,1,3,4);
|
|
insert into JOUE values ('J00005','G00004',17,18,1,0,1,3,4);
|
|
insert into JOUE values ('J00002','G00004',17,18,1,0,1,3,4);
|
|
insert into JOUE values ('J00003','G00004',17,18,1,0,1,3,4);
|
|
insert into JOUE values ('J00001','G00006',17,18,1,0,1,3,4);
|
|
insert into JOUE values ('J00003','G00006',17,18,1,0,1,3,4);
|
|
insert into JOUE values ('J00003','G00005',17,18,1,0,1,3,4);
|
|
insert into JOUE values ('J00004','G00004',17,18,1,0,1,3,4);
|
|
insert into JOUE values ('J00001','G00003',20,10,4,1,2,2,3);
|
|
insert into JOUE values ('J00001','G00002',19,11,3,2,2,1,2);
|
|
insert into JOUE values ('J00001','G00001',18,12,2,3,1,0,1);
|
|
|