Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select * from V$VERSION;


BANNER
BANNER_FULL
BANNER_LEGACY
CON_ID


Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
0


CREATE TABLE Personal (
 PersID               INTEGER      GENERATED ALWAYS AS IDENTITY
                                   CONSTRAINT PersPK PRIMARY KEY,
 Nachname             VARCHAR2(30) NOT NULL ,
 Vorname              VARCHAR2(30) NOT NULL ,
 Telefonnr            VARCHAR2(15) NULL ,
 Email                VARCHAR2(50) NOT NULL 
            CONSTRAINT pEmailSyntax CHECK (email LIKE '%@%.__'
                            OR email LIKE '%@$.___')
                        INITIALLY DEFERRED,
 Raum                 VARCHAR2(5)  NULL ,
 Typ                  CHAR(1)      NOT NULL
                      CONSTRAINT   PersTypChk CHECK ( UPPER(typ) IN
                                   ('W','P','S','V') ) INITIALLY IMMEDIATE,
 von                  DATE,
 bis                  DATE);


CREATE TABLE Projekte (
 ProjID               INTEGER      NOT NULL ,
 Bezeichnung          VARCHAR2(30) NOT NULL ,
 Semester             VARCHAR2(10) NOT NULL ,
 maxGGroesse          INTEGER      NULL ,
 Fach                 VARCHAR2(20) NULL ,
 WMA_PersID           INTEGER      NULL ,
 Prof_PersID          INTEGER      NULL ,
 Freigeschaltet       DATE         NULL ,
    CONSTRAINT PKProjekte PRIMARY KEY (ProjID),
    CONSTRAINT freischaltenCck CHECK ( (freigeschaltet IS NOT NULL
                                AND Fach IS NOT NULL AND Prof_persID IS NOT NULL)
                                OR freigeschaltet IS NULL ) INITIALLY DEFERRED );
INSERT INTO Personal (Nachname, Vorname, Telefonnr, Email, Raum, Typ, von, bis)
     VALUES ('Bauer', 'Erna', '02261 81961211', 'EB@thk.de', '2230', 'P', TO_DATE('01.03.2019', 'DD.MM.RRRR'), NULL);
1 rows affected

INSERT INTO Personal (Nachname, Vorname, Telefonnr, Email, Raum, Typ, von, bis)
     VALUES ('Mecker', 'Else', '02261 81964222', 'EM@thk.de', '2254', 'P', TO_DATE('16.06.2020', 'DD.MM.RRRR'), TO_DATE('20.02.2022', 'DD.MM.RRRR'));
1 rows affected

INSERT INTO Projekte 
     VALUES (1, 'Oracle-Praktikumsprojekt', 'SS2020', 2, 'DBS1', NULL, 2, TO_DATE ('15.04.2020', 'DD.MM.RRRR'));
1 rows affected

INSERT INTO Projekte 
     VALUES (2, 'Build-A-Bear Workshop', 'SS2020', 2, 'DBS1', NULL, 3, TO_DATE ('15.04.2020', 'DD.MM.RRRR'));
1 rows affected

SELECT proj.Semester
     , MIN(Nachname) AS LastName, MIN(Vorname) AS FirstName
     , COUNT (DISTINCT proj.prof_persID) AS Professors
  FROM Personal pers
  JOIN Projekte proj
    ON pers.persID = proj.prof_persID
 WHERE proj.Semester = 'SS2020'
 GROUP BY proj.Semester
HAVING COUNT (DISTINCT proj.prof_persID) = 1
;


SEMESTER
LASTNAME
FIRSTNAME
PROFESSORS


SS2020
Mecker
Else
1


SELECT MIN(proj.Semester) AS Semester
     , MIN(Nachname) AS LastName, MIN(Vorname) AS FirstName
     , COUNT (DISTINCT proj.prof_persID) AS Professors
  FROM Personal pers
  JOIN Projekte proj
    ON pers.persID = proj.prof_persID
 WHERE proj.Semester = 'SS2020'
HAVING COUNT (DISTINCT proj.prof_persID) = 1
;


SEMESTER
LASTNAME
FIRSTNAME
PROFESSORS


SS2020
Mecker
Else
1