---
# System prepended metadata

title: BDR - grTP4 - Grp_8
tags: [grTP4]

---

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

# TP3
### Exercice 1
Requête 1

1)
```SQL
SELECT nom_recette from recette
WHERE calories_recettes >= 90
``` 
2)
```SQL
SELECT descriptif_produit FROM produit
JOIN rangement USING (num_rangement)
WHERE nom_rangement = 'réfrigérateur'
```
3)

```SQL
SELECT nom_recette from recette
JOIN composition USING (num_recette)
JOIN ingredient USING (num_ingredient)
JOIN type_ingredient USING (num_type_ingredient)
WHERE nom_type_ingredient = 'fruit de mer'
```

4)
```SQL
SELECT nom_recette FROM recette
EXCEPT
(
SELECT nom_recette FROM recette
JOIN composition USING (num_recette)
JOIN ingredient USING (num_ingredient)
JOIN type_ingredient USING (num_type_ingredient)
WHERE type_ingredient = 'fruit de mer'
)
```

https://fr.wikipedia.org/wiki/Vue_(base_de_donn%C3%A9es)

5)
i)

```SQL
SELECT nom_ingredient FROM ingredient
JOIN type_ingredient USING (num_type_ingredient)
WHERE nom_type_ingredient = 'matière grasse'
```

ii)

```
CREATE VIEW ingredient_gras AS 
SELECT nom_ingredient FROM ingredient
JOIN type_ingredient USING (num_type_ingredient)
WHERE nom_type_ingredient = 'matière grasse'
```

iv)
```SQL
SELECT nom_recette
FROM recette JOIN composition USING(num_recette)
JOIN ingredient USING (num_ingredient)
JOIN ingredient_gras USING(num_ingredient)
GROUP BY nom_recette
HAVING COUNT(num_ingredient)>=2
```
v)
```SQL
SELECT DISTINCT nom_rangement
FROM rangement JOIN produit USING(num_rangement)
JOIN ingredient_gras USING(num_ingredient);
```


vi)
```SQL
SELECT DISTINCT nom_ingredient, COALESCE(SUM(quantite_produit), 0, SUM(quantite_produit)) 
--coalesce permet ici de remplacer les 'NULL' par 0 comme affiché dans la correction--
FROM ingredient_gras LEFT JOIN produit USING(num_ingredient)
GROUP BY nom_ingredient;
```

CREATE VIEW quantite_gras AS 
SELECT nom_recette from recette, SUM(quantite_produit)
Join composition USIG (num_recette)
Join ingredient USING (num_ingredient)
GROUP BY nom_recette
HAVING nom_type_ingredient = 'matière grasse'






