Représenter graphiquement le schéma relationnel. On constate que les relations Salarie et Service ne sont pas reliées. Comment peut-on joindre ces relations afin d’effectuer des requêtes portant sur l’ensemble des attributs de ces tables ?
Voici les requêtes permettant de créer les relations Salarie et Competences. Après avoir analysé comment créer une table, rédiger les requêtes pour Qualifications et Service
CREATE TABLE Salarie (
num INT ,
prenom VARCHAR(20),
nom VARCHAR(20),
dateEmbauche DATE,
salaire FLOAT,
numService INT,
PRIMARY KEY (num),
FOREIGN KEY(numService) REFERENCES Service(num)
)
CREATE TABLE Competences (
numSalarie INT,
numQualif INT,
dateValid DATE,
PRIMARY KEY (numSalarie, numQualif),
FOREIGN KEY(numSalarie) REFERENCES Salarie(num),
FOREIGN KEY(numQualif) REFERENCES Qualifications(num)
)
CREATE TABLE Service (
num INT,
libelle VARCHAR(20) ,
budget INT,
PRIMARY KEY (num)
)
CREATE TABLE Qualifications (
num INT ,
libelle VARCHAR(20),
PRIMARY KEY (num)
)
- Quelle règle peut-on mettre en évidence pour joindre des relations liaison entre clé primaire et clé étrangère, même type de champ (sauf not null et auto_increment)
- Recopier les requêtes de création des 4 tables de votre bdd avant de les coller dans l’onglet sql (explications préalables fournies oralement). Si cela ne fonctionnait pas, essayer d’en trouver l’explication (intégrité référentielle)
- Analyser les requêtes ci-dessous, écrire la signification chaque requête sur votre feuille et effectuer le test sur PhpMyAdmin pour vérifier.
Modifications de structure
ALTER TABLE Salarie
ADD poste VARCHAR(20)
ALTER TABLE Salarie
ADD test CHAR(2)
ALTER TABLE Salarie
change test tests VARCHAR(3)
ALTER TABLE Salarie
Modify tests VARCHAR(20)
ALTER TABLE Salarie
DROP tests
Requêtes de gestion des données
INSERT INTO Service VALUES (1,'Production', 300000) ;
INSERT INTO Service VALUES (2,'GRH', 40000) ;
INSERT INTO Service VALUES (3,'Informatique', 50000), (4,'Gestion', 300000), (5,'Administration', 300000), (6,'Dessin', 1000) ;
à Pourquoi certaines valeurs sont insérées sans ' (6) et d’autres entre '(Dessin) ? champs numériques ou non
INSERT INTO Salarie VALUES (1,'Alain', 'Terrieur', '2017-12-12', 1200, 'Ajusteur', 1), (2,'Aurélie', 'Déal', '2017-12-22', 1200, 'Secrétaire', 2), (3,'Gérard', 'Menvussa', '2017-12-18', 1800, 'Développeur', 3), (4,'Alex', 'Terieur', '2017-12-28', 1500, 'Comptable', 4), (5,'Jacques', 'Houille', '2018-01-02', 1300, 'Attaché', 5), (6,'Paul', 'Hochon', '2017-01-02', 1200, 'Ajusteur', 1), (7,'Brice', 'Deniss', '2017-12-27', 1200, 'Secrétaire', 2), (8,'Laure', 'Hible', '2017-12-18', 1800, 'Développeur', 3), (9,'Michel', 'Linne', '2018-01-04', 1500, 'Comptable', 4), (10,'Jean', 'Pouly', '2018-01-02', 1300, 'Attaché', 5) , (11,'Jean', 'Peuplus', '2018-01-06', 1600, NULL, 1), (12,'François', 'Premier', '2018-01-06', 1300, 'Ajusteur', 1) , (13,'Zorro', NULL, '2018-01-06', 1300, 'Ajusteur', 1), (14,'Eddy', 'Moitout', '2018-01-06', 1300, 'Ajusteur', 1) ;
INSERT INTO Salarie VALUES (1,'Alain', 'Terrieur', '2017-12-12', 1200, 'Ajusteur', 1) ;
à Pourquoi cette dernière requête ne fonctionne pas ? Salarié n°1 déjà rentré : Unicité clé primaire !
DELETE budget FROM Service
à Pourquoi cela ne fonctionne t-il pas ? DELETE permet de supprimer des données et non une ‘colonne’ Si l’on avait voulu supprimer le champ budget à ALTER TABLE Service DROP budget
Alain Terrieur s’écrit Terieur. Modifier le nom
UPDATE Salarie SET nom = 'Terieur'
WHERE num = 1
à Paul Hochon s’écrit en réalité Ochon. Effectuer la modification.
UPDATE Salarie SET nom = 'Ochon'
WHERE num = 6
à Le service ‘Dessin’ (n° 6) est une erreur. Il faut le supprimer.
DELETE FROM Service WHERE num = 6
Représenter sur votre dossier papier le schéma relationnel de l’ensemble de ces relations
Service Salarie Competences Qualifications
num num #numSalarie num
libelle prenom #numQualif libelle
budget nom dateValid
dateEmbauche
salaire
poste
#numService
Bilan : création de tables, modification de structure, gestion de données (insertion). Nous disposons de 4 tables remplies. On va s’attacher à réaliser la finalité d’une Bdd : interroger une bdd
Requêtes d’interrogation des données
SELECT * FROM Salarie ;
SELECT * FROM Service ;
SELECT prenom, nom, poste FROM Salarie
Afficher maintenant les libellés et les budgets de la table Service
SELECT libelle, budget FROM Service
Afficher les dates d’embauche de chaque salarié
SELECT prenom, nom, dateEmbauche FROM Salarie
L’affichage des dates d’embauche risque d’être illisible si l’entreprise comporte trop de salariés. Il faut donc trier les résultats projetés. On utilise la clause ORDER BY
Reprenez la requête précédente et afficher les salariés triés par ordre alphabétique. Pour trier, il faut utiliser ORDER BY xxxx ASC(croissant) ou ORDER BY xxxx DESC (décroissant)
SELECT prenom, nom, dateEmbauche FROM Salarie ORDER BY nom ASC
SELECT prenom, nom, dateEmbauche FROM Salarie ORDER BY nom
SELECT prenom, nom, dateEmbauche FROM Salarie ORDER BY nom DESC
En déduire la requête permettant de trier les salaries du plus ancien au plus récent
SELECT prenom, nom, dateEmbauche FROM Salarie ORDER BY dateEmbauche DESC
Afficher les services classes par ordre alphabétique
SELECT libelle, budget FROM Service ORDER BY libelle
Afficher les services classes par budget croissant
SELECT libelle, budget FROM Service ORDER BY budget ASC
Afficher les services classes par budget décroissant
SELECT libelle, budget FROM Service ORDER BY budget DESC
On sait désormais interroger les valeurs inscrites dans les tables avec des requêtes d’interrogation. On va se servir de critères de restrictions pour limiter la portée de la requête aux seuls enregistrements souhaités.
Que signifie ?
SELECT libelle, budget FROM Service
WHERE budget > 50000 manque GRH (40 000) et informatique (50 000)
Faire la même requête mais avec >= informatique apparaît
SELECT * FROM Service
WHERE budget >= 50000
Analyser les requêtes suivantes avant de passer à la pratique
SELECT prenom, nom, poste FROM Salarie
WHERE numService = 2
AND dateEmbauche > '2017-12-15' ; service 2 = GRH (Aurélie et Brice)
SELECT * FROM Salarie
WHERE salaire < 2000
AND poste = 'Comptable'
AND numService = 4
AND dateEmbauche = '2017-12-28' service 4 = Gestion à Alex Terrieur
SELECT * FROM Salarie
SELECT * FROM Salarie
WHERE dateEmbauche > '2017-12-23' AND dateEmbauche <'2018-01-02' 2 réponses
SELECT * FROM Salarie
WHERE dateEmbauche BETWEEN '2017-12-23' AND '2018-01-02'
SELECT * FROM Salarie
WHERE dateEmbauche NOT BETWEEN '2017-12-23' AND '2018-01-02'
SELECT * FROM Salarie
WHERE poste IS NULL
SELECT * FROM Salarie
WHERE poste IS NULL
SELECT * FROM Salarie
WHERE poste IN ('Comptable', 'Secrétaire')
SELECT * FROM Salarie
WHERE poste = 'Comptable' OR poste = 'Secretaire') 4
SELECT * FROM Salarie
WHERE dateEmbauche < '2018-01-01'
SELECT * FROM Salarie
WHERE YEAR(dateEmbauche) = 2017
SELECT * FROM Salarie
WHERE MONTH(dateEmbauche) = 12
SELECT * FROM Salarie
WHERE MONTH(dateEmbauche) <> 12 différent de décembre
SELECT * FROM Salarie
WHERE poste LIKE 'Com%'
SELECT * FROM Salarie
WHERE poste LIKE '%eur'
SELECT * FROM Salarie
WHERE poste LIKE '%pt%'
SELECT * FROM Salarie
WHERE prenom LIKE '_o%' dont la 2ème letter est un O
SELECT * FROM Salarie
WHERE poste NOT LIKE 'C%'
SELECT * FROM Salarie
WHERE nom NOT LIKE 'P%'
SELECT * FROM Salarie
WHERE nom IS NULL dont le nom n’est pas renseigné (Zorro)
SELECT * FROM Salarie
WHERE nom IS NOT NULL
ORDER BY salaire, nom ASC
Rechercher :
les salariés dont le salaire est > à 1500
SELECT * FROM Salarie
WHERE salaire > 1200
les salariés dont le salaire est < 2 000
SELECT * FROM Salarie
WHERE salaire < 2000
les services dont le budget est < 100 000 classés par ordre alphabétique 2 rep
SELECT * FROM Service
WHERE budget < 100000
ORDER BY libelle ASC
Le nom et le prénom des salariés qui appartiennent au service Production (n°1)
SELECT prenom, nom, poste FROM Salarie
WHERE numService = 1
Le nom et le prénom des salariés qui appartiennent au service Production (n°1) ET qui ont été embauchés après le 31 décembre 2017 (2017-12-31)
SELECT prenom, nom, poste FROM Salarie
WHERE numService = 1
AND dateEmbauche > '2017-12-31'
Les salariés qui sont développeurs classés par ordre inverse de l’ordre alphabétique
SELECT * FROM Salarie
WHERE poste = 'développeur'
ORDER BY nom DESC
Le prenom, le nom, le poste et le salaire des salariés qui gagnent 1 500 euros ou plus
SELECT prenom, nom, salaire FROM Salarie
WHERE salaire >= 1500
Les salariés qui gagnent plus de 1 200 euros, qui sont développeurs, qui travaillent pour le poste informatique (n°3) et qui ont été embauchés le 18 décembre 2017
SELECT * FROM Salarie WHERE salaire >1200
AND poste = 'développeur'
AND numService = 3
AND dateEmbauche = '2017-12-18'
Salariés embauchés entre le 14 et le 18 décembre 2017
SELECT * FROM Salarie
WHERE dateEmbauche BETWEEN '2017-12-14' AND '2017-12-18'
Salariés qui sont comptables ou développeurs
SELECT * FROM Salarie
WHERE poste IN ('Comptable', 'Développeur')
Tous les salariés à l’exception des développeurs
SELECT * FROM Salarie
WHERE poste <> 'Développeur'
Salariés dont le nom n’est pas indiqué
SELECT * FROM Salarie
WHERE nom IS NULL
Salariés dont le prénom commence par “Z”
SELECT * FROM Salarie
WHERE prenom LIKE 'Z%'
Salariés dont l’avant-dernière du nom lettre est un “U”
SELECT * FROM Salarie
WHERE nom LIKE '%u_'
Salariés dont le poste se termine en “eur”
SELECT * FROM Salarie
WHERE nom LIKE '%eur'
Tous les salaries dont le salaire est > 1 200 et qui ne sont pas développeur
SELECT * FROM Salarie
WHERE salaire > 1200
AND poste <> 'développeur'
Salariés dont le nom commence par « H » et qui ont été embauchés un mois de janvier
SELECT * FROM Salarie
WHERE nom LIKE 'H%'
AND MONTH(dateEmbauche) = 1
Salariés qui appartiennent au service production (n°1) et embauchés en 2017
SELECT * FROM Salarie
WHERE numService = 1
AND YEAR(dateEmbauche) = 2017
Requêtes multi tables : Les jointures
La dernière requête permet de rechercher les salaries qui appartiennent au service « Production » mais pour ce faire, il faut connaître le numéro du service (en l’occurrence n°1). Il est possible d’effectuer des requêtes portant sur plusieurs tables en effectuant une jointure…
SELECT * FROM Salarie
INNER JOIN Service ON Service.num = Salarie.numService
WHERE libelle = 'Production'
- Quels sont les salariés qui travaillent dans le service Production ?
SELECT prenom, nom FROM Salarie
INNER JOIN Service ON Service.num = Salarie.numService
WHERE libelle = 'Informatique'
- Quel est le budget du service dans lequel travaille Aurélie Déal ?
SELECT * FROM Service
INNER JOIN Salarie ON Service.num = Salarie.numService
WHERE prenom = 'Aurélie'
AND nom = 'Déal'
- Insérer les données suivantes dans la table Qualifications
INSERT INTO Qualifications VALUES (1, 'Bdd'), (2, 'Css'), (3, 'Html'), (4, 'Ttx'), (5, 'Tableur'), (6, 'Ppt'), (7, 'Compta1'), (8, 'Compta2'), (9, 'OS1'), (10, 'OS2') ;
- La requête pour compléter la table Competences a été préalablement rédigée (cf ci-dessous). Vérifier cette requête. Modifier si nécessaire puis recopier cette requête dans PhpMyAdmin.
INSERT INTO Competences VALUES (1, 9, '2017-12-12'), (2, 4, '2017-12-22'), (2, 5, '2017-12-22'), (2, 6, '2017-12-22'), (3, 1, '2017-12-18'), (3, 3, '2017-12-18'), (3, 2, '2018-01-18'), (3, 4, '2017-12-18'), (4, 7, '2017-12-28'), (4, 5, '2017-12-28'), (5, 4, '2018-01-02'), (6, 10, '2017-01-02'), (7, 4, '2017-12-27'), (8, 1, '2017-12-18'), (8, 2, '2017-12-18'), (9, 9, '2018-01-04'), (9, 1, '2018-01-04'), (10, 4, '2018-01-02'), (10, 5, '2018-01-02'), (11, 11, '2018-01-06'), (12, 10, '2018-01-06'), (13, 9, '2018-01-06'), (14, 10, '2018-01-06') ;
Service numQualif 11 n’existe pas => il faut mettre 10 au lieu de 11
- Quels sont les salariés qui ont validé des compétences en 2018 ?
SELECT prenom, nom FROM Salarie
INNER JOIN Competences ON Salarie.num = Competences.numSalarie
WHERE dateValid >='2018-01-01'
- Lister les salariés qui possèdent la qualification OS1 ?
SELECT prenom, nom FROM Salarie
INNER JOIN Competences ON Salarie.num = Competences.numSalarie
INNER JOIN Qualifications ON Competences.numQualif = Qualifications.num
WHERE libelle = 'OS1'
- Quels sont les salariés qui ont validé la compétence « Css » depuis le début de l’année ?
SELECT prenom, nom FROM Salarie
INNER JOIN Competences ON Salarie.num = Competences.numSalarie
INNER JOIN Qualifications ON Competences.numQualif = Qualifications.num
WHERE libelle = 'Css'
AND dateValid >= '2018-01-01'
- Que peut-on remarquer au niveau des jointures ?
Elles relient clé primaire à clé étrangère
- Afficher libellé et budget des services dont les salariés possèdent la qualification « Bdd » ? Si cela ne fonctionne pas, lisez le message d’erreur et essayez de comprendre pourquoi ? Rectifier Préfixage, ambiguité
SELECT Service.libelle, Service.budget FROM Service
INNER JOIN Salarie ON Service.num = Salarie.numService
INNER JOIN Competences ON Salarie.num = Competences.numSalarie
INNER JOIN Qualifications ON Competences.numQualif = Qualifications.num
WHERE Qualifications.libelle = 'Bdd'
Requêtes calculées : Les agrégats
Avec le SQL, il est aussi possible d’effectuer des calculs.
Effectuer les requêtes suivantes et en déduire la signification :
SELECT COUNT(*) FROM Salarie
Comptabilisation du nombre de salariés
SELECT COUNT(budget) FROM Service
Comptabilisation de l’ensemble des services qui possèdent un budget
SELECT SUM(budget) FROM Service
Somme des budgets de l’ensemble des services
SELECT MIN(budget) FROM Service
Plus petit budget de l’ensemble des services
SELECT MAX(budget) FROM Service
Plus gros budget de l’ensemble des services
SELECT AVG(budget) FROM Service
Budget moyen de l’ensemble des services
Rechercher :
- Combien y a-t-il de services ?
SELECT COUNT(*) FROM Service
- Combien a-t-on effectué d’enregistrement dans la table « Competences » ?
SELECT COUNT(*) FROM Competences
Alias : AS
- copier la requête suivante : SELECT COUNT(*) AS nombreSalaries FROM Salarie
À quoi sert la clause « AS » ? à afficher qlqchose de + significatif que COUNT(*)
Au-delà de l’affichage, un alias est obligatoire pour effectuer une restriction conditionnelle. En effet, il faudra comparer avec l’alias.
Regroupement de resultats : GROUP BY
Quelle est la signification des requêtes ci-dessous ?
SELECT libelle, COUNT(Salarie.num)
FROM Salarie
INNER JOIN Service ON Service.num = Salarie.numService
GROUP BY libelle
Comptabilise les salaries par services : calcul pour chaque service
SELECT nom, prenom, COUNT(numQualif)
FROM Salarie
INNER JOIN Competences ON Salarie.num = Competences.numQualif
GROUP BY nom
Comptabilise le nombre de qualifications validées par salarié (nom)
SELECT nom, prenom, COUNT(numQualif)
FROM Salarie
INNER JOIN Competences ON Salarie.num = Competences.numQualif
WHERE dateValid >= '2018-01-01'
GROUP BY nom
Comptabilise le nombre de qualifications validées par salarié (nom) à partir du 1er janvier 2018
SELECT Service.libelle, COUNT(Qualifications.num)
FROM Service
INNER JOIN Salarie ON Service.num = Salarie.numService
INNER JOIN Competences ON Salarie.num = Competences.numSalarie
INNER JOIN Qualifications ON Competences.numQualif = Qualifications.num
GROUP BY Service.libelle
Indiquer le nombre de qualifications dans chaque service. Regroupement par libellé de service
Having : restriction conditionnée à un calcul (agrégat)
SELECT Service.libelle, COUNT(Qualifications.num) AS Nbre_qualifications
FROM Service
INNER JOIN Salarie ON Service.num = Salarie.numService
INNER JOIN Competences ON Salarie.num = Competences.numSalarie
INNER JOIN Qualifications ON Competences.numQualif = Qualifications.num
GROUP BY Service.libelle
HAVING Nbre_Qualifications > 4
Faire apparaître les services qui disposent dans leurs ressources humaines de plus de 2 qualifications
Analyser la différence entre les deux dernières requêtes et induire le sens de la clause HAVING
Idem mais HAVING sert à restreindre les résultats. Il s’agit d’une restriction sur le critère du calcul
Applications générales sur ce schéma relationnel. Rédiger les requêtes suivantes :
- Rechercher les salariés qui appartiennent au service n°3
SELECT nom, prenom, numService FROM Salarie
WHERE numService = 3
- Rechercher les salariés qui appartiennent au service « informatique »
SELECT nom, prenom, libelle FROM Salarie
INNER JOIN Service ON Service.num = Salarie.numService
WHERE libelle= 'informatique'
- Sélectionner l’ensemble des services de l’entreprise
SELECT * FROM Service
- Sélectionner les services disposant d’un budget inférieur à 100 000 euros
SELECT * FROM Service
WHERE budget < 100000
- Quels sont les services commençant par ‘G’ et se terminant par ‘tion’
SELECT * FROM Service
WHERE libelle LIKE 'G%' AND libelle LIKE '%tion'
- Classer les salaries du plus ancien au plus récent
SELECT * FROM Salarie ORDER BY dateEmbauche ASC
- Dans quels services trouvent-on la qualification ‘Bdd’
SELECT Service.libelle, Qualifications.libelle FROM Service
INNER JOIN Salarie ON Service.num = Salarie.numService
INNER JOIN Competences ON Salarie.num = Competences.numSalarie
INNER JOIN Qualifications ON Competences.numQualif = Qualifications.num
WHERE Qualifications.libelle = 'Bdd'
- Faire même requête en rajoutant DISTINCT devant Service.libelle ... Préciser quel en est l’intérêt :
SELECT DISTINCT Service.libelle, Qualifications.libelle FROM Service
INNER JOIN Salarie ON Service.num = Salarie.numService
INNER JOIN Competences ON Salarie.num = Competences.numSalarie
INNER JOIN Qualifications ON Competences.numQualif = Qualifications.num
WHERE Qualifications.libelle = 'Bdd'
- Calculer le nombre de qualifications chez Claas Tractor ? (avec un Alias)
SELECT COUNT(num) AS nbreQualifs FROM Qualifications
- Calculer le nombre de competences validées chez Class Tractor (23)
SELECT COUNT(numSalarie) AS NbreCompValidees FROM Qualifications
- Calculer le nombre de compétences informatiques (Bdd, Css, Html) validées à ce jour
SELECT COUNT(numSalarie) AS NbreCompValidees FROM Competences
INNER JOIN Qualifications ON Competences.numQualif = Qualifications.num
WHERE libelle IN ('Bdd', 'Css', 'Html')
- Lister les salariés qui ont un poste de developpeur ET comptable pour constituer un groupe de travail
SELECT prenom, nom, poste FROM Salarie
WHERE poste = 'developpeur' OR poste = 'comptable'
- Compter les salariés (par service) ayant validé plus de 2 qualifications (sauf ceux du service GRH)
SELECT libelle, COUNT(numSalarie) AS NbreCompetences FROM Salarie
INNER JOIN Competences ON Salarie.num = Competences.numSalarie
INNER JOIN Service ON Service.num = Salarie.numService
Where libelle <> 'Production'
GROUP BY libelle
HAVING NbreCompetences >2
- Modifier le poste de M. Peuplus, embauché comme ajusteur mais la secrétaire a oublié de l’indiquer.
UPDATE Salarie SET poste ='ajusteur'
WHERE nom = 'Peuplus'
- Compter les salaries par poste
SELECT poste, COUNT(*) FROM Salarie
GROUP BY poste
- Compter les salariés par poste et par salaire
SELECT poste, salaire, COUNT(*) FROM Salarie
GROUP BY poste, salaire
- Compter les salaries qui sont au moins 5 dans un poste
SELECT poste, COUNT(poste) AS NbreParPoste FROM Salarie
GROUP BY poste
HAVING NbreParPoste >5
- Lister les salariés embauchés en 2018 dont le nom ne commence par ‘P’ (ordre alphabétique)
SELECT * FROM Salarie
WHERE YEAR(dateEmbauche) = 2018
AND nom NOT LIKE 'P%'
ORDER BY nom
- Supprimer la colonne prénom de la table Salarié
ALTER TABLE Salarie
DROP prenom