1. Create the following table. dbo.Flights Columns FlightID (PK, int, not null) FlightDateTime (datetime, null) FlightDepartureCity...

Transcribed Image Text:

1. Create the following table. dbo.Flights Columns FlightID (PK, int, not null) FlightDateTime (datetime, null) FlightDepartureCity (varchar(50), null) FlightDestinationCity (varchar(50), nul Ontime (int, null) a. There should be a Primary Key on FlightID b. There should be an Identity Constraint on FlightID (100,1) a. 100 = Seed b. 1 Increment = 2. Run the following script to Insert Data into the table. INSERT INTO dbo. Flights (FlightDateTime, Flight DepartureCity, Flight DestinationCity, Ontime) SELECT '1/1/2012', 'Dallas-Texas', 'L.A.', 1 UNION SELECT '1/2/2012', 'Austin-Texas', 'New York', 1 UNION SELECT 1/3/2012', 'Houston-Texas', 'New Jersy', 0 UNION SELECT 1/4/2012', 'San Antonio- Texas', 'Mesquite', 1 UNION SELECT 1/5/2012', 'Lewisville- Texas', 'Albany', 0 SELECT UNION 1/6/2012','Orlando- Florida', 'Atlanta', 1 UNION SELECT 1/7/2012', 'Chicago- Illinois', 'Oklahoma City', 1 UNION SELECT 1/8/2012', 'New Orleans- Louisiana', 'Memphis',0 UNION SELECT 1/9/2012', 'Miami- Florida', 'Charlotte', 1 UNION SELECT '1/10/2012', 'Sacramento- California', 'San Francisco', 1 3. Create and set a Variable equal to the number of Flights that were late. 4. Multiply that amount by the amount lost per late flight ($1,029) and store the amount in another variable. 5. Take the total amount lost (#4) and subtract it from Total profit ($45,000) and store that number in a variable. 6. Find out the Earliest FlightDate and add 10 years to it and store it in a variable. 7. Find out the day of the week for the Latest FlightDate and store it in a variable. 8. Create a table variable with Departure City and State in 2 different columns along with Flight Destination City and Ontime. 9. Create a Table Variable storing all info from the dbo.Flights table of flights that were on time. 10. Create a Table Variable storing all info from the dbo.Flights table of non Texas Flights. Run the following Script 1. Create the following table. dbo.Flights Columns FlightID (PK, int, not null) FlightDateTime (datetime, null) FlightDepartureCity (varchar(50), null) FlightDestinationCity (varchar(50), nul Ontime (int, null) a. There should be a Primary Key on FlightID b. There should be an Identity Constraint on FlightID (100,1) a. 100 = Seed b. 1 Increment = 2. Run the following script to Insert Data into the table. INSERT INTO dbo. Flights (FlightDateTime, Flight DepartureCity, Flight DestinationCity, Ontime) SELECT '1/1/2012', 'Dallas-Texas', 'L.A.', 1 UNION SELECT '1/2/2012', 'Austin-Texas', 'New York', 1 UNION SELECT 1/3/2012', 'Houston-Texas', 'New Jersy', 0 UNION SELECT 1/4/2012', 'San Antonio- Texas', 'Mesquite', 1 UNION SELECT 1/5/2012', 'Lewisville- Texas', 'Albany', 0 SELECT UNION 1/6/2012','Orlando- Florida', 'Atlanta', 1 UNION SELECT 1/7/2012', 'Chicago- Illinois', 'Oklahoma City', 1 UNION SELECT 1/8/2012', 'New Orleans- Louisiana', 'Memphis',0 UNION SELECT 1/9/2012', 'Miami- Florida', 'Charlotte', 1 UNION SELECT '1/10/2012', 'Sacramento- California', 'San Francisco', 1 3. Create and set a Variable equal to the number of Flights that were late. 4. Multiply that amount by the amount lost per late flight ($1,029) and store the amount in another variable. 5. Take the total amount lost (#4) and subtract it from Total profit ($45,000) and store that number in a variable. 6. Find out the Earliest FlightDate and add 10 years to it and store it in a variable. 7. Find out the day of the week for the Latest FlightDate and store it in a variable. 8. Create a table variable with Departure City and State in 2 different columns along with Flight Destination City and Ontime. 9. Create a Table Variable storing all info from the dbo.Flights table of flights that were on time. 10. Create a Table Variable storing all info from the dbo.Flights table of non Texas Flights. Run the following Script