DROP TYPE pruefungen_t FORCE;
DROP TYPE pruefungsordnung_t FORCE;
DROP TYPE pruefung_t FORCE;
DROP TYPE student_t FORCE;
DROP TYPE fachpruefung_t FORCE;
DROP TYPE fachpruefungen_t FORCE;
DROP TYPE note_t FORCE;
DROP TYPE noten_t FORCE;

--eigene datentypen
CREATE OR REPLACE TYPE pruefung_t AS OBJECT  (
  Pruefungsnummer varchar2(8),
  Bezeichnung     varchar2(40)
) NOT FINAL;
/

CREATE OR REPLACE TYPE pruefungen_t AS TABLE OF REF pruefung_t;
/
 
CREATE OR REPLACE TYPE fachpruefung_t UNDER pruefung_t (
  Studienphase      char(1),
  Studienleistungen pruefungen_t
);
/

CREATE OR REPLACE TYPE fachpruefungen_t AS TABLE OF REF fachpruefung_t;
/

CREATE OR REPLACE TYPE note_t as OBJECT (
  Datum         date,
  Note          number(2,1),
  Ref_Pruefung  REF pruefung_t
);
/

CREATE OR REPLACE TYPE noten_t AS TABLE OF note_t;
/
 
CREATE OR REPLACE TYPE pruefungsordnung_t as OBJECT (
  STG             varchar2(3),
  Version         number(2),
  Fachpruefungen  fachpruefungen_t
);
/
 
CREATE OR REPLACE TYPE student_t AS OBJECT (
  Matrikelnummer                number(8),
  Nachname                      varchar2(40),
  Vorname                       varchar2(40),
  PO                            REF pruefungsordnung_t,
  Erbrachte_Pruefungsleistungen noten_t,
  Vorpruefung                   number(2,1),
  Abschlussarbeit               number(2,1),
  Abschlussnote                 number(2,1)
); 
/

--typisierte tabellen:  
DROP TABLE pruefung_tab;
CREATE  TABLE pruefung_tab OF pruefung_t ( 
    Pruefungsnummer PRIMARY KEY, 
    Bezeichnung NOT NULL 
); 
 
DROP TABLE pruefungsordnung_tab;
CREATE TABLE pruefungsordnung_tab OF pruefungsordnung_t ( 
    STG NOT NULL, 
    Version NOT NULL, 
    Fachpruefungen NULL 
) NESTED TABLE Fachpruefungen STORE AS nested_fachpruefung_tab; 
 
DROP TABLE student_tab;
CREATE TABLE student_tab OF student_t ( 
    Matrikelnummer PRIMARY KEY, 
    Nachname NOT NULL, 
    Vorname NOT NULL, 
    PO NOT NULL, 
    Erbrachte_Pruefungsleistungen NULL, 
    Vorpruefung NULL, 
    Abschlussarbeit NULL, 
    Abschlussnote NULL 
) nested table Erbrachte_Pruefungsleistungen store as nested_pruefungsleistungen_tab;

-------------------------------------------------------------------------------
--aufgabe 1a
INSERT INTO pruefung_tab VALUES('WI2471', 'Datenbanken I');
INSERT INTO pruefung_tab VALUES('WI3671', 'Datenbanken II'); 
INSERT INTO pruefung_tab VALUES( fachpruefung_t( 'WIFP47','Datenbank','C', 
    pruefungen_t( 
                ( SELECT ref(p) FROM pruefung_tab p WHERE p.Pruefungsnummer = 'WI2471' ), 
                ( SELECT ref(p) FROM pruefung_tab p WHERE p.Pruefungsnummer = 'WI3671' ) 
                ) 
            ) );

--sql developer abfrage:
SELECT * FROM pruefung_tab p WHERE Bezeichnung like 'D%';
--sql web view abfrage:
--SELECT VALUE(p) FROM pruefung_tab p WHERE Bezeichnung like 'D%';


-------------------------------------------------------------------------------
--aufgabe 1b
DELETE FROM pruefung_tab;

-- pruefungen anlegen
INSERT INTO pruefung_tab  
    (SELECT pruefung_t(Pruefungsnummer, Bezeichnung) from pruefungsordnung WHERE STG='WI' AND PO_VERSION='2' AND ART = 'FT' 

);
 
-- fachpruefungen anlegen 
INSERT INTO pruefung_tab ( 
    SELECT fachpruefung_t  ( 
      a.Pruefungsnummer, a.Bezeichnung, a.Abschnitt, 
       ( CAST( MULTISET( 
            SELECT ref(b) FROM pruefung_tab b INNER JOIN pruefungsordnung c ON  b.Pruefungsnummer = c.Pruefungsnummer WHERE 

c.Fachpruefung_FK = a.Pruefungsnummer  
            ) AS pruefungen_t )   )   
    )
  FROM pruefungsordnung a WHERE a.Fachpruefung_FK IS NULL AND a.STG='WI' AND a.ART='FP' AND a.PO_VERSION='2');
 
-------------------------------------------------------------------------------
--aufgabe 1c 
DELETE FROM pruefungsordnung_tab;

--datensatz einfügen
INSERT INTO pruefungsordnung_tab VALUES( 'WI', '2', 
        (CAST(MULTISET( 
            SELECT TREAT(REF(a) as REF fachpruefung_t) FROM pruefung_tab a 
             INNER JOIN pruefungsordnung b ON a.pruefungsnummer = b.pruefungsnummer
             WHERE b.stg='WI' AND b.po_version=2 and b.ART='FP'   ) as fachpruefungen_t) 
        )  
); 

-- testen (24 zeilen erwartet)
select value(x).pruefungsnummer AS Pruefungsnummer, value(x).bezeichnung AS BEZEICHNUNG
from table(select fachpruefungen
from pruefungsordnung_tab
where version=2) x
order by value(x).bezeichnung

-------------------------------------------------------------------------------
--aufgabe 1e
DELETE FROM student_tab;

INSERT INTO student_tab VALUES( '67234', 'Fleissig', 'Lieschen', 
 (SELECT ref(a) FROM pruefungsordnung_tab a WHERE a.STG='WI' AND a.Version='2')
, NULL,NULL,NULL,NULL );

INSERT INTO student_tab VALUES( '36123', 'Palaver', 'Patrick', 
 (SELECT ref(a) FROM pruefungsordnung_tab a WHERE a.STG='WI' AND a.Version='2')
, NULL,NULL,NULL,NULL );
 

-------------------------------------------------------------------------------
--aufgabe 1f
update student_tab a set Erbrachte_Pruefungsleistungen=noten_t(
    note_t('07.07.2006',1.3, (SELECT ref(b) FROM pruefung_tab b WHERE b.Bezeichnung='Allgemeine BWL/Recht') ),
    note_t('11.07.2006',5.0, (SELECT ref(b) FROM pruefung_tab b WHERE b.Bezeichnung='Grundlagen d.Informatik') ),
    note_t('03.02.2007',2.0, (SELECT ref(b) FROM pruefung_tab b WHERE b.Bezeichnung='Grundlagen d.Informatik') ),
    note_t('07.02.2007',1.7, (SELECT ref(b) FROM pruefung_tab b WHERE b.Bezeichnung='Datenbanken I') )
    )
where a.Nachname='Fleissig' AND a.Vorname='Lieschen';