-- =========================
-- TABLA PASAJEROS
-- =========================
CREATE TABLE pasajeros (
    id_pasajero INTEGER PRIMARY KEY,
    nombre VARCHAR(50),
    apellido VARCHAR(50),
    nacionalidad VARCHAR(50)
);

INSERT INTO pasajeros VALUES
    (1, 'Ana', 'Pérez', 'Boliviana'),
    (2, 'Luis', 'García', 'Peruana'),
    (3, 'María', 'López', 'Boliviana'),
    (4, 'Carlos', 'Rojas', 'Argentina'),
    (5, 'Sofía', 'Mendoza', 'Boliviana'),
    (6, 'Juan', 'Flores', 'Chilena');

-- =========================
-- TABLA AEROPUERTOS
-- =========================
CREATE TABLE aeropuertos (
    id_aeropuerto INTEGER PRIMARY KEY,
    nombre VARCHAR(100),
    ciudad VARCHAR(50),
    pais VARCHAR(50)
);

INSERT INTO aeropuertos VALUES
    (1, 'El Alto', 'La Paz', 'Bolivia'),
    (2, 'Viru Viru', 'Santa Cruz', 'Bolivia'),
    (3, 'Jorge Chávez', 'Lima', 'Perú'),
    (4, 'Ezeiza', 'Buenos Aires', 'Argentina');

-- =========================
-- TABLA AVIONES
-- =========================
CREATE TABLE aviones (
    id_avion INTEGER PRIMARY KEY,
    modelo VARCHAR(50),
    capacidad INTEGER
);

INSERT INTO aviones VALUES
    (1, 'Airbus A320', 180),
    (2, 'Boeing 737', 220),
    (3, 'Airbus A350', 300);

-- =========================
-- TABLA VUELOS
-- =========================
CREATE TABLE vuelos (
    id_vuelo INTEGER PRIMARY KEY,
    origen_id INTEGER,
    destino_id INTEGER,
    id_avion INTEGER,
    fecha_vuelo VARCHAR(20),

    FOREIGN KEY (origen_id)
        REFERENCES aeropuertos(id_aeropuerto),

    FOREIGN KEY (destino_id)
        REFERENCES aeropuertos(id_aeropuerto),

    FOREIGN KEY (id_avion)
        REFERENCES aviones(id_avion)
);

INSERT INTO vuelos VALUES
    (1, 1, 2, 1, '2026-06-01'),
    (2, 2, 3, 2, '2026-06-02'),
    (3, 3, 4, 3, '2026-06-03'),
    (4, 1, 4, 2, '2026-06-04'),
    (5, 2, 1, 1, '2026-06-05');

-- =========================
-- TABLA BOLETOS
-- =========================
CREATE TABLE boletos (
    id_boleto INTEGER PRIMARY KEY,
    id_pasajero INTEGER,
    id_vuelo INTEGER,
    precio DECIMAL(10,2),

    FOREIGN KEY (id_pasajero)
        REFERENCES pasajeros(id_pasajero),

    FOREIGN KEY (id_vuelo)
        REFERENCES vuelos(id_vuelo)
);

INSERT INTO boletos VALUES
    (1, 1, 1, 850.50),
    (2, 2, 2, 1200.00),
    (3, 3, 3, 1500.00),
    (4, 4, 4, 990.50),
    (5, 5, 1, 850.50);

-- =========================
-- TABLA EMPLEADOS
-- =========================
CREATE TABLE empleados (
    id_empleado INTEGER PRIMARY KEY,
    nombre VARCHAR(50),
    cargo VARCHAR(50)
);

INSERT INTO empleados VALUES
    (1, 'Pedro', 'Piloto'),
    (2, 'Laura', 'Copiloto'),
    (3, 'Andrés', 'Azafata'),
    (4, 'Mónica', 'Azafata'),
    (5, 'Jorge', 'Piloto');

-- =========================
-- TABLA TRIPULACIONES
-- =========================
CREATE TABLE tripulaciones (
    id_tripulacion INTEGER PRIMARY KEY,
    id_vuelo INTEGER,
    id_empleado INTEGER,

    FOREIGN KEY (id_vuelo)
        REFERENCES vuelos(id_vuelo),

    FOREIGN KEY (id_empleado)
        REFERENCES empleados(id_empleado)
);

INSERT INTO tripulaciones VALUES
    (1, 1, 1),
    (2, 1, 2),
    (3, 1, 3),
    (4, 2, 5),
    (5, 2, 4),
    (6, 3, 1);

-- =========================
-- TABLA MALETAS
-- =========================
CREATE TABLE maletas (
    id_maleta INTEGER PRIMARY KEY,
    id_pasajero INTEGER,
    peso DECIMAL(5,2),

    FOREIGN KEY (id_pasajero)
        REFERENCES pasajeros(id_pasajero)
);

INSERT INTO maletas VALUES
    (1, 1, 15.50),
    (2, 2, 23.00),
    (3, 3, 18.75),
    (4, 5, 30.20);

-- =========================
-- CONSULTAS
-- =========================
SELECT '***SELECT***';
SELECT '1. Mostrar a todos los pasajeros.';
SELECT * FROM PASAJEROS;

SELECT ' ';
SELECT '2. Mostrar todos los aeropuertos.';
SELECT * FROM AEROPUERTOS;

SELECT ' ';
SELECT '3. Mostrar únicamente el nombre y apellido de los pasajeros.';
SELECT nombre, apellido FROM pasajeros;

SELECT ' ';
SELECT '4. Mostrar el modelo y capacidad de los aviones.';
SELECT modelo, capacidad FROM aviones;

