SELECT NOART, LIBELLE
FROM ARTICLES
WHERE STOCK<10;

Algèbre relationnelle :
R1=SELECTION(ARTICLES, STOCK<10)
R2=PROJECTION(R1, NOART, LIBELLE)

SELECT *
FROM ARTICLES
WHERE PRIXINVENT BETWEEN 100 AND 300;

AR :
R1=SELECTION(ARTICLES, PRIXINVENT>=100 et PRIXINVENT<=300)
R2=PROJECTION(R1, NOART, LIBELLE, STOCK, PRIXINVENT)

SELECT *
FROM FOURNISSEURS
WHERE ADRFOUR IS NULL;

AR :
R1=SELECTION(FOURNISSEURS, ADRFOUR est "non renseigné")
R2=PROJECTION(R1, NOFOUR, NOMFOUR, ADRFOUR, VILLEFOUR)

SELECT *
FROM FOURNISSEURS
WHERE NOMFOUR LIKE 'STE%';

AR :
R1=SELECTION(FOURNISSEURS, NOMFOUR comme "STE%")
R2=PROJECTION(R1, NOFOUR, NOMFOUR, ADRFOUR, VILLEFOUR)

SELECT NOMFOUR, ADRFOUR, VILLEFOUR
FROM FOURNISSEURS F 
JOIN ACHETER A 
	ON F.NOFOUR=A.NOFOUR
WHERE DELAI>20;

AR :
R1=SELECTION(ACHETER, DELAI>20)
R2=JOINTURE(R1, FOURNISSEURS, NOFOUR)
R2=PROJECTION(R1, NOMFOUR, ADRFOUR, VILLEFOUR)

SELECT COUNT(*) AS NbArticles
FROM ARTICLES;

AR :
NbArticles=CALCULER(ARTICLES, Comptage())

SELECT SUM(STOCK*PRIXINVENT) AS ValeurStock
FROM ARTICLES;

AR :
ValeurStock=CALCULER(ARTICLES, Somme(STOCK*PRIXINVENT))

SELECT NOART, LIBELLE, STOCK
FROM ARTICLES
ORDER BY STOCK DESC;

AR :
R1=PROJECTION(ARTICLES, NOART, LIBELLE, STOCK)
R2=TRI(R1, STOCK décroissant)

SELECT A.NOART, LIBELLE, MAX(PRIXACHAT) AS PMAX, 
       MIN(PRIXACHAT) AS PMIN, AVG(PRIXACHAT) AS PMOY
FROM ACHETER A 
JOIN ARTICLES B 
  ON A.NOART = B.NOART
GROUP BY A.NOART, LIBELLE;

AR :
R1=REGROUPER_ET_CALCULER(ACHETER, NOART, PAMAX : MAX(PRIXACHAT), PAMIN : MIN(PRIXACHAT), PAMOY : MOYENNE(PRIXACHAT))
R2=JOINTURE(R1, ARTICLES, NOART)
R3=PROJECTION(R2, NOART, LIBELLE, PAMAX, PAMIN, PAMOY)

SELECT A.NOFOUR, NOMFOUR, AVG(DELAI) AS DelaiMoyen
FROM ACHETER A 
JOIN FOURNISSEURS F 
  ON A.NOFOUR = F.NOFOUR
GROUP BY A.NOFOUR, NOMFOUR
HAVING COUNT(*) >=2;

AR :
R1=REGROUPER_ET_CALCULER(ACHETER, NOFOUR, Nb : Comptage(), DelaiMoyen : MOYENNE(DELAI))
R2=SELECTION(R1, Nb>=2)
R3=JOINTURE(R2, FOURNISSEURS, NOFOUR)
R4=PROJECTION(R3, NOFOUR, NOMFOUR, DelaiMoyen)

SELECT a.noart, libelle, nomfour, minprix
FROM acheter a 
JOIN
  (select noart, min(prixachat) as minprix
  FROM acheter 
  group by noart) PMIN
    ON a.noart = PMIN.noart AND a.prixachat = PMIN.minprix
JOIN fournisseurs f 
  ON a.nofour = f.nofour
JOIN articles l 
  ON a.noart = l.noart;

AR :
R1=REGROUPER_ET_CALCULER(ACHETER, NOART, minprix : MIN(prixachat))
R2=JOINTURE(R1, ACHETER, R1.NOART=ACHETER.NOART et R1.minprix=ACHETER.prixachat)
R3=JOINTURE(R2, FOURNISSEURS, NOFOUR)
R4=JOINTURE(R3, ARTICLES, NOART)
R5=PROJECTION(R3, NOART, LIBELLE, NOMFOUR, minprix)

