Gestion des intervenants
SQL sous Oracle
Soit le MCD ci-après.
Question
Produisez le MLD relationnel.
Solution
CTRL+C pour copier, CTRL+V pour coller
1
tIntervenant (#pknom:varchar, prenom:varchar, poste:integer)
2
tCours (#pkannee:2000..2100, #pknum:integer, titre:varchar, type:C|TD|TP, fkintervenant=>tIntervenant, debut:date)
tIntervenant (#pknom:varchar, prenom:varchar, poste:integer) tCours (#pkannee:2000..2100, #pknum:integer, titre:varchar, type:C|TD|TP, fkintervenant=>tIntervenant, debut:date)
Question
Produisez un code SQL qui fonctionne pour Oracle et pour PostgreSQL.
Solution
CTRL+C pour copier, CTRL+V pour coller
1
CREATE TABLE tIntervenant (
2
pknom VARCHAR(255) PRIMARY KEY,
3
prenom VARCHAR(255) NOT NULL,
4
poste INTEGER5
);
6
7
CREATE TABLE tCours (
8
pkannee INTEGER check (pkannee>2000 and pkannee<2100),
9
pknum INTEGER,10
titre VARCHAR(255),
11
type CHAR(2) CHECK (type='C' OR type='TD' OR type='TP') NOT NULL,
12
fkintervenant VARCHAR(255) REFERENCES tIntervenant(pknom) NOT NULL,
13
debut DATE,14
PRIMARY KEY(pkannee, pknum)
15
);
CREATE TABLE tIntervenant ( pknom VARCHAR(255) PRIMARY KEY, prenom VARCHAR(255) NOT NULL, poste INTEGER ); CREATE TABLE tCours ( pkannee INTEGER check (pkannee>2000 and pkannee<2100), pknum INTEGER, titre VARCHAR(255), type CHAR(2) CHECK (type='C' OR type='TD' OR type='TP') NOT NULL, fkintervenant VARCHAR(255) REFERENCES tIntervenant(pknom) NOT NULL, debut DATE, PRIMARY KEY(pkannee, pknum) );
Remarque :
Sous Oracle il est recommandé d'utiliser le type VARCHAR2, mais pour conserver un code standard, on préférera le type standard VARCHAR.
Question
Produisez un code spécialisé pour Oracle.
Solution
CTRL+C pour copier, CTRL+V pour coller
1
CREATE TABLE tIntervenant (
2
pknom VARCHAR2(255) PRIMARY KEY,3
prenom VARCHAR2(255) NOT NULL,
4
poste NUMBER(4)5
);
6
7
CREATE TABLE tCours (
8
pkannee NUMBER(4) check (pkannee>2000 and pkannee<2100),
9
pknum INTEGER,10
titre VARCHAR2(255),11
type CHAR(2) CHECK (type='C' OR type='TD' OR type='TP') NOT NULL,
12
fkintervenant VARCHAR2(255) REFERENCES tIntervenant(pknom) NOT NULL,
13
debut DATE,14
PRIMARY KEY(pkannee, pknum)
15
);
CREATE TABLE tIntervenant ( pknom VARCHAR2(255) PRIMARY KEY, prenom VARCHAR2(255) NOT NULL, poste NUMBER(4) ); CREATE TABLE tCours ( pkannee NUMBER(4) check (pkannee>2000 and pkannee<2100), pknum INTEGER, titre VARCHAR2(255), type CHAR(2) CHECK (type='C' OR type='TD' OR type='TP') NOT NULL, fkintervenant VARCHAR2(255) REFERENCES tIntervenant(pknom) NOT NULL, debut DATE, PRIMARY KEY(pkannee, pknum) );
Remarque :
On utilise le type VARCHAR2 recommandé par la documentation, et l'on peut aussi utiliser le type NUMBER pour spécifier des types numériques limités.
Question
Vérifier la création de vos tables dans le dictionnaire de données (existence de la table, description de la table, et liste des contraintes).
Indice
CTRL+C pour copier, CTRL+V pour coller
1
Name Null Type
2
------------- -------- -------------
3
PKANNEE NOT NULL NUMBER(4)
4
PKNUM NOT NULL NUMBER(38)
5
TITRE VARCHAR2(255)
6
TYPE NOT NULL CHAR(2)
7
FKINTERVENANT NOT NULL VARCHAR2(255)
8
DEBUT DATE
Name Null Type ------------- -------- ------------- PKANNEE NOT NULL NUMBER(4) PKNUM NOT NULL NUMBER(38) TITRE VARCHAR2(255) TYPE NOT NULL CHAR(2) FKINTERVENANT NOT NULL VARCHAR2(255) DEBUT DATE
Indice
CTRL+C pour copier, CTRL+V pour coller
1
TABLE_NAME TYPE CONSTRAINT_NAME SEARCH_CONDITION
2
------------------------------ ---- ------------------------------ ----------------------------------
3
TCOURS C SYS_C00411567 "FKINTERVENANT" IS NOT NULL
4
TCOURS C SYS_C00411568 pkannee>2000 and pkannee<2100
5
TCOURS C SYS_C00411569 type='C' OR type='TD' OR type='TP'
6
TCOURS C SYS_C00411566 "TYPE" IS NOT NULL
7
TCOURS P SYS_C00411570
8
TCOURS R SYS_C00411571
9
TINTERVENANT C SYS_C00411564 "PRENOM" IS NOT NULL
10
TINTERVENANT P SYS_C00411565
TABLE_NAME TYPE CONSTRAINT_NAME SEARCH_CONDITION ------------------------------ ---- ------------------------------ ---------------------------------- TCOURS C SYS_C00411567 "FKINTERVENANT" IS NOT NULL TCOURS C SYS_C00411568 pkannee>2000 and pkannee<2100 TCOURS C SYS_C00411569 type='C' OR type='TD' OR type='TP' TCOURS C SYS_C00411566 "TYPE" IS NOT NULL TCOURS P SYS_C00411570 TCOURS R SYS_C00411571 TINTERVENANT C SYS_C00411564 "PRENOM" IS NOT NULL TINTERVENANT P SYS_C00411565
Solution
CTRL+C pour copier, CTRL+V pour coller
1
SELECT table_name FROM user_tables;
2
3
DESCRIBE tCours;
4
5
SELECT table_name, constraint_type AS type, constraint_name, search_condition
6
FROM user_constraints7
WHERE constraint_name NOT LIKE 'BIN$%'
8
ORDER BY table_name, constraint_type
9
SELECT table_name FROM user_tables; DESCRIBE tCours; SELECT table_name, constraint_type AS type, constraint_name, search_condition FROM user_constraints WHERE constraint_name NOT LIKE 'BIN$%' ORDER BY table_name, constraint_type
Question
Initialiser la base avec les données suivantes :
Stéphane Crozat est un intervenant, poste 4287.
Il fait deux cours, Introduction et Modélisation, le 1 janvier et le 8 janvier 2001.
Indice
CTRL+C pour copier, CTRL+V pour coller
1
PKANNEE P TITRE TYPE FKINTERVENANT DEBUT
2
------- - --------------- ---- --------------- ---------
3
2001 1 Introduction C CROZAT 01-JAN-01
4
2001 2 Modélisation TD CROZAT 08-JAN-01
PKANNEE P TITRE TYPE FKINTERVENANT DEBUT ------- - --------------- ---- --------------- --------- 2001 1 Introduction C CROZAT 01-JAN-01 2001 2 Modélisation TD CROZAT 08-JAN-01
Solution
CTRL+C pour copier, CTRL+V pour coller
1
INSERT INTO tIntervenant (pknom, prenom, poste)
2
VALUES ('CROZAT', 'Stéphane', '4287');
3
4
INSERT INTO tCours (pkannee, pknum, titre, type, debut, fkintervenant)
5
VALUES (2001, 1, 'Introduction','C', TO_DATE('01-01-2001','DD-MM-YYYY'), 'CROZAT');
6
7
INSERT INTO tCours (pkannee, pknum, titre, type, debut, fkintervenant)
8
VALUES (2001, 2, 'Modélisation','TD', TO_DATE('08-01-2001','DD-MM-YYYY'), 'CROZAT');
9
10
SELECT * FROM tCours;
INSERT INTO tIntervenant (pknom, prenom, poste)
VALUES ('CROZAT', 'Stéphane', '4287');
INSERT INTO tCours (pkannee, pknum, titre, type, debut, fkintervenant)
VALUES (2001, 1, 'Introduction','C', TO_DATE('01-01-2001','DD-MM-YYYY'), 'CROZAT');
INSERT INTO tCours (pkannee, pknum, titre, type, debut, fkintervenant)
VALUES (2001, 2, 'Modélisation','TD', TO_DATE('08-01-2001','DD-MM-YYYY'), 'CROZAT');
SELECT * FROM tCours;Question
Écrivez la requête SQL permettant de renvoyer pour chaque cours le jour de la semaine (lundi, mardi...) et le numéro de la semaine (1..53).
Question
Écrivez la requête SQL permettant de renvoyer concaténés sur seule colonne le nom, le prénom et le poste entre parenthèse de chaque intervenant.
Question
Écrivez la requête SQL permettant de renvoyer la liste des cours avec leur type en toutes lettres : Cours, Travaux dirigés, Travaux pratiques.
Solution
CTRL+C pour copier, CTRL+V pour coller
1
SELECT pknum AS cours,
2
CASE
3
WHEN type='C' THEN 'Cours'
4
WHEN type='TD' THEN 'Travaux dirigés'
5
WHEN type='TP' THEN 'Travaux pratiques'
6
END AS type_label7
FROM tCoursSELECT pknum AS cours,
CASE
WHEN type='C' THEN 'Cours'
WHEN type='TD' THEN 'Travaux dirigés'
WHEN type='TP' THEN 'Travaux pratiques'
END AS type_label
FROM tCoursQuestion
Écrivez le code SQL*Plus et la requête permettant de renvoyer toutes les informations de la table tCours sur un minimum de caractères.
Indice
CTRL+C pour copier, CTRL+V pour coller
1
A N COURS T INTERVENANT DEBUT
2
---- - --------------- -- --------------- ---------
3
2001 1 Introduction C CROZAT 01-JAN-01
4
2001 2 Modélisation TD CROZAT 08-JAN-01
A N COURS T INTERVENANT DEBUT ---- - --------------- -- --------------- --------- 2001 1 Introduction C CROZAT 01-JAN-01 2001 2 Modélisation TD CROZAT 08-JAN-01
Solution
CTRL+C pour copier, CTRL+V pour coller
1
COLUMN a FORMAT A4
2
COLUMN n FORMAT A1
3
COLUMN cours FORMAT A15
4
COLUMN t FORMAT A2
5
COLUMN intervenant FORMAT A15
6
7
SELECT 8
pkannee AS a,9
pknum AS n,10
titre AS cours,11
type AS T,12
fkintervenant AS intervenant,13
debut
14
FROM tCours;15
COLUMN a FORMAT A4 COLUMN n FORMAT A1 COLUMN cours FORMAT A15 COLUMN t FORMAT A2 COLUMN intervenant FORMAT A15 SELECT pkannee AS a, pknum AS n, titre AS cours, type AS T, fkintervenant AS intervenant, debut FROM tCours;
PL/SQL sous Oracle
Pour la suite de l'exercice on introduira les données complémentaires suivantes dans la base de données.
Question
Écrivez une fonction PL/SQL prenant en paramètre un intervenant et renvoyant la chaîne 'OUI' s'il intervient au moins une fois dans un cours, et 'NON' sinon.
Solution
CTRL+C pour copier, CTRL+V pour coller
1
CREATE OR REPLACE FUNCTION fIntervient (pIntervenant VARCHAR2)
2
RETURN VARCHAR2
3
IS4
vNbInterventions NUMBER;5
BEGIN6
SELECT COUNT(fkintervenant) INTO vNbInterventions
7
FROM tCours 8
WHERE fkintervenant=pIntervenant;9
10
IF vNbInterventions > 0 THEN
11
RETURN 'OUI';
12
ELSE13
RETURN 'NON';
14
END IF;
15
END;16
/
17
18
SELECT pknom, fIntervient(pknom) AS I FROM tIntervenant;
CREATE OR REPLACE FUNCTION fIntervient (pIntervenant VARCHAR2)
RETURN VARCHAR2
IS
vNbInterventions NUMBER;
BEGIN
SELECT COUNT(fkintervenant) INTO vNbInterventions
FROM tCours
WHERE fkintervenant=pIntervenant;
IF vNbInterventions > 0 THEN
RETURN 'OUI';
ELSE
RETURN 'NON';
END IF;
END;
/
SELECT pknom, fIntervient(pknom) AS I FROM tIntervenant;Question
Écrivez une procédure PL/SQL permettant d'afficher la liste de toutes les intervenants.
Indice
CTRL+C pour copier, CTRL+V pour coller
1
CREATE OR REPLACE PROCEDURE pAfficheIntervenants
2
IS3
CURSOR ...4
vNom ...
5
vPrenom ...
6
BEGIN7
DBMS_OUTPUT.PUT_LINE...
8
OPEN ... 9
LOOP10
FETCH...
11
EXIT...
12
DBMS_OUTPUT.PUT_LINE...
13
END LOOP;
14
END;15
/
CREATE OR REPLACE PROCEDURE pAfficheIntervenants
IS
CURSOR ...
vNom ...
vPrenom ...
BEGIN
DBMS_OUTPUT.PUT_LINE...
OPEN ...
LOOP
FETCH...
EXIT...
DBMS_OUTPUT.PUT_LINE...
END LOOP;
END;
/Solution
CTRL+C pour copier, CTRL+V pour coller
1
CREATE OR REPLACE PROCEDURE pAfficheIntervenants
2
IS3
CURSOR cIntervenants IS
4
SELECT pknom, prenom FROM tIntervenant;
5
vNom tIntervenant.pknom%TYPE;6
vPrenom tIntervenant.prenom%TYPE;7
BEGIN8
DBMS_OUTPUT.PUT_LINE('** Liste des intervenants **');9
OPEN cIntervenants; 10
LOOP11
FETCH cIntervenants INTO vNom, vPrenom;
12
EXIT WHEN cIntervenants%NOTFOUND;
13
DBMS_OUTPUT.PUT_LINE('-' || INITCAP(TRIM(vPrenom)) || ' ' || INITCAP(TRIM(vNom)));
14
END LOOP;
15
END;16
/
17
18
SET SERVEROUTPUT ON;
19
BEGIN20
pAfficheIntervenants1;
21
END;22
/
CREATE OR REPLACE PROCEDURE pAfficheIntervenants
IS
CURSOR cIntervenants IS
SELECT pknom, prenom FROM tIntervenant;
vNom tIntervenant.pknom%TYPE;
vPrenom tIntervenant.prenom%TYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('** Liste des intervenants **');
OPEN cIntervenants;
LOOP
FETCH cIntervenants INTO vNom, vPrenom;
EXIT WHEN cIntervenants%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('-' || INITCAP(TRIM(vPrenom)) || ' ' || INITCAP(TRIM(vNom)));
END LOOP;
END;
/
SET SERVEROUTPUT ON;
BEGIN
pAfficheIntervenants1;
END;
/Question
Écrivez un trigger permettant de gérer la redondance entre pkannee et debut. L'on souhaite le comportement suivant :
si l'année de début du cours ne correspond pas à pkanne, alors la date de début du cours est mise à NULL
on ajoute un message sur le sortie standard.
Indice
CTRL+C pour copier, CTRL+V pour coller
1
SET SERVEROUTPUT ON;
2
3
INSERT INTO tCours (pkannee, pknum, titre, type, fkIntervenant, debut)
4
VALUES ('2001', 3, 'SQL', 'C', 'CROZAT', TO_DATE('15-01-2001','DD-MM-YYYY'));
5
6
UPDATE tCours7
SET debut=TO_DATE('15-01-2002','DD-MM-YYYY')
8
WHERE pknum=3;
9
10
SELECT pkannee, pknum, debut FROM tCours;
SET SERVEROUTPUT ON;
INSERT INTO tCours (pkannee, pknum, titre, type, fkIntervenant, debut)
VALUES ('2001', 3, 'SQL', 'C', 'CROZAT', TO_DATE('15-01-2001','DD-MM-YYYY'));
UPDATE tCours
SET debut=TO_DATE('15-01-2002','DD-MM-YYYY')
WHERE pknum=3;
SELECT pkannee, pknum, debut FROM tCours;CTRL+C pour copier, CTRL+V pour coller
1
TRIGGER trCours compiled
2
1 rows inserted.
3
1 rows updated.
4
Inconsistency between debut and pkannee, debut set to null
5
6
PKAN PKNUM DEBUT
7
---- ----- ---------
8
2001 1 01-JAN-01
9
2001 2 08-JAN-01
10
2001 3
TRIGGER trCours compiled 1 rows inserted. 1 rows updated. Inconsistency between debut and pkannee, debut set to null PKAN PKNUM DEBUT ---- ----- --------- 2001 1 01-JAN-01 2001 2 08-JAN-01 2001 3
Indice
CTRL+C pour copier, CTRL+V pour coller
1
CREATE OR REPLACE TRIGGER trCours
2
BEFORE INSERT OR UPDATE OF debut ON tCours
3
FOR EACH ROW
4
DECLARE 5
vAnneeDebut INTEGER;6
BEGIN7
vAnneeDebut := ...
8
IF ...9
END;10
/
11
CREATE OR REPLACE TRIGGER trCours BEFORE INSERT OR UPDATE OF debut ON tCours FOR EACH ROW DECLARE vAnneeDebut INTEGER; BEGIN vAnneeDebut := ... IF ... END; /
Solution
CTRL+C pour copier, CTRL+V pour coller
1
CREATE OR REPLACE TRIGGER trCours
2
BEFORE INSERT OR UPDATE OF debut ON tCours
3
FOR EACH ROW
4
DECLARE 5
vAnneeDebut INTEGER;6
BEGIN7
vAnneeDebut := TO_NUMBER(TO_CHAR(:new.debut,'YYYY'));
8
IF NOT(vAnneeDebut = :new.pkannee) THEN
9
:new.debut:=null;10
DBMS_OUTPUT.PUT_LINE('Inconsistency between debut and pkannee, debut set to null');11
END IF;
12
END;13
/
14
15
SET SERVEROUTPUT ON;
16
17
INSERT INTO tCours (pkannee, pknum, titre, type, fkIntervenant, debut)
18
VALUES ('2001', 3, 'SQL', 'C', 'CROZAT', TO_DATE('15-01-2001','DD-MM-YYYY'));
19
20
UPDATE tCours21
SET debut=TO_DATE('15-01-2002','DD-MM-YYYY')
22
WHERE pknum=3;
23
24
SELECT pkannee, pknum, debut FROM tCours;
CREATE OR REPLACE TRIGGER trCours
BEFORE INSERT OR UPDATE OF debut ON tCours
FOR EACH ROW
DECLARE
vAnneeDebut INTEGER;
BEGIN
vAnneeDebut := TO_NUMBER(TO_CHAR(:new.debut,'YYYY'));
IF NOT(vAnneeDebut = :new.pkannee) THEN
:new.debut:=null;
DBMS_OUTPUT.PUT_LINE('Inconsistency between debut and pkannee, debut set to null');
END IF;
END;
/
SET SERVEROUTPUT ON;
INSERT INTO tCours (pkannee, pknum, titre, type, fkIntervenant, debut)
VALUES ('2001', 3, 'SQL', 'C', 'CROZAT', TO_DATE('15-01-2001','DD-MM-YYYY'));
UPDATE tCours
SET debut=TO_DATE('15-01-2002','DD-MM-YYYY')
WHERE pknum=3;
SELECT pkannee, pknum, debut FROM tCours;