Requetes.sql

-- 1. Calculer le nombre moyen de vulnérabilités connues par produit
SELECT AVG(Vuln_count)
FROM PRODUIT;


-- 2. Afficher l'identifiant et la description de toutes les vulnérabilités dont le score CVSS est supérieur ou égal à 9.0 (critique).
SELECT CVE_Id, Description
FROM VULNERABILITE
WHERE Score_CVSS >= 9.0;


-- 3. Calculer le score CVSS moyen pour les vulnérabilités publiées après le 1er janvier 2024.
SELECT AVG(Score_CVSS)
FROM VULNERABILITE
WHERE Date_pub > TO_DATE('2024-01-01', 'YYYY-MM-DD');


-- 4. Quelle est la vulnérabilité la plus récemment mise à jour (avec son score CVSS) en précisant la version de produit qu'elle affecte.
SELECT V.CVE_Id, V.Description, V.Score_CVSS, VP.Version
FROM VULNERABILITE V
JOIN AFFECTE A ON V.CVE_Id = A.CVE_Id
JOIN VERSION_P VP ON A.Id_version = VP.IdVP
WHERE V.Date_MAJ = (SELECT MAX(Date_MAJ) FROM VULNERABILITE);


-- 5. Lister les identifiants et les dates de publication des vulnérabilités ayant à la fois un score CVSS supérieur à 8,0 et au moins un exploit enregistré par un auteur dont le nombre d'exploits est supérieur à 10.
SELECT DISTINCT V.CVE_Id, V.Date_pub
FROM VULNERABILITE V
JOIN EXPLOIT E ON V.CVE_Id = E.CVE_Id
JOIN AUTEUR AU ON E.Id_auteur = AU.Id_auteur
WHERE V.Score_CVSS > 8.0 AND AU.Nb_exploits > 10;


-- 6. Compter le nombre total de versions pour chaque produit
SELECT P.Nom, COUNT(VP.IdVP) AS Nombre_Versions
FROM PRODUIT P
JOIN VERSION_P VP ON P.Id_produit = VP.Id_produit
GROUP BY P.Nom;


-- 7. Afficher les produits avec une vulnérabilité critique et un fournisseur de grande taille (> 500 employés)
SELECT DISTINCT P.Nom AS Nom_Produit
FROM PRODUIT P
JOIN VERSION_P VP ON P.Id_produit = VP.Id_produit
JOIN AFFECTE A ON VP.IdVP = A.Id_version
JOIN VULNERABILITE V ON A.CVE_Id = V.CVE_Id
JOIN FOURNISSEUR F ON P.Id_fournisseur = F.Id_fournisseur
WHERE V.Score_CVSS >= 9.0 AND F.Nb_employer > 500;


-- 8. Afficher les versions affectées par plus de 5 vulnérabilités
SELECT VP.Version, COUNT(A.CVE_Id) AS Nombre_Vulns
FROM VERSION_P VP
JOIN AFFECTE A ON VP.IdVP = A.Id_version
GROUP BY VP.Version, VP.IdVP
HAVING COUNT(A.CVE_Id) > 5;


-- 9. Lister les versions qui n'ont jamais été la cible d'un exploit sur la plateforme 'Windows'
SELECT VP.Version FROM VERSION_P VP
WHERE VP.IdVP NOT IN (
    SELECT DISTINCT FS.IdVP FROM FONCTIONNE_SUR FS
    JOIN PLATEFORME PL ON FS.Id_plateforme = PL.Id_plateforme
    JOIN AFFECTE A ON FS.IdVP = A.Id_version
    JOIN EXPLOIT E ON A.CVE_Id = E.CVE_Id
    WHERE PL.Nom_OS LIKE '%Windows%'
);

-- 10. Afficher les fournisseurs dont les produits ne sont affectés que par des vulnérabilités 'Faible'
SELECT DISTINCT F.Nom
FROM FOURNISSEUR F
JOIN PRODUIT P ON F.Id_fournisseur = P.Id_fournisseur
JOIN VERSION_P VP ON P.Id_produit = VP.Id_produit
JOIN AFFECTE A ON VP.IdVP = A.Id_version
JOIN VULNERABILITE V ON A.CVE_Id = V.CVE_Id
WHERE F.Nom NOT IN (
    SELECT F2.Nom
    FROM FOURNISSEUR F2
    JOIN PRODUIT P2 ON F2.Id_fournisseur = P2.Id_fournisseur
    JOIN VERSION_P VP2 ON P2.Id_produit = VP2.Id_produit
    JOIN AFFECTE A2 ON VP2.IdVP = A2.Id_version
    JOIN VULNERABILITE V2 ON A2.CVE_Id = V2.CVE_Id
    WHERE V2.Gravite != 'FAIBLE'
);




-- 11. Afficher la référence d'exploit pour un produit avec un fournisseur de grande taille (> 500 employés)
SELECT DISTINCT R.IdRef, R.Url FROM REFERENCE R JOIN EXPLOIT_DOC ED ON R.IdRef = ED.IdRef JOIN EXPLOIT E ON ED.Id_exploit = E.Id_exploit JOIN VULNERABILITE V ON E.CVE_Id = V.CVE_Id JOIN AFFECTE A ON V.CVE_Id = A.CVE_Id JOIN VERSION_P VP ON A.Id_version = VP.IdVP JOIN PRODUIT P ON VP.Id_produit = P.Id_produit JOIN FOURNISSEUR F ON P.Id_fournisseur = F.Id_fournisseur WHERE F.Nb_employer > 500;

-- 12. Quels sont les exploits associés à une vulnérabilité critique (CVSS > 9.0)
SELECT E.Id_exploit, E.Description 
FROM EXPLOIT E
JOIN VULNERABILITE V ON E.CVE_Id = V.CVE_Id
WHERE V.Score_CVSS = 10.0;

