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 alle Matrikelnummern, die DB und Ma1 bestanden haben */
SELECT Note.Mat
  FROM Note, Note N2
  WHERE Note.Fach = 'DB'
    AND Note.Wert < 5
    AND N2.Fach = 'Ma1'
    AND N2.Wert < 5
    AND Note.Mat = N2.Mat;

SELECT Note.Mat
  FROM Note, Note N2;

SELECT Note.Mat
  FROM Note
    JOIN Note N2 ON (Note.Mat = N2.Mat)
  WHERE Note.Fach = 'DB'
    AND Note.Wert < 5
    AND N2.Fach = 'Ma1'
    AND N2.Wert < 5;

/* Gib alle Matrikelnummern, die in Ma1 mindestens
   genauso gut wie 42 waren */
SELECT N2.Mat
  FROM Note N, Note N2
  WHERE N.Mat = 42
    AND N.Fach ='Ma1'
    AND N2.Fach ='Ma1'
    AND N2.Wert <= N.Wert
    AND N.Mat <> N2.Mat;
    
SELECT N2.Mat
  FROM Note N
    JOIN Note N2 ON(N2.Wert <= N.Wert AND N.Mat <> N2.Mat)
  WHERE N.Mat = 42
    AND N.Fach ='Ma1'
    AND N2.Fach ='Ma1'
   ;




/* Gib die Durchschnittsnote von 42 aus. */
SELECT AVG(Note.Wert)
  FROM Note
  WHERE Note.Mat=42;





/* Gib die Anzahl der bestandenen Pruefungen in DB aus. */
SELECT COUNT(Note.Mat)
  FROM Note
  WHERE Note.Fach='DB'
    AND Note.Wert < 5.0;

-------------------------------
DROP TABLE T;

CREATE TABLE T (Nr INT);

INSERT INTO T VALUES (42);
INSERT INTO T VALUES (42);
INSERT INTO T VALUES (43);
INSERT INTO T VALUES (NULL);
INSERT INTO T VALUES (NULL);
INSERT INTO T VALUES (NULL);

SELECT * FROM T;

SELECT COUNT (*) FROM T; 

SELECT COUNT (T.nr) FROM T; 

SELECT COUNT (DISTINCT T.nr) FROM T; 

SELECT COUNT(*)
 FROM T
 WHERE T.Nr IS NOT NULL;
