All are based on the following airline database schema Fligh

All are based on the following airline database schema:

Flights(flno, from, to, distance, departs)

Aircraft(aid, aname, range)

Certified(eid, aid)

Employees(eid, ename, salary)

By definition, pilots are those employees who are certified on at least one aircraft. An aircraft can be used for any flight provided it has sufficient range. Pilots can pilot any flight provided they are certified on an aircraft with sufficient range.

Consider the following conditions in your instantiation:

A. Design a table with 10 tuples (rows) For each of four tables provided in airline data base schema.

B. Fill in tables with random values. (Values should be meaningful)

C. Tables that have common columns (e.g. ‘aid’), should have at least 4 common values for that column.

D. Based on your original tables, create result table for each operation.

PROBLEMS:

1. Find flno of flights that can be piloted by every pilot whose salary is over $100,000.

2. Solve problem 1 without using the division operator.

3. Find names of pilots who can operate planes with a range greater than 3,000 miles, but are not certified on any Boeing.

4. Find eid of employee(s) with the highest salary.

5. Find eid of employee(s) with the second highest salary.

THANK YOU

Solution

create table employee(eid varchar(20) primary key, ename varchar(20), salary integer(10));

create table aircraft(aid varchar(20) primary key, aname varchar(20), range_aircraft integer(10));

create table certified(eid varchar(20) references employee(eid), aid varchar(20) references aircraft(aid));

create table flights(flno varchar(10), from_origin varchar(20), to_destination varchar(20), departs varchar(10));

insert into employee values(\'E001\',\'Williamson\',120000);
insert into employee values(\'E002\',\'Taylor\',150000);
insert into employee values(\'E003\',\'Boult\',100000);
insert into employee values(\'E004\',\'McCullum\',220000);
insert into employee values(\'E005\',\'Guptill\',110000);
insert into employee values(\'E006\',\'Ponting\',100000);
insert into employee values(\'E007\',\'Smith\',150000);
insert into employee values(\'E008\',\'Watson\',120000);
insert into employee values(\'E009\',\'Bracewell\',120000);
insert into employee values(\'E010\',\'Lee\',120000);

insert into table aircraft values(\'A001\',\'Airbus100\',3500);
insert into table aircraft values(\'A002\',\'Airbus13\',4000);
insert into table aircraft values(\'A003\',\'Tupoloev\',3500);
insert into table aircraft values(\'A004\',\'Airbus155\',3500);
insert into table aircraft values(\'A005\',\'Boeing787\',3500);
insert into table aircraft values(\'A006\',\'Boeing123\',4500);
insert into table aircraft values(\'A007\',\'Embraer12\',5000);
insert into table aircraft values(\'A008\',\'Embraer456\',3000);
insert into table aircraft values(\'A009\',\'Bombardier100\',3000);
insert into table aircraft values(\'A010\',\'Bombardier200\',3500);

insert into certified values(\'E001\',\'A005\');
insert into certified values(\'E001\',\'A001\');
insert into certified values(\'E003\',\'A002\');
insert into certified values(\'E003\',\'A003\');
insert into certified values(\'E003\',\'A004\');
insert into certified values(\'E004\',\'A006\');
insert into certified values(\'E005\',\'A007\');
insert into certified values(\'E006\',\'A007\');
insert into certified values(\'E007\',\'A009\');
insert into certified values(\'E008\',\'A010\');

insert into flights values(\'AI-651\',\'Mumbai\',\'Visakhapatnam\',\'7:30AM\');
insert into flights values(\'SG-756\',\'Chennai\',\'Mumbai\',\'8:00AM\');
insert into flights values(\'AI-652\',\'Mumbai\',\'Delhi\',\'8:30AM\');
insert into flights values(\'JE-123\',\'Mumbai\',\'Bangalore\',\'7:30AM\');
insert into flights values(\'IG-456\',\'Bangalore\',\'Visakhapatnam\',\'7:30AM\');
insert into flights values(\'GA-333\',\'Goa\',\'Visakhapatnam\',\'7:30AM\');
insert into flights values(\'SG-122\',\'Delhi\',\'Kolkata\',\'9:00PM\');
insert into flights values(\'AI-653\',\'Mumbai\',\'Raipur\',\'7:30AM\');
insert into flights values(\'AI-654\',\'Mumbai\',\'Cochin\',\'7:30AM\');
insert into flights values(\'AI-655\',\'Mumbai\',\'Bhubhaneshwar\',\'7:30AM\');

1,2 Cannot be solved as there is no relation between certified table and flights table.

4. SELECT eid from employee e1 WHERE 1 -1 = (SELECT COUNT( DISTINCT salary ) FROM employee e2 WHERE e2.salary > e1.salary ) ;

5. SELECT eid from employee e1 WHERE 2 -1 = (SELECT COUNT( DISTINCT salary ) FROM employee e2 WHERE e2.salary > e1.salary )