SELECT f.nofour, nomfour, adrfour, villefour
FROM fournisseurs f 
LEFT JOIN acheter a 
  on f.nofour = a.nofour
WHERE a.noart IS NULL;

AR :
R1=PROJECTION(FOURNISSEURS, NOFOUR)
R2=PROJECTION(ACHETER, NOFOUR)
R3=DIFFERENCE(R1, R2)
R4=JOINTURE(R3, FOURNISSEURS, NOFOUR)
R5=PROJECTION(R4, NOFOUR, NOMFOUR, ADRFOUR, VILLEFOUR)
ou
R1=JOINTURE_GAUCHE(FOURNISSEURS, ACHETER, NOFOUR)
R2=SELECTION(R1, NOART est NULL)
R3=PROJECTION(R2, NOFOUR, NOMFOUR, ADRFOUR, VILLEFOUR)

SELECT nomfour, a.prixachat as prix_100, 
       b.prixachat as prix_106
FROM acheter a 
join acheter b 
  using(nofour)
JOIN fournisseurs 
  using(nofour)
WHERE a.noart=100 AND b.noart=106;

AR :
R1=SELECTION(ACHETER, NOART=100)
R2=SELECTION(ACHETER, NOART=106)
R3=JOINTURE(R1, R2, NOFOUR)
R4=JOINTURE(R3, FOURNISSEURS, NOFOUR)
R5=PROJECTION(R4, NOMFOUR, R1.prixachat, R2.prixachat)

SELECT a.noart, libelle, nomfour, a.prixachat, a.delai
FROM acheter a 
JOIN acheter b 
  ON a.noart = b.noart
JOIN fournisseurs f 
  ON a.nofour = f.nofour
JOIN articles l 
  ON a.noart = l.noart
WHERE a.nofour <> b.nofour
order by a.noart;

AR :
R1=ACHETER
R2=JOINTURE(R1, ACHETER, NOART)
R3=SELECTION(R2, R1.NOFOUR<>ACHETER.NOFOUR)
R4=JOINTURE(R3, FOURNISSEURS, R1.NOFOUR=FOURNISSEURS.NOFOUR)
R5=JOINTURE(R4, ARTICLES, R1.NOART=ACHETER.NOART)
R6=PROJECTION(R5, R1.NOART, LIBELLE, NOMFOUR, R1.PRIXACHAT, R1.DELAI )

SELECT nofour, nomfour, count(*) as nombre_produits
FROM fournisseurs 
join acheter 
  using(nofour)
GROUP BY nofour, nomfour 
HAVING count(*) = (SELECT MAX(nb_prod) FROM
  (SELECT count(*) as nb_prod
  FROM acheter
  GROUP BY nofour) A);

AR :
R1=REGROUPER_ET_CALCULER(ACHETER, NOFOUR, Nb_prod : Comptage())
Max_prod=CALCULER(R1, MAX(Nb_prod))
R2=REGROUPER_ET_CALCULER(ACHETER, NOFOUR, Nombre_produits : Comptage())
R3=SELECTION(R2, Nombre_produits=Max_prod)
R4=JOINTURE(R3, FOURNISSEURS, NOFOUR)
R5=PROJECTION(R4, NOFOUR, NOMFOUR, Nombre_produits)

1. SELECT nom, prenom
   FROM Artiste
   WHERE idArtiste IN
     (SELECT idActeur
     FROM Role
     WHERE nomRole = 'Morpheus');

2. SELECT nom, prenom
   FROM Artiste
   WHERE idArtiste IN
     (SELECT idMES
     FROM Film
     WHERE titre = 'Alien');

3. WITH NoteEqual4 AS 
     (SELECT email, idFilm 
     FROM Notation 
     WHERE note = 4)
   SELECT F.titre, I.prenom, I.nom
   FROM NoteEqual4 N
   JOIN Internaute I
     ON N.email = I.email
   JOIN Film F
     ON N.idFilm = F.idFilm

4. WITH ActeurRoleVertigo AS
     (SELECT idActeur, nomRole
     FROM Role
     WHERE idFilm IN
       (SELECT idFilm
       FROM Film
       WHERE titre = 'Vertigo'))
   SELECT A.nom, A.prenom, ARV.nomRole
   FROM Artiste A 
   JOIN  ActeurRoleVertigo ARV
     ON A.idArtiste = ARV.idActeur

