---
# System prepended metadata

title: BDR - grTP1 - Grp_4
tags: [grTP1]

---

---
title: BDR - grTP1 - Grp_4
tags: grTP1
---
[//]: <> (Ne pas modifier ce qui est au dessus SVP.)

# TP3
### Exercice 1
Requête 1

```sql
SELECT nom_recette FROM recette WHERE calories_recette>='90'
``` 
Requête 2
```sql=
SELECT descriptif_produit
FROM produit
JOIN rangement ON produit.num_rangement=rangement.num_rangement
WHERE rangement.nom_rangement='réfrigérateur'
```

Requête 3
```sql=
SELECT nom_recette
FROM recette JOIN (composition 
JOIN (ingredient 
JOIN type_ingredient 
ON ingredient.num_type_ingredient=type_ingredient.num_type_ingredient)
ON ingredient.num_ingredient=composition.num_ingredient) 
ON composition.num_recette=recette.num_recette
WHERE nom_type_ingredient='fruit de mer'
```
Requête 4
```sql=
SELECT nom_recette
FROM recette
EXCEPT (SELECT nom_recette
FROM recette JOIN (composition 
JOIN (ingredient 
JOIN type_ingredient 
ON ingredient.num_type_ingredient=type_ingredient.num_type_ingredient)
ON ingredient.num_ingredient=composition.num_ingredient) 
ON composition.num_recette=recette.num_recette
WHERE nom_type_ingredient='fruit de mer')
```
Requête 5
```sql=
SELECT num_ingredient, nom_ingredient, ingredient.num_type_ingredient
FROM ingredient
JOIN type_ingredient
ON ingredient.num_type_ingredient=type_ingredient.num_type_ingredient
WHERE nom_type_ingredient='matière grasse'
```

```sql=
CREATE VIEW ingredient_gras AS
SELECT num_ingredient, nom_ingredient, ingredient.num_type_ingredient
FROM ingredient
JOIN type_ingredient
ON ingredient.num_type_ingredient=type_ingredient.num_type_ingredient
WHERE nom_type_ingredient='matière grasse'
```

```sql=
SELECT num_recette,nom_recette
FROM recette
JOIN composition
USING(num_recette)
JOIN ingredient_gras
USING(num_ingredient)
GROUP BY num_recette,nom_recette
HAVING COUNT(*) >=2
```

```sql=
SELECT DISTINCT nom_rangement
FROM rangement
JOIN produit
USING(num_rangement)
JOIN ingredient_gras
USING (num_ingredient)
```

```sql=
SELECT quantite_produit
FROM produit
JOIN ingredient_gras
USING(num_ingredient)
```

Requête 6
```sql=
CREATE VIEW quantité_matière_grasse AS
SELECT SUM(quantite_composition), num_recette
FROM composition
JOIN ingredient_gras
USING(num_ingredient)
GROUP BY num_recette
ORDER BY num_recette
```

Requête 7
```sql=
SELECT num_recette, nom_recette,
CASE
    WHEN sum>120 THEN 'recette trop grasse'
    WHEN 0<sum AND sum<50 THEN 'recette legère'
    WHEN sum=0 THEN 'recette non grasse'
    ELSE 'recette moyennement grasse'
END
FROM recette
JOIN quantité_matière_grasse
USING (num_recette)
```