---Exercice 1, les questions et les réponses.

--1

select nomcategorie, enonce, difficulte
FROM categories c 
    inner join questions q on c.idcategorie=q.idcategorie;


--2
select q.idQuestion, enonce, difficulte, lareponse
from questions q 
    inner join reponses r on q.idquestion = r.idquestion
where r.estbonne='o';

--3
select  enonce, difficulte
FROM categories c 
    inner join questions q on c.idcategorie=q.idcategorie
where nomCategorie ='histoire et geographie';

-----4

select nomCategorie, enonce, difficulte, lareponse as La_Bonne_Reponse
from categories c 
    inner join questions q on c.idcategorie=q.idcategorie
    inner join reponses r  on q.idquestion = r.idquestion
where r.estbonne='o' order by nomCategorie;

-- 5

select nom, aliass, nomcategorie
from joueurstrivia j 
    inner join scores s on s.idjoueur = j.idjoueur
inner join categories c on c.idcategorie=s.idcategorie;

---- 5
select nom, aliass, nomcategorie, nbbonnereponse, nbmauvaisereponse
from joueurstrivia j 
    inner join scores s on s.idjoueur = j.idjoueur
    inner join categories c on c.idcategorie=s.idcategorie;

----6

select nom, aliass
from joueurstrivia j 
    inner join scores s  on s.idjoueur = j.idjoueur
    inner join categories c on c.idcategorie=s.idcategorie
where nbbonnereponse>2 and nomcategorie ='sciences';

-----7
select nomCategorie, enonce,difficulte
from categories c 
    left outer join questions q on c.idcategorie=q.idcategorie 
order by enonce;

----8
select nomCategorie, couleur
from categories c 
    left outer join questions q on c.idcategorie=q.idcategorie
where enonce is null;

--9

select nom, aliass, s.nbbonnereponse,s.nbmauvaisereponse
from joueurstrivia j 
    left outer join scores s on j.idjoueur=s.idjoueur
where s.nbbonnereponse is null and nbmauvaisereponse is null;

----10

select nomcategorie ,s.nbbonnereponse,s.nbmauvaisereponse
from categories c 
    left outer join scores s on c.idcategorie = s.idcategorie
where s.nbbonnereponse is null and nbmauvaisereponse is null;

-----------------------------------------------------------------------------

---Exercice 2 (les employes)
----1

select nom, prenom, nomdepartement
from employesClg e inner join Departements d on e.codedep=d.codedep;

--2

select nom, prenom, d.codedep,nomdepartement
from employesClg e inner join Departements d on e.codedep=d.codedep;

--3
select nom, prenom, nomdepartement
from employesClg e right outer join Departements d on e.codedep=d.codedep;

--4
select nom, prenom, nomdepartement
from employesClg e left outer join Departements d on e.codedep=d.codedep;

--5
select distinct descriptions
from formations f inner join employesformations m on f.codeformation =m.codeformation
where m.lieuformation like '%Montréal';

---6
select  distinct lieuformation
from formations f inner join employesformations m on f.codeformation =m.codeformation
where descriptions ='Introduction à la normalisation';

---7
select nom, prenom, descriptions, datedebut, lieuformation
from employesClg e 
    inner join employesformations m on e.empno =m.empno
    inner join formations f on f.codeformation =m.codeformation;

----8
 
select nom, prenom, descriptions, datedebut, lieuformation
from Departements D 
    inner join  employesClg e on d.codedep =e.codedep
    inner join employesformations m on e.empno =m.empno
    inner join formations f on f.codeformation =m.codeformation
where  nomDepartement ='Informatique';



---9
select nom, prenom, m.codeformation
from departements d 
  inner join employesClg e on e.codedep = d.codedep
  left outer join employesformations m on e.empno =m.empno
 where d.nomdepartement='Informatique' and m.codeformation is null;
 
--- 10
select f.codeformation ,descriptions, empno
from formations f 
    left outer join employesformations m on f.codeformation=m.codeformation
where empno is null ;