normalizationLab.sql

USE normalization_lab;

SELECT COUNT(DISTINCT flight_number) AS total_flights
FROM flights;

SELECT AVG(mileage) AS avg_flight_distance
FROM flights;

SELECT AVG(total_seats) AS avg_seats
FROM aircrafts;

SELECT status, AVG(total_mileage) AS avg_customer_mileage
FROM customers
GROUP BY status;

SELECT status, MAX(total_mileage) AS max_customer_mileage
FROM customers
GROUP BY status;

SELECT COUNT(*) AS boeing_aircraft_count
FROM aircrafts
WHERE name LIKE '%Boeing%';

SELECT *
FROM flights
WHERE mileage BETWEEN 300 AND 2000;

SELECT c.status, AVG(f.mileage) AS avg_booked_flight_distance
FROM bookings b
JOIN customers c ON b.customer_id = c.id
JOIN flights f ON b.flight_number = f.flight_number
GROUP BY c.status;

SELECT a.name, COUNT(*) AS total_bookings
FROM bookings b
JOIN customers c ON b.customer_id = c.id
JOIN flights f ON b.flight_number = f.flight_number
JOIN aircrafts a ON f.aircraft_id = a.id
WHERE c.status = 'Gold'
GROUP BY a.name
ORDER BY total_bookings DESC
LIMIT 1;