-- 13. Afficher les exploits dont toutes les références associées proviennent de 'GitHub'
SELECT Id_exploit, Description FROM EXPLOIT
WHERE Id_exploit NOT IN (
    SELECT ED.Id_exploit FROM EXPLOIT_DOC ED
    JOIN REFERENCE R ON ED.IdRef = R.IdRef
    WHERE R.Source != 'GitHub'
);

-- 14. Lister le nom de l'auteur et le nombre de plateformes distinctes pour ses exploits
SELECT AU.Nom, COUNT(DISTINCT PL.Nom_OS) AS Nb_Plateformes
FROM AUTEUR AU
JOIN EXPLOIT E ON AU.Id_auteur = E.Id_auteur
JOIN VULNERABILITE V ON E.CVE_Id = V.CVE_Id
JOIN AFFECTE A ON V.CVE_Id = A.CVE_Id
JOIN FONCTIONNE_SUR FS ON A.Id_version = FS.IdVP
JOIN PLATEFORME PL ON FS.Id_plateforme = PL.Id_plateforme
GROUP BY AU.Nom;

-- 15. Quels auteurs n'ont publié que des exploits visant des vulnérabilités de gravité 'Élevée'
SELECT DISTINCT AU.Nom
FROM AUTEUR AU
JOIN EXPLOIT E ON AU.Id_auteur = E.Id_auteur
WHERE AU.Nom NOT IN (
    SELECT AU2.Nom
    FROM AUTEUR AU2
    JOIN EXPLOIT E2 ON AU2.Id_auteur = E2.Id_auteur
    JOIN VULNERABILITE V2 ON E2.CVE_Id = V2.CVE_Id
    WHERE V2.Gravite != 'ELEVEE'
);

-- 16. Quels sont les auteurs qui ont publié plus de 10 exploits pour le produit 'Microsoft'
SELECT AU.Nom FROM AUTEUR AU
JOIN EXPLOIT E ON AU.Id_auteur = E.Id_auteur
JOIN VULNERABILITE V ON E.CVE_Id = V.CVE_Id
JOIN AFFECTE A ON V.CVE_Id = A.CVE_Id
JOIN VERSION_P VP ON A.Id_version = VP.IdVP
JOIN PRODUIT P ON VP.Id_produit = P.Id_produit
JOIN FOURNISSEUR F ON P.Id_fournisseur = F.Id_fournisseur
WHERE F.Nom = 'Microsoft Corporation'
GROUP BY AU.Nom
HAVING COUNT(E.Id_exploit) > 10;

-- 17. Quelle est la plateforme la plus ciblée par les vulnérabilités les plus critiques (CVSS > 9.0)
SELECT PL.Nom_OS
FROM PLATEFORME PL
JOIN FONCTIONNE_SUR FS ON PL.Id_plateforme = FS.Id_plateforme
JOIN AFFECTE A ON FS.IdVP = A.Id_version
JOIN VULNERABILITE V ON A.CVE_Id = V.CVE_Id
WHERE V.Score_CVSS > 9.0
GROUP BY PL.Nom_OS
HAVING COUNT(V.CVE_Id) = (
    SELECT MAX(COUNT(V2.CVE_Id))
    FROM PLATEFORME PL2
    JOIN FONCTIONNE_SUR FS2 ON PL2.Id_plateforme = FS2.Id_plateforme
    JOIN AFFECTE A2 ON FS2.IdVP = A2.Id_version
    JOIN VULNERABILITE V2 ON A2.CVE_Id = V2.CVE_Id
    WHERE V2.Score_CVSS > 9.0
    GROUP BY PL2.Nom_OS
);

-- 18. Quelles plateformes ne supportent aucun produit dont le fournisseur est 'Oracle' ?
SELECT Nom_OS FROM PLATEFORME
WHERE Id_plateforme NOT IN (
    SELECT DISTINCT FS.Id_plateforme FROM FONCTIONNE_SUR FS
    JOIN VERSION_P VP ON FS.IdVP = VP.IdVP
    JOIN PRODUIT P ON VP.Id_produit = P.Id_produit
    JOIN FOURNISSEUR F ON P.Id_fournisseur = F.Id_fournisseur
    WHERE F.Nom LIKE '%Oracle%'
);

-- 19. Afficher le nom des plateformes qui supportent au moins deux versions du même fournisseur
SELECT PL.Nom_OS FROM PLATEFORME PL
JOIN FONCTIONNE_SUR FS ON PL.Id_plateforme = FS.Id_plateforme
JOIN VERSION_P VP ON FS.IdVP = VP.IdVP
JOIN PRODUIT P ON VP.Id_produit = P.Id_produit
GROUP BY PL.Nom_OS, P.Id_fournisseur
HAVING COUNT(VP.IdVP) >= 2;

-- 20. Lister les plateformes qui supportent toutes les versions du produit 'Apache HTTP Server'
SELECT PL.Nom_OS
FROM PLATEFORME PL
JOIN FONCTIONNE_SUR FS ON PL.Id_plateforme = FS.Id_plateforme
JOIN VERSION_P VP ON FS.IdVP = VP.IdVP
JOIN PRODUIT P ON VP.Id_produit = P.Id_produit
WHERE P.Nom = 'HTTP Server'
GROUP BY PL.Nom_OS
HAVING COUNT(DISTINCT VP.IdVP) = (
    SELECT COUNT(VP2.IdVP)
    FROM VERSION_P VP2
    JOIN PRODUIT P2 ON VP2.Id_produit = P2.Id_produit
    WHERE P2.Nom = 'HTTP Server'
);