La (re)quête du Graal

On considère la base ci-dessous, concernant des livraisons de journaux à des dépositaires. Le préfixe N_ signifie numéro, et le suffixe _J journal. Les attributs QTE_L et QTE_R sont les quantités de journaux respectivement livrées et rendues (invendus).

Contraintes : un journal est déterminé par son code, un dépositaire par son numéro ; et pour un dépositaire, une date et un journal, les quantités sont déterminées.

Exprimer les requêtes suivantes en SQL. Les corrections sont dans le desordre.
Les solutions peuvent comporter des erreurs...
Si votre requete n'est pas celle de la correction, elles devraient etre equivalentes (voir definition au prochain cours... pour toute instance I, q1(I) = q2(I), etc.).

Donner pour chaque journal ses titre, prix, type et périodicité.
```
select titre, prix, type, periode
from journal;
```
Donner les différents journaux triés par ordre alphabétique.
```
select titre
from journal
order by titre;
```

Codes des journaux mensuels.

select code_j
from journal
where periode = 'MENSUEL';

Tous renseignements connus sur journaux ordonnés par type et pour chaque type par prix.
```
select *
from journal
order by type, prix;
```

Pour chaque journal repéré par son code la liste des numéros des dépositaires auxquels il est livré.

select journal.code_j, n_dep
from journal, livraison
where journal.code_j = livraison.code_j
order by journal.code_j;

Prix minimum, moyen et maximum d'un journal.

select min(prix), avg(prix), max(prix)
from journal;

Nombre de journaux dans la base. De types de journaux.

select count(distinct titre), count(distinct type)
from journal;

Codes des journaux dont le type est inconnu.

select code_j
from journal
where type is null;

Pour chaque type de journal, prix minimum, moyen et maximum.

select type, min(prix), avg(prix), max(prix)
from journal
group by type;

Numéros des dépositaires dont la moyenne par livraison de quantité de journaux livrés est supérieure à 15.
```
select n_dep
from livraison
group by n_dep
having avg(qte_l) > 15;
```

Villes où sont domiciliés au moins 5 journaux.

select adr_j
from journal
group by adr_j
having count(distinct code_j) >= 5;

Nombre des villes où sont domiciliés au moins 4 journaux.

select count(distinct adr_j)
from journal 
where adr_j in (select adr_j
                from journal    
              group by adr_j
              having count(distinct code_j) >= 4);

Codes des journaux livrés à Bures (2 manières).

select distinct code_j
from depot, livraison
where depot.n_dep = livraison.n_dep
and adr like 'BURES%';

select distinct code_j
from livraison
where n_dep in (select n_dep 
                from depot
                where adr like 'BURES%');

Codes des journaux livrés au dépositaire Les Brasseurs.

select distinct code_j
from livraison, depot
where livraison.n_dep=depot.n_dep
and nom_dep like 'LES BRASSEURS%';

Titre de ces journaux.

select distinct titre
from journal, livraison, depot
where livraison.n_dep=depot.n_dep
and journal.code_j=livraison.code_j
and nom_dep like 'LES BRASSEURS%';

Nombre de ces journaux.

select count(distinct titre), sum(qte_l)
from journal, livraison, depot
where livraison.n_dep=depot.n_dep
and journal.code_j=livraison.code_j
and nom_dep like 'LES BRASSEURS%';

Codes et titres des journaux dont le prix est supérieur à deux fois le prix minimum.

select distinct  code_j, titre 
from journal
where prix > (select 2*min(prix) from journal);

Numéros des dépositaires qui reçoivent plusieurs journaux.

select n_dep
from livraison
group by n_dep
having count(distinct code_j) > 1;

Numéros des dépositaires ayant au moins 3 livraisons d'au moins 50 journaux.

select n_dep
from livraison a
where date_l in (select date_l
from livraison b
where a.n_dep=b.n_dep
group by date_l
having sum(qte_l) > 50)
group by n_dep
having count(distinct date_l) > 2;

Titres des journaux livrés au moins une fois chez au moins 5 dépositaires parisiens différents.

select titre
from journal, livraison, depot
where journal.code_j=livraison.code_j
and livraison.n_dep=depot.n_dep
and adr like 'PARIS%'
group by titre
having count(distinct livraison.n_dep) >= 5;

Noms des dépositaires qui ne reçoivent pas Libération.

select distinct nom_dep
from depot
where n_dep not in (select n_dep
                    from livraison, journal
                    where livraison.code_j = journal.code_j
                    and titre like 'LIBERATION%');

Numéros des dépositaires qui ne reçoivent que des hebdomadaires.

select distinct n_dep
from livraison, journal
where livraison.code_j = journal.code_j
and periode = 'HEBDO'
and n_dep not in (select n_dep
                    from livraison, journal
                    where livraison.code_j = journal.code_j
                    and periode <> 'HEBDO');

On veut arriver aux : dépositaires parisiens qui reçoivent tous les mensuels féminins. ; on progresse comme suit. Donner d'abord les journaux.
```
select code_j from journal;
```

Donner les journaux que le dépositaire 007 ne reçoit pas (sans minus).

select code_j
from journal
where code_j not in (select code_j from livraison where n_dep = '007');

Donner les journaux que le dépositaire 007 reçoit.
```
select code_j from livraison where n_dep = '007';
```

On veut les dépositaires recevant tous les journaux.

Quel rapport avec ``les journaux que 007 ne reçoit pas'' ?

Donner ces derniers.

select n_dep from depot
where not exists (select * from journal
                   where code_j not in (select code_j from livraison
                                         where depot.n_dep = livraison.n_dep));

Donner les mensuels féminins.

select titre
from journal
where type='FEMININ' and PERIODE='MENSUEL';

Donner les mensuels féminins que 007 ne reçoit pas.

select titre
from journal
where type='FEMININ' and periode='MENSUEL'
and code_j not in (select code_j from livraison where n_dep = '007');

Dépositaires qui reçoivent tous les mensuels féminins.

select n_dep from depot
where not exists (select * from journal
                  where type='FEMININ' and PERIODE='MENSUEL'
                  and code_j not in (select code_j from livraison
                                      where depot.n_dep = livraison.n_dep));

Dépositaires parisiens.

select n_dep
from depot
where adr like 'PARIS%';

Dépositaires parisiens qui reçoivent tous les journaux.

select n_dep from depot
where adr like 'PARIS%'
and not exists (select * from journal
                   where code_j not in (select code_j from livraison
                                         where depot.n_dep = livraison.n_dep));

Dépositaires parisiens qui reçoivent tous les mensuels féminins.

select n_dep from depot
where adr like 'PARIS%'
and not exists (select * from journal
                where type='FEMININ' and PERIODE='MENSUEL'
                and code_j not in (select code_j 
                                     from livraison
                                    where depot.n_dep = livraison.n_dep));

Mensuels féminins livrés à tous les dépositaires parisiens.

select titre
from journal
where type='FEMININ' and PERIODE='MENSUEL'
and not exists (select * from depot
                where not exists (select * from livraison
                                  where livraison.code_j = journal.code_j
                                  and livraison.n_dep = depot.n_dep));