CREATE OR REPLACE DATABASE cuisto;
USE cuisto;
CREATE TABLE cooks (
email VARCHAR(100) NOT NULL,
name VARCHAR(100) NOT NULL,
join_date DATE NOT NULL DEFAULT CURDATE()
);
CREATE TABLE categories (
name VARCHAR(100) NOT NULL
);
CREATE TABLE recipes (
id INT UNSIGNED AUTO_INCREMENT KEY,
name VARCHAR(200) NOT NULL,
description VARCHAR(1000),
ingredients VARCHAR(1000),
cook_email VARCHAR(100) NOT NULL,
category_name VARCHAR(100)
);
CREATE TABLE steps (
id INT UNSIGNED AUTO_INCREMENT KEY,
description VARCHAR(100) NOT NULL,
duration TIME NOT NULL DEFAULT 0,
number SMALLINT UNSIGNED NOT NULL DEFAULT 0,
recipe_id INT UNSIGNED NOT NULL
);
INSERT INTO categories VALUES ('Déjeuner'), ('Diner'), ('Souper'), ('Collation');
INSERT INTO cooks
VALUES ('alice@mail.com', 'Alice', DEFAULT),
('bob@mail.com', 'Bob', DEFAULT),
('charlie@mail.com', 'Charlie', DEFAULT),
('david@mail.com', 'David', DEFAULT),
('eve@mail.com', 'Eve', DEFAULT);
INSERT INTO recipes
VALUES (DEFAULT, 'Nom A', 'Description A', 'Ingredients A', 'alice@mail.com', NULL),
(DEFAULT, 'Nom B', 'Description B', 'Ingredients B', 'alice@mail.com', 'Déjeuner'),
(DEFAULT, 'Nom C', 'Description C', 'Ingredients C', 'bob@mail.com', 'Diner'),
(DEFAULT, 'Nom D', 'Description D', 'Ingredients D', 'charlie@mail.com', 'Souper'),
(DEFAULT, 'Nom E', 'Description E', 'Ingredients E', 'charlie@mail.com', 'Souper'),
(DEFAULT, 'Nom F', 'Description F', 'Ingredients F', 'bob@mail.com', 'Diner'),
(DEFAULT, 'Nom G', 'Description G', 'Ingredients G', 'alice@mail.com', 'Déjeuner'),
(DEFAULT, 'Nom H', 'Description H', 'Ingredients H', 'bob@mail.com', NULL),
(DEFAULT, 'Nom I', 'Description I', 'Ingredients I', 'alice@mail.com', 'Souper'),
(DEFAULT, 'Nom J', 'Description J', 'Ingredients J', 'alice@mail.com', 'Diner'),
(DEFAULT, 'Nom K', 'Description K', 'Ingredients K', 'charlie@mail.com', 'Souper'),
(DEFAULT, 'Nom L', 'Description L', 'Ingredients L', 'charlie@mail.com', 'Diner'),
(DEFAULT, 'Nom M', 'Description M', 'Ingredients M', 'charlie@mail.com', 'Déjeuner');
INSERT INTO steps
VALUES (DEFAULT, 'Étape a', 0, 1, 1),
(DEFAULT, 'Étape aaaa', '00:01', 4, 1),
(DEFAULT, 'Étape aa', '00:02', 2, 1),
(DEFAULT, 'Étape aaa', '00:03', 3, 1),
(DEFAULT, 'Étape b', '00:04', 1, 2),
(DEFAULT, 'Étape bb', '00:05', 2, 2),
(DEFAULT, 'Étape ccc', '00:02', 3, 3),
(DEFAULT, 'Étape cc', '00:04', 2, 3),
(DEFAULT, 'Étape c', '00:12', 1, 3),
(DEFAULT, 'Étape d', '00:02', 1, 4),
(DEFAULT, 'Étape dd', '00:01', 2, 4),
(DEFAULT, 'Étape ddd', 0, 3, 4),
(DEFAULT, 'Étape dddddd', '00:01', 6, 4),
(DEFAULT, 'Étape ddddd', '00:02', 5, 4),
(DEFAULT, 'Étape dddd', '00:03', 4, 4);
Récupérer les catégories ayant des recettes
+-----------+
| name |
+-----------+
| Déjeuner |
| Diner |
| Souper |
+-----------+
Récupérer les catégories n'ayant PAS de recettes
+-----------+
| name |
+-----------+
| Collation |
+-----------+
Récupérer toutes les recettes en ajoutant le nom du cuisinier
+--------+---------+
| recipe | cook |
+--------+---------+
| Nom A | Alice |
| Nom F | Bob |
...
| Nom H | Bob |
| Nom J | Alice |
+--------+---------+
Récupérer les cuisiniers qui préparent des déjeuners
+----------------+-------+------------+
| email | name | join date |
+----------------+-------+------------+
| alice@mail.com | Alice | 2021-10-06 |
+----------------+-------+------------+
Récupérer les recettes sans étapes
+----+-------+----------------+-----------------+------------------+---------------+
| id | name | description | ingredients | cook email | category name |
+----+-------+----------------+-----------------+------------------+---------------+
| 5 | Nom E | Description EE | Ingredients EEE | charlie@mail.com | Souper |
| 6 | Nom F | Description FF | Ingredients FFF | bob@mail.com | Diner |
| 7 | Nom G | Description GG | Ingredients GGG | alice@mail.com | Dejeuner |
| 8 | Nom H | Description HH | Ingredients HHH | bob@mail.com | NULL |
| 9 | Nom I | Description II | Ingredients III | alice@mail.com | Souper |
| 10 | Nom J | Description JJ | Ingredients JJJ | alice@mail.com | Diner |
+----+-------+----------------+-----------------+------------------+---------------+
Récupérer les catégories et le nombre de recettes que chacune contient, triées en ordre croissant du nombre de recettes
+-----------+--------------+
| name | *nb_recipes* |
+-----------+--------------+
| Collation | 0 |
| Déjeuner | 2 |
| Diner | 3 |
| Souper | 3 |
+-----------+--------------+
Récupérer les recettes ayant moins de 5 étapes
+----+-------+
| id | name |
+----+-------+
| 1 | Nom A |
| 2 | Nom B |
...
| 9 | Nom I |
| 10 | Nom J |
+----+-------+
Récupérer les recettes ayant moins de 5 étapes en ajoutant le nombre d'étapes, trié par le nombre d'étapes
+----+-------+---------------+
| id | name | recipes count |
+----+-------+---------------+
| 1 | Nom A | 4 |
| 2 | Nom B | 4 |
...
| 3 | Nom D | 2 |
| 3 | Nom H | 0 |
+----+-------+---------------+
Récupérer les statistiques suivantes sur la durée de préparation(somme des étapes) pour chaque recette: minimum, maximum, moyenne
+----------+----------+----------+
| Min | Max | Avg |
+----------+----------+----------+
| 00:06:00 | 00:28:00 | 00:14:30 |
+----------+----------+----------+
Récupérer le/les cuisiniers ayant le plus de recettes en indiquant le nombre de recettes
+----------------+-------+------------+-----------------+
| email | name | join date | *recipes_count* |
+----------------+-------+------------+-----------------+
| alice@mail.com | Alice | 2021-10-06 | 5 |
+----------------+-------+------------+-----------------+
Supprimer les recettes sans catégorie ou possédant au moins une étapes d'une durée de 0, en affichant le nom des recettes affectées
+-------+
| name |
+-------+
| Nom B |
| Nom C |
+-------+
Tester différentes mécaniques des sous-requêtes dans les bases de données des exercices précédents