---
# System prepended metadata

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

---

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

# TP3
### Exercice 1
Requête 1

```SQL 
SELECT * from test
``` 
# Question 1
SELECT nom_recette 
FROM recette 
WHERE calorie_recette/nombre_personne > 90

# Question 2
SELECT descriptif_produit
FROM produit p
JOIN rangement r
    ON p.num_rangement=r.num_rangement
WHERE r.nom_rangement='frigo'

# Question 3
SELECT nom_recette 
FROM recette
JOIN composition 
    ON recette.num_recette=composition.num_recette
JOIN ingredient 
    ON composition.num_ingredient=ingredient.num_ingrendient
JOIN type_ingredient
    ON ingredient.num_type=type_ingredient.num_type_ingredient
WHERE nom_type_ingredient='Fruit de mer'

# Question 4
SELECT nom_recette
FROM recette
EXCEPT (SELECT nom_recette 
FROM recette
JOIN composition 
    ON recette.num_recette=composition.num_recette
JOIN ingredient 
    ON composition.num_ingredient=ingredient.num_ingrendient
JOIN type_ingredient
    ON ingredient.num_type=type_ingredient.num_type_ingredient
WHERE nom_type_ingredient='Fruit de mer')

# Question 5 i
SELECT * 
FROM ingredient
JOIN type_ingredient 
    ON ingredient.num_type=type_ingredient.num_type_ingredient
WHERE nom_type_ingredient='Matière grasse'

# Question 5 ii
CREATE VIEW ingredient_gras AS (SELECT * 
FROM ingredient
JOIN type_ingredient 
    ON ingredient.num_type=type_ingredient.num_type_ingredient
WHERE nom_type_ingredient='Matière grasse')

# Question 5 iii
SELECT ingredient_gras

# Question 5 iv
SELECT nom_recette
FROM recette
JOIN composition 
    ON recette.num_recette=composition.num_recette
JOIN ingredient_gras 
    ON composition.num_ingredient=ingredient_gras.num_ingrendient
WHERE (SELECT COUNT(DISTINCT(nom_ingredient))
FROM ingredient_gras
JOIN composition 
    ON composition.num_ingredient=ingredient_gras.num_ingrendient
JOIN recette
    ON recette.num_recette=composition.num_recette
JOIN ingredient_gras 
GROUP BY recette) >2


# Question 5 v
SELECT nom_rangement 
FROM rangement 
JOIN produit 
    ON rangement.num_rangement=produit.num_rangement
JOIN ingredient 
    ON ingredient.num_ingredient=produit.num_ingredient 
JOIN type_ingredient 
    ON ingredient.num_type=type_ingredient.num_type_ingredient
WHERE nom_type_ingredient='Matière grasse'

# Question 5 vi
SELECT quantite_produit
FROM produit p
JOIN ingredient_gras i
    ON p.num_ingredient=i.num_ingredient

# Question 5 vii

# Question 6
CREATE VIEW matière_grasse_recette
AS (SELECT SUM(quantite_composition),nom_recette
FROM composition
JOIN recette 
    ON recette.num_recette=composition.num_recette
JOIN ingredient
    ON ingredient.num_ingredient=composition.num_ingredient
JOIN type_ingredient 
    ON ingredient.num_type=type_ingredient.num_type_ingredient
WHERE nom_type_ingredient='Matière grasse')

# Question 7
SELECT recette
 CASE WHEN quantite_composition>120 THEN 'recette trop grasse'
 WHEN quantite_composition<50 AND quantite_composition>0 THEN 'recette légère'
 WHEN quantite_composition=0 THEN 'recette non grasse'
 ELSE 'recette moyennement grasse'
 END
 FROM matiere_grasse_recette
 
# Question 8
SELECT nom_recette
FROM recette
JOIN composition
    ON recette.num_recette=composition.num_recette
JOIN ingredient
    ON ingredient.num_ingredient=composition.num_ingredient
WHERE nom_ingredient='Viande rouge' OR nom_ingredient='Jaune d'oeuf'

# Question 9
SELECT nom_type_ingredient
FROM type_ingredient t
JOIN ingredient i
    ON i.num_type=t.num_type_ingredient
JOIN produit p
    ON i.num_ingredient=p.num_ingredient
WHERE p.date_peremption BETWEEN 01-11-2015 AND 31-12-2015

# Question 10
(SELECT nom_rangement 
JOIN produit 
    ON rangement.num_rangement=produit.num_rangement
JOIN ingredient
    ON ingredient.num_ingredient=produit.num_ingredient
JOIN type_ingredient 
    ON type_ingredient.nm_type_ingredient=ingredient.num_type
WHERE nom_type_ingredient='Fruit')
INTERSECT (SELECT nom_rangement 
JOIN produit 
    ON rangement.num_rangement=produit.num_rangement
JOIN ingredient
    ON ingredient.num_ingredient=produit.num_ingredient
JOIN type_ingredient 
    ON type_ingredient.nm_type_ingredient=ingredient.num_type
WHERE nom_type_ingredient='Légume')

# Question 11
SELECT nom_rangement
FROM rangement r
JOIN produit p
    ON r.num_rangement=p.num_rangement
JOIN ingredient i
    ON p.num_ingredient=i.num_ingredient
JOIN composition c
    ON i.num_ingredient=c.num_ingredient
JOIN recette re
    ON c.num_recette=re.num_recette
WHERE nom_recette='crêpes'

# Question 12
Select COUNT(nom_recette) 
FROM recette

# Question 13
SELECT COUNT(*)
FROM recette
JOIN composition 
    ON recette.num_recette=composition.num_recette
JOIN ingredient 
    ON composition.num_ingredient=ingredient.num_ingrendient
WHERE ingredient.nom_ingredient='oeuf'

# Question 14


# Question 15
