DROP SCHEMA IF EXISTS AMIS; 
CREATE SCHEMA AMIS; 
USE AMIS;

CREATE TABLE PERSONNE (
    idPersonne INT primary key,
    nomPersonne TEXT,
    age INT
);
       
CREATE TABLE AMI (
    idPersonne1 INT,
    idPersonne2 INT,
    primary key (idPersonne1 , idPersonne2),
    foreign key (idPersonne1)
        references Personne (idPersonne),
    foreign key (idPersonne2)
        references Personne (idPersonne)
);

CREATE TABLE FAMILLE (
    idPersonne1 INT,
    idPersonne2 INT,
    primary key (idPersonne1 , idPersonne2),
    foreign key (idPersonne1)
        references Personne (idPersonne),
    foreign key (idPersonne2)
        references Personne (idPersonne)
);

insert into PERSONNE values (1, 'Elvia', 19);
insert into PERSONNE values (2, 'Farouk', 19);
insert into PERSONNE values (3, 'Sam', 19);
insert into PERSONNE values (4, 'Tiffany', 19);
insert into PERSONNE values (5, 'Nadia', 14);
insert into PERSONNE values (6, 'Chris', 12);
insert into PERSONNE values (7, 'Kris', 10);
insert into PERSONNE values (8, 'Bethany', 16);
insert into PERSONNE values (9, 'Louis', 17);
insert into PERSONNE values (10, 'Austin', 22);
insert into PERSONNE values (11, 'Gabriel', 21);
insert into PERSONNE values (12, 'Jessica', 20);
insert into PERSONNE values (13, 'John', 16);
insert into PERSONNE values (14, 'Alfred', 19);
insert into PERSONNE values (15, 'Samantha', 17);
insert into PERSONNE values (16, 'Craig', 17);

insert into AMI values (1, 3);
insert into AMI values (1, 2);
insert into AMI values (2, 4);
insert into AMI values (3, 9);
insert into AMI values (4, 9);
insert into AMI values (2, 6);
insert into AMI values (6, 7);
insert into AMI values (6, 10);
insert into AMI values (6, 13);
insert into AMI values (7, 5);
insert into AMI values (7, 8);
insert into AMI values (5, 8);
insert into AMI values (9, 11);
insert into AMI values (9, 12);
insert into AMI values (11, 12);
insert into AMI values (12, 15);
insert into AMI values (10, 15);
insert into AMI values (15, 13);
insert into AMI values (13, 16);
insert into AMI values (16, 14);

insert into FAMILLE values(2, 4);
insert into FAMILLE values(4, 2);
insert into FAMILLE values(6, 4);
insert into FAMILLE values(11, 9);
insert into FAMILLE values(9, 7);
insert into FAMILLE values(8, 7);
insert into FAMILLE values(10, 13);
insert into FAMILLE values(12, 15);
insert into FAMILLE values(15, 12);
insert into FAMILLE values(14, 5);

-- 1. La liste des noms des amis d’Elvia

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

-- 2. La liste des noms des personnes avec des amis plus jeunes qu’elles

SELECT 
    nomPersonne
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));

-- 3. La liste des noms des personnes avec des amis ayant le meme age. 
-- Le resultat doit etre ordonne par age, puis par nom pour chaque tranche d'age.

SELECT distinct
    P1.nomPersonne, P1.age
FROM
    PERSONNE P1,
    PERSONNE P2
WHERE
    P1.age = P2.age
        AND ((P1.idPersonne , P2.idPersonne) in (SELECT 
            idPersonne1, idPersonne2
        FROM
            AMI)
        OR (P2.idPersonne , P1.idPersonne) in (SELECT 
            idPersonne1, idPersonne2
        FROM
            AMI))
ORDER BY P1.age , P1.nomPersonne;

-- 4. La liste des noms des personnes qui ont au moins deux personnes dans leur famille

SELECT 
    idPersonne, nomPersonne
FROM
    PERSONNE
