CREATE TYPE Institute_List_Type AS TABLE OF VARCHAR2(20);
/
CREATE TYPE Faculty_Type AS OBJECT (id VARCHAR2(20), name VARCHAR2(20),
institute_list Institute_List_Type);
/
CREATE TYPE Address_Type AS OBJECT (country VARCHAR2(20), town VARCHAR2(20),
street VARCHAR2(20));
/
CREATE TYPE Telephone_List_Type AS VARRAY(3) OF VARCHAR2(20);
/
CREATE TYPE Student_Type AS OBJECT(matrnr VARCHAR2(10), name VARCHAR2(20),
address Address_Type, tel Telephone_List_Type, fid VARCHAR2(20));
/
CREATE TABLE Faculties OF Faculty_Type (PRIMARY KEY(id))NESTED TABLE institute_list STORE AS institute_tab;
CREATE TABLE Students OF Student_Type (FOREIGN KEY(fid) REFERENCES Faculties(id));

INSERT INTO Faculties VALUES('1','Computer Science',Institute_List_Type('Information Systems','Distributed Systems','Software Engineering','Computer Graphics'));
INSERT INTO Faculties VALUES('2','Economics',Institute_List_Type('Political Economics','Business Economics','Marketing'));


INSERT INTO Students VALUES('123456','Mueller',Address_Type('DE','MD','Hauptstrasse'),Telephone_List_Type('039111','017211'),'1');
INSERT INTO Students VALUES('123457','Meier',Address_Type('DE','Braunschweig','Nebenstrasse'),Telephone_List_Type('039111','017211'),'1');
INSERT INTO Students VALUES('123458','Schulze',Address_Type('DE','Berlin','Kleinstrasse'),Telephone_List_Type('030232','0176166'),'2');
INSERT INTO Students VALUES('123459','Schmidt',Address_Type('DE','Potsdam','Grossstrasse'),Telephone_List_Type('039111','017211'),'2');



SELECT f.name FROM Faculties f, Students s WHERE s.address.town='MD' AND s.fid=f.id;
SELECT s.tel FROM Students s WHERE s.name='Schulze';
SELECT f.name FROM Faculties f, TABLE(f.institute_list) il WHERE VALUE(il)='Marketing';

DROP TABLE Students;
DROP TABLE Faculties;
DROP TYPE Student_Type;
DROP TYPE Telephone_List_Type;
DROP TYPE Address_Type;
DROP TYPE Faculty_Type;
DROP TYPE Institute_List_Type;