I need the mysql querry for this problem, that I cannot seem to get right. Below

Question

I need the mysql querry for this problem, that I cannot seem to get right. Below is the database structure.

List the airport code of origin and airport code of destination, the departure and arrival time, the airline name, and plane manufacturer and model for all flights that will occur in the future. (The manufacturer and model may be in two separate fields; they do not need to be concatenated.)

Airlines Database

Primary Key = Plane_ID                                                         

Foreign Key = Carrier_ID references Carriers (Carrier_ID)

Aircraft

Plane_ID

Manufacurer

Model_Num

Original_Purchase_Date

Last_Service

Carrier_ID

Number_of_Seats

Airports

Airport_Code

Airport_Location

Year_Opened

Nuber_of_Terminals

      Primary Key = Airport_code

Carriers

Carrier_ID

Carrier_Name

Primary Key = Carrier_ID       

                                               

Flights

Flight_ID

Plane_ID

Carrier_ID

Airport_Code_Origin

Airport_Code_Destination

Departure_DateTime

Arrival_DateTime

Primary Key = Flight_ID

Foreign Key = Airport_Code_Origin references Airports(Airpot_Code)

Airport_Code_Destination references Airports(Airport_Code)

Aircraft

Plane_ID

Manufacurer

Model_Num

Original_Purchase_Date

Last_Service

Carrier_ID

Number_of_Seats

Explanation / Answer

First I provide Table creation scripts for tables:

CREATE TABLE [dbo].[Aircraft](
   [Plane_ID] [int] NOT NULL,
   [Manufacurer] [varchar](50) NOT NULL,
   [Model_Num] [nvarchar](50) NOT NULL,
   [Original_Purchase_Date] [datetime] NOT NULL,
   [Last_Service] [varchar](max) NOT NULL,
   [Carrier_ID] [int] NOT NULL,
   [Number_of_Seats] [int] NOT NULL,
CONSTRAINT [PK_Aircraft] PRIMARY KEY CLUSTERED
(
   [Plane_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Aircraft] WITH CHECK ADD CONSTRAINT [FK_Aircraft_Carriers] FOREIGN KEY([Carrier_ID])
REFERENCES [dbo].[Carriers] ([Carrier_ID])
GO

ALTER TABLE [dbo].[Aircraft] CHECK CONSTRAINT [FK_Aircraft_Carriers]
GO

2nd Table:

CREATE TABLE [dbo].[Airports](
   [Airport_Code] [int] NOT NULL,
   [Airport_Location] [varchar](50) NOT NULL,
   [Year_Opened] [int] NOT NULL,
   [Number_of_Terminals] [int] NOT NULL,
CONSTRAINT [PK_Airports] PRIMARY KEY CLUSTERED
(
   [Airport_Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

3rd Table:

CREATE TABLE [dbo].[Carriers](
   [Carrier_ID] [int] NOT NULL,
   [Carrier_Name] [varchar](100) NOT NULL,
CONSTRAINT [PK_Carriers] PRIMARY KEY CLUSTERED
(
   [Carrier_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

4th table:

CREATE TABLE [dbo].[Flights](
   [Flight_ID] [int] NOT NULL,
   [Plane_ID] [int] NOT NULL,
   [Carrier_ID] [int] NOT NULL,
   [Airport_Code_Origin] [int] NOT NULL,
   [Airport_Code_Destination] [int] NOT NULL,
   [Departure_DateTime] [datetime] NOT NULL,
   [Arrival_Datetime] [datetime] NOT NULL,
CONSTRAINT [PK_Flights] PRIMARY KEY CLUSTERED
(
   [Flight_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Flights] WITH CHECK ADD CONSTRAINT [FK_Flights_Airports] FOREIGN KEY([Airport_Code_Destination])
REFERENCES [dbo].[Airports] ([Airport_Code])
GO

ALTER TABLE [dbo].[Flights] CHECK CONSTRAINT [FK_Flights_Airports]
GO

ALTER TABLE [dbo].[Flights] WITH CHECK ADD CONSTRAINT [FK_Flights_Airports1] FOREIGN KEY([Airport_Code_Origin])
REFERENCES [dbo].[Airports] ([Airport_Code])
GO

ALTER TABLE [dbo].[Flights] CHECK CONSTRAINT [FK_Flights_Airports1]
GO

Query:

select A.Airport_Code_Origin,
A.Airport_code_Destination,
A.departure_DateTime,
A.Arrival_DateTime,
C.Carrier_Name,
B.Manufacurer,
B.Model_Num

from Flights A join Carriers C ON A.Carrier_ID=C.Carrier_ID
join Aircraft B ON b.Plane_ID=A.Plane_ID

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.