where
    (idPersonne in (SELECT 
            idPersonne1
        FROM
            FAMILLE
        GROUP BY idPersonne1
        HAVING count(idPersonne1) >= 2))
        OR (idPersonne in (SELECT 
            idPersonne2
        FROM
            FAMILLE
        GROUP BY idPersonne2
        HAVING count(idPersonne2) >= 2))
        OR (idPersonne in (SELECT 
            idPersonne1
        FROM
            FAMILLE)
        AND idPersonne in (SELECT 
            idPersonne2
        FROM
            FAMILLE)
        AND idPersonne NOT IN (SELECT 
            P1.idPersonne1
        FROM
            FAMILLE P1,
            FAMILLE P2
        WHERE
            (P1.idPersonne1 = P2.idPersonne2
                and P1.idPersonne2 = P2.idPersonne1)
                OR (P2.idPersonne1 = P1.idPersonne2
                and P2.idPersonne2 = P1.idPersonne1)));

-- 5. La liste des noms des personnes qui n’ont pas de famille.

SELECT 
    nomPersonne
FROM
    PERSONNE
where
    (idPersonne not in (SELECT 
            idPersonne1
        FROM
            FAMILLE))
        AND (idPersonne not in (SELECT 
            idPersonne2
        FROM
            FAMILLE));

-- 6. La liste des noms des amis en commun des personnes de la meme famille mais qui ne sont pas amies.

SELECT 
    P3.nomPersonne as 'Ami en Commun'
FROM
    PERSONNE P1,
    PERSONNE P2,
    PERSONNE P3
WHERE
    (((P1.idPersonne , P2.idPersonne) in (SELECT 
            idPersonne1, idPersonne2
        FROM
            FAMILLE)
        OR (P1.idPersonne , P2.idPersonne) in (SELECT 
            idPersonne2, idPersonne1
        FROM
            FAMILLE))
        AND (P1.idPersonne , P2.idPersonne) not in (SELECT 
            *
        FROM
            AMI)
        AND (P2.idPersonne , P1.idPersonne) not in (SELECT 
            *
        FROM
            AMI)
        AND P1.idPersonne < P2.idPersonne)
        AND (P3.idPersonne in (SELECT 
            idPersonne1
        FROM
            AMI
        where
            idPersonne2 = P1.idPersonne)
        OR P3.idPersonne in (SELECT 
            idPersonne2
        FROM
            AMI
        where
            idPersonne1 = P1.idPersonne))
        AND (P3.idPersonne in (SELECT 
            idPersonne1
        FROM
            AMI
        where
            idPersonne2 = P2.idPersonne)
        OR P3.idPersonne in (SELECT 
            idPersonne2
        FROM
            AMI
        where
            idPersonne1 = P2.idPersonne));

-- 7. La difference entre le nombre des personnes et le nombre de
-- personnes majeures.  Une personne est majeure si elle a plus de 21
-- ans.

SELECT 
    (SELECT 
            count(idPersonne)
        FROM
            PERSONNE) - (SELECT 
            count(idPersonne)
        FROM
            PERSONNE
        WHERE
            age >= 21);

-- ou tout simplement:

SELECT 
    count(idPersonne)
FROM
    PERSONNE
WHERE
    age < 21;

-- 8. La liste des noms des personnes qui ont au moins un ami et au
-- moins une personne dans leur famille.

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));

-- 9 La moyenne d'amis par personne

SELECT 
    (SELECT 
            count(idPersonne1)
        FROM
            AMI) DIV (SELECT 
            count(idPersonne)
        FROM
            PERSONNE) as MoyenneAmis;

-- 10. Le nombre des personnes qui sont amis ou amis d’amis de Tiffany.

SELECT 
    count(idPersonne)
FROM
    PERSONNE