5. SELECT *
   FROM Film
   WHERE idRES IN
     (SELECT idArtiste
     FROM Artiste
     WHERE prenom = 'Tim' AND nom = 'Burton')
   AND idFilm IN
     (SELECT idFilm
     FROM Role
     WHERE idActeur IN
       (SELECT idArtiste
       FROM Artiste
       WHERE prenom = 'Johnny' AND nom = 'Depp'));

6. SELECT F.titre, R.nomRole
   FROM Role R
   JOIN Artiste A
     ON A.idArtiste = R.idActeur
   JOIN Film F
     ON F.idFilm = R.idFilm
   WHERE A.prenom = 'Bruce' AND A.nom = 'Willis';
   -- Note : serait plus efficace avec des requêtes étagées, comme ci-dessus.

7. SELECT DISTINCT A.prenom, A.nom
   FROM Artiste A
   WHERE EXISTS
     (SELECT 1
     FROM Role R
     WHERE R.idActeur = A.idArtiste AND R.idActeur IN 
       (SELECT idMES FROM Film WHERE idFilm = R.idFilm));

8. WITH MemePrenom AS 
     (SELECT A1.idArtiste AS idArtiste1, A2.idArtiste AS idArtiste2, 
             A1.prenom, A1.nom AS nom1, A2.nom AS nom2
     FROM Artiste A1
     JOIN Artiste A2
       ON A1.prenom = A2.prenom AND A1.idArtiste != A2.idArtiste)
   SELECT F.titre, MP.prenom, MP.nom1 AS nomMES, MP.nom2 AS nomActeur
   FROM Film F
   JOIN MemePrenom MP
     ON MP.idArtiste1 = F.idMES
   WHERE MP.idArtiste2 IN
     (SELECT R.idActeur
     FROM Role R
     WHERE R.idFilm = F.idFilm);
   -- TODO: sans doute pas optimal...

1. WITH NotesRigaux AS
   	(SELECT note
   	FROM Notation
   	WHERE email =  'rigaux@cnam.fr')
   SELECT COUNT(*) AS nombreDeFilms, AVG(note) AS noteMoyenne, 
          MIN(note) AS noteMin, MAX(note) AS noteMax
   FROM NotesRigaux;

2. SELECT COUNT(*)
   FROM Role
   WHERE nomRole = 'McCane' AND idActeur IN
     (SELECT idArtiste
     FROM Artiste
     WHERE prenom = 'Bruce' AND nom = 'Willis');

3. SELECT A.idArtiste, A.nom, A.prenom, IdNb.nombreDeFilms
   FROM Artiste A
   JOIN
     (SELECT idMES, COUNT(*) AS nombreDeFilms
     FROM Film
     GROUP BY idMES) AS IdNb
       ON A.idArtiste = IdNb.idMES;

1. SELECT *
   FROM Film F
   WHERE NOT EXISTS
     (SELECT 1
     FROM Role R
     WHERE F.idFilm = R.idFilm);

2. SELECT nom, prenom
   FROM Artiste
   WHERE idArtiste NOT IN
     (SELECT idMES
     FROM Film);

3. SELECT *
   FROM Internaute
   WHERE email NOT IN
     (SELECT email
     FROM Notation
     WHERE idFilm IN
       (SELECT idFilm
       FROM Film
       WHERE annee = 1999));

1. SELECT nom, prenom
   FROM Artiste
   WHERE idArtiste IN
     (SELECT idMES
     FROM Film);

2. SELECT titre, annee
   FROM Film
   WHERE genre IN
     (SELECT genre
     FROM Film
     WHERE titre = 'Matrix');

3. SELECT I.nom, N.note
   FROM Notation N
   JOIN Internaute I
     ON N.email = I.email
   WHERE N.idFilm IN
     (SELECT idFilm
     FROM Film
     WHERE titre = 'Alien');

4. SELECT A.nom, R.nomRole, F1.titre
   FROM Role R
   JOIN Artiste A
     ON R.idActeur = A.idArtiste
   JOIN Film F1
     ON F1.idFilm = R.idFilm
   WHERE A.idArtiste <> F1.idMES AND A.idArtiste IN
     (SELECT F2.idMES
     FROM Film F2);