BDD_ADRESSE (Id, Num, Complement, Rue, CodeP, Ville, Region, Departement, Tournee)
BDD_RESIDE (IdClient, IdAdresse)
BDD_CLIENT (Id, Nom, Prenom, DateN, Civilite)
BDD_BDD_POSTIER (Id, Nom, PosteReferente)
BDD_PLATEFORME (Id, Nom, TypeP, Adresse)
BDD_TRANSPORTEUR (Id, Nom, TypeTransport, Condition, Vehicule)
BDD_TRAJET (NumTrajet, Transporteur, PlateformeDep, PlateformeArr)
BDD_HISTORIQUE (NumTrajet, NumCourrier)
BDD_FILIALE (SIRET, Nom, Siege)
BDD_FORMULE (Id, Nom, Conditions, Taux, Filiale)
BDD_COURRIER (Numero, Description, Affranchissement, Poids, PlateformeCourante, AdresseDep, AdresseDest, Etat, DateEnvoi, Formule, Tournee)
BDD_TOURNEE (Id, Postier)


--1 Afficher la liste des adresses d'une personne
SELECT IdClient, Num, Rue, Ville, CodeP
FROM BDD_ADRESSE JOIN BDD_RESIDE ON BDD_ADRESSE.Id = BDD_RESIDE.IdAdresse
ORDER BY IdClient ASC;

--2 Afficher nom, prenom des habitants habitant à Dijon
SELECT Nom, Prenom
FROM BDD_CLIENT JOIN BDD_RESIDE ON BDD_CLIENT.Id = BDD_RESIDE.IdClient
JOIN BDD_ADRESSE ON BDD_RESIDE.IdAdresse = BDD_ADRESSE.Id
WHERE Ville = 'Dijon';

--3 Age moyen des clients
SELECT AVG(age) FROM (SELECT (MONTHS_BETWEEN(SYSDATE,DateN)/12) age FROM BDD_CLIENT);

--4 Adresse qui n'a qu'un seul client
SELECT IdAdresse
FROM (SELECT IdAdresse, COUNT(*) nb_residents FROM BDD_RESIDE GROUP BY IdAdresse)
WHERE nb_residents = 1;

--5 Les courriers transportés à la fois par le transporteur 88 et par le transporteur 89
SELECT NumCourrier
FROM BDD_HISTORIQUE JOIN BDD_TRAJET ON BDD_HISTORIQUE.NumTrajet = BDD_TRAJET.NumTrajet
WHERE BDD_TRAJET.Transporteur = 88
INTERSECT
SELECT NumCourrier
FROM BDD_HISTORIQUE JOIN BDD_TRAJET ON BDD_HISTORIQUE.NumTrajet = BDD_TRAJET.NumTrajet
WHERE BDD_TRAJET.Transporteur = 89;


--6 Nombre d'étapes pour chaque courrier ?
SELECT NumCourrier, COUNT(*) nombre_etapes
FROM BDD_TRAJET JOIN BDD_HISTORIQUE ON BDD_TRAJET.NumTrajet = BDD_HISTORIQUE.NumTrajet
GROUP BY NumCourrier;

--7 Le/les courrier(s) qui a/ont le moins d'étape
SELECT NumCourrier, nombre_etapes
FROM ( SELECT NumCourrier, COUNT(*) nombre_etapes
FROM BDD_TRAJET JOIN BDD_HISTORIQUE ON BDD_TRAJET.NumTrajet = BDD_HISTORIQUE.NumTrajet
GROUP BY NumCourrier) tab
WHERE nombre_etapes = (SELECT MIN(nombre_etapes) FROM tab);

--8 Les transporteurs qui ne livre qu'à dijon
SELECT BDD_TRANSPORTEUR.Nom
FROM BDD_TRANSPORTEUR JOIN BDD_TRAJET ON BDD_TRANSPORTEUR.Id = BDD_TRAJET.Transporteur
MINUS
SELECT BDD_TRANSPORTEUR.Nom
FROM BDD_TRANSPORTEUR JOIN BDD_TRAJET ON BDD_TRANSPORTEUR.Id = BDD_TRAJET.Transporteur
JOIN BDD_PLATEFORME ON BDD_TRAJET.PlateformeArr = BDD_PLATEFORME.Id
JOIN BDD_ADRESSE ON BDD_PLATEFORME.Adresse = BDD_ADRESSE.Id
WHERE Ville <> 'Dijon';

--9 La même avec ALL
SELECT BDD_TRANSPORTEUR.Nom
FROM BDD_TRANSPORTEUR JOIN BDD_TRAJET ON BDD_TRANSPORTEUR.Id = BDD_TRAJET.Transporteur
WHERE 'Dijon' = ALL ( SELECT Ville
FROM BDD_TRANSPORTEUR JOIN BDD_TRAJET ON BDD_TRANSPORTEUR.Id = BDD_TRAJET.Transporteur
JOIN BDD_PLATEFORME ON BDD_TRAJET.PlateformeArr = BDD_PLATEFORME.Id
JOIN BDD_ADRESSE ON BDD_PLATEFORME.Adresse = BDD_ADRESSE.Id
)
;

--10 Les transporteurs qui livrent à toutes les plateformes de bourgogne
SELECT Transporteur
FROM BDD_TRAJET t1
WHERE NOT EXISTS (
SELECT p.Id
FROM BDD_PLATEFORME p JOIN BDD_ADRESSE ON p.Adresse = BDD_ADRESSE.Id
WHERE Ville = 'Dijon'
AND NOT EXISTS (
SELECT *
FROM BDD_TRAJET t2
WHERE t1.NumTrajet = t2.NumTrajet
AND t2.PlateformeArr = p.Id
)
);