Consigner les commandes de chaque exercice dans un fichier de script, ils seront réutilisés plus tard.
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 toutes les recettes en calculant l'indice de complexité avec la formule suivante Longueur ingredients * Longueur steps / preparation_time, sans décimales. Trier le résultat par la 'Complexité'.
round et format pour éliminer les décimales?select json_array(round(1.2345, 3), format(1.2345, 3));ATTENTION Lors d'un tri ordre numérique VS alphabétique
+---------------+------------------+-------------+
| name | preparation_time |*complexite* |
+---------------+------------------+-------------+
| 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'' pour les valeurs NULL 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 | | En cours |
...
| f | tache f | | 2020-01-01 | Annulée |
| i | tache i | | | 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 completee* |
+------+------------+---------+--------------------+
| 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 |
+------+------------+---------+--------------------+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 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 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 prevue* |*arrivee prevue* |*duree reelle* |*arrivee reelle* |
+--------+---------------------+---------------+---------------------+---------------+---------------------+
| 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* | *arrivee* |
+--------+------------+------------+
| 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 |*depart* |*arrivee* | *duree* |
+--------+------------+------------+--------------+
| X086 | 2022-06-25 | 2022-06-25 | 19h34 |
| H651 | 2022-03-30 | 2022-03-31 | 30h23 |
| R106 | 2021-02-08 | 2021-02-08 | 11h45 |
...
+--------+------------+------------+--------------+