Please find attached a lab on database management systems. It is on sql queries. All the details are in the word document.

Thanks

Instruction
Imagine that you are hired to design a new database to support this system. In lab 5, you have
created and populated the tables. Now you need to make a few changes to the table structure.
You also need to write queries to answer some data questions. Please write SQL statements to
finish the following tasks:
1. Alter table “AircraftSpecs” to add one more columns for number of cabins. Name the
column as “cabinNumber”. The column data type is INT, and it cannot be null.
2. Update information in table “AircraftSpecs”. For planes whose “fuelCapacity” is
63705, change their “fuelCapacity” to be 73608.
3. Simple data questions
a) Find out all flight schedule information of flight “3312”. Make sure to show all
the fields.
b) Find all flights departing from JFK airport. Show flight number, arrival airport,
and depart time and the arrival time.
c) Find all planes purchased in the year 2012. Show all the information.
d) Find all flights that are of status “On Time”. Show flight number, flight date, and
delay arrive time only.
e) Find all aircraft types whose number of seats is more than 200. Show aircraft
version only. 4. Use aggregate functions to answer data questions
a) Count the number of flights departing each day. Show the date and the number of
flights.
b) Sort AircraftSpecs table by fuel capacity in ascending order. Show the result with
aircraft version and fuel capacity.
c) Calculate the average, min, and max value of cabin seats numbers for aircraft
spec. Show average seat numbers, min seat numbers, and max seat numbers.
d) Show the average fuel capacity of airplanes from Boeing and Airbus separately.
Hint: airplanes which are created by Airbus has a aircraftType ID starting with
“AIR”, while Boeing’s aircrafts’ aircraftTypeID starting with “BOE”.
5. Open ended Questions:
From the given ERD, draft and then answer two data questions that you consider
yield significant results. The only requirement for the query that you write for your
data question is that it has to have GROUP BY clause as part of it. You can also choose from one of the aggregate functions such as SUM, COUNT, AVG or other
functions thought in class.
Remember to first write your data question in plain English and then write the SQL
query and execute the same. Paste screenshots of the Query with the result. Submission instruction
After each question, copy and paste your SQL statement, followed by the screenshot to show
that your SQL statement has been successfully executed. Remember to add comments to your
SQL statements to explain the purpose of the code blocks.

• 1493270825-DBMS.docx

ComputerScienceExpert

(11)

Status NEW Posted 27 Apr 2017 05:04 AM My Price 11.00

-----------

Attachments

file 1493270827-Solutions file 2.docx preview (51 words )

H-----------ell-----------o S-----------ir/-----------Mad-----------am ----------- Th-----------ank----------- yo-----------u f-----------or -----------you-----------r i-----------nte-----------res-----------t a-----------nd -----------buy-----------ing----------- my----------- po-----------ste-----------d s-----------olu-----------tio-----------n. -----------Ple-----------ase----------- pi-----------ng -----------me -----------on -----------cha-----------t I----------- am----------- on-----------lin-----------e o-----------r i-----------nbo-----------x m-----------e a----------- me-----------ssa-----------ge -----------I w-----------ill----------- be----------- qu-----------ick-----------ly -----------onl-----------ine----------- an-----------d g-----------ive----------- yo-----------u e-----------xac-----------t f-----------ile----------- an-----------d t-----------he -----------sam-----------e f-----------ile----------- is----------- al-----------so -----------sen-----------t t-----------o y-----------our----------- em-----------ail----------- th-----------at -----------is -----------reg-----------ist-----------ere-----------d o-----------n -----------THI-----------S W-----------EBS-----------ITE-----------. ----------- Th-----------ank----------- yo-----------u -----------

Not Rated(0)

• Buy Answer