USE `luna2_innodb`;

#a
SELECT DISTINCT A_Nr FROM tblmitarbeiter
WHERE M_Nr IN (
   SELECT M_Nr FROM tbltaetigkeit
   WHERE P_Nr = "p3");

#b
SELECT DISTINCT P_Nr FROM tbltaetigkeit
WHERE M_Nr < (
   SELECT MIN(M_Nr) FROM tblmitarbeiter
   WHERE M_Name = "Müller");
   
#c
SELECT * FROM tblmitarbeiter
WHERE A_Nr IN (
   SELECT A_Nr FROM tblabteilung
   WHERE A_Stadt = "München");
   
#d
SELECT M_Name FROM tblmitarbeiter
WHERE M_Nr IN (
   SELECT M_Nr FROM tbltaetigkeit
   WHERE P_Nr IN (
      SELECT P_Nr FROM tblprojekt
      WHERE P_Name = "Apollo"
      )
   );
   
#e
#wat
SELECT MIN(M_Nr) FROM tblmitarbeiter;

#f
SELECT M_Nr, M_Name FROM tblmitarbeiter
WHERE M_Nr = (SELECT MIN(M_Nr) FROM tblmitarbeiter);

#g
SELECT M_Nr FROM tblmitarbeiter
WHERE M_Nr IN (
   SELECT M_Nr FROM tbltaetigkeit
   WHERE T_Taetigkeit = "Projektleiter" AND T_Einstellungsdatum = (
      SELECT MAX(T_Einstellungsdatum) FROM tbltaetigkeit
      WHERE T_Taetigkeit = "Projektleiter"
      )
   );

#h
SELECT SUM(P_Mittel) FROM tblprojekt;

#i
SELECT AVG(P_Mittel) FROM tblprojekt
WHERE P_Mittel > 100000;

#j
SELECT P_Nr, COUNT(DISTINCT T_Taetigkeit) FROM tbltaetigkeit
GROUP BY P_Nr;

#k
SELECT P_Nr, COUNT(DISTINCT M_Nr) FROM tbltaetigkeit
GROUP BY P_Nr;

#l
SELECT T_Taetigkeit, COUNT(DISTINCT M_Nr) FROM tbltaetigkeit
GROUP BY T_Taetigkeit;

#m
SELECT P_Nr FROM tbltaetigkeit
GROUP BY P_Nr
HAVING COUNT(DISTINCT M_Nr) < 4;