Ci-dessous, les différences entre deux révisions de la page.
Prochaine révision | Révision précédente | ||
3:bases_de_donnees:tp3 [d-m-Y H:i] 130.223.226.193 créée |
3:bases_de_donnees:tp3 [d-m-Y H:i] (Version actuelle) 83.76.103.242 |
||
---|---|---|---|
Ligne 2: | Ligne 2: | ||
1: | 1: | ||
<code sql> | <code sql> | ||
- | SELECT DateNaissance, | + | SELECT DateNaissance, |
+ | FROM employe | ||
+ | WHERE Nom=' | ||
</ | </ | ||
2: | 2: | ||
<code sql> | <code sql> | ||
- | SELECT Nom, Adresse FROM Employe NATURAL JOIN Departement WHERE NomD = ' | + | SELECT Nom, Adresse |
+ | FROM Employe NATURAL JOIN Departement | ||
+ | WHERE NomD = ' | ||
</ | </ | ||
3: | 3: | ||
<code sql> | <code sql> | ||
- | SELECT E1.Nom, E1.Prenom FROM Employe E1, Employe E2 WHERE E1.superieur = E2.noavs AND E2.nom = ' | + | SELECT E1.Nom, E1.Prenom |
+ | FROM Employe E1, Employe E2 | ||
+ | WHERE E1.superieur = E2.noavs AND E2.nom = ' | ||
</ | </ | ||
Ligne 18: | Ligne 24: | ||
4: | 4: | ||
<code sql> | <code sql> | ||
+ | Select Nomp FROM Projet P NATURAL JOIN Travaille T JOIN Employe E USING (noavs) | ||
+ | WHERE (E.nom = ' | ||
+ | OR (E.nom = ' | ||
</ | </ | ||
Ligne 24: | Ligne 32: | ||
5: | 5: | ||
<code sql> | <code sql> | ||
+ | SELECT p.nomp FROM Projet p, Travaille t, Employe e, Travaille t2, Employe e2 | ||
+ | WHERE e.nom = ' | ||
+ | AND p.nopro = t.nopro AND t.noavs = e.noavs | ||
+ | AND e2.nom = ' | ||
+ | AND p.nopro = t2.nopro AND t2.noavs = e2.noavs | ||
</ | </ | ||
Ligne 30: | Ligne 42: | ||
6: | 6: | ||
<code sql> | <code sql> | ||
+ | SELECT Nom, Prenom FROM Employe e LEFT OUTER JOIN Travaille t ON e.noavs = t.noavs | ||
+ | WHERE t.nopro IS NULL | ||
</ | </ | ||
Ligne 36: | Ligne 49: | ||
7: | 7: | ||
<code sql> | <code sql> | ||
+ | SELECT | ||
+ | JOIN Employe e ON t.noavs = e.noavs JOIN Departement d ON e.nodep = d.nodep | ||
+ | GROUP BY NomP HAVING COUNT(DISTINCT nodep)=(SELECT COUNT(*)FROM Departement) | ||
</ | </ | ||