2020-21 19MCAL38 1NZ19MCA08
Exercise 2: Airline Database
The following relations keep track of airline flight information:
Flights (no: integer, from: string, to: string, distance: integer, Departs: time, arrives: time,
price: real)
Aircraft (aid: integer, aname: string, cruisingrange: integer)
Certified (eid: integer, aid: integer)
Employees (eid: integer, ename: string, salary: integer)
Note that the Employees relation describes pilots and other kinds of employees as well; Every
pilot is certified for some aircraft, and only pilots are certified to fly.
Write each of the following queries in SQL.
i. Find the names of aircraft such that all pilots certified to operate them have salaries
more than Rs.80, 000.
ii. Find the names of pilots whose salary is less than the price of the cheapest route from
Bengaluru to Frankfurt.
iii. Find the names of pilots certified for some Boeing aircraft.
iv. Find the aids of all aircraft that can be used on routes from Bengaluru to New Delhi.
SQL>create table flights(no number(5) primary key, from1 varchar2(20),to1 varchar2(20),distance
number(5),departs varchar2(10),arrives varchar2(10),price number(8));
SQl>create table aircraft(aid number(5) primary key,aname varchar2(20),crusingrange number(5));
SQl>create table employ(eid number(5) primary key,ename varchar2(20),salary number(7));
SQL>create table certified(eid number(5) references employ(eid),aid numbe(5) references
aircraft(aid));
SQL>insert into flights values(&no,’&from1’,’&to1’,&distance,’&departs’,’&arrives’,&price);
SQL>insert into aircraft values(&aid,’&aname’,&cruisingrange);
SQL>insert into employees values(&eid,’&ename’,&salary);
SQL>insert into certified values(&eid,&aid);
PAGE-4
2020-21 19MCAL38 1NZ19MCA08
SQL> select * from flights;
NO FROM1 TO1 DISTANCE DEPARTS FROM1 TO1
100 bangalore frankfurt 1000 9am bangalorefrankfurt
101 bangalore newdelhi 1200 7am bangalorenewdelhi
103 bangalore frankfurt 6000 1am bangalorefrankfurt
102 newdelhi bangalore 1200 1am newdelhi bangalore
SQL> select * from aircraft;
AID ANAME CRUSINGRANGE
400 boeing 2000
401 qatar 2500
403 boeing 7500
402 spicejet 5500
404 kingfisher 4500
SQL>select * from certified;
EID AID
203 400
200 403
203 403
201 404
201 402
204 401
200 401
203 402
204 400
SQL>select * from employees;
EID ENAME SALARY
200 john 80000
201 kim 95000
202 jack 100000
203 Tomas 78000
204 jill 80000
PAGE-5
2020-21 19MCAL38 1NZ19MCA08
1 Find the names of aircraft such that all pilots certified to operate them have salaries more
than Rs.80, 000
select aname from aircraft where aid in(select c.aid from certified c, employees e where c.eid=e.eid
and e.salary>80000)
ANAME
Kingfisher
Spicejet
2 Find the names of pilots whose salary is less than the price of the cheapest route from
Bengaluru to Frankfurt
select distinct e.ename from employees e,certified c where e.eid=c.eid and salary<(select
min(price)from flights where from1 like 'bangalore' and to1 like'frankfurt')
ENAME
Tomas
3 Find the names of pilots certified for some Boeing aircraft.
select distinct ename from employees e,certified c,aircraft a where c.eid=e.eid and a.aid=c.aid and
aname='boeing';
ENAME
John
Jill
Tomas
4 Find the aids of all aircraft that can be used on routes from Bengaluru to New Delhi.
select aid from aircraft a where crusingrange>(select max(distance)from flights where
from1='bangalore' and to1='newdelhi');
AID
400
401
402
403
404
PAGE-6
