#Solution 1
SELECT AVG(DATEDIFF(inspection_date, CURDATE())) as Average_DaysFROM Aircraft order by registration;
#OR
SELECT AVG(TIMESTAMPDIFF(DAY, CURDATE(), inspection_date)) as Average_DaysFROM Aircraft order by registration;

#Solution 2
delimiter //
CREATE PROCEDURE proc_plane_for_route(route_name VARCHAR(25), route_distance INT UNSIGNED)
BEGIN
INSERT INTO Route(`route_description`, `distance`) VALUES (route_name, route_distance);
SET @new_route_id = LAST_INSERT_ID();
SELECT Aircraft.aircraft_id
FROM Aircraft
LEFT JOIN Route
ON Route.aircraft_id = Aircraft.aircraft_id
WHERE
(
(aircraft_type = 'A380'
OR
aircraft_type = 'B747')
AND route_distance > 3000
)
OR
(
(aircraft_type = 'A320'
OR
aircraft_type = 'B737')
AND route_distance <= 3000
)
GROUP BY registration ORDER BY sum(distance) LIMIT 0, 1
INTO @aircraft_id;
UPDATE Route SET aircraft_id = @aircraft_id WHERE route_id = @new_route_id;
END
//
delimiter ;
CALL proc_plane_for_route('Auckland-Tokyo', '8800');
CALL proc_plane_for_route('Auckland-Honolulu', '7100');

#Solution 3
PREPARE stmt_possible_aircraft_routes
FROM 'SELECT route_description, distance
FROM Aircraft, Route
WHERE 
(
    IF(distance > ?, (aircraft_type = \'A380\' OR aircraft_type = \'B747\'), ( aircraft_type = \'A320\' OR aircraft_type = \'B737\'))
       
)
AND registration = ?';
SET @distance = 3000;
SET @registration = 'ZK-GIGA';
EXECUTE stmt_possible_aircraft_routes USING @distance, @registration;