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 Person_Type AS OBJECT(name VARCHAR2(20), address Address_Type, tel Telephone_List_Type) NOT FINAL;
/
CREATE TYPE Student_Type UNDER Person_Type ();
/
CREATE TYPE Employee_Type UNDER Person_Type (salary NUMBER(10), office_number VARCHAR2(10), MEMBER FUNCTION yearly_salary RETURN NUMBER);
/
CREATE TYPE BODY Employee_Type IS
	MEMBER FUNCTION yearly_salary RETURN NUMBER IS
	BEGIN
		RETURN 12*salary;
	END;
END;
/	

CREATE TABLE Persons OF Person_Type;
CREATE TABLE Students OF Student_Type;
CREATE TABLE Employees OF Employee_Type;


INSERT INTO Students VALUES('Mueller',Address_Type('DE','MD','Hauptstrasse'),Telephone_List_Type('039111','017211'));
INSERT INTO Students VALUES('Knueller',Address_Type('DE','HD','Hauptstrasse'),Telephone_List_Type('039111','017211'));
INSERT INTO Persons VALUES('Fueller',Address_Type('DE','Berlin','Hauptstrasse'),Telephone_List_Type('039111','017211'));
INSERT INTO Persons VALUES('Brueller',Address_Type('DE','MD','Hauptstrasse'),Telephone_List_Type('039111','017211'));
INSERT INTO Employees VALUES('ZMeier',Address_Type('DE','MD','Hauptstrasse'),Telephone_List_Type('039111','017211'),'1100','067');
INSERT INTO Employees VALUES('Lenz',Address_Type('DE','Koeln','Hauptstrasse'),Telephone_List_Type('039111','017211'),'2000','100');


CREATE VIEW PersonsView OF Person_Type AS SELECT * FROM Persons p WHERE p.address.town='MD';
CREATE VIEW StudentsView OF Student_Type UNDER PersonsView AS SELECT * FROM Students s WHERE s.address.town='MD';
CREATE VIEW EmployeesView OF Employee_Type UNDER PersonsView AS SELECT * FROM Employees e WHERE e.address.town='MD';

SELECT e.name, e.salary, e.yearly_salary() FROM Employees e;



SELECT * FROM PersonsView;



DROP VIEW EmployeesView;
DROP VIEW PersonsView;
DROP VIEW StudentsView;

DROP TABLE Employees;
DROP TABLE Students;
DROP TABLE Persons;

DROP TYPE BODY Employee_Type;
DROP TYPE Employee_Type;
DROP TYPE Student_Type;
DROP TYPE Person_Type;
DROP TYPE Telephone_List_Type;
DROP TYPE Address_Type;