SELECT ' ';
SELECT '5. Mostrar todos los boletos.';
SELECT * FROM boletos;

SELECT ' ';
SELECT '***********************************************************';
SELECT '***WHERE***';
SELECT '6. Mostrar pasajeros de nacionalidad boliviana.';
SELECT * FROM pasajeros WHERE nacionalidad = 'Boliviana';

SELECT ' ';
SELECT '7. Mostrar aviones con capacidad mayor a 200 pasajeros.';
SELECT * FROM aviones WHERE capacidad > 200;

SELECT ' ';
SELECT '8. Mostrar boletos con precio superior a 1000.';
SELECT * FROM boletos WHERE precio > 1000;

SELECT ' ';
SELECT '9. Mostrar maletas con peso mayor a 20 kg.';
SELECT * FROM maletas WHERE peso > 20;

SELECT ' ';
SELECT '10. Mostrar empleados cuyo cargo sea "Piloto".';
SELECT * FROM empleados WHERE cargo = 'Piloto';

SELECT ' ';
SELECT '***********************************************************';
SELECT '***ORDER BY***';
SELECT '11. Ordenar pasajeros por apellido ascendente.';
SELECT * FROM pasajeros ORDER BY apellido ASC;

SELECT ' ';
SELECT '12. Ordenar boletos por precio descendente.';
SELECT * FROM boletos ORDER BY precio DESC;

SELECT ' ';
SELECT '13. Ordenar aviones por capacidad descendente.';
SELECT * FROM aviones ORDER BY capacidad DESC;

SELECT ' ';
SELECT '***********************************************************';
SELECT '***FUNCIONES DE AGREGACIÓN***';
SELECT '14. Contar cuántos pasajeros existen.';
SELECT COUNT (*) AS totalPasajeros FROM pasajeros;

SELECT ' ';
SELECT '15. Obtener el precio más alto de un boleto.';
SELECT MAX(precio) AS precioMasAlto FROM boletos;

SELECT ' ';
SELECT '16. Calcular el promedio de precios de boletos.';
SELECT AVG(precio) AS promedioPrecio FROM boletos;

SELECT ' ';
SELECT '***********************************************************';
SELECT '***INNER JOIN***';
SELECT '17. Mostrar nombre del pasajero y precio de su boleto.';
SELECT p.nombre, b.precio FROM pasajeros p 
    INNER JOIN boletos b
        ON p.id_pasajero = b.id_pasajero;

SELECT ' ';
SELECT '18. Mostrar nombre del pasajero y fecha del vuelo.';
SELECT p.nombre, v.fecha_vuelo FROM pasajeros p
    INNER JOIN boletos b
        ON p.id_pasajero = b.id_pasajero
    INNER JOIN vuelos v
        ON b.id_vuelo = v.id_vuelo;

SELECT ' ';
SELECT '19. Mostrar el modelo del avión utilizado en cada vuelo.';
SELECT v.id_vuelo, a.modelo FROM vuelos v
    INNER JOIN aviones a
        ON v.id_avion = a.id_avion;
        
SELECT ' ';
SELECT '20. Mostrar el nombre del empleado y el vuelo al que fue asignado.';
SELECT e.nombre, t.id_vuelo FROM empleados e
    INNER JOIN tripulaciones t
        ON e.id_empleado = t.id_empleado
    INNER JOIN vuelos v
        ON t.id_vuelo = v.id_vuelo;

SELECT ' ';
SELECT '***********************************************************';
SELECT '***EJERCICIOS EXTRA***';
SELECT '21. Usar LEFT JOIN para mostrar a todos los pasajeros, incluso aquellos que no
compraron boletos.';
SELECT p.nombre, p.apellido, b.id_boleto, b.precio FROM pasajeros p
    LEFT JOIN boletos b
        ON p.id_pasajero = b.id_pasajero;

SELECT ' ';
SELECT '22. Mostrar todos los vuelos y los boletos asociados utilizando LEFT JOIN.';
SELECT v.id_vuelo, v.fecha_vuelo, b.id_boleto FROM vuelos v
    LEFT JOIN boletos b
        ON v.id_vuelo = b.id_vuelo;

SELECT ' ';
SELECT '23. Mostrar todos los empleados y sus vuelos asignados usando LEFT JOIN.';
SELECT e.nombre, e.cargo, t.id_vuelo FROM empleados e
    LEFT JOIN tripulaciones t
        ON e.id_empleado = t.id_empleado;

SELECT ' ';
SELECT '24. Usar RIGHT JOIN para mostrar todos los boletos aunque el pasajero no exista
en la tabla de pasajeros.';
SELECT p.nombre, p.apellido, b.id_boleto FROM pasajeros p
    RIGHT JOIN boletos b 
        ON p.id_pasajero = b.id_pasajero;
    
SELECT ' ';
SELECT '25. Mostrar todos los vuelos aunque no tengan tripulación asignada utilizando LEFT
JOIN.';
SELECT v.id_vuelo, v.fecha_vuelo, t.id_empleado FROM vuelos v
    LEFT JOIN tripulaciones t
        ON v.id_vuelo = t.id_vuelo;

SELECT ' ';
SELECT '***********************************************************';
SELECT '***DESAFIO FINAL***';
SELECT p.nombre, p.apellido, v.fecha_vuelo, b.precio, a.modelo FROM pasajeros p
    INNER JOIN boletos b
        ON p.id_pasajero = b.id_pasajero
    INNER JOIN vuelos v
        ON b.id_vuelo = v.id_vuelo
    INNER JOIN aviones a
        ON v.id_avion = a.id_avion
    ORDER BY v.fecha_vuelo ASC;