USE air;
GO

--Identify the flights that can be piloted by every pilot whose salary is more than $100,000.

USE air
SELECT
 f.flno , COUNT(DISTINCT eid), origin, destination
FROM
 aircraft a JOIN certified c ON a.aid=c.aid JOIN flights f ON a.cruisingrange>=f.distance
WHERE
 eid IN (SELECT e.eid FROM employees e JOIN certified c ON e.eid=c.eid WHERE salary > 200000)
GROUP BY
 f.flno, origin, destination
HAVING
 COUNT(DISTINCT eid) = (SELECT COUNT(DISTINCT e.eid)
    FROM employees e JOIN certified c ON e.eid=c.eid WHERE salary > 200000)

GO


DECLARE @salary NUMERIC(10,2) = 200000;
SELECT DISTINCT
 F.flno, F.origin, F.destination, F.distance,
 --A.aname, a.cruisingrange
 COUNT(DISTINCT e.eid) LiczbaPilotow,
 (SELECT COUNT(DISTINCT c1.eid) FROM employees E1 JOIN certified C1 ON e1.eid=c1.eid WHERE salary > @salary) TotalLP
FROM flights F JOIN aircraft A ON F.distance < A.cruisingrange
 JOIN certified C ON a.aid=c.aid
 JOIN employees E ON c.eid=e.eid
WHERE e.salary > @salary
GROUP BY F.flno, F.origin, F.destination, F.distance
GO


SELECT
 *
FROM
 employees e JOIN certified c ON e.eid=c.eid JOIN aircraft a ON a.aid=c.aid
WHERE
 cruisingrange>3000
 AND e.eid NOT IN (
 SELECT e.eid FROM employees e JOIN certified c ON e.eid=c.eid JOIN aircraft a ON a.aid=c.aid WHERE aname LIKE '%boeing%'
 )
GO



SELECT e.eid
FROM
 employees e JOIN certified c ON e.eid=c.eid
GROUP BY
 e.eid
HAVING COUNT(aid)=
     (SELECT top 1 COUNT(aid)
     FROM
      employees e JOIN certified c ON e.eid=c.eid
     GROUP BY
      e.eid
     ORDER BY COUNT(aid) DESC)


SELECT SUM(employees.salary) FROM employees



SELECT eid FROM (
 SELECT
  eid, COUNT(aid) lc
 FROM
  certified
 GROUP BY eid
) pp WHERE pp.lc=(SELECT MAX(lc) FROM ( SELECT
  eid, COUNT(aid) lc
 FROM
  certified
 GROUP BY eid
) pp2);

SELECT aid FROM aircraft
EXCEPT
SELECT DISTINCT aid FROM certified