5.2 - Exercices | Base de données

5.2 - Exercices

Modifier le script SQL fournis en implémentant les clés primaires et étrangères pour répondre au modèle relationnel

Assurez-vous de respecter les règles de cascade suivantes

Supprimer un cuisinier entraîne la suppression de ses recettes
Supprimer une recette entraîne la suppression de ses étapes
Supprimer une catégorie assigne NULL aux recettes correspondantes
On peut modifier le nom des catégories et le courriel des cuisiniers

La table steps ne comporte pas de clé primaire, quelle(s) colonne(s) devrait-on utiliser pour assurer l'intégrité des données? Pourquoi?

Implémenter la clé primaire et démontrer son rôle par une requête d'insertion

Vérifier que le script peut être exécuté à répétition sans erreurs.

CREATE OR REPLACE DATABASE cuisto;

USE cuisto;

CREATE TABLE cooks (
    email VARCHAR(100),
    name VARCHAR(100) NOT NULL,
    join_date DATE NOT NULL DEFAULT CURDATE()
);

CREATE TABLE categories (
    name VARCHAR(100)  
);

CREATE TABLE recipes (
    id INT UNSIGNED AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    description VARCHAR(1000),
    ingredients VARCHAR(1000),
    preparation_time TIME DEFAULT 0,
    cook_email VARCHAR(100) NOT NULL,
    category_name VARCHAR(100)
);


