Corrigé du TP

|Cours : | – Base de données | | |

Schéma de la base de données (3 pts)
Création de la BD :

CREATE TABLE Salaire-Tranche
( tranche int not null, salmin int not null, salmax int not null,
Primary key (tranche) );

CREATE TABLE Departement
( nodpt int not null , nomdpt varchar(15) not null, loc varchar(10) not null,
Primary key (nodpt) );

CREATE TABLE Employe
( noemp int not null, nome varchar(10) not null, emploi varchar(10) not null, nosup int , dateemb date not null, sal int not null, commis number(10,2) , nodpt int not null,
Primary key (noemp),
Foreign key (nodpt) references departement(nodpt),
Foreign key (nosup) references employe(noemp) );

Chargement de la base de données (1 pt)

INSERT into Salaire-Tranche values (1,700,1200) values (2,1201,1400) values (3,1401,2000) values (4,2001,3000) values (5,3001,9999);

INSERT into Departement values (10,'DIRECTION','MONTREAL') values (20,'RECHERCHE','ALMA') values (30,'VENTE','QUEBEC') values (40,'PRODUCTION','LONGUEUIL');

INSERT into Employe values (7839, 'ROY', 'PRESIDENT', null, '17-NOV-81',5000,null,10) values(7566, 'COUSINEAU', 'GERANT', 7839, '02-APR-81',2998,null,20) values(7698, 'TREMBLAY', 'GERANT', 7839, '01-MAY-81',2850,null,30) values(7782, 'GERVAIS', 'GERANT', 7839, '09-JUN-81',2450,null,10) values(7788, 'LEDUC', 'ANALYSTE', 7566, '19-APR-87',3000,null,20) values(7499, 'ALLARD', 'VENDEUR', 7698, '20-FEB-81',1250,300,30) values(7521, 'LAPIERRE', 'VENDEUR', 7698, '22-FEB-81',1600,300,30) values(7654, 'MERTIN', 'VENDEUR', 7698, '28-SEP-81',1250,1400,30) values(7844, 'MARTEL', 'VENDEUR', 7698, '08-SEP-81',1500,801,20) values(7900, 'LAFLEUR', 'COMMIS',