Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- DROP TABLE ACTIVITES_PRATIQUEES CASCADE CONSTRAINTS;
- DROP TABLE CHARGE CASCADE CONSTRAINTS;
- DROP TABLE RESULTATS CASCADE CONSTRAINTS;
- DROP TABLE ACTIVITES CASCADE CONSTRAINTS;
- DROP TABLE PROFESSEURS CASCADE CONSTRAINTS;
- DROP TABLE COURS CASCADE CONSTRAINTS;
- DROP TABLE ELEVES CASCADE CONSTRAINTS;
- CREATE TABLE ELEVES
- (NUM_ELEVE NUMBER(4)
- ,NOM VARCHAR2(25)
- ,PRENOM VARCHAR2(25)
- ,DATE_NAISSANCE DATE
- ,POIDS NUMBER
- ,ANNEE NUMBER
- ,SEXE CHAR(1)
- ,CONSTRAINT PK_ELEVES PRIMARY KEY(NUM_ELEVE)
- ,CONSTRAINT NN_ELEVE_NOM CHECK(NOM IS NOT NULL)
- ,CONSTRAINT NN_ELEVE_PRENOM CHECK(PRENOM IS NOT NULL)
- );
- CREATE TABLE COURS
- (NUM_COURS NUMBER(2)
- ,NOM VARCHAR2(20)
- ,NBHEURES NUMBER(2)
- ,ANNEE NUMBER(1)
- ,CONSTRAINT PK_COURS PRIMARY KEY(NUM_COURS)
- ,CONSTRAINT NN_COURS_NOM CHECK(NOM IS NOT NULL)
- );
- CREATE TABLE PROFESSEURS
- (NUM_PROF NUMBER(4)
- ,NOM VARCHAR2(25)
- ,SPECIALITE VARCHAR2(20)
- ,DATE_ENTREE DATE
- ,DER_PROM DATE
- ,SALAIRE_BASE NUMBER
- ,SALAIRE_ACTUEL NUMBER
- ,CONSTRAINT PK_PROFESSEURS PRIMARY KEY(NUM_PROF)
- ,CONSTRAINT NN_PROFESSEURS_NOM CHECK(NOM IS NOT NULL)
- );
- CREATE TABLE ACTIVITES
- (NIVEAU NUMBER(1)
- ,NOM VARCHAR2(20)
- ,EQUIPE VARCHAR2(32)
- ,CONSTRAINT PK_ACTIVITES PRIMARY KEY(NIVEAU, NOM)
- );
- CREATE TABLE RESULTATS
- (NUM_ELEVE NUMBER(4)
- ,NUM_COURS NUMBER(4)
- ,POINTS NUMBER
- ,CONSTRAINT PK_RESULTATS PRIMARY KEY(NUM_ELEVE, NUM_COURS)
- ,CONSTRAINT FK_ELEVE FOREIGN KEY (NUM_ELEVE) REFERENCES ELEVES(NUM_ELEVE)
- ,CONSTRAINT FK_COURS FOREIGN KEY (NUM_COURS) REFERENCES COURS(NUM_COURS)
- );
- CREATE TABLE CHARGE
- (NUM_PROF NUMBER(4) NOT NULL
- ,NUM_COURS NUMBER(4) NOT NULL
- ,CONSTRAINT PK_CHARGE PRIMARY KEY(NUM_COURS, NUM_PROF)
- ,CONSTRAINT FK_PROFESSEUR FOREIGN KEY (PROFESSEUR_NUM) REFERENCES PROFESSEURS(NUM_PROF)
- ,CONSTRAINT FK_COURS FOREIGN KEY (NUM_COURS) REFERENCES COURS(NUM_COURS)
- );
- CREATE TABLE ACTIVITES_PRATIQUEES
- (NUM_ELEVE NUMBER(4)
- ,NIVEAU NUMBER(1)
- ,NOM VARCHAR2(20)
- ,CONSTRAINT PK_ACTIVITES_PRATIQUEES PRIMARY KEY(NUM_ELEVE, NIVEAU, NOM)
- ,CONSTRAINT FK_ACTIVITE FOREIGN KEY (NIVEAU_NOM, NOM_NOM) REFERENCES ACTIVITES(NIVEAU, NOM)
- ,CONSTRAINT FK_ELEVE FOREIGN KEY (NUM_ELEVE) REFERENCES ELEVES(NUM_ELEVE)
- );
- INSERT INTO ELEVES (NUM_ELEVE, NOM, PRENOM, DATE_NAISSANCE, POIDS, ANNEE, SEXE) VALUES
- (1, 'Brisefer', 'Benoit', '10-12-1978', 35, 1, 'M'),
- (2, 'Génial', 'Olivier', '10-04-1978', 42, 1, 'M'),
- (3, 'Jourdan', 'Gil', '28-06-1974', 72, 2, 'F'),
- (4, 'Spring', 'Jerry', '16-02-1974', 78, 2, 'M'),
- (5, 'Tsuno', 'Yoko', '29-10-1977', 45, 1, 'F'),
- (6, 'Lebut', 'Marc', '29-04-1974', 75,2, 'M'),
- (7, 'Lagaffe', 'Gaston', '08-04-1975', 61,1, 'M'),
- (8, 'Dubois', 'Robin', '20-04-1976', 60, 2, 'M'),
- (9, 'Walthéry', 'Natacha', '07-09-1977', 59,1, 'F'),
- (10, 'Danny', 'Buck', '15-02-1973', 82, 2, 'M');
- INSERT INTO COURS (NUM_COURS, NOM, NBHEURES, ANNEE) VALUES
- (1, 'Réseau', 15, 1),
- (2, 'Sgbd', 30, 1),
- (3, 'Programmation', 15, 1),
- (4, 'Sgbd', 30, 2),
- (5, 'Analyse', 60, 2);
- INSERT INTO PROFESSEURS (NUM_PROF, NOM, SPECIALITE, DATE_ENTREE, DER_PROM, SALAIRE_BASE, SALAIRE_ACTUEL) VALUES
- (1, 'Bottle', 'poésie', '01-10-1970', '01-10-1988', 2000000, 2600000),
- (2, 'Bolenov', 'réseau', '15-11-1968', '01-10-1998', 1900000, 2468000),
- (3, 'Tonilaclasse', 'poo', '01-10-1979', '01-01-1989', 1900000, 2360000),
- (4, 'Pastecnov', 'sql', '01-10-1975', '', 2500000, 2500000),
- (5, 'Selector', 'sql', '15-10-1982', '01-10-1988', 1900000, 1900000),
- (6, 'Vilplusplus', 'poo', '25-04-1990', '05-06-1994', 1900000, 2200000),
- (7, 'Francesca', '', '01-10-1975', '11-01-1998', 2000000, 3200000),
- (8, 'Pucette', 'sql', '06-12-1988', '29-02-1996', 2000000, 2500000);
- INSERT INTO CHARGE (NUM_PROF, NUM_COURS) VALUES
- (1,1),
- (1,4),
- (2,1),
- (3,2),
- (3,4),
- (3,5),
- (4,2),
- (7,4),
- (8,1),
- (8,2),
- (8,3),
- (8,4),
- (8,5);
- INSERT INTO RESULTATS (NUM_ELEVE, NUM_COURS, POINTS) VALUES
- (1, 1, 15),
- (1, 2, 10.5),
- (1, 4, 8),
- (1, 5, 20),
- (2, 1, 13.5),
- (2, 2, 12),
- (2, 4, 11),
- (2, 5, 1.5),
- (3, 1, 14),
- (3, 2, 15),
- (3, 4, 16),
- (3, 5, 20),
- (4, 1, 16.5),
- (4, 2, 14),
- (4, 4, 11),
- (4, 5, 8),
- (5, 1, 5),
- (5, 2, 6.5),
- (5, 4, 13),
- (5, 5, 13),
- (6, 1, 15),
- (6, 2, 3.5),
- (6, 4, 16),
- (6, 5, 5),
- (7, 1, 2.5),
- (7, 2, 18),
- (7, 4, 11),
- (7, 5, 10),
- (8, 1, 16),
- (8, 2, 0),
- (8, 4, 6),
- (8, 5, 11.5),
- (9, 1, 20),
- (9, 2, 20),
- (9, 4, 14),
- (9, 5, 9.5),
- (10, 1, 3),
- (10, 2, 12.5),
- (10, 4, 0),
- (10, 5, 16);
- INSERT INTO ACTIVITES (NIVEAU, NOM, EQUIPE) VALUES
- (1,'Mini foot','Amc Indus'),
- (1,'Surf','Les planchistes ...'),
- (2,'Tennis','Ace Club'),
- (3,'Tennis','Ace Club'),
- (1,'Volley ball', 'Avs80'),
- (2,'Mini foot', 'Les as du ballon'),
- (2,'Volley ball', 'smash');
- INSERT INTO ACTIVITES_PRATIQUEES (NUM_ELEVE, NIVEAU, NOM) VALUES
- (1, 1, 'Mini foot'),
- (1, 1, 'Surf'),
- (1, 1, 'Volley ball'),
- (1, 2, 'Tennis'),
- (2, 1, 'Mini foot'),
- (2, 2, 'Tennis'),
- (3, 2, 'Mini foot'),
- (3, 2, 'Tennis'),
- (3, 2, 'Volley ball'),
- (4, 1, 'Surf'),
- (4, 2, 'Mini foot'),
- (5, 1, 'Mini foot'),
- (5, 1, 'Surf'),
- (5, 1, 'Volley ball'),
- (8, 1, 'Mini foot'),
- (8, 1, 'Volley ball'),
- (8, 2, 'Volley ball'),
- (9, 1, 'Mini foot'),
- (9, 2, 'Volley ball'),
- (10, 1, 'Mini foot'),
- (10, 2, 'Tennis'),
- (10, 2, 'Volley ball');
- COMMIT;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement