Création des tables
TD : Création des tables. Recherche parmi 300 000+ dissertationsPar Hajer Bouhlel • 22 Février 2018 • TD • 953 Mots (4 Pages) • 565 Vues
[pic 1]
Création des tables
SQL> create table seance_conduite(
2 cin references candidate(cin),
3 seance integer,
4 datesc date,
5 cin_monit references moniteur(cin),
6 immat references voiture(immat),
7 etat var_char(7) check( etat in ('annule','present','abscent')),
8 primary key (cin, seance , datesc));
SQL> create table seance_code(
2 cin references candidate(cin),
3 seance integer,
4 datesc date,
5 codemoniteur references moniteur(cin),
6 etat var_char(7) check( etat in ('annule','present','abscent')),
7 primary key (cin, seance , datesc));
SQL> create table Examen(
2 cin char(8),
3 datex date,
4 type var_char(8) check( type in (' code,'conduite' )),
5 resultat char(1) check ( resultat in ('R','A' ) ),
6 primary key (cin, datesc, type));
SQL> create table suivi_voiture (
2 dat date,
3 immat references voiture(immat),
4 kilometrage float ,
5 primary key (dat, immat));
SQL> create table affectation_tarif(
2 cin references candidate(cin),
3 codetarif references tarif(codetarif),
4 type var_char(8) check( type in (' code,'conduite' )),
5 primary key (cin, codetarif, type));
SQL> create table reglement(
2 numreglement var_char(8) primary key,
3 montant float,
4 moder char(6) check ( moder in ('cheque' , ' espece') ) ,
5 nature var_char(15) check( etat in ('avance','frais d examen','autre ')),
6 datesc date,
7 cincandidat references candidat(cin));
Remplissage des Tables:
1)Table Examen:
SQL > insert into Examen values ('11235680' , '15-01-2016','code','R');
SQL > insert into Examen values ('11235680' , '11-02-2016','code','A');
SQL >insert into Examen values ('11235680' , '29-07-2016','conduite','R');
SQL > insert into Examen values ('11235680' , '10-11-2016','conduite','A');
SQ L >insert into Examen values ('11235655' , '02-01-2017','code','R');
SQL > insert into Examen values ('11235655' , '30-03-2017','code','A');
SQL > insert into Examen values ('11235655' , '01-09-2017','conduite','R');
SQL > insert into Examen values ('11235655' , '19-11-2017','conduite','A');
SQL> commit;
2)Tableau Seance_Conduite:
SQL > insert into seance_conduite values ('11235680' ,1, '20-06-2016','8569523','122 tn 1257','present');
SQL > insert into seance_conduite values ('11235680' ,2, '27-06-2016','8569523','122 tn 1257','present');
SQL > insert into seance_conduite values ('11235680' ,3, '04-07-2016','8569523','122tn 1257','present');
SQL > insert into seance_conduite values ('11235680' ,4, '12-07-2016','8569523','122 tn 1257','present');
SQL > insert into seance_conduite values ('11235680' ,5, '05-11-2016','8569523','122 tn 1257','present');
SQL > insert into seance_conduite values ('11235680' ,6, '05-02-2016','8569523','1282tn 1257','present');
SQL > insert into seance_conduite values ('11235655' ,1, '05-08-2017','89657412','127 tn 5555','present');
SQL > insert into seance_conduite values ('11235655' ,2, '12-08-2017','8569523','127 tn 1257','present');
SQL > insert into seance_conduite values ('11235655' ,3, '25-05-2017', ,'89657412','127tn 5555','present');
SQL > insert into seance_conduite values ('11235655' ,4, '29-08-2017', ,'89657412','127tn 5555','present');
SQL > insert into seance_conduite values ('11235655' ,5, '11-11-2017', ,'89657412','127 tn 5555','absent');
SQL> commit;
...