Insert the following records into the tables created before: Airplane - (\'AC911
ID: 3756131 • Letter: I
Question
Insert the following records into the tables created before:
Airplane - ('AC911','Boeing 747', '2001-01-25') ; ('WJ455', 'Airbus A380', '2008-11-15')
Airport - ('YLW','Kelowna Airport','Kelowna','British Columbia', 'Canada') ; ('YWG','Winnipeg Airport','Winnipeg','Manitoba', 'Canada')
Flight - ('AC35', 'YLW', 'YWG', 'AC911', '2009-03-14 07:00:00', '2009-03-14 15:00:00', 2009-03-14 07:05:00', '2009-03-14 15:30:00')
('WJ111', 'YWG', 'YLW', 'WJ455', '2009-03-15 10:00:00', '2009-03-15 12:00:00', 2009-03-15 09:55:00', '2009-03-14 11:49:55')
Passenger - (1, 'Joe', 'Smith', '1970-12-15', '1350 Springfield Road', 'Kelowna', 'British Columbia', 'Canada')
(2, 'Fred', 'Brothers', '1950-01-02', '22 Pembina Highway', 'Winnipeg', 'Manitoba', 'Canada')
OnFlight - (1, 'AC35', '2009-03-14 07:00:00', '1A')
(1, 'WJ111', '2009-03-15 10:00:00', '10C')
(2, 'AC35', '2009-03-14 07:00:00', '2A')
(2, 'WJ111', '2009-03-15 10:00:00', '10D')
Then,
Delay all flights that depart Kelowna airport by 1 hour. (advance actual departure time) Note: To add an hour to a date use the syntax DATE_ADD(your date field, INTERVAL 1 HOUR).
Finally,
Delete all on flight records for 'Fred Brothers'.
Here's the tables for you:
1)
CREATE TABLE Airplane
{
Airplane_ID STRING(10) ,
Model STRING(20),
Manufacture_date DATE,
PRIMARY KEY(Airplane_ID),
};
2) CREATE TABLE Airport
{
Airport_ID VARCHAR(5) PRIMARY KEY,
Name VARCHAR(30),
City VARCHAR(40),
Province VARCHAR(20),
Country VARCHAR(20),
PRIMARY KEY(Airport_ID),
};
3)CREATE TABLE Flight
{
Airplane_ID STRING(10) REFERENCES Airplane(Airplane_ID) ON DELETE SET NULL [ON UPDATE CASCADE],
Flight_Number INT(5),
Departing_From VARCHAR(20),
Departing_To VARCHAR(20),
Expected_Departure DATETIME,
Expected_Arrival DATETIME,
Actual_Departure DATETIME,
Actual__Arrival DATETIME,
PRIMARY KEY(Flight_Number,Actual_Departure),
};
4) CREATE TABLE Passenger
{Passenger_ID INT(20),
First_Name VARCHAR(30),
Last_Name VARCHAR(30),
Birth_Date DATE,
Street VARCHAR(50),
City VARCHAR(40),
Province VARCHAR(20),
Country VARCHAR(20),
PRIMARY KEY(Passenger_ID),
};
5) CREATE TABLE OnFlight
{
Passenger_ID INT(20) REFERENCES Passenger(Passenger_ID) ON DELETE NO ACTION [ON UPDATE CASCADE ,
Flight_Number INT(5) REFERENCES Flight(Flight_Number) ON DELETE NO ACTION [ON UPDATE CASCADE ,
Seat_Number VARCHAR(4),
};
Thank you very much.
Explanation / Answer
If you have any doubts, please give me comment...
INSERT INTO Airplane VALUES ('AC911','Boeing 747', '2001-01-25'), ('WJ455', 'Airbus A380', '2008-11-15');
INSERT INTO Airport VALUES ('YLW','Kelowna Airport','Kelowna','British Columbia', 'Canada'), ('YWG','Winnipeg Airport','Winnipeg','Manitoba', 'Canada');
INSERT INTO Flight VALUES('AC35', 'YLW', 'YWG', 'AC911', '2009-03-14 07:00:00', '2009-03-14 15:00:00', '2009-03-14 07:05:00', '2009-03-14 15:30:00'), ('WJ111', 'YWG', 'YLW', 'WJ455', '2009-03-15 10:00:00', '2009-03-15 12:00:00', '2009-03-15 09:55:00', '2009-03-14 11:49:55');
INSERT INTO Passenger VALUES (1, 'Joe', 'Smith', '1970-12-15', '1350 Springfield Road', 'Kelowna', 'British Columbia', 'Canada'), (2, 'Fred', 'Brothers', '1950-01-02', '22 Pembina Highway', 'Winnipeg', 'Manitoba', 'Canada');
INSERT INTO OnFlight VALUES(1, 'AC35', '2009-03-14 07:00:00', '1A'), (1, 'WJ111', '2009-03-15 10:00:00', '10C'), (2, 'AC35', '2009-03-14 07:00:00', '2A'), (2, 'WJ111', '2009-03-15 10:00:00', '10D');
UPDATE Flight SET Expected_Departure = DATE_ADD(Expected_Departure, INTERVAL 1 HOUR) WHERE Departing_From = (SELECT Name FROM Airport);
DELETE FROM OnFlight WHERE PassengerID = (SELECT PassengerID FROM Passenger WHERE First_Name = 'Fred' AND Last_Name = 'Brothers');