Dvns

Here is a simpler explanation and approach for the selected SQL tasks:

c) Find aircraft names where all pilots certified to operate them earn more than 80,000

Simplified Query:

SELECT a.aname
FROM Aircraft a
JOIN Certified c ON a.aid = c.aid
JOIN Employees e ON c.eid = e.eid
GROUP BY a.aname
HAVING MIN(e.salary) > 80000;

	•	Explanation:
This groups all pilots certified for each aircraft and ensures the minimum salary among the certified pilots for an aircraft is greater than 80,000.

d) For pilots certified for more than three aircraft, find eid and max cruising range

Simplified Query:

SELECT c.eid, MAX(a.cruisingrange) AS max_cruisingrange
FROM Certified c
JOIN Aircraft a ON c.aid = a.aid
GROUP BY c.eid
HAVING COUNT(c.aid) > 3;

	•	Explanation:
This groups the certifications by eid and checks where the count of certified aircraft exceeds 3. For those pilots, it calculates the maximum cruising range.

e) Find names of pilots whose salary is less than the cheapest route from Los Angeles to Honolulu

Simplified Query:

SELECT e.ename
FROM Employees e
WHERE e.salary < (
    SELECT MIN(price)
    FROM Flights
    WHERE from_city = 'Los Angeles' AND to_city = 'Honolulu'
);

	•	Explanation:
The query finds the cheapest flight price from Los Angeles to Honolulu, then compares it with the salary of each pilot.

f) Find the second-highest salary of an employee

Simplified Query:

SELECT MAX(salary) AS second_highest_salary
FROM Employees
WHERE salary < (SELECT MAX(salary) FROM Employees);

	•	Explanation:
First, find the highest salary, then find the maximum salary below it.

g) Create a stored procedure to remove all employees

Simplified Procedure:

CREATE PROCEDURE RemoveAllEmployees()
BEGIN
    DELETE FROM Employees;
END;

	•	Explanation:
This deletes all rows in the Employees table. It can be called using:

CALL RemoveAllEmployees();

These simplified queries aim to make the logic clearer while maintaining functionality. Let me know if you’d like further assistance!