[DB] select.sql

--; \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}