DROP TABLE Note;

CREATE TABLE Note(
  mat INTEGER,
  fach VARCHAR(4),
  wert DECIMAL(2,1)
);

INSERT INTO Note VALUES(42,'DB',1.3);
INSERT INTO Note VALUES(42,'Ma1',2.7);
INSERT INTO Note VALUES(43,'DB',3);
INSERT INTO Note VALUES(43,'Ma1',5);
INSERT INTO Note VALUES(44,'Ma1',3);
INSERT INTO Note VALUES(45,'DB',1.3);
INSERT INTO Note VALUES(45,'Ma1',2.7);

/* Gib die Durchschnittsnoten aller Studis */
SELECT Note.Mat, AVG(Note.Wert)
  FROM Note
  GROUP BY Note.Mat;




/* Gib die Durchschnittsnoten der bestandenen Pruefungen aller Studis */
SELECT Note.Mat, AVG(Note.Wert)
  FROM Note
  WHERE Note.Wert < 5.0
  GROUP BY Note.Mat;






/* Gib pro Fach die Durchschnittsnote und die Anzahl der Pruefungen */
SELECT Note.Fach, COUNT(*) Anz, AVG(Note.Wert) Av, MIN(Note.Wert), MAX(Note.Wert)
  FROM Note
  GROUP BY Note.Fach;






/* Gib pro Fach die Durchschnittsnote, die Anzahl der Pruefungen
   und die Anzahl der durchgefallenen Pruefungen */
SELECT A.Fach, A. av, A.Alle, B.Ko  
FROM (SELECT Note.Fach, AVG(Note.Wert) av, COUNT(*) Alle
  FROM Note
  GROUP BY Note.Fach) A
 ,
     (SELECT Note.Fach, COUNT(*) KO
  FROM Note
  WHERE Note.Wert = 5.0
  GROUP BY Note.Fach
UNION
SELECT Note.Fach, 0 KO
  FROM Note
  WHERE NOT EXISTS ( SELECT *
                       FROM Note N
                       WHERE Note.Fach = N.Fach
                         AND N.Wert = 5.0)) B
WHERE A.Fach = B.Fach;                         

--formatiert
SELECT A.Fach, A.av, A.Alle, B.Ko  
  FROM (SELECT Note.Fach, AVG(Note.Wert) av ,COUNT(*) Alle
        FROM Note
        GROUP BY Note.Fach) A
    ,
       (SELECT Note.Fach, COUNT(*) KO
          FROM Note
          WHERE Note.Wert = 5.0
          GROUP BY Note.Fach
        UNION
        SELECT Note.Fach, 0 Ko
          FROM Note
          WHERE NOT EXISTS ( SELECT *
                               FROM Note N
                               WHERE Note.Fach = N.Fach
                                 AND N.Wert = 5.0)) B
  WHERE A.Fach = B.Fach; 

----
SELECT Note.Fach, COUNT(*) KO
  FROM Note
  WHERE Note.Wert = 5.0
  GROUP BY Note.Fach
UNION
SELECT Note.Fach, 0 KO
  FROM Note
  WHERE NOT EXISTS ( SELECT *
                       FROM Note N
                       WHERE Note.Fach = N.Fach
                         AND N.Wert = 5.0);
   


