Coke

Below is the SQL solution for the given airline schema with complete explanations for each query:

a) Create Tables with Primary Keys and Foreign Keys

CREATE TABLE Flights (
    flno INTEGER PRIMARY KEY,
    from_city VARCHAR(50),
    to_city VARCHAR(50),
    distance INTEGER,
    departs TIME,
    arrives TIME,
    price INTEGER
);

CREATE TABLE Aircraft (
    aid INTEGER PRIMARY KEY,
    aname VARCHAR(50),
    cruisingrange INTEGER
);

CREATE TABLE Employees (
    eid INTEGER PRIMARY KEY,
    ename VARCHAR(50),
    salary INTEGER
);

CREATE TABLE Certified (
    eid INTEGER,
    aid INTEGER,
    PRIMARY KEY (eid, aid),
    FOREIGN KEY (eid) REFERENCES Employees(eid),
    FOREIGN KEY (aid) REFERENCES Aircraft(aid)
);

b) Insert Around 10 Records into Each Table

-- Insert records into Flights
INSERT INTO Flights VALUES 
(101, 'Los Angeles', 'New York', 2500, '08:00:00', '16:00:00', 500),
(102, 'Los Angeles', 'Honolulu', 2600, '09:00:00', '15:00:00', 700),
(103, 'Chicago', 'Miami', 1500, '10:00:00', '14:00:00', 300),
(104, 'New York', 'London', 3500, '20:00:00', '08:00:00', 1000),
(105, 'Seattle', 'San Francisco', 800, '06:00:00', '09:00:00', 200),
(106, 'Boston', 'Los Angeles', 2700, '11:00:00', '19:00:00', 550),
(107, 'Los Angeles', 'Tokyo', 5500, '13:00:00', '06:00:00', 1200),
(108, 'Dallas', 'Chicago', 900, '12:00:00', '14:00:00', 250),
(109, 'Honolulu', 'San Francisco', 2400, '15:00:00', '21:00:00', 650),
(110, 'Miami', 'Seattle', 3300, '16:00:00', '22:00:00', 800);

-- Insert records into Aircraft
INSERT INTO Aircraft VALUES 
(1, 'Boeing 747', 8000),
(2, 'Airbus A320', 6000),
(3, 'Boeing 737', 4000),
(4, 'Boeing 777', 9000),
(5, 'Cessna 172', 1000),
(6, 'Airbus A380', 8500),
(7, 'Embraer 190', 2500),
(8, 'Boeing 787', 7600),
(9, 'Gulfstream G550', 6700),
(10, 'Bombardier CRJ', 2000);

-- Insert records into Employees
INSERT INTO Employees VALUES 
(1, 'John', 85000),
(2, 'Alice', 60000),
(3, 'Bob', 95000),
(4, 'Charlie', 88000),
(5, 'Diana', 75000),
(6, 'Eve', 82000),
(7, 'Frank', 72000),
(8, 'Grace', 94000),
(9, 'Hank', 98000),
(10, 'Ivy', 77000);

-- Insert records into Certified
INSERT INTO Certified VALUES 
(1, 1), (1, 4), (1, 6),
(2, 3), (2, 5),
(3, 2), (3, 7), (3, 8), (3, 9),
(4, 6), (4, 8),
(5, 3), (5, 4),
(6, 1), (6, 9),
(7, 2), (7, 3), (7, 5),
(8, 10),
(9, 4), (9, 6),
(10, 5), (10, 7);

c) Find Aircraft Names for Which All Pilots Earn More Than 80,000

SELECT a.aname
FROM Aircraft a
JOIN Certified c ON a.aid = c.aid
JOIN Employees e ON c.eid = e.eid
GROUP BY a.aid, a.aname
HAVING MIN(e.salary) > 80000;

d) Find the eid and Maximum Cruising Range for Pilots Certified for More Than 3 Aircraft

SELECT c.eid, MAX(a.cruisingrange) AS max_cruising_range