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

# TP3
### Exercice 1
Requête 1

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