Exercise 2
1. Create a new database called
‘Flight_Management’

2. Use SQL Query Analyzer to work with Flight_Management database and create a SQL
script file follow those steps:
1. Create tables and insert data as follows:
Flight
Column Name Data Type Length Allow Nulls
Aircraft_code char 5
Aircode char 2
Type varchar 10
Source varchar 15
Destination varchar 15
Category varchar 15
Dep_time varchar 15 x
Journey_hrs int 4 x
Flight_details
Column Name Data Type Length Allow Nulls
Aircraft_code char 5
Class_code char 2
Fare numeric 5
Seats numeric 5
Flight_days
Column Name Data Type Length Allow Nulls
Aircraft_code char 5
day_code int 4
insert the following data into tables
Flight table
Aircraft_code Aircode Type Source Destination Category Dep_time Journey_hrs
9W01 9W Airbus Cal Che D 15.30 3

2. Display total number of domestic flight (Category=‘D’)
3. Display flight detail has fly at Monday (day_code =2)

4. Create a view named ‘Flight_List’ as following: (the list must sorted by Aircode, Type,
ClassCode, Fare, Daycode)
Aircode Type ClassCode Fare Daycode
9W Airbus Fx 5000 1
9W Airbus Fx 5000 2
9W Airbus Fx 5000 1
AI Boeing Ec 4500 2
AI Boeing Ec 4500 1
5. Modify the view named ‘Flight_List’ such that you can create an index on it. (Hint: Add
‘With SchemaBinding’ option)

6. Create an index named ‘indFlight_List’ on the (‘Aircode,’ ClassCode) column of the
‘Flight_List’ view.
7. Display flight details that has Fare = 5000

8. Drop References Constraint between Flight table and Flight_details
9. Create an Delete Trigger named ‘tgDeleteAll’ on the Flight table to prevent you from
deleting a row has Aircarft_code =’9W01’ and display a notice ‘You can not
delete’
10. Create a stored procedure named ‘spDropFlight’ that accept an Aircode as parameter.
The stored procedure will delete all Flight which has the Aircode like
parameter
Notes: You must ensure that this action will completely.