DATABASE MANAGEMENT SYSTEMS LABORATORY 10CSL57 VTU prg 2

The Eduladder is a community of students, teachers, and programmers just interested to make you pass any exams. So we solve previous year question papers for you.
See Our team
Wondering how we keep quality?
Got unsolved questions?

Ask Questions


You are here:Open notes-->VTU-->DATABASE-MANAGEMENT-SYSTEMS-LABORATORY-10CSL57-VTU-prg-2

DATABASE MANAGEMENT SYSTEMS LABORATORY [10CSL57] VTU prg-2

2. 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. For each pilot who is certified for more than three aircrafts, find the eid and the maximum cruisingrange of the aircraft for which she or he is certified.
iii. Find the names of pilots whose salary is less than the price of the cheapest route from Bengaluru to Frankfurt.
iv. For all aircraft with cruisingrange over 1000 Kms,. Find the name of the aircraft and the average salary of all pilots certified for this aircraft.
v. Find the names of pilots certified for some Boeing aircraft.
vi. Find the aids of all aircraft that can be used on routes from Bengaluru to New Delhi.


2. The following relations keep track of airline flight information:
Flights (flno: 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.

CREATE TABLE FLIGHTS
(FLNO INTEGER PRIMARY KEY,
FFROM VARCHAR(15) NOT NULL,
TTO VARCHAR(15) NOT NULL,
DISTANCE INTEGER,
DEPARTS TIMESTAMP,
ARRIVES TIMESTAMP,
PRICE NUMBER(10,2));
CREATE TABLE AIRCRAFT
(AID INTEGER PRIMARY KEY,
ANAME VARCHAR(10),
CRUISINGRANGE INTEGER);
CREATE TABLE EMPLOYEES
(EID INTEGER PRIMARY KEY,
ENAME VARCHAR(15),
SALARY NUMBER(10,2));
CREATE TABLE CERTIFIED
(EID INTEGER NOT NULL,
AID INTEGER NOT NULL,
PRIMARY KEY (EID, AID),
FOREIGN KEY (EID) REFERENCES EMPLOYEES (EID),
FOREIGN KEY (AID) REFERENCES AIRCRAFT (AID));
INSERT INTO FLIGHTS VALUES (&FLNO, ‘&FFROM’, ‘&TTO’, &DISTANCE, ‘&DEPARTS’, ‘&ARRIVES’, &PRICE);
INSERT INTO AIRCRAFT VALUES (&AID, ‘&ANAME’, &CRUISRANGE);
INSERT INTO EMPLOYEES VALUES (&EID, ‘&ENAME’, &SALARY);
INSERT INTO CERTIFIED VALUES (&EID, &AID);
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.

SELECT DISTINCT A.ANAME
FROM AIRCRAFT A
WHERE A.AID IN (SELECT C.AID
FROM CERTIFIED C, EMPLOYEES E
WHERE C.EID = E.EID AND
NOT EXISTS (SELECT *
FROM EMPLOYEES E1
WHERE E1.EID = E.EID AND E1.SALARY < 80000));

ii) For each pilot who is certified for more than three aircrafts, find the eid and the maximum cruisingrange of the aircraft for which she or he is certified.

SELECT C.EID, MAX (A.CRUISINGRANGE)
FROM CERTIFIED C, AIRCRAFT A
WHERE C.AID = A.AID
GROUP BY C.EID
HAVING COUNT (*) > 3;
iii) Find the names of pilots whose salary is less than the price of the cheapest route from Bengaluru to Frankfurt.
SELECT DISTINCT E.ANAME
FROM EMPLOYEE E
WHERE E.SALARY < ( SELECT MIN (F.PRICE)
FROM FLIGHTS F
WHERE F.FFROM = ‘Bengaluru’ AND F.TTO = ‘Frankfurt’);

iv) For all aircraft with cruisingrange over 1000 Kms,. Find the name of the aircraft and the average salary of all pilots certified for this aircraft.

Observe that aid is the key for Aircraft, but the question asks for aircraft names; we deal with this complication by using an intermediate relation Temp;
SELECT TEMP.NAME, TEMP.AVGSALARY
FROM (SELECT A.AID, A.ANAME AS NAME,
AVG (E.SALARY) AS AVGSALARY
FROM AIRCRAFT A, CERTIFIED C, EMPLOYEES E
WHERE A.AID = C.AID AND
C.EID = E.EID AND A.CRUISINGRANGE > 1000
GROUP BY A.AID, A.ANAME) AS TEMP;
v) Find the names of pilots certified for some Boeing aircraft.
SELECT DISTINCT E.ENAME
FROM EMPLOYEES E, CERTIFIED C, AIRCRAFT A
WHERE E.EID = C.EID AND
C.AID = A.AID AND
A.ANAME = ‘Boeing’;

vi) Find the aids of all aircraft that can be used on routes from Bengaluru to New Delhi.

SELECT A.AID
FROM AIRCRAFT A
WHERE A.CRUISINGRANGE > (SELECT MIN (F.DISTANCE)
FROM FLIGHTS F
WHERE F.FFROM = ‘Bengaluru’ AND F.TTO = ‘New Delhi’);

Editors




Join eduladder!