TP3
-- 1 afficher le nombre d'appartement dans chaque immeuble
SELECT idImmeuble,COUNT(idAppart) AS NbAppart FROM Appart GROUP BY idImmeuble;
-- 2 afficher la moyenne des surfaces des appartements de chaque immeuble
SELECT idImmeuble,AVG(surface) AS MoyenneSurface FROM Appart GROUP BY
idImmeuble;
-- 3 afficher la surface minimale des appartements de chaque immeuble
SELECT idImmeuble,MIN(surface) AS SurfaceMin FROM Appart GROUP BY idImmeuble;
-- 4 afficher la surface maximale des appartements de chaque immeuble
SELECT idImmeuble,MAX(surface) AS SurfaceMax FROM Appart GROUP BY idImmeuble;
-- 5 afficher la somme des surfaces des appartements de chaque immeuble
SELECT idImmeuble,SUM(surface) AS TotalSurface FROM Appart GROUP BY
idImmeuble;
-- 6 Afficher les immeubles dont les appartements ayant la surface maximale
supérieure à 200
SELECT idImmeuble, MAX(surface) FROM appart GROUP by idImmeuble HAVING
max(surface)>200;
-- 7 Afficher les immeubles dont les appartements ayant la surface minimale
supérieure à 70
SELECT idImmeuble,MIN(surface) FROM appart GROUP by idImmeuble HAVING
min(surface)>70;
-- 8 Afficher le nombre d’appartements pour chaque propriétaire.
SELECT idPersonne,count(idAppart) AS nbAppart FROM propriétaire GROUP by
idPersonne;
-- 9 Afficher le nombre de propriétaires pour chaque appartement.
SELECT idAppart,count(idPersonne) AS nbPropriétaire FROM propriétaire GROUP by
idAppart;
-- 10 Afficher les appartements ayant plus d’un propriétaire
SELECT idAppart,COUNT(idPersonne) FROM propriétaire GROUP BY idAppart HAVING
COUNT(idPersonne)>1;
-- 11 Afficher le nombre de personnes dans chaque profession
SELECT profession, count(idPersonne) FROM personne GROUP BY profession;
-- 12 Regrouper les propriétaires d’appartements en utilisant les quotes part
SELECT quotePart FROM propriétaire GROUP BY quotePart;
-- 13 Afficher le nombre de propriétaires pour chaque valeur de quote-part
SELECT quotePart,COUNT(idPersonne) AS NbPropriétaire FROM propriétaire GROUP
BY quotePart;
-- 14 Afficher, pour chaque valeur de quote-part, les quotes-parts dont le
nombre de propriétaires est supérieur ou égale à 5.
SELECT quotePart,COUNT(idPersonne) AS NbPropriétaire FROM propriétaire GROUP
BY quotePart HAVING COUNT(idPersonne)>=5;
TP4
-- 1. Afficher les noms et les prénoms des propriétaires
-- Avec INNER JOIN
SELECT nom,prénom FROM personne AS p INNER JOIN propriétaire AS pr ON
p.idPersonne=pr.idPersonne;
-- OU
SELECT nom,prénom FROM personne INNER JOIN propriétaire ON
personne.idPersonne=propriétaire.idPersonne;
-- Sans INNER JOIN
SELECT nom,prénom FROM personne AS p,propriétaire as pr WHERE
p.idPersonne=pr.idPersonne;
-- OU
SELECT nom,prénom FROM personne,propriétaire WHERE
personne.idPersonne=propriétaire.idPersonne;
-- 2. Afficher l’identifiant et le nom de l’immeuble qui contient les
appartements 100, 101 et 102, les identifiants des appartements et leurs
surface.
-- Avec INNER JOIN
SELECT im.idImmeuble,nom,idAppart,surface FROM immeuble AS im INNER JOIN
appart as Ap ON im.idImmeuble=ap.idImmeuble WHERE idAppart IN(100,101,102);
-- OU
SELECT immeuble.idImmeuble,nom,idAppart,surface FROM immeuble INNER JOIN
appart ON immeuble.idImmeuble=appart.idImmeuble WHERE idAppart
IN(100,101,102);
-- Sans INNER JOIN
SELECT im.idImmeuble,nom,idAppart,surface FROM immeuble AS im, appart as Ap
WHERE im.idImmeuble=ap.idImmeuble AND idAppart IN(100,101,102);
-- OU
SELECT immeuble.idImmeuble,nom,idAppart,surface FROM immeuble, appart WHERE
immeuble.idImmeuble=appart.idImmeuble AND idAppart IN(100,101,102);
-- 3. Afficher la surface et le niveau de l’appartement dans laquelle habite
la personne 3
-- Avec INNER JOIN
SELECT surface,niveau FROM appart AS ap INNER JOIN personne as p ON
ap.idAppart=p.idAppart WHERE idPersonne=3;
-- OU
SELECT surface,niveau FROM appart INNER JOIN personne ON
appart.idAppart=personne.idAppart WHERE idPersonne=3;
-- Sans INNER JOIN
SELECT surface,niveau FROM appart AS ap, personne as p WHERE
ap.idAppart=p.idAppart AND idPersonne=3;
-- OU
SELECT surface,niveau FROM appart, personne WHERE
appart.idAppart=personne.idAppart AND idPersonne=3;
-- 4. Afficher le nom de l’immeuble, le numéro de l’appartement, le nom et
le prénom des personnes qui habitent dans les appartement 103 et 104.
-- Avec INNER JOIN
SELECT i.nom,numéro,p.nom,prénom FROM immeuble as i
INNER JOIN appart AS a ON i.idImmeuble=a.idImmeuble
INNER JOIN personne AS p ON a.idAppart=p.idAppart
WHERE a.idAppart IN(103,104);
-- OU
SELECT immeuble.nom,numéro,p.nom,prénom FROM appart
INNER JOIN immeuble ON immeuble.idImmeuble=appart.idImmeuble
INNER JOIN personne ON appart.idAppart=personne.idAppart
WHERE appart.idAppart IN(103,104);
-- Sans INNER JOIN
SELECT i.nom,numéro,p.nom,prénom FROM immeuble as i,appart AS a,personne AS p
WHERE i.idImmeuble=a.idImmeuble AND a.idAppart=p.idAppart AND a.idAppart
IN(103,104);
-- ou
SELECT immeuble.nom,numéro,personne.nom,prénom FROM immeuble,appart,personne
WHERE immeuble.idImmeuble=appart.idImmeuble AND
appart.idAppart=personne.idAppart AND appart.idAppart IN(103,104);
-- 5. Afficher les noms et les prénoms et le quote-part des propriétaires
-- Avec INNER JOIN
SELECT nom,prénom,quotePart FROM personne as p INNER JOIN propriétaire as pr
ON p.idPersonne=pr.idPersonne;
-- OU
SELECT nom,prénom,quotePart FROM personne INNER JOIN propriétaire
ON personne.idPersonne=propriétaire.idPersonne;
-- Sans INNER JOIN
SELECT nom,prénom,quotePart FROM personne as p, propriétaire as pr
WHERE p.idPersonne=pr.idPersonne;
-- OU
SELECT nom,prénom,quotePart FROM personne,propriétaire
WHERE personne.idPersonne=propriétaire.idPersonne;
-- 6. Afficher les numéros, les surfaces et les niveaux des appartements,
ainsi que le nom et le prénom de ses propriétaires.
-- Avec INNER JOIN
SELECT numéro,surface,niveau,nom,prénom FROM propriétaire AS pr
INNER JOIN appart as a ON a.idAppart=pr.idAppart
INNER JOIN personne as p ON p.idPersonne=pr.idPersonne;
-- ou
SELECT numéro,surface,niveau,nom,prénom FROM propriétaire
INNER JOIN appart ON appart.idAppart=propriétaire.idAppart
INNER JOIN personne ON personne.idPersonne=propriétaire.idPersonne;
-- Sans INNER JOIN
SELECT numéro,surface,niveau,nom,prénom FROM appart AS a,personne as p,
propriétaire as pr WHERE a.idAppart=pr.idAppart AND
p.idPersonne=pr.idPersonne;
-- ou
SELECT numéro,surface,niveau,nom,prénom FROM appart,personne, propriétaire
WHERE appart.idAppart=propriétaire.idAppart AND
personne.idPersonne=propriétaire.idPersonne;
-- 7. Afficher la profession, et le quote-part du propriétaire 5
-- Avec INNER JOIN
SELECT profession,quotePart FROM personne as p INNER JOIN propriétaire AS pr
ON p.idPersonne=pr.idPersonne WHERE pr.idPersonne=5;
-- OU
SELECT profession,quotePart FROM personne INNER JOIN propriétaire ON
personne.idPersonne=propriétaire.idPersonne WHERE propriétaire.idPersonne=5;
-- Sans INNER JOIN
SELECT profession,quotePart FROM personne as p, propriétaire AS pr WHERE
p.idPersonne=pr.idPersonne AND pr.idPersonne=5;
-- OU
SELECT profession,quotePart FROM personne, propriétaire WHERE
personne.idPersonne=propriétaire.idPersonne AND propriétaire.idPersonne=5;
-- 8. Afficher la liste des personnes qui habite dans une appartement ayant
une surface entre 100 et 250.
-- Avec INNER JOIN
SELECT idPersonne,prénom,nom,profession,p.idAppart FROM personne as p INNER
JOIN appart AS a ON p.idAppart=a.idAppart WHERE surface BETWEEN 100 AND 250;
-- OU
SELECT idPersonne,prénom,nom,profession,personne.idAppart FROM personne INNER
JOIN appart ON personne.idAppart=appart.idAppart WHERE surface BETWEEN 100 AND
250;
-- Sans INNER JOIN
SELECT idPersonne,prénom,nom,profession,p.idAppart FROM personne AS p,appart
AS a WHERE p.idAppart=a.idAppart AND surface BETWEEN 100 AND 250;
-- OU
SELECT idPersonne,prénom,nom,profession,personne.idAppart FROM personne,appart
WHERE personne.idAppart=appart.idAppart AND surface BETWEEN 100 AND 250;
-- 9. Afficher le propriétaire de l’appartement 100 dont la profession
commence par ‘R’ et se termine par ‘ier’
-- Avec INNER JOIN
SELECT pr.idPersonne,pr.idAppart,quotePart FROM personne as p INNER JOIN
propriétaire as pr ON p.idPersonne=pr.idPersonne WHERE profession LIKE 'R%ier';
-- OU
SELECT propriétaire.idPersonne,propriétaire.idAppart,quotePart FROM personne
INNER JOIN propriétaire ON personne.idPersonne=propriétaire.idPersonne WHERE
profession LIKE 'R%ier';
-- Sans INNER JOIN
SELECT pr.idPersonne,pr.idAppart,quotePart FROM personne as p, propriétaire as
pr WHERE p.idPersonne=pr.idPersonne AND p.profession LIKE 'R%ier';
-- OU
SELECT propriétaire.idPersonne,propriétaire.idAppart,quotePart FROM personne,
propriétaire WHERE personne.idPersonne=propriétaire.idPersonne AND profession
LIKE 'R%ier';
-- 10. Afficher l’identifiant et le nom de l’immeuble, le numéro, la surface
et le niveau des appartement qui appartiennent à l’immeuble dont l’adresse
contient ‘Mart’
-- Avec INNER JOIN
SELECT immeuble.idImmeuble,nom,numéro,surface,niveau FROM immeuble INNER JOIN
appart ON immeuble.idImmeuble=appart.idImmeuble WHERE adresse LIKE '%Mart%';
-- Sans INNER JOIN
SELECT immeuble.idImmeuble,nom,numéro,surface,niveau FROM immeuble,appart
WHERE immeuble.idImmeuble=a.idImmeuble AND adresse LIKE '%Mart%';
-- 11. Afficher le nombre d’appartement dans l’immeuble dont le nom se
termine par ‘ou’
-- Avec INNER JOIN
SELECT COUNT(idAppart) AS NbAppart FROM immeuble INNER JOIN appart ON
immeuble.idImmeuble=appart.idImmeuble WHERE nom LIKE '%ou';
-- Sans INNER JOIN
SELECT COUNT(idAppart) AS NbAppart FROM immeuble, appart WHERE
immeuble.idImmeuble=appart.idImmeuble AND nom LIKE '%ou';
-- 12. Afficher les noms et prénoms des propriétaires d’appartements et les
numéros des appartement dont le niveaux est 5, 14 ou 15.
-- Avec INNER JOIN
SELECT nom,prénom,numéro FROM propriétaire
INNER JOIN personne ON personne.idPersonne=propriétaire.idPersonne
INNER JOIN appart ON appart.idAppart=propriétaire.idAppart
WHERE niveau IN(5,14,15);
-- Sans INNER JOIN
SELECT nom,prénom,numéro FROM propriétaire, personne,appart WHERE
personne.idPersonne=propriétaire.idPersonne AND
appart.idAppart=propriétaire.idAppart AND niveau IN(5,14,15);
TP5
-- 1. Afficher les noms et les prénoms et les quotes-parts des propriétaires
en utilisant une jointure à gauche LEFT JOIN
SELECT nom,prénom,quotePart FROM personne LEFT JOIN propriétaire ON
personne.idPersonne=propriétaire.idPersonne;
-- 2. Afficher les noms des immeubles et les numéros des appartements qui
existent en utilisant une jointure à droite RIGHT JOIN
SELECT nom,numéro FROM immeuble RIGHT JOIN appart ON
immeuble.idImmeuble=appart.idImmeuble;
-- 3. Afficher les immeubles et les appartements en utilisant une jointure
complète FULL JOIN
SELECT * FROM immeuble FULL JOIN appart ON
immeuble.idImmeuble=appart.idImmeuble;
-- 4. Mettre à jour la surface de l’appartement 104 en 100 (UPDATE)
UPDATE appart SET surface=100 WHERE idAppart=104;
-- 5. Supprimer la personne 3
DELETE FROM personne WHERE idPersonne=3;
-- 6. Supprimer l’appartement 101
DELETE FROM appart WHERE idAppart=101;
-- 7. Ajouter le champ num_tel de type INT à la table personne
UPDATE TABLE personne ADD num_tel int;
-- 8. Changer le nom du champ niveau de la table appart en étage
UPDATE TABLE appart CHANGE niveau étage int;
-- 9. Changer le type du champ num_tel en VARCHAR(10)
UPDATE TABLE personne MODIFY num_tel VARCHAR(10);
-- 10. Supprimer le champ num_tel de la table personne
UPDATE TABLE personne DROP num_tel;
