Ecrire les requêtes SQL permettant de créer les quatre tables de la base «
Société Express » tout en précisant les différents types de contraintes.
-- Table Personne
CREATE TABLE Personne (
NumP NUMBER(4) PRIMARY KEY,
NomP VARCHAR(9) NOT NULL,
PosteP VARCHAR(12) NOT NULL,
Date_embP DATE,
SalaireP NUMBER(8,2),
NumServP NUMBER(2),
FOREIGN KEY (NumServP) REFERENCES Service(NumServ)
);
-- Table Service
CREATE TABLE Service (
NumServ NUMBER (2) PRIMARY KEY,
NomServ VARCHAR(12) NOT NULL,
LocServ VARCHAR(10)
);
-- Table Projet
CREATE TABLE Projet (
NumProj VARCHAR(4) PRIMARY KEY,
TitreProj VARCHAR(14) UNIQUE,
NumResp_Proj NUMBER(4),
BudgetProj NUMBER(9,2),
HeurBudget NUMBER(4),
DateDeb DATE,
FOREIGN KEY (NumResp_Proj) REFERENCES Personne(NumP)
);
-- Table Affectation
CREATE TABLE Affectation (
NumPersAff NUMBER(4),
NumProjAff VARCHAR(4),
DateAffect DATE,
NbHeures NUMBER(3),
PRIMARY KEY (NumPersAff, NumProjAff),
FOREIGN KEY (NumPersAff) REFERENCES Personne(NumP),
FOREIGN KEY (NumProjAff) REFERENCES Projet(NumProj)
);
2. Ecrire les requêtes permettant d’insérer une ligne dans chaque table.
-- Insérer des données dans la table Service
INSERT INTO Service (NumServ, NomServ, LocServ) VALUES
(10, 'VENTES', 'Tunis'),
(20, 'RECHERCHE', 'Sousse'),
(30, 'FINANCE', 'Tunis'),
(40, 'INFORMATIQUE', 'Sfax');
-- Insérer des données dans la table Personne
INSERT INTO Personne (NumP, NomP, PosteP, Date_embP, SalaireP,
NumServP) VALUES
(7501, 'BEN AYED', 'Directeur', '2022-01-01', 1200, 10),
(7502, 'HAMROUNI', 'Ingénieur', '2022-02-01', 1000, 20),
(7503, 'BEN MOUSSA', 'Secrétaire', '2022-03-01', 800, 30),
(7504, 'BEN ROMDHAN', 'Technicien', '2022-04-01', 900, 40),
(7505, 'CHARFI', 'Directeur', '2022-05-01', 1100, 10),
(7506, 'MABROUK', 'Ingénieur', '2022-06-01', 950, 20);
-- Insérer des données dans la table Projet
INSERT INTO Projet (NumProj, TitreProj, NumResp_Proj, BudgetProj,
HeurBudget, DateDeb) VALUES
(101, 'SUPER_RAPIDE', 7501, 5000.00, 100, '2022-05-01'),
(102, 'MEGA_PROJET', 7502, 7000.00, 150, '2022-06-01'),
(103, 'PROJET_XYZ', 7503, 6000.00, 120, '2022-07-01'),
(104, 'PROJET_ABC', 7504, 8000.00, 200, '2022-08-01');
-- Insérer des données dans la table Affectation
INSERT INTO Affectation (NumPersAff, NumProjAff, DateAffect, NbHeures)
VALUES
(7501, 101, '2022-05-01', 40),
(7502, 102, '2022-06-01', 50),
(7503, 103, '2022-07-01', 30),
(7504, 104, '2022-08-01', 60),
(7505, 101, '2022-05-01', 35),
(7506, 102, '2022-06-01', 45);
3. Ecrire une requête permettant d’ajouter à la table personne une colonne
COMMISSION en format NUMBER(7,2).
ALTER TABLE Personne
ADD COMMISSION NUMBER(7,2);
4. Ecrire une requête permettant de modifier le domaine de l’attribut
NomServ à C16 au lieu de C12 .
ALTER TABLE Personne
MODIFY NomServ VARCHAR2(16);
5. Ecrire en SQL les requêtes permettant de :
a. de lister les noms des personnes appartenant au service 30.
SELECT NomP
FROM Personne
WHERE NumServP = 30;
b. de lister les noms et numéros des personnes des services 20 ou 30.
SELECT NumP, NomP
FROM Personne
WHERE NumServP IN (20, 30);
c.de lister les noms des services situés à Tunis.
SELECT NomServ
FROM Service
WHERE LocServ = 'Tunis';
6. Quels sont les numéros des personnes responsables d’au moins un projet
SELECT DISTINCT NumResp_Proj
FROM Projet
WHERE NumResp_Proj IS NOT NULL;
7. Quels sont les noms et les salaires des personnes qui ont un salaire
supérieur à 600DT ?
SELECT NomP, SalaireP
FROM Personne
WHERE SalaireP > 600;
8. Quels sont les noms et les salaires des personnes qui ont un salaire compris
dans l’intervalle [500,1500] ?
SELECT NomP, SalaireP
FROM Personne
WHERE SalaireP BETWEEN 500 AND 1500;
9. Quels sont les noms des personnes du service 10 qui sont des directeurs ou des
secrétaires ?
SELECT NomP
FROM Personne
WHERE NumServP = 10 AND (PosteP = 'DIRECTEUR' OR PosteP =
'SECRETAIRE');
10. Quels sont les noms des personnes qui ne font pas partie des services 10, 20
et 60 ?
SELECT NomP
FROM Personne
WHERE NumServP NOT IN (10, 20, 60);
11. Quels sont les noms, les postes et les salaires des personnes par ordre croissant
des postes et décroissant des salaires ?
SELECT NomP, PosteP, SalaireP
FROM Personne
ORDER BY PosteP ASC, SalaireP DESC;
12. Quel est le nombre total des personnes responsables d’au moins un projet ?
SELECT COUNT(DISTINCT NumResp_Proj) AS NombreResponsables
FROM Projet
WHERE NumResp_Proj IS NOT NULL;
13. Quel est le salaire le plus élevé parmi les salaires des personnes qui occupent
un poste d’ingénieur ?
SELECT MAX(SalaireP) AS SalaireMaximum
FROM Personne
WHERE PosteP = 'INGENIEUR';
14. Quel est le salaire moyen des personnes qui occupent un poste de secrétaire ?
SELECT AVG(SalaireP) AS SalaireMoyenSecretaire
FROM Personne
WHERE PosteP = 'SECRETAIRE';
15. Quelle est la somme totale des salaires des personnes du service 10 ?
SELECT SUM(SalaireP) AS SommeSalaires
FROM Personne
WHERE NumServP = 10;
16. Donnez les noms des personnes dont les noms comportent les lettres B et E.
SELECT NomP
FROM Personne
WHERE NomP LIKE '%B%' AND NomP LIKE '%E%';
17. Quels sont les noms des personnes contenant la lettre E en deuxième position
et se terminant par M ?
SELECT NomP
FROM Personne
WHERE NomP LIKE '_E%M';
18. Quel est le nombre de personnes qui ont un salaire inférieur à 600 après une
augmentation de 5% ?
SELECT COUNT(*)
FROM Personne
WHERE SalaireP * 1.05 < 600;
19. Quels sont les titres de projets qui ont débuté en Décembre ?
SELECT TitreProj
FROM Projet
WHERE DateDeb between “2022-12-01” and “2022-12-31”;
1. Quels sont les numéros des projets auxquels sont affectées à la fois les
personnes ayant les numéros 7501 et 7902 ?
SELECT NumProjAff
FROM Affectation
WHERE NumPersAff IN (7501, 7902)
GROUP BY NumProjAff
HAVING COUNT(DISTINCT NumPersAff) = 2;
OR
SELECT NumProjAff
FROM Affectation
WHERE NumPersAff = 7501
INTERSECT
SELECT NumProjAff
FROM Affectation
WHERE NumPersAff = 7902;
2. Quels sont les noms des personnes qui travaillent au service ‘FINANCE’ ?
SELECT NomP
FROM Personne, Service
where Personne.NumServP = Service.NumServ
and Service.NomServ = 'FINANCE';
3. Donnez la liste des couples de noms d’employés occupant un poste identique
et percevant des salaires différents.
SELECT P1.NomP, P2.NomP, P1.PosteP, P1.SalaireP, P2.SalaireP
FROM Personne P1, Personne P2 where P1.PosteP = P2.PosteP AND P1.NumP
< P2.NumP AND P1.SalaireP <> P2.SalaireP;
4. Quels sont les titres de projets auxquels ‘BEN AYED’ est affecté ?
SELECT DISTINCT Projet.TitreProj
FROM Personne, Affectation, Projet
where Personne.NumP = Affectation.NumPersAff
and Affectation.NumProjAff = Projet.NumProj
and Personne.NomP = 'BEN AYED';
5. Quels sont les numéros des employés qui participent à au moins un projet
auquel participe l’ingénieur ‘HAMROUNI’ ?
SELECT DISTINCT Affectation.NumPersAff
FROM Affectation, Personne, Affectation AS A, Personne AS P
Where Affectation.NumPersAff = Personne.NumP
And Affectation.NumProjAff = A.NumProjAff
And A.NumPersAff = P.NumP
And P.NomP = 'HAMROUNI';
6. Quels sont les noms des employés du service RECHERCHE qui sont des
directeurs ou des secrétaires ?
SELECT Personne.NomP
FROM Personne, Service
where Personne.NumServP = Service.NumServ
and Service.NomServ = 'RECHERCHE' AND (Personne.PosteP =
'DIRECTEUR' OR Personne.PosteP = 'SECRETAIRE');
7. Quel est le nombre total des directeurs responsables de projets ?
SELECT COUNT(DISTINCT NumResp_Proj) AS
NombreDirecteursResponsables
FROM Projet
WHERE NumResp_Proj IS NOT NULL;
1. Donnez la liste des personnes percevant un salaire supérieur au salaire
moyen.
SELECT NomP, SalaireP
FROM Personne
WHERE SalaireP > (SELECT AVG(SalaireP) FROM Personne);
2. Quels sont les noms des personnes qui travaillent au service ‘FINANCE’ ?
SELECT NomP
FROM Personne
JOIN Service ON Personne.NumServP = Service.NumServ
WHERE Service.NomServ = 'FINANCE';
2. Combien de personnes du service recherche ont été affectées à des projets
?
SELECT COUNT(DISTINCT NumP) AS NombrePersonnes
from Personne as P, Service as S, Affectation as A
where P.Nump=A.NumPersAff
and S.numServ=P.NumServP
and S.Nomserv="RECHERCHE";
3. Quels sont les noms des personnes qui gagnent plus que CHARFI ?
SELECT NomP
FROM Personne
WHERE SalaireP > (SELECT SalaireP FROM Personne WHERE NomP =
'CHARFI');
5. Quels sont les noms des personnes basées à ARIANA et qui travaillent sur le
projet SUPER_VITE ?
select NomP from personne as p, Affectation as A, Projet as Pr, service as s
where S.NumServ=P.NumServp
and A.NumpersAff=P.Nump
AND Pr.NumProj=A.NumprojAff
and S.LocServ = 'Tunis' AND Pr.TitreProj = 'SUPER_RAPIDE';
OR
SELECT NomP
FROM Personne
JOIN Service ON Personne.NumServP = Service.NumServ
JOIN Affectation ON Personne.NumP = Affectation.NumPersAff
JOIN Projet ON Affectation.NumProjAff = Projet.NumProj
WHERE Service.LocServ = 'ARIANA' AND Projet.TitreProj = 'SUPER_VITE';
6. Quels sont les personnes du service VENTES embauchées après « BEN
MOUSSA », qui occupent le même poste que « BEN ROMDHAN » et qui
touchent un salaire moins élevé que « MABROUK » ?
SELECT NomP
FROM Personne
WHERE NumServP = 20
AND Date_embP > (SELECT Date_embP FROM Personne WHERE NomP =
'BEN MOUSSA')
AND PosteP = (SELECT PosteP FROM Personne WHERE NomP = 'BEN
ROMDHAN')
AND SalaireP < (SELECT SalaireP FROM Personne WHERE NomP =
'MABROUK');
7. Quels sont les personnes affectées à des projets dont le responsable est « BEN
AYED » ?
SELECT DISTINCT Personne.NomP
FROM Personne, projet, affectation
where Personne.NumP = Projet.NumResp_Proj
and Personne.NumP = Affectation.NumPersAff
and Projet.NumResp_Proj = (SELECT NumP FROM Personne WHERE NomP
= 'BEN AYED');
OR
SELECT DISTINCT Personne.NomP
FROM Personne
JOIN Projet ON Personne.NumP = Projet.NumResp_Proj
JOIN Affectation ON Personne.NumP = Affectation.NumPersAff
WHERE Projet.NumResp_Proj = (SELECT NumP FROM Personne WHERE
NomP = 'BEN AYED');
8. Quels sont les noms des personnes qui participent à tous les projets ? (division)
SELECT NomP
FROM Personne
WHERE NumP IN (
SELECT NumPersAff
FROM Affectation
GROUP BY NumPersAff
HAVING COUNT(DISTINCT NumProjAff) = (SELECT COUNT(*) FROM
Projet)
);
9. Quels sont les personnes affectées à tous les projets dont le responsable est la
personne numéro 7902 ? (utiliser NOT EXISTS)
SELECT NomP
FROM Personne P
WHERE NOT EXISTS (
SELECT *
FROM Projet Pr
WHERE Pr.NumResp_Proj = 7501
AND NOT EXISTS (
SELECT *
FROM Affectation A
WHERE A.NumPersAff = P.NumP AND A.NumProjAff = Pr.NumProj
)
);
10. Quels sont les noms des personnes qui gagnent plus que tous les employés du
service « VENTES » ?
SELECT P.NomP
FROM Personne P
WHERE SalaireP > MAX (
SELECT SalaireP
FROM Personne
WHERE NumServP = (SELECT NumServ FROM Service WHERE NomServ =
'VENTES')
);
11. Quels sont les noms des personnes qui gagnent plus qu’au moins un employé du
service « VENTES » ?
SELECT P.NomP
FROM Personne P
WHERE SalaireP > MIN (
SELECT SalaireP
FROM Personne
WHERE NumServP = (SELECT NumServ FROM Service WHERE NomServ =
'VENTES')
);
12. Quels sont les noms des personnes qui travaillent dans le service «
RECHERCHE » et qui sont responsables d’un projet » ?
SELECT P.NomP
FROM Personne P, projet
where P.NumP = Projet.NumResp_Proj
and P.NumServP = (SELECT NumServ FROM Service WHERE NomServ =
'RECHERCHE');
OR
SELECT P.NomP
FROM Personne P
JOIN Projet ON P.NumP = Projet.NumResp_Proj
WHERE P.NumServP = (SELECT NumServ FROM Service WHERE NomServ =
'RECHERCHE');
13. Quels sont les noms des personnes qui travaillent dans le service «
RECHERCHE » et qui ne sont pas responsables de projet » ?
SELECT NomP
FROM Personne
JOIN Service ON Personne.NumServP = Service.NumServ
WHERE Service.NomServ = 'RECHERCHE' AND Personne.NumP NOT IN (
SELECT NumResp_Proj
FROM Projet
WHERE NumResp_Proj IS NOT NULL
);
1. Donner pour chaque service
▪ Le numéro de service
▪ Le montant total des salaires
▪ Le nombre de personnes
▪ Le montant du salaire moyen
SELECT
S.NumServ,
SUM(P.SalaireP) AS MontantTotalSalaires,
COUNT(P.NumP) AS NombrePersonnes,
AVG(P.SalaireP) AS SalaireMoyen
FROM Service S, Personne P
where S.NumServ = P.NumServP
GROUP BY S.NumServ;
2. Donner par service et par poste
▪ Le nom du service
▪ Le nom du poste
▪ Le montant total des salaires
▪ Le nombre de personnes occupant le poste
▪ Le montant du salaire moyen
select s.nomserv, p.postep, sum(p.salairep), count(p.nump), avg(salairep)
from service as s, personne as p
where s.numserv=p.numservp
group by s.nomserv, p.postep;
4. Donner les noms des services comportant plus de 3 personnes.
SELECT S.NomServ
FROM Service S, Personne P where S.NumServ = P.NumServP
GROUP BY S.NomServ
HAVING COUNT(P.NumP) > 3;
5. Donner le nom du service comportant le plus de personnes.
SELECT S.NomServ
FROM Service S, where S.NumServ = P.NumServP
GROUP BY S.NomServ
ORDER BY COUNT(P.NumP) DESC
LIMIT 1;
6. Quels sont les postes ayant le salaire moyen le plus faible ?
SELECT PosteP, AVG(SalaireP) AS SalaireMoyen
FROM Personne
GROUP BY PosteP
ORDER BY SalaireMoyen ASC
LIMIT 1;
