TP3

1:

SELECT DateNaissance, Adresse
FROM employe
WHERE Nom='Rochat' AND Prenom='Juliette'

2:

SELECT Nom, Adresse
FROM Employe NATURAL JOIN Departement
WHERE NomD = 'Recherche'

3:

SELECT E1.Nom, E1.Prenom
FROM Employe E1, Employe E2
WHERE E1.superieur = E2.noavs AND E2.nom = 'Rochat' AND E2.prenom = 'Juliette'

4:

SELECT Nomp FROM Projet P NATURAL JOIN Travaille T JOIN Employe E USING (noavs)
WHERE (E.nom = 'Muller' AND E.prenom = 'Jean')
OR (E.nom = 'Grandjean' AND E.prenom = 'Annie')

5:

SELECT p.nomp FROM Projet p, Travaille t, Employe e, Travaille t2, Employe e2
WHERE  e.nom = 'Grandjean' AND e.prenom = 'Annie'
AND p.nopro = t.nopro AND t.noavs = e.noavs
AND e2.nom = 'Muller' AND e2.prenom = 'Jean'
AND p.nopro = t2.nopro AND t2.noavs = e2.noavs

6:

SELECT Nom, Prenom FROM Employe e LEFT OUTER JOIN Travaille t ON e.noavs = t.noavs
WHERE t.nopro IS NULL

7:

SELECT  NomP FROM Projet p JOIN Travaille t ON p.nopro = t.nopro
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)
3/bases_de_donnees/tp3.txt · Dernière modification: d-m-Y H:i de 83.76.103.242
chimeric.de = chi`s home Creative Commons License Valid CSS Driven by DokuWiki do yourself a favour and use a real browser - get firefox!! Recent changes RSS feed Valid XHTML 1.0 Fristcher.ch