1. Engineering
  2. Computer Science
  3. please show me how to write question 35 in sql...

Question: please show me how to write question 35 in sql...

Question details

Please show me how to write question 3-5 in SQL using Microsoft SQL Server Management Studio.

1) Create a new database called INFO3410_HW_XX, where XX are your initials. This will be your homework database for the rest of this semester. Comment the code you used to make this database as STEP 1.

2) Run the following SQL script in your HW database (Place this in your SQL script and comment it as STEP 2):

-- An airline is a commercial company offering flights to the public
CREATE TABLE Airlines (
AirlineID INTEGER PRIMARY KEY IDENTITY(1,1),
AirlineName VARCHAR(20)
);
GO

-- One airline can fly many types of planes
CREATE TABLE Planes (
PlaneID INTEGER PRIMARY KEY IDENTITY(100,1),
Manufacturer VARCHAR(20),
ModelNum VARCHAR(25),
PurchaseDate DATE,
LastService DATE,
AirlineID INTEGER REFERENCES Airlines(AirlineID),
NumSeats INTEGER
);
GO

-- One airport facilitates many flights
CREATE TABLE Airports (
AirportCode VARCHAR(3) PRIMARY KEY,
City VARCHAR(25),
YearOpened INTEGER,
NumTerminals INTEGER
);
GO

-- Each flight involves many airports
CREATE TABLE Flights (
FlightID INTEGER PRIMARY KEY IDENTITY(1000,1),
PlaneID INTEGER REFERENCES Planes(PlaneID),
AirlineID INTEGER REFERENCES Airlines(AirlineID),
AirCodeOrig VARCHAR(3) REFERENCES Airports(AirportCode),
AirCodeDestn VARCHAR(3) REFERENCES Airports(AirportCode),
DepartDateTime DATETIME,
ArriveDateTime DATETIME
);
GO

3) Write the necessary SQL to add the following data (comment these SQL commands as STEP 3):
- Alaska Airlines, PanAm Airlines
- Alaska Airlines owns a Embraer EJ900 (purchased 3/15/2016 and serviced on 8/15/2018) and a Bombardier CRJ700 (purchased 12/1/2010 and serviced 7/30/2018); number of seats unknown at this time
- PanAm owns a Hughes Aircraft Spruce Goose (purchased 4/12/1951 and serviced on 8/21/2018)
- Dallas Love Field (DAL) opened in 1913 and has one terminal
- Portland International Airport (PDX) opened in 1940 and has three terminals
- Charles Lindbergh VII will pilot a flight for Alaska Airlines that will leave Love Field at 7:50 AM on October 1st, 2018 and arrive in Portland that same day at 10:47 AM. He will pilot the CRJ700, because it is a prop plane, and he doesn't know how to fly jets.
- Amelia Earhart IX piloted the Spruce Goose for PanAm from Portland to Dallas, leaving on 8/12/2018 at 10:52 PM and arriving on 8/13/2018 at 1:08 AM.

4) PanAm is out of business. Alaska Airlines is buying all of PanAm's planes, and assuming ownership of all PanAm flights, past, present and future. Write SQL that would transfer ownership of PanAm's aircraft, and SQL to transfer ownership of all PanAm's flights to Alaska Air. These SQL statements must be written so that they would transfer all planes and flights, regardless of how many there are, or could be. Comment this code as STEP 4.

5) Write a statement to wipe PanAm from the face of your database, as if it never existed. Comment this as STEP 5.

Solution by an expert tutor
Blurred Solution
This question has been solved
Subscribe to see this solution