--; \begin{verbatim}
connect to dbs;

-- 1. a)
SELECT *
FROM flugzeug
WHERE flugzeugnummer IN (
	SELECT flugzeugnummer
	FROM flug
	GROUP BY flugzeugnummer
	HAVING COUNT(flugzeugnummer) > 1
);

-- 1. b)
SELECT *
FROM flugzeug
WHERE flugzeugnummer IN (
	SELECT flugzeugnummer
	FROM zertifiziert, mitarbeiter
	WHERE zertifiziert.personalnummer = mitarbeiter.personalnummer
		AND gehalt > 100
	GROUP BY flugzeugnummer
	HAVING COUNT(flugzeugnummer) = (SELECT COUNT(personalnummer) FROM mitarbeiter WHERE gehalt > 100000)
);

-- 1. c)
SELECT mitarbeiter.*
FROM mitarbeiter, zertifiziert, flugzeug
WHERE mitarbeiter.personalnummer = zertifiziert.personalnummer
	AND zertifiziert.flugzeugnummer = flugzeug.flugzeugnummer
	AND flugzeug.reichweite > 5000
	AND mitarbeiter.personalnummer NOT IN (
		SELECT mitarbeiter.personalnummer
		FROM mitarbeiter, zertifiziert, flugzeug
		WHERE mitarbeiter.personalnummer = zertifiziert.personalnummer
			AND zertifiziert.flugzeugnummer = flugzeug.flugzeugnummer
			AND flugzeug.name LIKE '%Boeing%'
	);

-- 1. d)
SELECT *
FROM mitarbeiter
WHERE gehalt = (
	SELECT MAX(gehalt)
	FROM mitarbeiter
);

-- 1. e)
SELECT *
FROM mitarbeiter
WHERE gehalt = (
	SELECT MAX(m1.gehalt)
	FROM mitarbeiter m1, mitarbeiter m2
	WHERE m1.gehalt < m2.gehalt
);

-- 1. f)
SELECT *
FROM mitarbeiter
WHERE personalnummer IN (
	SELECT personalnummer
	FROM zertifiziert
	GROUP BY personalnummer
	HAVING COUNT(personalnummer) = (
		SELECT MAX(anzahl)
		FROM (
			SELECT COUNT(personalnummer) AS anzahl
			FROM zertifiziert
			GROUP BY personalnummer
		)
	)
);

-- 1. g)
SELECT mitarbeiter.personalnummer, mitarbeiter.name, mitarbeiter.gehalt
FROM mitarbeiter, zertifiziert
WHERE mitarbeiter.personalnummer = zertifiziert.personalnummer
GROUP BY mitarbeiter.personalnummer, mitarbeiter.name, mitarbeiter.gehalt
HAVING COUNT(mitarbeiter.personalnummer) = 3;

-- 1. h)
SELECT SUM(gehalt) AS gesamtGehalt
FROM mitarbeiter;

-- 1. i)
SELECT AVG(gehalt) - (
	SELECT AVG(gehalt)
	FROM mitarbeiter
)
FROM mitarbeiter, zertifiziert
WHERE mitarbeiter.personalnummer = zertifiziert.personalnummer;

-- 1. j)
SELECT CASE
		WHEN COUNT(f1.flugnummer) > 0 THEN 1
		ELSE 0
	END
FROM flug f1, flug f2
WHERE f1.ursprung = 'Hamburg' AND f1.ziel = 'Pisa'
	OR f1.ursprung = 'Hamburg' AND f1.ziel = f2.ursprung AND f2.ziel = 'Pisa';

-- 1. k)
ALTER TABLE flug
ADD preis INT;

--; \end{verbatim}