
-- Extraction d'informations

-- Projections

-- Q1.

SELECT nomcours
FROM cours;

-- Q2.

SELECT nomcours
FROM cours
ORDER BY nomcours;


-- Q3
SELECT prenom, nom
FROM etudiants
ORDER BY nom, prenom;

-- Restrictions

-- Q4.
SELECT prenom, nom
FROM etudiants
WHERE ville = "GRENOBLE";

-- Q5.

SELECT prenom, nom, ville
FROM etudiants
WHERE ville IN ("LYON", "PARIS");

-- Q6.

SELECT nomcours, jour, heure
FROM horaires
WHERE jour = "LUNDI" AND heure <12;

-- Q7.

SELECT prenom, nom                                                                                  
FROM etudiants                                                                  
WHERE (ville = "PARIS") AND (prenom = "LOUIS");                                 
 
-- Q8
SELECT prenom, nom
FROM etudiants
WHERE (nom LIKE "B%") AND (ville = "Paris");

-- Q9
SELECT prenom, nom
FROM etudiants
WHERE nom LIKE "_u%";

-- Q10
SELECT valeur, cours
FROM notes
WHERE cours = "analyse 1" AND valeur IS NOT NULL
ORDER BY valeur DESC;

-- Projection + restrictions

-- Q11
SELECT nomcours, prerequis
FROM cours
WHERE nomcours IN ("Analyse 1", "Analyse 2")

-- Q12
SELECT prenom
FROM etudiants
WHERE ville = "LYON" and nom = "LUMIERE";

-- Q13
SELECT *
FROM etudiants, notes;

-- Q14
SELECT COUNT(*)   --- 50 * 101
FROM etudiants, notes;

-- Q15

SELECT cours, valeur
FROM notes JOIN etudiants ON etudiants.numero = notes.numetudiant
WHERE nom = "RAVEL" AND prenom = "MAURICE";

SELECT cours, valeur
FROM notes, etudiants 
WHERE etudiants.numero = notes.numetudiant AND nom = "RAVEL" AND prenom = "MAURICE";

-- Q16

SELECT prenom, nom, notes.cours
FROM notes JOIN etudiants ON etudiants.numero = notes.numetudiant
WHERE valeur IS NULL
ORDER BY nom, prenom;


-- Q17

SELECT nomcours
FROM cours JOIN salles ON cours.numsalle = salles.numsalle
WHERE adresse = "AMPERE NORD";

-- Groupements et calculs

-- Q18

SELECT cours
FROM notes
WHERE valeur IS NULL
GROUP BY cours;

-- ou bcp mieux

SELECT DISTINCT cours
FROM notes
WHERE valeur IS NULL;

-- Q19

SELECT COUNT(*)
FROM etudiants;

-- Q20

SELECT COUNT(*)
FROM etudiants
WHERE ville = "PARIS";

-- Q21

SELECT SUM(valeur)
FROM notes
WHERE cours = "Analyse 1";


--- OU BIEN
SELECT SUM(valeur)/COUNT(valeur) -- et pas : COUNT(*)
FROM notes
WHERE cours = "Analyse 1";

-- Q22

SELECT cours, COUNT(valeur)
FROM notes
WHERE valeur IS NULL
GROUP BY cours;
-- ORDER BY cours;

-- ou encore

SELECT cours, SUM(valeur IS NULL)
FROM notes
GROUP BY cours;
-- ORDER BY cours;

-- Q23

SELECT prerequis, COUNT(*)
FROM cours
GROUP BY prerequis;
--ORDER BY prerequis;

-- Q24

SELECT ville, COUNT(*)
FROM etudiants
GROUP BY ville
ORDER BY COUNT(*) DESC;

-- Q25

SELECT prenom, nom, AVG(valeur)
FROM notes JOIN etudiants ON notes.numetudiant = etudiants.numero
WHERE valeur IS NOT NULL
GROUP BY etudiants.numero
ORDER BY AVG(valeur) DESC;

-- Q26

SELECT prenom, nom, AVG(valeur)
FROM notes JOIN etudiants ON notes.numetudiant = etudiants.numero
-- WHERE valeur IS NOT NULL
GROUP BY etudiants.numero
HAVING AVG(valeur) >= 16
ORDER BY nom ASC, prenom ASC;


-- Q15

SELECT nomcours
FROM cours LEFT OUTER JOIN notes ON (cours.nomcours = notes.cours 
                                    AND notes.valeur IS NULL)
WHERE notes.numetudiants IS NULL;








