Consigner les commandes de chaque exercice dans un fichier de script.
CREATE DATABASE IF NOT EXISTS cuisto;
USE cuisto;
CREATE OR REPLACE TABLE recipes (
name VARCHAR(250) NOT NULL,
description VARCHAR(1000),
ingredients VARCHAR(1000),
steps VARCHAR(2000),
preparation_time INT DEFAULT 0,
category VARCHAR(50)
);
INSERT INTO recipes
VALUES ('Sandwich', 'Une garniture entre 2 tranches de pain', 'Pain, Garniture', 'Trancher, Garnir, Déguster', 5, 'Principal'),
('Sandwich', 'Du fromage entre 2 tranches de pain', 'Pain, Fromage', 'Trancher, Garnir, Griller, Déguster', 7, 'Principal'),
('Paté chinois', NULL, 'Steak, Blé d''inde, Patates', 'Cuire la viande, mélanger', 30, 'Principal'),
('Lasagne', 'Pâtes étagées', 'Pâtes, sauce', 'Cuire, Étendre, Garnir', 35, 'Principal'),
('Poutine', NULL, 'Patates, Sauce brune, Fromage en grain', 'Couper les patates en frites, cuire au four, réchauffer la sauce, servir', 30, 'Principal'),
('Soupe', 'Bouillon de légumes', 'Légumes de saison', 'Mélanger dans un chaudron, bouillir', 20, 'Entrée'),
('Soupe', 'Crème de brocoli', 'Bouillon, Patates, Brocoli', 'Cuire, Passer au mélangeur', 25, 'Entrée'),
('Soupe', 'Poulet et nouilles', 'Poulet, nouilles', 'Cuire le poulet et les pâtes séparément, mélanger', 45, 'Entrée');
INSERT INTO recipes (name, description, category)
VALUES ('Pomme', 'Fruit frais', 'Collation'), ('Cheddar', 'Batonnets de fromage', 'Collation'), ('Craquelins', 'Petits biscuits', 'Collation');
INSERT INTO recipes (name)
VALUES ('Café'), ('Chocolat chaud'), ('Thé');
Récupérer les recettes avec un temps de préparation de 20 à 30 minutes.
Récupérer les recettes dont les étapes contiennent 'cuire' sans 'patates' dans les ingredients.
Récupérer le nom des recettes, sans répétition, classés du mot le plus long au plus court
+---------------+
| `Nom` |
+---------------+
| Paté chinois |
| Craquelins |
...
| Pomme |
| Soupe |
+---------------+
Récupérer, pour chaque catégorie, son nom, le nombre de recettes, les temps de préparation minimum, maximum et moyen.
+-----------+--------------+-----------+-----------+---------------+
|`Catégorie`|*Nb. recettes*|*Prep. min*|*Prep. max*|*Prep. moyenne*|
+-----------+--------------+-----------+-----------+---------------+
| Collation | 3 | 0 | 0 | 0.0000 |
...
+-----------+--------------+-----------+-----------+---------------+
Récupérer le nombre total de recettes et les catégories possibles en utilisant la fonction GROUP_CONCAT. On peut utiliser les fonctions d'aggrégation sans GROUP BY, elles s'appliquent donc sur l'ensemble des lignes.
Où est la catégorie NULL?
Nombres de recettes, Catégories
+--------------------+---------------------------------+
|*Nombre de recettes*| *Categories* |
+--------------------+---------------------------------+
| 11 | Collation | Entrée | Principal |
+--------------------+---------------------------------+
Récupérer toutes les recettes en calculant l'indice de complexité avec la formule suivante Longueur ingredients * Longueur steps / preparation_time, arrondie sans décimales. Trier le résultat par la 'Complexité'.
Tester les fonctions ROUND et FORMAT pour arrondir la valeur. Quelle est la différence?
Nom, Temps de préparation, Complexité
+---------------+------------------+-------------+
| name | preparation_time |*Complexité* |
+---------------+------------------+-------------+
| Pomme | 0 | 0 |
| Cheddar | 0 | 0 |
| Craquelins | 0 | 0 |
| Lasagne | 35 | 8 |
| Soupe | 45 | 17 |
| Paté chinois | 30 | 22 |
| Soupe | 25 | 27 |
| Soupe | 20 | 30 |
| Sandwich | 7 | 65 |
| Sandwich | 5 | 78 |
| Poutine | 30 | 91 |
+---------------+------------------+-------------+
CREATE DATABASE IF NOT EXISTS todo;
USE todo;
CREATE OR REPLACE TABLE tasks(
id INT UNSIGNED NOT NULL AUTO_INCREMENT KEY,
name VARCHAR(100) NOT NULL,
description VARCHAR(500),
employee VARCHAR(250),
due_date DATE DEFAULT CURDATE(),
estimated_duration TIME NOT NULL,
actual_duration TIME,
completed_date DATE DEFAULT NULL
);
INSERT INTO tasks(id, name, description, estimated_duration, due_date, employee, actual_duration, completed_date)
VALUES (DEFAULT, 'a', 'tache a', 100000, CONCAT(YEAR(CURDATE()) - 2, '-01-01'), 'alice', 80000, CONCAT(YEAR(CURDATE()) - 2, '-01-01')),
(DEFAULT, 'b', 'tache b', 30000, CONCAT(YEAR(CURDATE()) - 1, '-01-01'), 'bob', 53000, CONCAT(YEAR(CURDATE()) - 1, '-01-02')),
(DEFAULT, 'c', 'tache c', 300000, CONCAT(YEAR(CURDATE()) - 2, '-01-01'), 'charlie', 100000, CONCAT(YEAR(CURDATE()) - 2, '-01-03')),
(DEFAULT, 'd', NULL, 100000, CONCAT(YEAR(CURDATE()), '-02-01'), 'alice', 50000, NULL),
(DEFAULT, 'e', NULL, 120000, CONCAT(YEAR(CURDATE()), '-02-01'), 'alice', 140000, NULL),
(DEFAULT, 'f', 'tache f', 240000, CONCAT(YEAR(CURDATE()) - 2, '-02-01'), 'charlie', NULL, CONCAT(YEAR(CURDATE()) - 2, '-01-01')),
(DEFAULT, 'g', 'tache g', 340000, CONCAT(YEAR(CURDATE()), '-03-01'), 'bob', 300000, NULL),
(DEFAULT, 'h', NULL, 10000, CONCAT(YEAR(CURDATE()), '-03-01'), 'bob', 11500, CONCAT(YEAR(CURDATE()), '-03-01')),
(DEFAULT, 'i', 'tache i', 180000, CONCAT(YEAR(CURDATE()), '-04-01'), 'bob', NULL, NULL),
(DEFAULT, 'j', 'tache j', 40000, CONCAT(YEAR(CURDATE()), '-05-01'), 'charlie', NULL, CONCAT(YEAR(CURDATE()), '-05-01')),
(DEFAULT, 'k', 'tache k', 160000, CONCAT(YEAR(CURDATE()) - 1, '-06-01'), 'charlie', 124500, CONCAT(YEAR(CURDATE()) - 1, '-06-02')),
(DEFAULT, 'l', 'tache l', 220000, CONCAT(YEAR(CURDATE()) - 2, '-06-01'), 'charlie', 210000, NULL),
(DEFAULT, 'm', NULL, 80000, CONCAT(YEAR(CURDATE()), '-07-01'), 'charlie', NULL, CONCAT(YEAR(CURDATE()), '-07-01')),
(DEFAULT, 'n', NULL, 60000, CONCAT(YEAR(CURDATE()), '-08-01'), 'alice', NULL, NULL),
(DEFAULT, 'o', NULL, 20000, CONCAT(YEAR(CURDATE()), '-08-01'), 'charlie', 43000, NULL),
(DEFAULT, 'p', 'tache p', 100000, CONCAT(YEAR(CURDATE()) - 2, '-08-01'), 'bob', 80000, NULL),
(DEFAULT, 'q', NULL, 10000, CONCAT(YEAR(CURDATE()) - 1, '-09-01'), 'alice', 100000, CONCAT(YEAR(CURDATE()) - 1, '-09-02')),
(DEFAULT, 'r', 'tache r', 30000, CONCAT(YEAR(CURDATE()), '-09-01'), 'alice', 70000, CONCAT(YEAR(CURDATE()), '-09-03')),
(DEFAULT, 's', 'tache s', 50000, CONCAT(YEAR(CURDATE()), '-10-01'), 'bob', 40000, NULL),
(DEFAULT, 't', NULL, 70000, CONCAT(YEAR(CURDATE()), '-10-01'), 'charlie', 60000, CONCAT(YEAR(CURDATE()), '-09-01')),
(DEFAULT, 'u', NULL, 20000, CONCAT(YEAR(CURDATE()) - 1, '-10-01'), 'charlie', 50000, NULL),
(DEFAULT, 'v', 'tache v', 40000, CONCAT(YEAR(CURDATE()), '-11-01'), 'bob', 50000, NULL),
(DEFAULT, 'w', 'tache w', 60000, CONCAT(YEAR(CURDATE()), '-11-01'), 'alice', 100000, NULL),
(DEFAULT, 'x', 'tache x', 120000, CONCAT(YEAR(CURDATE()), '-12-01'), 'alice', 100000, CONCAT(YEAR(CURDATE()), '-12-02')),
(DEFAULT, 'y', 'tache y', 240000, CONCAT(YEAR(CURDATE()) - 1, '-12-01'), 'charlie', 200000, NULL),
(DEFAULT, 'z', 'tache z', 360000, CONCAT(YEAR(CURDATE()), '-12-01'), 'bob', 300000, CONCAT(YEAR(CURDATE()), '-11-10'));
Récupérer toutes les tâches dont la due_date est en été(de juin à août).
Récupérer toutes les tâches dont la due_date est en été de l'année courante.
Récupérer toutes les tâches en affichant 'n/a' si la description est absente et indiquer le statut:
'Annulée'(actual_duration absente & completed_date présente),
'En attente'(actual_duration absente & completed_date absente),
'En cours'(actual_duration présente & completed_date absente),
'Complétée'(actual_duration présente & completed_date présente).
n/a si la description est nulleXXhYY'' si la tâche n'est pas complétée+------+-------------+-----------------+----------------+-------------+
| name | Description | actual_duration | completed_date | Status |
+------+-------------+-----------------+----------------+-------------+
| a | tache a | 05h30 | 2020-01-01 | Complétée |
| d | n/a | 12h00 | NULL | En cours |
...
| f | tache f | NULL | 2020-01-01 | Annulée |
| i | tache i | NULL | NULL | En attente |
+------+-------------+-----------------+----------------+-------------+
Récupérer toutes les tâches, triées par date d'échéance et date complétée, en calculant le délai de réalisation(différence en jours entre date complétée et date d'échéance) et en formatant la date complétée comme ceci 'January 1st 2021'.
+------+------------+-------+--------------------+
| name | due_date | Delai | Date complétée |
+------+------------+-------+--------------------+
| a | 2020-01-01 | 0 | January 1st 2020 |
| c | 2020-01-01 | 2 | January 3rd 2020 |
...
| d | 2020-02-01 | NULL | NULL |
| f | 2020-02-01 | -31 | January 1st 2020 |
+------+------------+-------+--------------------+
Récupérer les tâches pour chaque employé en calculant le nombre de tâches,
le nombre de tâches complétées,
le nombre de tâches en cours,
le nombre de tâches en attente,
le nombre de tâches annulées,
la date de la dernière complétion,
le nombre de jours depuis la dernière complétion
et le délai moyen de complétion(le délai de complétion étant différence entre la date de complétion et la date d'échéance) arrondi au jours près.
Garder en tête que les fonctions d'agrégations ignorent les valeurs nulles.
+----------+------------------+---------------+-------------+---------------+------------+---------------------+----------------------------------+---------------------------+
| employee | Nombre de taches | Nb completees | Nb en cours | Nb en attente | Nb annulee | Derniere completion | Jours depuis derniere completion | Delai moyen de completion |
+----------+------------------+---------------+-------------+---------------+------------+---------------------+----------------------------------+---------------------------+
| alice | 8 | 4 | 3 | 1 | 0 | 2020-12-02 | 656 | 1 |
| bob | 8 | 3 | 4 | 1 | 0 | 2020-11-10 | 678 | -7 |
| charlie | 10 | 3 | 4 | 0 | 3 | 2020-09-01 | 748 | -10 |
+----------+------------------+---------------+-------------+---------------+------------+---------------------+----------------------------------+---------------------------+
Récupérer les tâches pour chaque employé en calculant
le nombre de tâches complétées,
le nombre de tâches complétées avec une durée inférieure à la durée estimée,
le nombre de tâches complétées avec une durée supérieure à la durée estimée,
et l'efficacité moyenne(moyenne de la différence entre la durée réelle et la durée estimée en heures)
Récupérer uniquement les employés dont l'efficacité moyenne est inférieure à 0, donc qui terminent généralement en avance.
+----------+------------------------------+------------------------------------------------------+------------------------------------------------------+---------------------+
| employee | Nombre de taches completées | Nombre de taches completées avec durée INFÉRIEURE | Nombre de taches completées avec durée SUPÉRIEURE | Efficacité moyenne |
+----------+------------------------------+------------------------------------------------------+------------------------------------------------------+---------------------+
| alice | 4 | 2 | 2 | -1.23 |
| bob | 3 | 1 | 1 | -4.5678 |
| charlie | 3 | 3 | 0 | -2.543 |
+----------+------------------------------+------------------------------------------------------+------------------------------------------------------+---------------------+
Supprimer les tâches annulées en récupérant les items affectés
+------+--------------------+----------------+------------+
| name | estimated_duration | completed_date | due_date |
+------+--------------------+----------------+------------+
| f | 24 | 2020-01-01 | 2020-02-01 |
| j | 4 | 2020-05-01 | 2020-05-01 |
| m | 8 | 2020-07-01 | 2020-07-01 |
+------+--------------------+----------------+------------+
CREATE DATABASE IF NOT EXISTS airport;
USE airport;
CREATE OR REPLACE TABLE `pilots` (
`name` VARCHAR(255) NOT NULL,
`birthday` DATE NOT NULL,
`country` VARCHAR(20) NOT NULL,
`licence` DATE,
`weekly_flights` TINYINT UNSIGNED NOT NULL DEFAULT 0
);
INSERT INTO `pilots` (`birthday`, `country`, `licence`, `name`, `weekly_flights`) values ('1997-09-26', 'uk', '2022-09-26', 'Dacey Ward', 9), ('1985-09-26', 'spain', '2010-09-26', 'Timothy Foley', 3), ('1998-09-26', 'spain', '2021-09-26', 'Kyla Bryan', 2), ('1984-09-26', 'uk', '2004-09-26', 'Celeste Mcclain', 10), ('1973-09-26', 'uk', '1995-09-26', 'Danielle Luna', 7), ('1972-09-26', 'uk', '1996-09-26', 'Odysseus Gill', 1), ('1995-09-26', 'italy', '2015-09-26', 'Vielka Kelley', 5), ('1977-09-26', 'mexico', '1996-09-26', 'Tate Bonner', 1), ('1969-09-26', 'france', '1990-09-26', 'Carla Drake', 8), ('1963-09-26', 'spain', '1986-09-26', 'Hyatt Chandler', 5), ('1972-09-26', 'usa', '1998-09-26', 'Marsden Anderson', 5), ('1988-09-26', 'usa', '2007-09-26', 'Phillip Silva', 1), ('1990-09-26', 'italy', '2013-09-26', 'Donovan Walton', 5), ('1970-09-26', 'usa', '1994-09-26', 'Keely Maynard', 10), ('1989-09-26', 'france', '2012-09-26', 'Cadman Sheppard', 5), ('1987-09-26', 'usa', '2010-09-26', 'Alyssa Orr', 2), ('1990-09-26', 'usa', '2010-09-26', 'Plato Rhodes', 2), ('1968-09-26', 'france', '1989-09-26', 'Thor Cortez', 8), ('1965-09-26', 'usa', '1990-09-26', 'Clinton Carey', 5), ('1972-09-26', 'spain', '1993-09-26', 'Jesse Berg', 6), ('1989-09-26', 'france', '2008-09-26', 'Tanek Wilder', 9), ('1979-09-26', 'usa', '2000-09-26', 'Karina Schroeder', 6), ('1985-09-26', 'spain', '2008-09-26', 'Gemma Donovan', 4), ('1985-09-26', 'france', '2011-09-26', 'Thaddeus Henson', 2), ('1989-09-26', 'spain', '2015-09-26', 'Igor Frank', 5), ('1997-09-26', 'canada', '2021-09-26', 'Charlotte Warner', 8), ('1981-09-26', 'canada', '2004-09-26', 'Travis Merritt', 2), ('1998-09-26', 'france', '2023-09-26', 'Caryn Dickson', 6), ('1963-09-26', 'spain', '1986-09-26', 'Willow Yates', 6), ('1984-09-26', 'france', '2004-09-26', 'Lesley Jennings', 2), ('1970-09-26', 'italy', '1991-09-26', 'Jaquelyn Munoz', 9), ('1980-09-26', 'canada', '2005-09-26', 'Baxter Pratt', 9), ('1983-09-26', 'italy', '2005-09-26', 'Demetria Browning', 3), ('1978-09-26', 'italy', '1999-09-26', 'Azalia Ramirez', 6), ('1966-09-26', 'spain', '1990-09-26', 'Isabelle Herrera', 8), ('1995-09-26', 'uk', '2015-09-26', 'Felix Sexton', 1), ('1980-09-26', 'mexico', '2000-09-26', 'Kieran Shannon', 9), ('1964-09-26', 'italy', '1986-09-26', 'Yuli Spencer', 10), ('1972-09-26', 'canada', '1996-09-26', 'Chancellor Delacruz', 2), ('1980-09-26', 'usa', '2000-09-26', 'Maggie Boyle', 8), ('1996-09-26', 'france', '2020-09-26', 'Lois Merrill', 1), ('1963-09-26', 'france', '1987-09-26', 'Sophia Casey', 7), ('1974-09-26', 'usa', '1995-09-26', 'Raven Lamb', 2), ('1972-09-26', 'france', '1998-09-26', 'Thaddeus Valenzuela', 3), ('1969-09-26', 'spain', '1990-09-26', 'Fitzgerald Blackburn', 9), ('1979-09-26', 'mexico', '1998-09-26', 'Amity Flowers', 10), ('1971-09-26', 'france', '1990-09-26', 'Imelda York', 8), ('1998-09-26', 'uk', '2018-09-26', 'Deacon Anderson', 6), ('1972-09-26', 'canada', '1991-09-26', 'Geraldine Castillo', 5), ('1975-09-26', 'france', '1998-09-26', 'Donna Rodriguez', 9), ('1978-09-26', 'canada', '2000-09-26', 'Sylvester Wise', 8), ('1994-09-26', 'italy', '2016-09-26', 'Sydney Summers', 1), ('1995-09-26', 'france', '2021-09-26', 'Elaine Foley', 10), ('1995-09-26', 'usa', '2015-09-26', 'Isabelle Glenn', 1), ('1986-09-26', 'usa', '2006-09-26', 'Veda Holden', 4), ('1979-09-26', 'spain', '2001-09-26', 'Randall Bishop', 1), ('1986-09-26', 'usa', '2008-09-26', 'Sigourney Donovan', 10), ('1995-09-26', 'mexico', '2020-09-26', 'Tyrone Jacobs', 8), ('1986-09-26', 'france', '2005-09-26', 'Shafira Kent', 6), ('1988-09-26', 'france', '2013-09-26', 'Cynthia Dejesus', 6), ('1995-09-26', 'usa', '2021-09-26', 'Daniel Ferguson', 3), ('1972-09-26', 'mexico', '1998-09-26', 'Mufutau Bowman', 9), ('1983-09-26', 'uk', '2003-09-26', 'Sophia Patton', 4), ('1996-09-26', 'uk', '2021-09-26', 'Marvin Craft', 4), ('1968-09-26', 'usa', '1991-09-26', 'Mariko Cole', 5), ('1968-09-26', 'canada', '1989-09-26', 'Zachery Cash', 6), ('1983-09-26', 'italy', '2003-09-26', 'Reagan Bass', 5), ('1997-09-26', 'uk', '2023-09-26', 'Kirby Stanton', 9), ('1985-09-26', 'mexico', '2010-09-26', 'Xander Frank', 5), ('1965-09-26', 'uk', '1989-09-26', 'Jacob Schultz', 7), ('1977-09-26', 'italy', '2001-09-26', 'Kelly Douglas', 6), ('1974-09-26', 'mexico', '1995-09-26', 'Alden Snider', 6), ('1997-09-26', 'italy', '2018-09-26', 'Chanda Santos', 5), ('1979-09-26', 'italy', '2005-09-26', 'Upton Gordon', 6), ('1974-09-26', 'mexico', '1998-09-26', 'Tashya Wagner', 5), ('1974-09-26', 'canada', '1998-09-26', 'Todd Zimmerman', 9), ('1989-09-26', 'uk', '2010-09-26', 'Alan Hebert', 3), ('1985-09-26', 'mexico', '2008-09-26', 'Vivien Hickman', 6), ('1977-09-26', 'usa', '2002-09-26', 'Kennedy Pena', 3), ('1998-09-26', 'france', '2019-09-26', 'Scott Bond', 2), ('1972-09-26', 'france', '1992-09-26', 'Justina Nixon', 1), ('1986-09-26', 'france', '2012-09-26', 'Wanda Donovan', 4), ('1997-09-26', 'france', '2018-09-26', 'Tasha Smith', 2), ('1980-09-26', 'spain', '2002-09-26', 'Jackson Patel', 5), ('1965-09-26', 'mexico', '1985-09-26', 'Wyatt Mueller', 6), ('1989-09-26', 'france', '2015-09-26', 'Halla Romero', 10), ('1963-09-26', 'mexico', '1983-09-26', 'Eugenia Mullins', 6), ('1967-09-26', 'italy', '1992-09-26', 'Ferris Schultz', 2), ('1988-09-26', 'uk', '2010-09-26', 'Ruby Gilliam', 7), ('1977-09-26', 'canada', '2001-09-26', 'Drake Case', 10), ('1994-09-26', 'uk', '2013-09-26', 'Basil Estrada', 5), ('1966-09-26', 'canada', '1987-09-26', 'Cara Mccoy', 2), ('1975-09-26', 'italy', '1998-09-26', 'Dane Brooks', 10), ('1977-09-26', 'spain', '2002-09-26', 'Ulric Hooper', 3), ('1978-09-26', 'mexico', '1998-09-26', 'Belle Hinton', 10), ('1986-09-26', 'mexico', '2008-09-26', 'Zachary Park', 1), ('1981-09-26', 'canada', '2005-09-26', 'Tucker Watts', 8), ('1967-09-26', 'france', '1986-09-26', 'Roanna Frost', 5), ('1969-09-26', 'canada', '1990-09-26', 'Amena Howe', 3), ('1974-09-26', 'mexico', '1996-09-26', 'Sarah Allen', 3);
CREATE OR REPLACE TABLE `flights` (
`pilot` VARCHAR(255) NOT NULL,
`number` CHAR(4) NOT NULL,
`departure` DATETIME(0) NOT NULL,
`origin` VARCHAR(255) NOT NULL,
`destination` VARCHAR(255) NOT NULL,
`duration` TIME(0) NOT NULL,
`delay` TIME(0) NOT NULL DEFAULT 0,
`plane` VARCHAR(255) NOT NULL
);
INSERT INTO `flights` (`delay`, `departure`, `destination`, `duration`, `number`, `origin`, `pilot`, `plane`) values ('06:59:00', '2022-06-25 03:53:02', 'italy', '12:21:00', 'X086', 'uk', 'franck', 'A350'), ('23:43:00', '2022-03-30 02:53:27', 'spain', '06:57:00', 'H651', 'mexico', 'franck', 'B747'), ('06:36:00', '2021-02-08 05:35:11', 'mexico', '05:08:00', 'R106', 'uk', 'bob', 'A380'), ('22:00:00', '2020-07-10 10:02:53', 'uk', '11:57:00', 'L433', 'uk', 'charlie', 'B747'), ('17:36:00', '2020-02-12 15:00:39', 'usa', '02:42:00', 'J891', 'spain', 'eve', 'A330'), ('08:46:00', '2021-01-23 03:27:42', 'france', '02:38:00', 'I131', 'spain', 'alice', 'A330'), ('11:55:00', '2021-04-13 15:22:51', 'usa', '03:48:00', 'F571', 'mexico', 'franck', 'A330'), ('-01:50:00', '2021-06-12 10:31:56', 'france', '07:23:00', 'E681', 'uk', 'dave', 'B747'), ('11:09:00', '2022-02-18 09:32:03', 'mexico', '04:42:00', 'B981', 'france', 'charlie', 'A330'), ('01:07:00', '2022-05-12 00:47:38', 'canada', '13:34:00', 'Y135', 'uk', 'alice', 'B747'), ('09:06:00', '2021-03-30 19:54:36', 'spain', '00:36:00', 'N271', 'spain', 'bob', 'A350'), ('04:49:00', '2020-09-01 17:10:57', 'italy', '13:35:00', 'U380', 'italy', 'franck', 'B747'), ('18:11:00', '2021-11-09 10:57:46', 'mexico', '16:08:00', 'N453', 'italy', 'charlie', 'A380'), ('04:52:00', '2021-08-29 20:06:07', 'uk', '14:54:00', 'R520', 'mexico', 'alice', 'A350'), ('10:22:00', '2020-08-26 13:14:22', 'spain', '10:07:00', 'D755', 'uk', 'eve', 'A380'), ('19:37:00', '2021-03-12 17:10:56', 'spain', '14:54:00', 'W104', 'mexico', 'alice', 'A380'), ('03:06:00', '2020-12-17 21:34:57', 'uk', '13:47:00', 'U295', 'france', 'dave', 'A330'), ('08:58:00', '2020-06-22 10:07:27', 'canada', '04:36:00', 'L228', 'uk', 'eve', 'B737'), ('11:06:00', '2022-01-09 09:49:47', 'spain', '13:29:00', 'V996', 'spain', 'charlie', 'B747'), ('11:00:00', '2020-08-08 21:23:09', 'canada', '02:17:00', 'H737', 'france', 'charlie', 'A350'), ('07:09:00', '2022-05-23 16:32:48', 'france', '04:56:00', 'T294', 'usa', 'charlie', 'B747'), ('10:11:00', '2020-05-31 09:50:39', 'uk', '07:24:00', 'Q573', 'uk', 'charlie', 'B747'), ('21:06:00', '2020-12-31 01:44:09', 'uk', '14:49:00', 'R942', 'france', 'eve', 'B737'), ('23:34:00', '2021-08-27 19:14:02', 'uk', '10:55:00', 'N938', 'france', 'eve', 'B747'), ('14:34:00', '2020-10-06 03:26:02', 'usa', '06:39:00', 'K960', 'mexico', 'dave', 'A380'), ('15:30:00', '2020-11-23 18:35:45', 'italy', '10:32:00', 'P540', 'france', 'alice', 'A330'), ('01:26:00', '2022-07-24 11:42:47', 'canada', '12:31:00', 'G592', 'france', 'dave', 'B737'), ('-01:38:00', '2021-09-22 11:55:18', 'italy', '08:58:00', 'J527', 'italy', 'charlie', 'B737'), ('10:48:00', '2021-04-10 03:28:28', 'uk', '08:58:00', 'O552', 'canada', 'bob', 'B747'), ('21:59:00', '2020-08-03 17:44:10', 'spain', '12:04:00', 'X727', 'uk', 'dave', 'B747'), ('17:09:00', '2022-05-22 20:35:20', 'usa', '06:40:00', 'T619', 'italy', 'bob', 'A350'), ('13:39:00', '2020-08-11 04:19:32', 'uk', '15:04:00', 'G988', 'spain', 'alice', 'A350'), ('09:17:00', '2021-02-26 09:27:06', 'uk', '12:03:00', 'J980', 'france', 'bob', 'A330'), ('10:43:00', '2022-07-31 20:01:23', 'italy', '04:16:00', 'Q963', 'uk', 'eve', 'A350'), ('13:31:00', '2021-04-21 08:11:06', 'mexico', '01:14:00', 'E230', 'canada', 'bob', 'A350'), ('11:41:00', '2020-03-18 07:42:21', 'mexico', '11:47:00', 'V564', 'canada', 'franck', 'A330'), ('16:04:00', '2021-06-16 00:17:18', 'canada', '05:50:00', 'A069', 'mexico', 'bob', 'A330'), ('22:36:00', '2021-01-20 22:42:08', 'canada', '06:42:00', 'R006', 'usa', 'franck', 'A330'), ('12:24:00', '2022-05-17 11:10:43', 'mexico', '02:22:00', 'G227', 'uk', 'charlie', 'A380'), ('23:52:00', '2022-08-30 03:12:41', 'spain', '00:59:00', 'E604', 'france', 'charlie', 'B747'), ('15:48:00', '2022-01-25 17:23:55', 'italy', '01:59:00', 'D782', 'usa', 'bob', 'A380'), ('12:41:00', '2022-07-31 00:08:08', 'canada', '09:01:00', 'F448', 'france', 'alice', 'B747'), ('10:42:00', '2021-05-25 21:56:14', 'uk', '14:25:00', 'B313', 'uk', 'franck', 'A380'), ('19:43:00', '2020-05-27 19:41:12', 'italy', '04:03:00', 'G998', 'spain', 'dave', 'A380'), ('03:39:00', '2020-10-15 02:43:03', 'canada', '05:48:00', 'G123', 'spain', 'bob', 'A350'), ('04:39:00', '2020-11-28 14:28:22', 'france', '06:41:00', 'F197', 'italy', 'franck', 'A350'), ('07:36:00', '2020-02-24 01:47:47', 'spain', '07:40:00', 'S511', 'usa', 'franck', 'A380'), ('02:38:00', '2020-05-16 15:55:17', 'usa', '13:47:00', 'S852', 'italy', 'franck', 'B737'), ('22:00:00', '2022-07-26 07:59:21', 'france', '06:46:00', 'G820', 'usa', 'dave', 'A350'), ('03:17:00', '2020-09-27 22:19:23', 'france', '05:32:00', 'B011', 'uk', 'franck', 'B737'), ('17:01:00', '2021-04-10 18:07:11', 'france', '01:28:00', 'V379', 'france', 'alice', 'A380'), ('14:56:00', '2021-10-20 08:25:37', 'spain', '05:22:00', 'A950', 'italy', 'alice', 'A330'), ('15:43:00', '2021-12-22 03:28:17', 'uk', '01:29:00', 'B190', 'canada', 'charlie', 'B747'), ('15:22:00', '2020-10-22 05:17:12', 'uk', '11:58:00', 'O726', 'usa', 'dave', 'A350'), ('10:08:00', '2020-02-23 14:38:33', 'spain', '02:23:00', 'E044', 'mexico', 'bob', 'A330'), ('22:38:00', '2022-07-10 01:47:46', 'spain', '10:01:00', 'X387', 'canada', 'eve', 'A380'), ('15:51:00', '2021-06-18 16:01:43', 'canada', '08:57:00', 'Y704', 'mexico', 'charlie', 'B737'), ('05:18:00', '2021-10-11 00:47:19', 'france', '02:53:00', 'G293', 'france', 'eve', 'A380'), ('07:02:00', '2021-04-23 04:16:34', 'mexico', '11:11:00', 'B329', 'spain', 'franck', 'B747'), ('21:54:00', '2022-07-23 03:46:01', 'spain', '00:44:00', 'C739', 'uk', 'franck', 'A330'), ('14:50:00', '2021-03-10 23:33:42', 'spain', '02:20:00', 'E757', 'mexico', 'alice', 'A380'), ('11:07:00', '2022-07-02 18:05:03', 'mexico', '12:41:00', 'D759', 'mexico', 'bob', 'A330'), ('03:06:00', '2022-07-20 01:10:42', 'uk', '08:27:00', 'C346', 'spain', 'dave', 'A380'), ('11:55:00', '2021-07-29 15:16:41', 'mexico', '02:44:00', 'B027', 'canada', 'bob', 'A350'), ('18:54:00', '2021-08-13 03:06:59', 'france', '05:14:00', 'E914', 'france', 'franck', 'A380'), ('03:57:00', '2021-01-21 08:37:52', 'canada', '13:31:00', 'U789', 'canada', 'eve', 'B747'), ('01:44:00', '2022-01-12 12:40:26', 'france', '09:06:00', 'Z604', 'spain', 'charlie', 'B737'), ('17:27:00', '2020-01-29 11:47:04', 'canada', '04:30:00', 'S571', 'usa', 'bob', 'A330'), ('15:09:00', '2020-06-15 22:50:43', 'uk', '03:36:00', 'W118', 'spain', 'franck', 'B737'), ('20:37:00', '2021-01-29 03:38:02', 'mexico', '13:40:00', 'D520', 'spain', 'franck', 'A380'), ('17:21:00', '2020-12-06 05:49:01', 'mexico', '08:39:00', 'D252', 'usa', 'franck', 'B747'), ('09:26:00', '2021-12-24 14:29:31', 'usa', '01:28:00', 'J331', 'uk', 'bob', 'A330'), ('23:41:00', '2021-02-04 19:13:30', 'italy', '14:48:00', 'S251', 'canada', 'charlie', 'A380'), ('04:59:00', '2022-07-09 06:14:11', 'italy', '14:08:00', 'B945', 'usa', 'eve', 'A330'), ('05:53:00', '2022-04-06 23:16:31', 'canada', '14:36:00', 'D825', 'canada', 'charlie', 'A380'), ('18:51:00', '2020-12-12 12:58:49', 'canada', '14:49:00', 'M597', 'france', 'bob', 'A350'), ('21:01:00', '2020-04-14 14:32:08', 'mexico', '09:10:00', 'I214', 'spain', 'eve', 'B737'), ('06:51:00', '2020-10-03 05:22:21', 'france', '04:32:00', 'F245', 'usa', 'bob', 'B737'), ('23:38:00', '2021-12-04 05:11:45', 'spain', '08:02:00', 'A761', 'mexico', 'dave', 'A380'), ('18:37:00', '2022-01-06 13:28:24', 'mexico', '10:56:00', 'A536', 'france', 'charlie', 'B737'), ('23:02:00', '2020-04-25 05:30:32', 'uk', '09:05:00', 'G378', 'uk', 'franck', 'A330'), ('17:36:00', '2021-07-28 18:53:22', 'usa', '01:03:00', 'E263', 'mexico', 'bob', 'B737'), ('09:08:00', '2022-02-28 21:31:31', 'usa', '03:34:00', 'R853', 'uk', 'eve', 'A380'), ('19:02:00', '2020-05-12 02:58:20', 'france', '05:02:00', 'Z619', 'usa', 'alice', 'A350'), ('18:49:00', '2022-01-30 20:08:56', 'italy', '11:52:00', 'S953', 'spain', 'dave', 'A350'), ('23:22:00', '2020-06-25 13:43:59', 'uk', '09:59:00', 'H734', 'canada', 'alice', 'A350'), ('04:25:00', '2020-08-26 06:20:14', 'spain', '15:41:00', 'F371', 'uk', 'bob', 'B747'), ('00:55:00', '2021-05-15 19:10:51', 'italy', '02:44:00', 'I437', 'italy', 'eve', 'A380'), ('08:12:00', '2020-01-27 17:11:29', 'canada', '15:15:00', 'A713', 'spain', 'dave', 'B737'), ('16:55:00', '2020-08-10 08:00:43', 'mexico', '04:12:00', 'E778', 'uk', 'eve', 'A330'), ('13:00:00', '2020-10-19 23:42:56', 'france', '05:02:00', 'X387', 'italy', 'dave', 'A350'), ('21:19:00', '2021-03-13 04:55:22', 'uk', '07:27:00', 'T398', 'canada', 'franck', 'B737'), ('10:40:00', '2020-05-11 14:54:18', 'france', '14:45:00', 'A594', 'spain', 'eve', 'B737'), ('12:53:00', '2021-12-09 12:58:43', 'uk', '08:08:00', 'D858', 'mexico', 'franck', 'A330'), ('04:23:00', '2020-02-15 18:43:20', 'uk', '09:42:00', 'M601', 'italy', 'charlie', 'B747'), ('22:18:00', '2021-05-18 03:45:38', 'mexico', '16:30:00', 'O815', 'france', 'franck', 'A330'), ('02:22:00', '2021-10-02 01:33:19', 'france', '03:17:00', 'X364', 'spain', 'alice', 'A330'), ('07:04:00', '2020-09-17 05:07:05', 'spain', '14:20:00', 'X474', 'usa', 'franck', 'A350'), ('10:00:00', '2021-09-17 10:02:34', 'italy', '02:02:00', 'Z601', 'mexico', 'bob', 'B747'), ('20:01:00', '2022-02-23 10:59:52', 'france', '08:00:00', 'X797', 'mexico', 'alice', 'A350'), ('10:42:00', '2020-12-04 09:35:32', 'canada', '10:03:00', 'J297', 'spain', 'franck', 'A350'), ('03:08:00', '2022-01-09 01:12:29', 'mexico', '14:29:00', 'I167', 'italy', 'alice', 'B737'), ('18:41:00', '2021-02-24 03:48:25', 'italy', '13:03:00', 'Q753', 'usa', 'dave', 'A350'), ('02:41:00', '2021-03-19 04:00:29', 'mexico', '04:44:00', 'O075', 'france', 'dave', 'A380'), ('05:22:00', '2020-05-14 17:36:22', 'italy', '07:48:00', 'A285', 'italy', 'alice', 'A330'), ('06:21:00', '2021-05-23 18:55:01', 'spain', '03:54:00', 'M961', 'italy', 'franck', 'A330'), ('03:45:00', '2020-07-19 03:31:23', 'italy', '16:11:00', 'T258', 'france', 'eve', 'B737'), ('09:13:00', '2021-10-18 06:18:57', 'mexico', '04:36:00', 'E912', 'mexico', 'dave', 'B737'), ('23:23:00', '2022-05-30 11:29:32', 'canada', '07:30:00', 'Y297', 'italy', 'bob', 'B747'), ('02:29:00', '2021-02-27 05:22:36', 'usa', '10:32:00', 'M418', 'uk', 'franck', 'A330'), ('03:58:00', '2021-08-14 16:39:05', 'canada', '08:31:00', 'D842', 'france', 'charlie', 'A330'), ('18:21:00', '2020-01-21 05:16:51', 'canada', '15:38:00', 'L171', 'canada', 'eve', 'A350'), ('-00:56:00', '2020-11-30 12:42:57', 'france', '07:07:00', 'L384', 'usa', 'franck', 'B747'), ('12:09:00', '2021-08-12 05:47:32', 'spain', '10:42:00', 'M149', 'france', 'alice', 'B747'), ('20:43:00', '2022-05-15 04:08:55', 'spain', '11:32:00', 'D941', 'italy', 'dave', 'A350'), ('16:33:00', '2020-10-02 13:53:02', 'mexico', '00:47:00', 'E140', 'mexico', 'eve', 'B737'), ('16:38:00', '2021-02-08 02:29:01', 'canada', '04:03:00', 'Y583', 'italy', 'charlie', 'A380'), ('06:28:00', '2020-02-27 16:08:16', 'uk', '09:18:00', 'M556', 'italy', 'bob', 'A330'), ('15:01:00', '2021-09-25 02:21:40', 'usa', '12:46:00', 'V342', 'mexico', 'dave', 'B737'), ('19:48:00', '2021-12-28 10:29:50', 'spain', '13:38:00', 'Z045', 'france', 'dave', 'B737'), ('13:13:00', '2022-03-30 09:01:33', 'mexico', '16:09:00', 'K562', 'uk', 'charlie', 'B747'), ('05:09:00', '2021-11-07 23:42:58', 'usa', '16:23:00', 'H687', 'usa', 'dave', 'A350'), ('15:21:00', '2021-10-26 19:16:32', 'italy', '03:16:00', 'S881', 'usa', 'franck', 'B747'), ('02:32:00', '2022-01-24 05:28:10', 'usa', '14:49:00', 'H899', 'mexico', 'eve', 'A350'), ('11:49:00', '2021-01-11 17:47:58', 'italy', '11:07:00', 'H866', 'mexico', 'franck', 'A380'), ('15:24:00', '2022-03-22 19:47:21', 'usa', '12:33:00', 'R530', 'france', 'eve', 'A380'), ('11:32:00', '2021-04-21 15:55:11', 'italy', '11:00:00', 'R480', 'mexico', 'alice', 'A350'), ('20:56:00', '2020-06-08 17:21:03', 'france', '11:23:00', 'K545', 'uk', 'alice', 'A350'), ('23:04:00', '2021-03-27 03:52:49', 'usa', '03:37:00', 'S000', 'canada', 'alice', 'B737'), ('18:27:00', '2020-05-14 07:34:19', 'usa', '10:20:00', 'N733', 'canada', 'charlie', 'A350'), ('12:43:00', '2021-07-08 07:31:47', 'usa', '12:22:00', 'Q042', 'canada', 'bob', 'A380'), ('07:38:00', '2020-01-30 17:16:05', 'usa', '09:35:00', 'O796', 'spain', 'eve', 'B747'), ('01:18:00', '2021-06-03 22:31:52', 'italy', '02:40:00', 'K609', 'italy', 'eve', 'A380'), ('03:04:00', '2022-02-26 12:13:56', 'france', '04:44:00', 'U334', 'france', 'eve', 'B737'), ('23:15:00', '2021-04-24 22:05:02', 'france', '02:43:00', 'F398', 'mexico', 'dave', 'A380'), ('19:03:00', '2020-02-10 17:33:16', 'france', '02:38:00', 'I343', 'spain', 'franck', 'A330'), ('15:21:00', '2020-08-22 08:25:18', 'canada', '14:17:00', 'S493', 'uk', 'eve', 'A380'), ('22:01:00', '2021-05-01 03:10:56', 'canada', '02:26:00', 'Y812', 'mexico', 'charlie', 'B747'), ('19:26:00', '2022-03-12 07:12:30', 'canada', '14:05:00', 'K409', 'mexico', 'eve', 'A330'), ('00:22:00', '2020-01-13 03:04:36', 'canada', '14:37:00', 'H839', 'italy', 'charlie', 'B737'), ('13:26:00', '2022-07-15 04:20:49', 'italy', '04:30:00', 'T826', 'france', 'bob', 'A350'), ('14:42:00', '2021-03-16 08:28:17', 'mexico', '09:01:00', 'O307', 'canada', 'alice', 'B737'), ('11:56:00', '2021-09-11 09:10:57', 'mexico', '15:09:00', 'C900', 'canada', 'dave', 'B737'), ('03:58:00', '2022-08-02 21:57:41', 'canada', '11:50:00', 'S456', 'france', 'alice', 'A330'), ('03:33:00', '2020-05-25 07:15:56', 'usa', '04:54:00', 'H760', 'uk', 'bob', 'A350'), ('16:15:00', '2020-04-21 03:38:48', 'italy', '11:55:00', 'D749', 'canada', 'dave', 'A380'), ('13:58:00', '2020-09-03 16:13:18', 'canada', '14:52:00', 'W527', 'italy', 'dave', 'A380'), ('04:04:00', '2020-04-30 14:52:40', 'canada', '07:38:00', 'Q398', 'uk', 'eve', 'B747'), ('14:55:00', '2022-05-01 10:26:30', 'spain', '01:02:00', 'P205', 'uk', 'dave', 'B737'), ('12:41:00', '2021-06-15 12:21:37', 'italy', '13:25:00', 'T042', 'usa', 'dave', 'A350'), ('06:58:00', '2020-10-26 15:54:28', 'italy', '15:59:00', 'G682', 'canada', 'eve', 'A350'), ('18:24:00', '2021-08-31 00:40:16', 'uk', '10:05:00', 'W099', 'canada', 'alice', 'B737'), ('18:42:00', '2022-07-23 00:30:41', 'uk', '04:36:00', 'T068', 'italy', 'franck', 'A380'), ('23:52:00', '2020-08-21 01:29:52', 'spain', '02:17:00', 'E705', 'spain', 'alice', 'B747'), ('08:40:00', '2020-11-06 22:33:12', 'usa', '02:03:00', 'W271', 'italy', 'charlie', 'B747'), ('03:45:00', '2021-04-27 14:09:12', 'canada', '15:34:00', 'D355', 'france', 'dave', 'A350'), ('08:45:00', '2021-11-22 12:21:30', 'spain', '02:11:00', 'B533', 'uk', 'bob', 'A330'), ('11:19:00', '2020-09-08 01:02:35', 'uk', '14:49:00', 'L284', 'italy', 'franck', 'A330'), ('20:48:00', '2021-11-02 13:11:44', 'uk', '08:46:00', 'D532', 'italy', 'eve', 'A330'), ('01:06:00', '2021-02-03 16:36:55', 'france', '06:39:00', 'J736', 'canada', 'franck', 'A330'), ('09:23:00', '2022-04-24 13:33:30', 'canada', '03:23:00', 'J156', 'italy', 'bob', 'A380'), ('02:03:00', '2022-07-31 10:52:02', 'uk', '09:43:00', 'B906', 'uk', 'dave', 'A330'), ('15:31:00', '2021-05-31 16:04:52', 'uk', '08:15:00', 'T091', 'italy', 'dave', 'A380'), ('08:41:00', '2021-08-31 14:19:49', 'france', '09:47:00', 'O916', 'italy', 'bob', 'B737'), ('04:43:00', '2022-07-15 19:17:21', 'mexico', '04:38:00', 'L168', 'italy', 'charlie', 'B737'), ('16:10:00', '2021-01-23 19:26:31', 'spain', '15:34:00', 'C997', 'france', 'bob', 'A380'), ('03:37:00', '2021-11-09 19:10:33', 'canada', '05:02:00', 'I106', 'canada', 'bob', 'A380'), ('09:41:00', '2022-09-07 02:22:13', 'mexico', '07:24:00', 'O352', 'usa', 'eve', 'A350'), ('21:07:00', '2021-02-07 10:54:31', 'italy', '02:09:00', 'A275', 'spain', 'franck', 'B747'), ('09:34:00', '2021-12-06 00:26:53', 'france', '11:16:00', 'R885', 'france', 'alice', 'B747'), ('11:19:00', '2022-05-31 18:36:03', 'italy', '03:36:00', 'C682', 'spain', 'franck', 'A380'), ('03:07:00', '2022-08-13 10:58:05', 'canada', '13:03:00', 'T483', 'mexico', 'charlie', 'B737'), ('16:42:00', '2021-12-14 02:10:08', 'uk', '02:06:00', 'Y415', 'uk', 'dave', 'B747'), ('06:08:00', '2022-04-01 19:30:34', 'spain', '04:28:00', 'N260', 'canada', 'dave', 'B747'), ('00:21:00', '2020-11-21 06:27:01', 'mexico', '11:38:00', 'F458', 'canada', 'charlie', 'A380'), ('-01:42:00', '2020-02-19 18:20:52', 'mexico', '03:17:00', 'O764', 'spain', 'franck', 'A380'), ('13:28:00', '2021-12-06 10:55:31', 'france', '03:47:00', 'W085', 'italy', 'franck', 'A380'), ('10:33:00', '2020-05-19 12:24:32', 'mexico', '09:07:00', 'P151', 'canada', 'dave', 'A330'), ('22:39:00', '2022-09-02 03:34:11', 'canada', '12:09:00', 'V176', 'mexico', 'eve', 'B737'), ('09:30:00', '2022-06-11 16:58:47', 'uk', '00:40:00', 'O324', 'spain', 'dave', 'B747'), ('07:16:00', '2021-11-19 01:15:46', 'uk', '13:31:00', 'E099', 'usa', 'bob', 'A380'), ('22:05:00', '2021-09-28 00:39:41', 'usa', '13:10:00', 'R753', 'italy', 'eve', 'A330'), ('11:40:00', '2021-10-15 19:26:36', 'uk', '12:03:00', 'D997', 'spain', 'bob', 'A330'), ('18:57:00', '2021-05-01 13:27:03', 'italy', '01:26:00', 'G141', 'canada', 'charlie', 'A380'), ('19:27:00', '2021-03-26 22:06:17', 'uk', '10:11:00', 'I712', 'canada', 'dave', 'A330'), ('11:37:00', '2020-11-09 00:42:22', 'canada', '09:47:00', 'C438', 'canada', 'bob', 'A350'), ('06:36:00', '2021-11-17 04:44:24', 'italy', '06:09:00', 'O297', 'spain', 'franck', 'A330'), ('15:05:00', '2021-12-06 02:09:40', 'spain', '10:56:00', 'Y306', 'italy', 'franck', 'B747'), ('20:54:00', '2021-03-31 22:49:57', 'france', '11:31:00', 'S654', 'canada', 'bob', 'B737'), ('19:32:00', '2020-09-27 21:37:41', 'usa', '07:34:00', 'N271', 'uk', 'charlie', 'B747'), ('19:56:00', '2022-01-16 12:52:36', 'france', '07:14:00', 'P349', 'usa', 'charlie', 'A350'), ('-00:03:00', '2021-03-18 19:03:54', 'uk', '12:27:00', 'O427', 'france', 'eve', 'A350'), ('02:00:00', '2022-09-02 03:16:20', 'italy', '05:37:00', 'C471', 'uk', 'dave', 'B737'), ('00:29:00', '2022-04-01 04:59:22', 'usa', '02:41:00', 'D122', 'usa', 'franck', 'A350'), ('05:36:00', '2022-01-20 20:38:06', 'spain', '14:58:00', 'B663', 'usa', 'franck', 'A380'), ('-01:22:00', '2020-08-16 10:45:18', 'spain', '01:51:00', 'V017', 'canada', 'eve', 'A330'), ('15:47:00', '2022-05-26 12:16:19', 'usa', '10:51:00', 'P259', 'spain', 'charlie', 'A330'), ('21:36:00', '2022-08-28 15:53:38', 'canada', '06:15:00', 'V829', 'canada', 'franck', 'B747'), ('06:03:00', '2021-04-26 05:19:50', 'spain', '09:47:00', 'T484', 'spain', 'bob', 'A330'), ('02:18:00', '2020-08-25 05:06:57', 'mexico', '04:04:00', 'S459', 'mexico', 'charlie', 'A330');
Récupérer tous les pilotes ayant de 15 à 20 ans d'expérience.
+---------------+-----+---------+------------+----------------+
| name |*age*| country |*experience*| weekly_flights |
+---------------+-----+---------+------------+----------------+
| Timothy Foley | 38 | spain | 15 | 3 |
| Danielle Luna | 50 | uk | 17 | 7 |
| Vielka Kelley | 28 | italy | 20 | 5 |
...
+---------------+-----+---------+------------+----------------+
Récupérer tous les pilotes dont le nom débute par la lettre 'a'.
+----------------+---------+----------------+
| name | country | weekly_flights |
+----------------+---------+----------------+
| Alyssa Orr | usa | 2 |
| Azalia Ramirez | italy | 6 |
| Amity Flowers | mexico | 10 |
...
+----------------+-----+---------+------------+----------------+
Récupérer les 10 premiers pilotes dont la 2ème lettre du nom est un 'e', trié par le nom.
+--------------------+
| name |
+--------------------+
| Belle Hinton |
| Celeste Mcclain |
| Deacon Anderson |
...
+--------------------+
Récupérer les pilotes pour chaque pays en calculant le nombre de pilotes et la liste des noms pour ce pays via la fonction GROUP_CONCAT
+--------+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| `Pays` |*Nombre de pilotes*|*Pilotes* |
+--------+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| canada | 12 | Charlotte Warner | Travis Merritt | Baxter Pratt | Chancellor Delacruz | Geraldine Castillo | Sylvester Wise | Zachery Cash | Todd Zimmerman | Drake Case | Cara Mccoy | Tucker Watts | Amena Howe |
| france | 21 | Carla Drake | Cadman Sheppard | Thor Cortez | Tanek Wilder | Thaddeus Henson | Caryn Dickson | Lesley Jennings | Lois Merrill | Sophia Casey | Thaddeus Valenzuela | Imelda York | Donna Rodriguez | Elaine Foley | Shafira Kent | Cynthia Dejesus | Scott Bond | Justina Nixon | Wanda Donovan | Tasha Smith | Halla Romero | Roanna Frost |
| italy | 13 | Vielka Kelley | Donovan Walton | Jaquelyn Munoz | Demetria Browning | Azalia Ramirez | Yuli Spencer | Sydney Summers | Reagan Bass | Kelly Douglas | Chanda Santos | Upton Gordon | Ferris Schultz | Dane Brooks |
...
+--------+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Récupérer les valeurs possibles de pays d'origine des pilotes, sans répétition
+---------+
| country |
+---------+
| uk |
| spain |
| italy |
...
+---------+
Récupérer les pilotes provenant de l'Amérique, trié par pays, age et expérience.
+---------------------+-----+---------+------------+----------------+
| name |*age*| country |*experience*| weekly_flights |
+---------------------+-----+---------+------------+----------------+
| Charlotte Warner | 26 | canada | 15 | 8 |
| Travis Merritt | 42 | canada | 16 | 2 |
| Tucker Watts | 42 | canada | 16 | 8 |
...
+---------------------+-----+---------+------------+----------------+
Récupérer les pilotes, pour chaque pays dont la moyenne d'expérience des pilotes est supérieure à 20, en calculant le nombre de pilotes, la moyenne d'expérience, le minimum d'expérience et le maximum d'expérience.
+--------+-------------------+----------------------+--------------------+--------------------+
| Pays |*Nombre de pilotes*|*Moyenne d'experience*|*Experience minimum*|*Experience maximum*|
+--------+-------------------+----------------------+--------------------+--------------------+
| france | 21 | 22.1905 | 10 | 29 |
| uk | 13 | 21.4615 | 11 | 29 |
| usa | 15 | 20.9333 | 10 | 30 |
...
+--------+-------------------+----------------------+--------------------+--------------------+
Récupérer le prénom des pilotes qui se répètent et le nombre de répétion. Utiliser la fonction SUBSTRING_INDEX pour séparer le prénom et le nom de famille.
+----------+----------------------+
|*Prénom* |*Nombre de répétion* |
+----------+----------------------+
| Isabelle | 2 |
| Sophia | 5 |
| Thaddeus | 3 |
...
+----------+----------------------+
Récupérer tous les vols intra-europe.
+--------+--------+---------+-------------+
|`Pilote`|`Numero`|`Origine`|`Destination`|
+--------+--------+---------+-------------+
| franck | X086 | uk | italy |
| charlie| L433 | uk | uk |
| alice | I131 | spain | france |
...
+--------+--------+---------+-------------+
Récupérer les vols, pour chaque avion dont le nombre de vols est d'au moins 40 OU la durée totale des vols de plus de 350 heures, en calculant
le Modèle d'avion qui ajoute 'Airbus ' ou 'Boeing ' selon si plane commence par 'A' ou 'B',
la durée totale du temps de vol
et le nombre de vols
+-------------+--------------+------+
|*Avion* |*Temps de vol*|*Vols*|
+-------------+--------------+------+
| Airbus A330 | 234:34:32 | 44 |
| Airbus A350 | 354:12:56 | 39 |
...
+-------------+--------------+------+
Récupérer les informations de chaque pilote, pour chaque avion, dont le nombre de vols dépasse 5 en calculant
le nombre de vols
et la durée moyenne des vols à la minutes près.
+---------+-------+------+--------------------+
|`Pilote` |`Avion`|*Vols*|*Temps de vol moyen*|
+---------+-------+------+--------------------+
| alice | A330 | 6 | 16:23 |
| alice | A350 | 7 | 56:42 |
| bob | A330 | 10 | 08:34 |
...
+---------+-------+------+--------------------+
Récupérer tous les vols en calculant
la durée prévue au format XXhYY
la date d'arrivée prévue
la durée réelle(en considérant le délai) au format XXhYY
la date d'arrivée réelle(en considérant le délai)
+--------+---------------------+---------------+---------------------+---------------+---------------------+
|`Numero`|`Depart` |*Duree prévue* |*Arrivée prévue* |*Duree réelle* |*Arrivée réelle* |
+--------+---------------------+---------------+---------------------+---------------+---------------------+
| X086 | 2022-06-25 03:53:02 | 12h21 | 2022-06-25 16:14:02 | 19h20 | 2022-06-25 23:13:02 |
| H651 | 2022-03-30 02:53:27 | 06h57 | 2022-03-30 09:50:27 | 30h40 | 2022-03-31 09:33:27 |
| R106 | 2021-02-08 05:35:11 | 05h08 | 2021-02-08 10:43:11 | 11h44 | 2021-02-08 17:19:11 |
...
+--------+---------------------+---------------+---------------------+---------------+---------------------+
Récupérer tous les vols dont l'arrivée n'a pas eu lieu la même date que le départ
+--------+------------+------------+
| number | Depart | Arrivée |
+--------+------------+------------+
| H651 | 2022-03-30 | 2022-03-31 |
| L433 | 2020-07-10 | 2020-07-11 |
| J891 | 2020-02-12 | 2020-02-13 |
...
+--------+------------+------------+
Récupérer tous les vols dont la durée a dépassé 10h
+--------+------------+------------+--------------+
| number |*Départ* |*Arrivée* | *Durée* |
+--------+------------+------------+--------------+
| X086 | 2022-06-25 | 2022-06-25 | 19h34 |
| H651 | 2022-03-30 | 2022-03-31 | 30h23 |
| R106 | 2021-02-08 | 2021-02-08 | 11h45 |
...
+--------+------------+------------+--------------+
En utilisant le contexte de votre choix, effectuer des requêtes d'insertion / modification / suppression / récupération.