CREATE TABLE steps (
    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', 0, 'alice@mail.com', NULL),
        (DEFAULT, 'Nom B', 'Description B', 'Ingredients B', '00:10', 'alice@mail.com', 'Déjeuner'),
        (DEFAULT, 'Nom C', 'Description C', 'Ingredients C', '00:20', 'bob@mail.com', 'Diner'),
        (DEFAULT, 'Nom D', 'Description D', 'Ingredients D', '00:30', 'charlie@mail.com', 'Souper'),
        (DEFAULT, 'Nom E', 'Description E', 'Ingredients E', '00:50', 'charlie@mail.com', 'Souper'),
        (DEFAULT, 'Nom F', 'Description F', 'Ingredients F', '1:20', 'bob@mail.com', 'Diner'),
        (DEFAULT, 'Nom G', 'Description G', 'Ingredients G', '2:10', 'alice@mail.com', 'Déjeuner'),
        (DEFAULT, 'Nom H', 'Description H', 'Ingredients H', '1:30', 'bob@mail.com', NULL),
        (DEFAULT, 'Nom I', 'Description I', 'Ingredients I', '1:10', 'alice@mail.com', 'Souper'),
        (DEFAULT, 'Nom J', 'Description J', 'Ingredients J', 0, 'alice@mail.com', 'Diner'),
        (DEFAULT, 'Nom K', 'Description K', 'Ingredients K', '00:05', 'charlie@mail.com', 'Souper'),
        (DEFAULT, 'Nom L', 'Description L', 'Ingredients L', '00:35', 'charlie@mail.com', 'Diner'),
        (DEFAULT, 'Nom M', 'Description M', 'Ingredients M', '00:10', 'charlie@mail.com', 'Déjeuner');

INSERT INTO steps
VALUES  ('Étape a', 0, 1, 1),
        ('Étape aaaa', '00:01', 4, 1),
        ('Étape aa', '00:02', 2, 1),
        ('Étape aaa', '00:03', 3, 1),
        ('Étape b', '00:04', 1, 2),
        ('Étape bb', '00:05', 2, 2),
        ('Étape c', '00:12', 1, 3),
        ('Étape d', '00:02', 1, 4),
        ('Étape dd', '00:01', 2, 4),
        ('Étape ddd', 0, 3, 4),
        ('Étape dddddd', '00:01', 6, 4),
        ('Étape ddddd', '00:02', 5, 4),
        ('Étape dddd', '00:03', 4, 4);

Lorsque précisé, effectuer la requête en utilisant uniquement les (jointure) ou comparer l'implémentation en utilisant (sous-requête VS jointure)

Pour les premiers énoncés, comparer l'utilisation des JOIN avec les sous-requêtes de l'exercice 4.2

Récupérer(jointure) les catégories utilisées

+-----------+
| name      |
+-----------+
| Déjeuner  |
| Diner     |
| Souper    |
+-----------+

Récupérer(jointure) les catégories n'ayant PAS de recettes

+-----------+
| name      |
+-----------+
| Collation |
+-----------+

Récupérer(jointure) toutes les recettes en ajoutant le nom du cuisinier

+----+-------+----------------+-----------------+------------------+------------------+---------------+-------------+
| id | name  | description    | ingredients     | preparation time | cook email       | category name | *Cook name* |
+----+-------+----------------+-----------------+------------------+------------------+---------------+-------------+
|  1 | Nom A | Description AA | Ingredients AAA | 00:00:00         | alice@mail.com   | NULL          | Alice       |
|  2 | Nom B | Description BB | Ingredients BBB | 00:00:10         | alice@mail.com   | Déjeuner      | Alice       |
|  3 | Nom C | Description CC | Ingredients CCC | 00:00:20         | bob@mail.com     | Diner         | Bob         |
...
+----+-------+----------------+-----------------+------------------+------------------+---------------+-------------+

Récupérer(jointure) les cuisiniers qui préparent des déjeuners

+----------------+-------+------------+
| email          | name  | join date  |
+----------------+-------+------------+
| alice@mail.com | Alice | 2021-10-06 |
+----------------+-------+------------+

Récupérer(jointure) les recettes sans étapes

+----+-------+----------------+-----------------+------------------+------------------+---------------+
| id | name  | description    | ingredients     | preparation time | cook email       | category name |
+----+-------+----------------+-----------------+------------------+------------------+---------------+
|  5 | Nom E | Description EE | Ingredients EEE | 00:50:00         | charlie@mail.com | Souper        |
|  6 | Nom F | Description FF | Ingredients FFF | 01:20:00         | bob@mail.com     | Diner         |
|  7 | Nom G | Description GG | Ingredients GGG | 02:10:00         | alice@mail.com   | Dejeuner      |
|  8 | Nom H | Description HH | Ingredients HHH | 01:30:00         | bob@mail.com     | NULL          |
|  9 | Nom I | Description II | Ingredients III | 01:10:00         | alice@mail.com   | Souper        |
| 10 | Nom J | Description JJ | Ingredients JJJ | 00:00:00         | alice@mail.com   | Diner         |
+----+-------+----------------+-----------------+------------------+------------------+---------------+

Récupérer(jointure) 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(jointure) les recettes ayant moins de 5 étapes

+----+-------+
| id | name  |
+----+-------+
|  1 | Nom A |
|  2 | Nom B |
...
|  8 | Nom H |
|  9 | Nom I |
| 10 | Nom J |
+----+-------+

Récupérer(jointure) les recettes ayant moins de 5 étapes en ajoutant le nombre d'étapes

+----+-------+---------------+
| id | name  | *steps count* |
+----+-------+---------------+
|  1 | Nom A |             4 |
|  2 | Nom B |             4 |
...
|  9 | Nom I |             0 |
| 10 | Nom J |             0 |
+----+-------+---------------+

Récupérer le/les cuisiniers ayant le plus de recettes

+----------------+-------+------------+-----------------+
| email          | name  | join date  | *Recipes Count* |
+----------------+-------+------------+-----------------+
| alice@mail.com | Alice | 2021-10-06 |               5 |
+----------------+-------+------------+-----------------+

Récupérer toutes les combinaisons possibles cuisinier-categorie en ajoutant s'il existe au moins une recette répondant à chaque combinaison

+---------+-----------+----------+
| Cook    | Category  | *Existe* |
+---------+-----------+----------+
| Alice   | Collation |   Non    |
| Alice   | Déjeuner  |   Oui    |
| Alice   | Diner     |   Oui    |
| Alice   | Souper    |   Oui    |
| Bob     | Collation |   Non    |
| Bob     | Déjeuner  |   Non    |
| Bob     | Diner     |   Oui    |
| Bob     | Souper    |   Non    |
...
+---------+-----------+----------+

Récupérer(sous-requête VS jointure) toutes les recettes en ajoutant le nom du cuisinier, le nom de la catégorie et le nombre d'étapes

+----+-------+----------------+-------------+-----------------+---------------+
| id | name  | description    | *Cook name* | Category name   | *Steps count* |
+----+-------+----------------+-------------+-----------------+---------------+
|  1 | Nom A | Description AA | Alice       | NULL            |             4 |
|  2 | Nom B | Description BB | Alice       | Déjeuner        |             4 |
|  3 | Nom C | Description CC | Bob         | Diner           |             4 |
|  4 | Nom D | Description DD | Charlie     | Souper          |             6 |
|  5 | Nom E | Description EE | Charlie     | Souper          |             0 |
...
+----+-------+----------------+-------------+-----------------+---------------+

Récupérer(sous-requête VS jointure) toutes les recettes en ajoutant le nom du cuisinier, le nom de la catégorie, le nombre d'étapes, la durée de préparation totale(preparation_time + duration de toutes les étapes), le résumé des étapes (concatenation de la description des étapes).

+----+-------+----------------+-----------------+-------------+---------------+-----------------+---------------------------------------+
| id | name  | description    |  Category name  | *Cook name* | *Steps count* | *Total time*    | *Summary*                             |
+----+-------+----------------+-----------------+-------------+---------------+-----------------+---------------------------------------+
|  1 | Nom A | Description AA | NULL            | Alice       |             4 | 00:06:00.000000 | Étape aaaa,Étape aaa,Étape a,Étape aa |
|  2 | Nom B | Description BB | Déjeuner        | Alice       |             4 | 00:25:00.000000 | Étape b,Étape bbbb,Étape bbb,Étape bb |
...
|  8 | Nom H | Description HH | Bob             | NULL        |             0 | 01:30:00.000000 | NULL                                  |
|  9 | Nom I | Description II | Alice           | Souper      |             0 | 01:10:00.000000 | NULL                                  |
| 10 | Nom J | Description JJ | Alice           | Diner       |             0 | 00:00:00.000000 | NULL                                  |
+----+-------+----------------+-------------+-----------------+---------------+-----------------+---------------------------------------+

À partir de la même requête, récupérer

les recettes dont la durée totale est inférieure à 1h
les recettes dont le nom du cuisinier contient la lettre a
combiner les 2 conditions précédentes

Supprimer les recettes sans catégorie ou possédant au moins une étapes d'une durée de 0

Delete avec plusieurs tables
En affichant le nom des recettes affectées?

🔥 Récupérer les statistiques globales de durée des recettes: minimum, maximum et moyenne; à partir de la durée de préparation totale de chaque recette(temps de préparation + somme des étapes).

+----------+----------+----------+
| Min      | Max      | Avg      |
+----------+----------+----------+
| 00:00:00 | 02:10:00 | 00:53:48 |
+----------+----------+----------+

🔥 Récupérer tous les cuisinier en précisant le nombre de recettes de chaque catégorie qu'ils possèdent

+-----------+------------+-----------+--------+---------+
| Cuisinier | Collations | Dejeuners | Diners | Soupers |
+-----------+------------+-----------+--------+---------+
| Alice     |          0 |         2 |      1 |       1 |
| Bob       |          0 |         0 |      2 |       0 |
| Charlie   |          0 |         0 |      0 |       2 |
| David     |          0 |         0 |      0 |       0 |
| Eve       |          0 |         0 |      0 |       0 |
+-----------+------------+-----------+--------+---------+

Insérer une recette

Insérer une recette avec la categorie 'Dessert', analyser le résultat

Insérer la categorie 'Souper', analyser le résultat

Insérer une étape

Modifier le courriel d'un cuisinier, analyser l'impact de la CASCADE

Modifier le nom d'une catégorie, analyser l'impact de la CASCADE

Supprimer une étape, analyser l'impact de la CASCADE

Supprimer une catégorie, analyser l'impact de la CASCADE

Supprimer une recette, analyser l'impact de la CASCADE

Supprimer un cusinier, analyser l'impact de la CASCADE

À partir du modèle relationnel ci-dessus, créer un script SQL permettant de mettre en place la base de données TODO et les tables. Le script doit pouvoir être exécuté à plusieurs reprises en obtenant le même résultat. Vous devez ajouter les clés étrangères(colonne + référence) représentées par les relations et affecter les règles de cascade suivantes

Supprimer un employé met à NULL les tâches dont il était responsable
On peut supprimer seulement les tâches sur lesquelles aucuns temps de travail n'a été enregistré
Aucune clé primaire ne peut être modifiée car d'autres systèmes(facturation, payes) en dépendent

-- Insertions de départ

INSERT INTO employees
VALUES  (DEFAULT, 'Employee A', DEFAULT), (DEFAULT, 'Employee B', 10.20), (DEFAULT, 'Employee C', 20.30),
        (DEFAULT, 'Employee D', DEFAULT), (DEFAULT, 'Employee E', 33.44), (DEFAULT, 'Employee F', 55.66),
        (DEFAULT, 'Employee G', DEFAULT), (DEFAULT, 'Employee H', 40.30), (DEFAULT, 'Employee I', 60.50);

INSERT INTO tasks
VALUES  (DEFAULT, 'Task A1', 'Description A1', '2021-01-01', 3, '2021-01-01', 1),
        (DEFAULT, 'Task A2', 'Description A2', '2021-01-05', 6, '2021-01-03', 1),
        (DEFAULT, 'Task A3', 'Description A3', '2021-01-10', 9, '2021-01-11', 1),
        (DEFAULT, 'Task B4', 'Description B4', '2021-02-01', 2, NULL, 2),
        (DEFAULT, 'Task B5', 'Description B5', '2021-02-07', 4, '2021-02-01', 2),
        (DEFAULT, 'Task C6', 'Description C6', '2021-02-11', 6, NULL, 3),
        (DEFAULT, 'Task C7', 'Description C7', '2021-03-22', 10, '2021-05-18', 3),
        (DEFAULT, 'Task C8', 'Description C8', '2021-03-03', 20, '2021-03-04', 3),
        (DEFAULT, 'Task C9', 'Description C9', '2021-04-01', 30, NULL, 3),
        (DEFAULT, 'Task D10', 'Description D10', '2021-04-01', 4, NULL, 4),
        (DEFAULT, 'Task X11', 'Description X11', '2021-05-02', 3, NULL, NULL),
        (DEFAULT, 'Task Y12', 'Description Y12', '2021-06-03', 2, NULL, NULL),
        (DEFAULT, 'Task Z13', 'Description Z13', '2021-07-04', 1, NULL, NULL);

INSERT INTO worktimes
VALUES  (DEFAULT, 'Comment A1', '2021-01-01 08:00', '2021-01-01 09:00', 1),
        (DEFAULT, 'Comment A2', '2021-01-02 09:00', '2021-01-02 12:20', 2),
        (DEFAULT, 'Comment AA2', '2021-01-03 13:30', '2021-01-03 17:15', 2),
        (DEFAULT, 'Comment B4', '2021-02-01 08:00', '2021-02-01 10:00', 4),
        (DEFAULT, 'Comment B5', '2021-01-02 10:00', '2021-01-02 12:00', 5),
        (DEFAULT, 'Comment BB5', '2021-01-04 08:30', '2021-01-04 10:00', 5),
        (DEFAULT, 'Comment BBB5', '2021-01-06 15:00', '2021-01-06 16:00', 5),
        (DEFAULT, 'Comment C7', '2021-04-04 08:00', '2021-04-04 12:00', 7),
        (DEFAULT, 'Comment CC7', '2021-05-05 13:00', '2021-05-05 17:00', 7),
        (DEFAULT, 'Comment C8', '2021-03-03 18:00', '2021-03-04 05:00', 8);

Récupérer Les employées sans tâches

+----+------------+-------------+
| id | name       | hourly rate |
+----+------------+-------------+
|  5 | Employee E |       33.44 |
|  6 | Employee F |       55.66 |
|  7 | Employee G |       13.50 |
|  8 | Employee H |       40.30 |
|  9 | Employee I |       60.50 |
+----+------------+-------------+

Récupérer Les employées qui ont des tâches

+----+------------+-------------+
| id | name       | hourly rate |
+----+------------+-------------+
|  1 | Employee A |       13.50 |
|  2 | Employee B |       10.20 |
|  3 | Employee C |       20.30 |
|  4 | Employee D |       13.50 |
+----+------------+-------------+

Récupérer Les tâches assignées à un employé, en ajoutant le nom de l'employée

+----+----------+-----------------+------------+--------------------+----------------+-------------+
| id | name     | description     | due date   | estimated duration | completed date | employee    |
+----+----------+-----------------+------------+--------------------+----------------+-------------+
|  1 | Task A1  | Description A1  | 2021-01-01 |                  3 | 2021-01-01     | Employee A  |
|  2 | Task A2  | Description A2  | 2021-01-05 |                  6 | 2021-01-03     | Employee A  |
|  3 | Task A3  | Description A3  | 2021-01-10 |                  9 | 2021-01-11     | Employee B  |
|  4 | Task B4  | Description B4  | 2021-02-01 |                  2 | NULL           | Employee A  |
|  5 | Task B5  | Description B5  | 2021-02-07 |                  4 | 2021-02-01     | Employee B  |
|  6 | Task C6  | Description C6  | 2021-02-11 |                  6 | NULL           | Employee C  |
|  7 | Task C7  | Description C7  | 2021-03-22 |                 10 | 2021-05-18     | Employee C  |
|  8 | Task C8  | Description C8  | 2021-03-03 |                 20 | 2021-03-04     | Employee A  |
|  9 | Task C9  | Description C9  | 2021-04-01 |                 30 | NULL           | Employee B  |
| 10 | Task D10 | Description D10 | 2021-04-01 |                  4 | NULL           | Employee A  |
+----+----------+-----------------+------------+--------------------+----------------+-------------+

Récupérer Les employés qui ont inscrit du travail

+----+------------+-------------+
| id | name       | hourly rate |
+----+------------+-------------+
|  1 | Employee A |       13.50 |
|  2 | Employee B |       10.20 |
|  3 | Employee C |       20.30 |
+----+------------+-------------+

Récupérer Les temps de travail(et la tâche concernée) d'un employé choisi par son nom, par exemple employé B

+----+--------------+---------------------+---------------------+---------+----+---------+----------------+------------+--------------------+----------------+-------------+
| id | comment      | start               | end                 | task id | id | name    | description    | due date   | estimated duration | completed date | employee id |
+----+--------------+---------------------+---------------------+---------+----+---------+----------------+------------+--------------------+----------------+-------------+
|  4 | Comment B4   | 2021-02-01 00:08:00 | 2021-02-01 00:10:00 |       4 |  4 | Task B4 | Description B4 | 2021-02-01 |                  2 | NULL           |           2 |
|  5 | Comment B5   | 2021-01-02 00:10:00 | 2021-01-01 00:12:00 |       5 |  5 | Task B5 | Description B5 | 2021-02-07 |                  4 | 2021-02-01     |           2 |
|  6 | Comment BB5  | 2021-01-04 00:08:00 | 2021-01-01 00:10:00 |       5 |  5 | Task B5 | Description B5 | 2021-02-07 |                  4 | 2021-02-01     |           2 |
|  7 | Comment BBB5 | 2021-01-06 00:15:00 | 2021-01-01 00:16:00 |       5 |  5 | Task B5 | Description B5 | 2021-02-07 |                  4 | 2021-02-01     |           2 |
+----+--------------+---------------------+---------------------+---------+----+---------+----------------+------------+--------------------+----------------+-------------+

Récupérer Le nombre de tâches pour chaque employé, en identifiant les employés, classés par le nombre de tâches décroissants

+----+------------+-------------+---------+
| id | name       | hourly rate | *Tasks* |
+----+------------+-------------+---------+
|  3 | Employee C |       20.30 |       4 |
|  1 | Employee A |       13.50 |       3 |
|  2 | Employee B |       10.20 |       2 |
|  4 | Employee D |       13.50 |       1 |
|  8 | Employee H |       40.30 |       0 |
|  5 | Employee E |       33.44 |       0 |
|  7 | Employee G |       13.50 |       0 |
|  9 | Employee I |       60.50 |       0 |
|  6 | Employee F |       55.66 |       0 |
+----+------------+-------------+---------+

Récupérer Le coût de réalisation de chaque tâche(temps de travail * salaire de l'employé assigné)

Ajouter Worktime qui est la somme des temps travaillés

Trier par coût décroissant

+----------+----------+---------+-----------+-------------+
| Task     |*Worktime*|  *Cost* |Employee   | Hourly rate |
+----------+----------+---------+-----------+-------------+
| Task C8  | 11:00:00 |  223.30 |Employee C |       20.30 |
| Task C7  | 08:00:00 |  162.40 |Employee C |       20.30 |
| Task A2  | 07:05:00 |   95.62 |Employee A |       13.50 |
| Task B5  | 04:30:00 |   45.90 |Employee B |       10.20 |
| Task B4  | 02:00:00 |   20.40 |Employee B |       10.20 |
| Task A1  | 01:00:00 |   13.50 |Employee A |       13.50 |
| Task A3  | NULL     |    NULL |Employee A |       13.50 |
| Task D10 | NULL     |    NULL |Employee D |       13.50 |
| Task C6  | NULL     |    NULL |Employee C |       20.30 |
| Task Z13 | NULL     |    NULL |NULL       |        NULL |
| Task C9  | NULL     |    NULL |Employee C |       20.30 |
| Task Y12 | NULL     |    NULL |NULL       |        NULL |
| Task X11 | NULL     |    NULL |NULL       |        NULL |
+----------+----------+---------+-----------+-------------+

Récupérer La paye de chaque employés pour leurs heures travaillées

Ajouter Worktime qui est la somme des temps travaillées

+------------+----------+-------------+--------+
| Employee   |*Worktime*| Hourly rate |*Salary*|
+------------+----------+-------------+--------+
| Employee A | 08:05:00 |       13.50 | 109.12 |
| Employee B | 06:30:00 |       10.20 | 66.30  |
| Employee C | 19:00:00 |       20.30 | 385.70 |
| Employee D | 00:00:00 |       13.50 | 0.00   |
| Employee E | 00:00:00 |       33.44 | 0.00   |
| Employee F | 00:00:00 |       55.66 | 0.00   |
| Employee G | 00:00:00 |       13.50 | 0.00   |
| Employee H | 00:00:00 |       40.30 | 0.00   |
| Employee I | 00:00:00 |       60.50 | 0.00   |
+------------+----------+-------------+--------+

🔥 Récupérer Les employés qui ont plus de tâches que la moyenne, en indiquant combien de tâches et la différence avec la moyenne(combien de plus)

+----+------------+-------------+-----------+
| id | name       | hourly rate |  *Tasks*  |
+----+------------+-------------+-----------+
|  1 | Employee A |       13.50 | 3 +0.4000 |
|  3 | Employee C |       20.30 | 4 +1.4000 |
+----+------------+-------------+-----------+

🔥 Récupérer La rentabilité de chaque tâches: en considérant le salaire de l'employé assigné, la durée prévue et le temps travaillé; quel est le montant de profit ou de perte?

+----------+------------+-------------+-----------+----------+--------+
| Task     | Employee   | Hourly rate |*Estimated*| *Worked* |*Profit*|
+----------+------------+-------------+-----------+----------+--------+
| Task C8  | Employee C |       20.30 | 20:00:00  | 11:00:00 | 182.70 |
| Task C7  | Employee C |       20.30 | 10:00:00  | 08:00:00 | 40.60  |
| Task A1  | Employee A |       13.50 | 03:00:00  | 01:00:00 | 27.00  |
| Task B4  | Employee B |       10.20 | 02:00:00  | 02:00:00 | 0.00   |
| Task B5  | Employee B |       10.20 | 04:00:00  | 04:30:00 | -5.10  |
| Task A2  | Employee A |       13.50 | 06:00:00  | 07:05:00 | -14.62 |
| Task Y12 | NULL       |        NULL | 02:00:00  | NULL     | NULL   |
| Task C9  | Employee C |       20.30 | 30:00:00  | NULL     | NULL   |
| Task C6  | Employee C |       20.30 | 06:00:00  | NULL     | NULL   |
| Task A3  | Employee A |       13.50 | 09:00:00  | NULL     | NULL   |
| Task X11 | NULL       |        NULL | 03:00:00  | NULL     | NULL   |
| Task Z13 | NULL       |        NULL | 01:00:00  | NULL     | NULL   |
| Task D10 | Employee D |       13.50 | 04:00:00  | NULL     | NULL   |
+----------+------------+-------------+-----------+----------+--------+

🔥 Récupérer Le sommaire des tâches(en gérant les valeurs NULLES)

Nom de l'employée
Nom de la tâche
Date complétée
Durée estimée
Temps travaillé (somme des temps de travail)
Efficacité (différence en TIME entre la durée estimée et la durée réelle des tâches complétées, sinon 'n/a')
Statut
- 'Annulée'(aucuns temps de travail & completed_date présente),
- 'En attente'(aucuns temps de travail & completed_date absente),
- 'En cours'(temps de travail présents & completed_date absente),
- 'Complétée'(temps de travail présents & completed_date présente).

Trier par status(Annulée, En attente, En cours, Complétée) ET par efficacité

+------------+----------+------------+-----------+----------+------------+-------------+
| Employee   | Task     | Completed  | Estimated | *Worked* |*Efficiency*|  *Status*   |
+------------+----------+------------+-----------+----------+------------+-------------+
| Employee A | Task A3  | 2021-01-11 |         9 | 00:00:00 | n/a        | Canceled    |
| Employee C | Task C9  | no         |        30 | 00:00:00 | n/a        | Waiting     |
| unassigned | Task X11 | no         |         3 | 00:00:00 | n/a        | Waiting     |
| unassigned | Task Z13 | no         |         1 | 00:00:00 | n/a        | Waiting     |
| Employee C | Task C6  | no         |         6 | 00:00:00 | n/a        | Waiting     |
| Employee D | Task D10 | no         |         4 | 00:00:00 | n/a        | Waiting     |
| unassigned | Task Y12 | no         |         2 | 00:00:00 | n/a        | Waiting     |
| Employee B | Task B4  | no         |         2 | 02:00:00 | n/a        | In progress |
| Employee C | Task C8  | 2021-03-04 |        20 | 11:00:00 | -09:00:00  | Completed   |
| Employee A | Task A1  | 2021-01-01 |         3 | 01:00:00 | -02:00:00  | Completed   |
| Employee C | Task C7  | 2021-05-18 |        10 | 08:00:00 | -02:00:00  | Completed   |
| Employee B | Task B5  | 2021-02-01 |         4 | 04:30:00 | 00:30:00   | Completed   |
| Employee A | Task A2  | 2021-01-03 |         6 | 07:05:00 | 01:05:00   | Completed   |
+------------+----------+------------+-----------+----------+------------+-------------+

Analyser les changements à apporter au modèle pour pouvoir assigner plusieurs employés à une tâche.

Comment s'assurer qu'un employé ne peut pas être assigné plusieurs fois?

Insérer une tâche

Insérer un temps de travail

Modifier le id d'un employé, analyser l'impact de la CASCADE

Supprimer les tâches Annulée(aucuns temps de travail & completed_date présente), en affichant le nom de la tâche

Supprimer une tâche(avec VS sans temps de travail), analyser l'impact de la CASCADE

Supprimer un employé, analyser l'impact de la CASCADE

5.1 - Relations

5.3 - Laboratoire 3

Base de données

5.2 - Exercices

5.2 - Exercices

1- Cuisto

2- TODO