-- (a) SELECT * FROM PERSONNE WHERE age > 15

CREATE INDEX index1 ON PERSONNE(age);

SELECT 
    nomPersonne, age
FROM
    PERSONNE
WHERE
    age > 15;

-- (b) SELECT * FROM PERSONNE WHERE age > 15 and nomPersonne like ‘‘F%’’

CREATE UNIQUE INDEX index2 ON PERSONNE(nomPersonne);

-- OR

CREATE INDEX index4 ON PERSONNE(nomPersonne, age);

-- (c) La liste des noms des amis d’Elvia

SELECT 
    *
FROM
    PERSONNE
WHERE
    idPersonne in (SELECT 
            idPersonne2
        FROM
            AMI
        WHERE
            idPersonne1 = (SELECT 
                    idPersonne
                FROM
                    PERSONNE
                WHERE
                    nomPersonne = 'Elvia'));

-- Optimisation

SELECT 
    P2.nomPersonne
FROM
    PERSONNE P1,
    PERSONNE P2,
    AMI
WHERE
    P1.idPersonne = AMI.idPersonne1
        AND P2.idPersonne = AMI.idPersonne2
        AND P1.nomPersonne = 'Elvia';

-- (d) SELECT * FROM PERSONNE GROUP BY idPersonne HAVING age < 21;

SELECT 
    nomPersonne
FROM
    PERSONNE
WHERE
    age < 21;

-- (e) 

SELECT 
    nomPersonne
FROM
    PERSONNE
WHERE
    (idPersonne in (SELECT 
            idPersonne1
        FROM
            AMI)
        OR idPersonne in (SELECT 
            idPersonne2
        FROM
            AMI))
        AND (idPersonne in (SELECT 
            idPersonne1
        FROM
            FAMILLE)
        OR idPersonne in (SELECT 
            idPersonne2
        FROM
            FAMILLE));

-- Optimisation

SELECT 
    nomPersonne
FROM
    PERSONNE
WHERE
    EXISTS( SELECT 
            idPersonne1
        FROM
            AMI
        WHERE
            idPersonne1 = idPersonne
                OR idPersonne2 = idPersonne)
        AND EXISTS( SELECT 
            idPersonne1
        FROM
            FAMILLE
        WHERE
            idPersonne1 = idPersonne
                OR idPersonne2 = idPersonne)
;

-- (f)

SELECT 
    nomPersonne
FROM
    PERSONNE
WHERE
    idPersonne IN (SELECT 
            idPersonne2
        FROM
            AMI) 
UNION SELECT 
    nomPersonne
FROM
    PERSONNE
WHERE
    idPersonne IN (SELECT 
            idPersonne1
        FROM
            AMI);

-- Optimisation

SELECT 
    nomPersonne
FROM
    PERSONNE,
    AMI
WHERE
    PERSONNE.idPersonne = AMI.idPersonne1
        OR PERSONNE.idPersonne = AMI.idPersonne2
;

-- (g) 

SELECT 
    idPersonne
FROM
    PERSONNE
WHERE
    (idPersonne in (SELECT 
            idPersonne1
        FROM
            FAMILLE)
        OR idPersonne in (SELECT 
            idPersonne2
        FROM
            FAMILLE))
        AND (idPersonne not in (SELECT 
            idPersonne1
        FROM
            AMI))
        AND (idPersonne not in (SELECT 
            idPersonne2
        FROM
            AMI));


-- Optimisation en 2 sous-requêtes SQL utilisant des EXISTS au lieu des IN

SELECT 
    nomPersonne
FROM
    PERSONNE
WHERE
    EXISTS( SELECT 
            idPersonne1
        FROM
            FAMILLE
        WHERE
            idPersonne1 = idPersonne
                OR idPersonne2 = idPersonne)
        AND NOT EXISTS( SELECT 
            idPersonne1
        FROM
            AMI
        WHERE
            idPersonne1 = idPersonne
                OR idPersonne2 = idPersonne);

-- (h)


SELECT 
    *
FROM
    PERSONNE P
WHERE
    P.age > any (SELECT 
            age
        FROM
            PERSONNE
        WHERE
            idPersonne in (SELECT 
                    idPersonne2
                FROM
                    AMI
                WHERE
                    idPersonne1 = P.idPersonne)
                OR idPersonne in (SELECT 
                    idPersonne1
                FROM
                    AMI
                WHERE
                    idPersonne2 = P.idPersonne));


-- Optimisation en 1 sous-requête SQL utilisant EXISTS et une jointure

SELECT 
    P1.nomPersonne
FROM
    PERSONNE P1,
    PERSONNE P2
WHERE
    P1.age > P2.age
        AND EXISTS( SELECT 
            idPersonne1, idPersonne2
        FROM
            AMI
        WHERE
            (idPersonne1 = P1.idPersonne
                AND idPersonne2 = P2.idPersonne)
                OR (idPersonne1 = P2.idPersonne
                AND idPersonne2 = P1.idPersonne));