Question

FLIGHT COMPANY
-PILOT (Pilot No; Pilot-name;Addr; Salary);
- FLIGHT (Flight-ID; Flight-name; H-Dep; D-Arr; Pilot-ID; Plane-ID; City-Dep; City-Arr);
- PLANE (Plane-ID; Pilot-name; Address);
Questions
1. What are the pilots names that are domicile in Lusaka and their whose salary greater than k15000
2. What are the names of the planes that are located in Ndola whose capacity is greater 100 passengers
3. What are the names and IDs of pilots staying in the same city as pilot Benard
4. What are IDs of flights that are captained by plane not located in Lusaka
5. Is there any hormones among the pilot if yes give their IDs and the name

Answer 1

Answers:
1. SELECT Pilot-name
FROM PILOT
WHERE Addr = 'Lusaka' AND Salary > 15000;

2. SELECT Plane-ID, Pilot-name
FROM PLANE
WHERE Address = 'Ndola' AND Capacity > 100;

3. SELECT Pilot-ID, Pilot-name
FROM PILOT
WHERE Addr IN (SELECT Addr FROM PILOT WHERE Pilot-name = 'Benard');

4. SELECT Flight-ID
FROM FLIGHT
WHERE Plane-ID NOT IN (SELECT Plane-ID FROM PLANE WHERE Address = 'Lusaka');

5. SELECT Pilot-No, Pilot-name
FROM PILOT
WHERE Pilot-name LIKE '%hormones%';