solution.sql

-- === EJERCICIO 1: BLOG DATABASE ===

CREATE TABLE authors (
                         author_id INTEGER PRIMARY KEY AUTOINCREMENT,
                         author_name TEXT NOT NULL UNIQUE
);

CREATE TABLE blog_posts (
                            post_id INTEGER PRIMARY KEY AUTOINCREMENT,
                            author_id INTEGER NOT NULL,
                            title TEXT NOT NULL,
                            word_count INTEGER NOT NULL,
                            views INTEGER DEFAULT 0,
                            FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

INSERT INTO authors (author_name) VALUES ('Maria Charlotte'), ('Juan Perez'), ('Gemma Alcocer');

INSERT INTO blog_posts (author_id, title, word_count, views) VALUES
                                                                 (1, 'Best Paint Colors', 814, 14),
                                                                 (2, 'Small Space Decorating Tips', 1146, 221),
                                                                 (1, 'Hot Accessories', 986, 105),
                                                                 (1, 'Mixing Textures', 765, 22),
                                                                 (2, 'Kitchen Refresh', 1242, 307),
                                                                 (1, 'Homemade Art Hacks', 1002, 193),
                                                                 (3, 'Refinishing Wood Floors', 1571, 7542);

-- === EJERCICIO 2: AIRLINE DATABASE ===

CREATE TABLE aircrafts (
                           aircraft_id INTEGER PRIMARY KEY AUTOINCREMENT,
                           model_name TEXT NOT NULL UNIQUE,
                           total_seats INTEGER NOT NULL
);

CREATE TABLE flights (
                         flight_number TEXT PRIMARY KEY,
                         aircraft_id INTEGER NOT NULL,
                         flight_mileage INTEGER NOT NULL,
                         FOREIGN KEY (aircraft_id) REFERENCES aircrafts(aircraft_id)
);

CREATE TABLE customers (
                           customer_id INTEGER PRIMARY KEY AUTOINCREMENT,
                           customer_name TEXT NOT NULL,
                           customer_status TEXT DEFAULT 'None',
                           total_customer_mileage INTEGER DEFAULT 0
);

CREATE TABLE bookings (
                          booking_id INTEGER PRIMARY KEY AUTOINCREMENT,
                          customer_id INTEGER NOT NULL,
                          flight_number TEXT NOT NULL,
                          FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
                          FOREIGN KEY (flight_number) REFERENCES flights(flight_number)
);

-- Inserción de datos Ejercicio 2
INSERT INTO aircrafts (model_name, total_seats) VALUES ('Boeing 747', 400), ('Airbus A330', 236), ('Boeing 777', 264);

INSERT INTO flights (flight_number, aircraft_id, flight_mileage) VALUES
                                                                     ('DL143', 1, 135), ('DL122', 2, 4370), ('DL53', 3, 2078), ('DL222', 3, 1765), ('DL37', 1, 531);

INSERT INTO customers (customer_name, customer_status, total_customer_mileage) VALUES
                                                                                   ('Agustine Riviera', 'Silver', 115235), ('Alaina Sepulvida', 'None', 6008), ('Tom Jones', 'Gold', 205767),
                                                                                   ('Sam Rio', 'None', 2653), ('Jessica James', 'Silver', 127656), ('Ana Janco', 'Silver', 136773),
                                                                                   ('Jennifer Cortez', 'Gold', 300582), ('Christian Janco', 'Silver', 146427);

INSERT INTO bookings (customer_id, flight_number) VALUES
                                                      (1, 'DL143'), (1, 'DL122'), (2, 'DL122'), (1, 'DL143'), (3, 'DL122'), (3, 'DL53'), (1, 'DL143'), (4, 'DL143'),
                                                      (3, 'DL222'), (5, 'DL143'), (4, 'DL143'), (6, 'DL222'), (7, 'DL222'), (5, 'DL122'), (4, 'DL37'), (8, 'DL222');

-- Total número de vuelos
SELECT COUNT(*) AS total_flights FROM flights;

-- Promedio de distancia de vuelo
SELECT AVG(flight_mileage) AS avg_distance FROM flights;

-- Promedio de asientos por avión
SELECT AVG(total_seats) AS avg_seats FROM aircrafts;

-- Millas promedio por estatus de cliente
SELECT customer_status, AVG(total_customer_mileage)
FROM customers
GROUP BY customer_status;

-- Máximas millas por estatus
SELECT customer_status, MAX(total_customer_mileage)
FROM customers
GROUP BY customer_status;

-- Aviones Boeing
SELECT COUNT(*) FROM aircrafts WHERE model_name LIKE '%Boeing%';

-- Vuelos entre 300 y 2000 millas
SELECT * FROM flights WHERE flight_mileage BETWEEN 300 AND 2000;

-- Promedio de distancia volada según estatus del cliente (JOIN)
SELECT c.customer_status, AVG(f.flight_mileage)
FROM bookings b
         JOIN customers c ON b.customer_id = c.customer_id
         JOIN flights f ON b.flight_number = f.flight_number
GROUP BY c.customer_status;

-- Avión más reservado por miembros Gold
SELECT a.model_name, COUNT(*) AS total_bookings
FROM bookings b
         JOIN customers c ON b.customer_id = c.customer_id
         JOIN flights f ON b.flight_number = f.flight_number
         JOIN aircrafts a ON f.aircraft_id = a.aircraft_id
WHERE c.customer_status = 'Gold'
GROUP BY a.model_name
ORDER BY total_bookings DESC
LIMIT 1;