WHERE
    idPersonne in (SELECT 
            idPersonne1
        FROM
            AMI
        WHERE
            idPersonne2 = (SELECT 
                    idPersonne
                FROM
                    PERSONNE
                WHERE
                    nomPersonne = 'Tiffany'))
        OR idPersonne in (SELECT 
            idPersonne2
        FROM
            AMI
        WHERE
            idPersonne1 = (SELECT 
                    idPersonne
                FROM
                    PERSONNE
                WHERE
                    nomPersonne = 'Tiffany'))
        OR idPersonne in (SELECT 
            idPersonne1
        FROM
            AMI
        WHERE
            idPersonne2 in (SELECT 
                    idPersonne
                FROM
                    PERSONNE
                WHERE
                    idPersonne in (SELECT 
                            idPersonne1
                        FROM
                            AMI
                        WHERE
                            idPersonne2 = (SELECT 
                                    idPersonne
                                FROM
                                    PERSONNE
                                WHERE
                                    nomPersonne = 'Tiffany'))
                        OR idPersonne in (SELECT 
                            idPersonne2
                        FROM
                            AMI
                        WHERE
                            idPersonne1 = (SELECT 
                                    idPersonne
                                FROM
                                    PERSONNE
                                WHERE
                                    nomPersonne = 'Tiffany'))))
        OR idPersonne in (SELECT 
            idPersonne2
        FROM
            AMI
        WHERE
            idPersonne1 in (SELECT 
                    idPersonne
                FROM
                    PERSONNE
                WHERE
                    idPersonne in (SELECT 
                            idPersonne1
                        FROM
                            AMI
                        WHERE
                            idPersonne2 = (SELECT 
                                    idPersonne
                                FROM
                                    PERSONNE
                                WHERE
                                    nomPersonne = 'Tiffany'))
                        OR idPersonne in (SELECT 
                            idPersonne2
                        FROM
                            AMI
                        WHERE
                            idPersonne1 = (SELECT 
                                    idPersonne
                                FROM
                                    PERSONNE
                                WHERE
                                    nomPersonne = 'Tiffany'))));

-- 11. Le nom et l'age des personnes ayant le plus d’amis.

SELECT distinct
    P.nomPersonne,
    P.age,
    ((SELECT 
            count(idPersonne1)
        FROM
            AMI
        WHERE
            idPersonne1 = P.idPersonne) + (SELECT 
            count(idPersonne1)
        FROM
            AMI
        WHERE
            idPersonne2 = P.idPersonne)) as nombreAmis
from
    PERSONNE P
HAVING nombreAmis >= all (SELECT 
        ((SELECT 
                    count(idPersonne1)
                FROM
                    AMI
                WHERE
                    idPersonne1 = P.idPersonne) + (SELECT 
                    count(idPersonne1)
                FROM
                    AMI
                WHERE
                    idPersonne2 = P.idPersonne)) as nombreAmis
    from
        PERSONNE P)
;


-- 12. Pour chaque personne, la liste des membres de sa famille. 
-- Si A est un membre de la famille de B et C un membre de la famille de B, 
-- C doit apparaıtre dans la liste des membres de la famille de A.

SELECT 
    P1.idPersonne, P1.nomPersonne, P2.idPersonne, P2.nomPersonne
FROM
    PERSONNE P1,
    PERSONNE P2
where
    P1.idPersonne < P2.idPersonne
        AND (P2.idPersonne in (SELECT 
            idPersonne2
        FROM
            FAMILLE
        WHERE
            idPersonne1 = P1.idPersonne)
        OR P2.idPersonne in (SELECT 
            idPersonne1
        FROM
            FAMILLE
        WHERE
            idPersonne2 = P1.idPersonne)
        OR P2.idPersonne in (SELECT 
            idPersonne1
        FROM
            FAMILLE
        where
            idPersonne2 in (SELECT 
                    idPersonne2
                FROM
                    FAMILLE
                WHERE
                    idPersonne1 = P1.idPersonne)
                OR idPersonne2 in (SELECT 
                    idPersonne1
                FROM
                    FAMILLE
                WHERE
                    idPersonne2 = P1.idPersonne)
                OR P2.idPersonne in (SELECT 
                    idPersonne2
                FROM
                    FAMILLE
                where
                    idPersonne1 in (SELECT 
                            idPersonne2
                        FROM
                            FAMILLE
                        WHERE
                            idPersonne1 = P1.idPersonne)
                        OR idPersonne1 in (SELECT 
                            idPersonne1
                        FROM
                            FAMILLE
                        WHERE
                            idPersonne2 = P1.idPersonne))));

-- 13. La liste des trios de personnes differentes, d’ages differents, 
-- tels que la premiere personne est l’aınee, 
-- la seconde est cadette et la troisieme est la plus jeune. 
-- La requete doit retourner les noms de ces personnes et non leur identifiants.

SELECT distinct
    P1.nomPersonne,
    P1.age,
    P2.nomPersonne,
    P2.age,
    P3.nomPersonne,
    P3.age
FROM
    PERSONNE P1,
    PERSONNE P2,
    PERSONNE P3
WHERE
    P1.age > P2.age AND P2.age > P3.age
        AND P1.idPersonne < P2.idPersonne
        AND P2.idPersonne < P3.idPersonne;


-- 14. La liste des personnes ayant une personne dans leur famille mais aucun ami.

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));
