CÂU HỎI TEST SQL DATA ANALYST VÀ CÁCH LÀM Ở ẢNH

Chào cả nhà, mình đang có bài tập SQL này khó quá nhờ mọi người giúp

Đề bài: Imagine you are asked to design a simple database model for an airport. You should accommodate for:

- passengers, their personal and booking information

- airplanes, their cargo and their destinations

- all available destinations and their distances

Please create or sketch the relational model of the database structure, including the relationships. Feel free to insert an image below. Write SQL queries for the designed database which will return:

- All people on the same flight

- All planes on the same destination

If the sum of all cargo weight on the plane is below a threshold

Mình tạo database model như sau:

-- Create tables for passengers, flights, and airplanes

CREATE TABLE passenger (

passenger_id INT PRIMARY KEY,

name VARCHAR(255) NOT NULL,

email VARCHAR(255) NOT NULL,

phone VARCHAR(255) NOT NULL

);

CREATE TABLE airplane (

flight_id INT PRIMARY KEY,

model VARCHAR(255) NOT NULL,

capacity INT NOT NULL,

cargo INT NOT NULL

);

CREATE TABLE flight (

flight_id INT PRIMARY KEY,

passenger_id INT NOT NULL,

airplane_id INT NOT NULL,

destination_id INT NOT NULL,

date DATE NOT NULL,

FOREIGN KEY (passenger_id) REFERENCES passenger(passentger_id),

FOREIGN KEY (airplane_id) REFERENCES airplane(airplane_id)

);

Xin hỏi làm thế nào để return:

- All people on the same flight

- All planes on the same destination