06_requetes.sql

-- 06_requetes.sql
-- Requetes reparties du projet.
-- Les commentaires indiquent l'objectif, le site d'execution conseille
-- et le caractere local ou distribue de la requete.

SET search_path TO pharma_repartie, public;

-- Requete 1
-- Objectif : afficher les patients appartenant au site de Casablanca.
-- Site conseille : S1 (Casablanca)
-- Type : locale
SELECT
    id_patient,
    nom,
    prenom,
    date_naissance,
    sexe,
    adresse,
    telephone
FROM patient_casa
ORDER BY id_patient;

-- Requete 2
-- Objectif : afficher les medicaments dont le prix unitaire est maximal.
-- Site conseille : S1 ou S2
-- Type : locale (catalogue replique)
SELECT
    id_medicament,
    nom_medicament,
    prix_unitaire,
    fabricant,
    ville_production
FROM medicament_global
WHERE prix_unitaire = (
    SELECT MAX(prix_unitaire)
    FROM medicament_global
)
ORDER BY id_medicament;

-- Requete 3
-- Objectif : afficher les consultations d'un patient donne avec le nom du medecin.
-- Exemple retenu : patient 1003
-- Site conseille : S1
-- Type : repartie
SELECT
    c.id_consultation,
    c.date_consultation,
    c.diagnostic,
    m.nom_medecin,
    m.specialite,
    m.ville AS ville_consultation
FROM consultation_global AS c
JOIN medecin_global AS m
    ON m.id_medecin = c.id_medecin
WHERE c.id_patient = 1003
ORDER BY c.date_consultation;

-- Requete 4
-- Objectif : afficher tous les medicaments prescrits lors d'une consultation donnee.
-- Exemple retenu : consultation 5004
-- Site conseille : S2
-- Type : repartie
SELECT
    p.id_consultation,
    lp.id_medicament,
    m.nom_medicament,
    lp.quantite,
    lp.posologie
FROM prescription_global AS p
JOIN ligne_prescription_global AS lp
    ON lp.id_prescription = p.id_prescription
JOIN medicament_global AS m
    ON m.id_medicament = lp.id_medicament
WHERE p.id_consultation = 5004
ORDER BY lp.id_medicament;

-- Requete 5
-- Objectif : afficher le chiffre d'affaires total par ville.
-- Site conseille : S1 ou S2
-- Type : repartie
SELECT
    ville,
    SUM(montant_total) AS chiffre_affaires_total
FROM vente_global
GROUP BY ville
ORDER BY ville;

-- Requete 6
-- Objectif : afficher le medicament le plus vendu sur l'ensemble du reseau.
-- Site conseille : S1 ou S2
-- Type : repartie
WITH ventes_agregees AS (
    SELECT
        lv.id_medicament,
        SUM(lv.quantite) AS quantite_totale
    FROM ligne_vente_global AS lv
    GROUP BY lv.id_medicament
),
classement AS (
    SELECT
        va.id_medicament,
        va.quantite_totale,
        RANK() OVER (ORDER BY va.quantite_totale DESC) AS rang_vente
    FROM ventes_agregees AS va
)
SELECT
    c.id_medicament,
    m.nom_medicament,
    c.quantite_totale
FROM classement AS c
JOIN medicament_global AS m
    ON m.id_medicament = c.id_medicament
WHERE c.rang_vente = 1;

-- Requete 7
-- Objectif : afficher les medicaments dont le stock est inferieur au seuil d'alerte.
-- Site conseille : S1 ou S2
-- Type : repartie
SELECT
    s.ville,
    s.id_medicament,
    m.nom_medicament,
    s.quantite_disponible,
    s.seuil_alerte
FROM stock_global AS s
JOIN medicament_global AS m
    ON m.id_medicament = s.id_medicament
WHERE s.quantite_disponible < s.seuil_alerte
ORDER BY s.ville, s.id_medicament;

-- Requete 8
-- Objectif : afficher, pour chaque medecin, le nombre total de consultations realisees.
-- Site conseille : S1 ou S2
-- Type : repartie
SELECT
    m.id_medecin,
    m.nom_medecin,
    m.specialite,
    m.ville,
    COUNT(c.id_consultation) AS nombre_consultations
FROM medecin_global AS m
LEFT JOIN consultation_global AS c
    ON c.id_medecin = m.id_medecin
GROUP BY
    m.id_medecin,
    m.nom_medecin,
    m.specialite,
    m.ville
ORDER BY m.ville, m.nom_medecin;

-- Requete 9
-- Objectif : afficher les patients ayant effectue une consultation dans une ville
--            et un achat dans l'autre.
-- Site conseille : S1 ou S2
-- Type : repartie
SELECT DISTINCT
    p.id_patient,
    p.nom,
    p.prenom,
    m.ville AS ville_consultation,
    v.ville AS ville_achat
FROM patient_global AS p
JOIN consultation_global AS c
    ON c.id_patient = p.id_patient
JOIN medecin_global AS m
    ON m.id_medecin = c.id_medecin
JOIN vente_global AS v
    ON v.id_patient = p.id_patient
WHERE m.ville <> v.ville
ORDER BY p.id_patient;

-- Requete 10
-- Objectif : afficher les patients ayant achete au moins un medicament
--            qui figurait dans leur prescription.
-- Site conseille : S1 ou S2
-- Type : repartie
SELECT DISTINCT
    p.id_patient,
    p.nom,
    p.prenom,
    m.nom_medicament
FROM patient_global AS p
JOIN consultation_global AS c
    ON c.id_patient = p.id_patient
JOIN prescription_global AS pr
    ON pr.id_consultation = c.id_consultation
JOIN ligne_prescription_global AS lp
    ON lp.id_prescription = pr.id_prescription
JOIN vente_global AS v
    ON v.id_patient = p.id_patient
JOIN ligne_vente_global AS lv
    ON lv.id_vente = v.id_vente
   AND lv.id_medicament = lp.id_medicament
JOIN medicament_global AS m
    ON m.id_medicament = lv.id_medicament
ORDER BY p.id_patient, m.nom_medicament;