Oracle Database Example Object Database (ORDB) type/table/body/method/ddl/dml

** Myself
1) Create type
2) Create table
3) Create body & Method
4) Insert data
5) Query information
6) Drop type and Table

1) Create type
--- Create Types --
set echo on;
drop type RegistrationObj;
drop type SubjectNTab;
drop type RegisSubject;
drop type StudentObj;
drop type SubjectObj;
drop type CourseObj;
drop type CostObj;
drop type InstructorObj;
drop type PersonObj;
drop type AddressObj;
drop type EmailArray;
drop type PhoneArray;
--1
create type PhoneArray as varray(3) of varchar2(40)
/
--2
create type EmailArray as varray(2) of varchar2(50)
/
--3
create type AddressObj as object(
  address varchar2(70),
  district varchar2(30),
  amphur varchar2(50),
  province varchar2(50),
  country varchar2(50),
  zipCode varchar2(10)
)
/
--4
create type PersonObj as object(
  titleName varchar2(30),
  firstName varchar2(30),
  lastName varchar2(30)
)
/
--5
create type InstructorObj as object(
  instructorID varchar(4),
  instructor PersonObj,
  eMail EmailArray,
  order member function isInstructor(f in InstructorObj) return integer
)
/
--6
create type CourseObj as object(
  courseID number(3),
  year varchar2(4),
  code varchar2(6),
  courseNameEng varchar2(80),
  courseNameTh varchar2(100),
  degreeEng varchar2(50),
  degreeTh varchar2(80),
  totalCredit number(3),
  normalYear number(1),
  maxYear number(1)
)
/
--7
create type CostObj as object(
  costID number(8),
  academicyear varchar2(4),
  semester varchar2(1),
  creditCost number(4),
  healtCareCost number(3),
  fee number(6),
  courseRef REF courseObj
)
/
--8
create type StudentObj as object(
  studentID number(8),
  courseRef REF CourseObj,
  studentName PersonObj,
  gender varchar2(1),
  dateOfBirth date,
  contactAddress AddressObj,
  contactPhone PhoneArray,
  address AddressObj,
  phoneNo PhoneArray,
  mobileNo PhoneArray,
  startDate date,
  endDate date,
  citizenID number(13),
  nationality varchar2(30),
  eMail EmailArray,
  academicyear varchar2(4),
  semester varchar2(1),
  map member function getStudentID return number,
  member function getAge return number,
  member function isStudent(f in StudentObj) return integer
)
/
--9
create type SubjectObj as object(
  subjectCode varchar2(6),
  courseRef REF CourseObj,
  subjectName varchar2(80),
  instructorRef REF InstructorObj,
  prerequisiteRef REF SubjectObj,
  description varchar2(255),
  credit number(2),
  type varchar2(1),
  order member function isSubject(s in SubjectObj) return integer
)
/
--10
create type RegisSubject as object(
  regisSubjectID number(8),
  subjectIDRef REF subjectObj
)
/
--11
create type SubjectNTab as table of  RegisSubject
/
--12
create type RegistrationObj as object(
  regissubjectID number(8),
  year varchar2(4),
  semester varchar2(1),
  studentRef REF StudentObj,
  regisdate date,
  subjectList SubjectNTab,
  type varchar2(1),
  member function regiscost(name in varchar,academicyear in varchar,semester in varchar ) return number
)
/
select * from user_types;

2) Create table
--- Create Tables ---
drop table RegistrationTab;
drop table SubjectNTab;
drop table StudentTab;
drop table SubjectTab;
drop table CourseTab;
drop table CostTab;
drop table InstructorTab;
create table InstructorTab of InstructorObj (instructorID primary key) object id primary key;
create table CourseTab of CourseObj (courseID primary key)
  object id primary key;
create table CostTab of CostObj (
  primary key(costID),
  foreign key(courseRef) references CourseTab)
  object id primary key;
create table StudentTab of StudentObj (
  primary key(studentID),
  foreign key(courseRef) references CourseTab)
  object id primary key;
create table SubjectTab of SubjectObj (
  primary key(subjectCode),
  foreign key(courseRef) references CourseTab,
  foreign key(instructorRef) references InstructorTab,
  foreign key(prerequisiteRef) references SubjectTab)
  object id primary key;
create table registrationTab of RegistrationObj (
  primary key(regisSubjectID),
  foreign key(studentRef) references StudentTab)
  object id primary key
nested table subjectList store as RegisSubjectNTab(
  (primary key(nested_table_id,regisSubjectID))
  organization index compress)
  return as locator;
alter table RegisSubjectNTab add (scope for (subjectIDRef) is SubjectTab);
select * from tab;


3) Create body & Method
set echo on;
create or replace type body InstructorObj as
  order member function
  isInstructor(f  InstructorObj) return integer is
  begin
         return   instructorid-f.instructorid;
  end;
end;
/
------------------------------------------------------
create or replace type body SubjectObj as
order member function isSubject(s in SubjectObj) return integer is
  begin
         return   subjectCode -s.subjectCode ;
  end;
end;
/
------------------------------------------------------
create or replace type body StudentObj as
  map member function getStudentID return number is
  begin
         return studentid;
  end;
  member function getAge return number is
  begin
         return  ROUND((SYSDATE-DATEOFBIRTH)/365) ;
  end;
  member function isStudent(f StudentObj) return integer is
  begin
         return   studentID -f.studentID ;
  end;
end;
/
------------------------------------------------------
create or replace type body registrationObj as
  member function regiscost(name in varchar,academicyear in varchar,semester in varchar ) return number is
  sumcredit number;
  result    number;
  begin
       select sum(s.subjectIDRef.credit)   into sumcredit
       from registrationtab r,table(r.subjectList) s
       where r.year=academicyear
       and r.semester=semester
       and r.studentref.STUDENTNAME.firstname = name
       and r.type='1';
       select sumcredit*c.creditcost+c.healtcarecost+c.fee into result
       from studenttab s,costtab c
       where s.academicyear=c.academicyear
       and   s.semester=c.semester
       and   s.courseref.courseid=c.courseref.courseid
       and   s.STUDENTNAME.firstname = name;
   return result ;
  end;
end;
/
------------------------------------------------------
4) Insert data
-- DML --
select * from tab;
DESC CostTab;
DESC CourseTab;
DESC InstructorTab;
DESC RegistrationTab;
DESC RegistrationTab;
DESC StudentTab;
DESC SubjectTab;
-- INSERT INTO INSTRUCTORTAB TABLE --
-- Record 1
INSERT INTO INSTRUCTORTAB VALUES('0001',personobj('Dr.','KITTICHAI','LAVANGNANANDA'),emailarray('KITTICHAI@IT.KMUTT.AC.TH','KITTICHAI@YAHOO.COM'))
/
-- Record 2
INSERT INTO INSTRUCTORTAB VALUES('0002',personobj('Dr.','PRASERT','KANTHAMANON'),emailarray('PRASERT@IT.KMUTT.AC.TH','PRASERT@YAHOO.COM'))
/
-- Record 3
INSERT INTO INSTRUCTORTAB VALUES('0003',personobj('Dr.','BORWORN','PAPASRATORN'),emailarray('BORWORN@IT.KMUTT.AC.TH','BORWORN@GMAIL.COM'))
/
-- Record 4
INSERT INTO INSTRUCTORTAB VALUES('0004',personobj('Dr.','WICHIAN','CHUTIMASKUL'),emailarray('WICHIAN@IT.KMUTT.AC.TH'))
/
-- Record 5
INSERT INTO INSTRUCTORTAB VALUES('0005',personobj('Dr.','KRIENGKRAI','PORKAEW'),emailarray('KRIENGKRAI@IT.KMUTT.AC.TH','KRIENGKRAI@GMAIL.COM'))
/
-- Record 6
INSERT INTO INSTRUCTORTAB VALUES('0006',personobj('Dr.','NIPON','CHAROENKITKARN'),emailarray('NIPON@IT.KMUTT.AC.TH','NIPON@YAHOO.COM'))
/
SELECT * FROM INSTRUCTORTAB;
-- INSERT INTO COURSETAB TABLE --
-- Record 1
INSERT INTO COURSETAB(COURSEID,YEAR,CODE,COURSENAMEENG,COURSENAMETH,DEGREEENG,DEGREETH,TOTALCREDIT,NORMALYEAR,MAXYEAR)
VALUES (1,2003,'MSC_IT','INFORMATION TECHNOLOGY','ÇÔ·ÂÒÈÒʵÃìÁËҺѳ±Ôµ ÊÒ¢ÒÇÔªÒà·¤â¹âÅÂÕÊÒÃʹà·È','MASTER DEGREE OF SCIENCE','ÇÔ·ÂÒÈÒʵÃìÁËҺѳ±Ôµ',39,2,5)
/
-- Record 2
INSERT INTO COURSETAB(COURSEID,YEAR,CODE,COURSENAMEENG,COURSENAMETH,DEGREEENG,DEGREETH,TOTALCREDIT,NORMALYEAR,MAXYEAR)
VALUES (2,2003,'MSC_EB','ELECTRONIC BUSINESS','ÇÔ·ÂÒÈÒʵÃìÁËҺѳ±Ôµ ÊÒ¢ÒÇÔªÒ¸ØáԨÍÔàÅç¡·Ã͹ԡÊì','MASTER DEGREE OF SCIENCE','ÇÔ·ÂÒÈÒʵÃìÁËҺѳ±Ôµ',36,2,5)
/
SELECT * FROM COURSETAB
/
-- INSERT INTO COSTTAB TABLE --
-- Record 1
INSERT INTO COSTTAB(academicyear, SEMESTER, COSTID, CREDITCOST, HEALTCARECOST, FEE, COURSEREF)
SELECT '2003','1',1,1800,75,16000,REF(C)
FROM COURSETAB C
WHERE C.COURSENAMEENG = 'INFORMATION TECHNOLOGY'
/
-- Record 2
INSERT INTO COSTTAB(academicyear, SEMESTER, COSTID, CREDITCOST, HEALTCARECOST, FEE, COURSEREF)
SELECT '2004','2',2,2000,75,18000,REF(C)
FROM COURSETAB C
WHERE C.COURSENAMEENG = 'INFORMATION TECHNOLOGY'
/
-- Record 3
INSERT INTO COSTTAB(academicyear, SEMESTER, COSTID, CREDITCOST, HEALTCARECOST, FEE, COURSEREF)
SELECT '2004','1',3,2000,75,20000,REF(C)
FROM COURSETAB C
WHERE C.COURSENAMEENG = 'ELECTRONIC BUSINESS'
/
-- Record 4
INSERT INTO COSTTAB(academicyear, SEMESTER, COSTID, CREDITCOST, HEALTCARECOST, FEE, COURSEREF)
SELECT '2003','2',4,1800,75,16000,REF(C)
FROM COURSETAB C
WHERE C.COURSENAMEENG = 'INFORMATION TECHNOLOGY'
/
-- Record 5
INSERT INTO COSTTAB(academicyear, SEMESTER, COSTID, CREDITCOST, HEALTCARECOST, FEE, COURSEREF)
SELECT '2004','2',5,2000,75,20000,REF(C)
FROM COURSETAB C
WHERE C.COURSENAMEENG = 'ELECTRONIC BUSINESS'
/
-- Record 6
INSERT INTO COSTTAB(academicyear, SEMESTER, COSTID, CREDITCOST, HEALTCARECOST, FEE, COURSEREF)
SELECT '2004','1',6,2000,75,18000,REF(C)
FROM COURSETAB C
WHERE C.COURSENAMEENG = 'INFORMATION TECHNOLOGY'
/
SELECT COSTID,C.COURSEREF.COURSENAMEENG,academicyear,SEMESTER,CREDITCOST,HEALTCARECOST,FEE
FROM COSTTAB C
/
-- INSERT INTO SUBJECTTAB TABLE --
-- RECORD 1
INSERT INTO SUBJECTTAB(SUBJECTCODE,SUBJECTNAME,DESCRIPTION,CREDIT,TYPE,COURSEREF,INSTRUCTORREF) 
SELECT 'INT601' ,'COMPUTER SYSTEM CONCEPT' ,'Overview and introduction to computer systems, computer system and its interconnection structures.' ,3 ,'1',REF(C),REF(I)
FROM COURSETAB C,INSTRUCTORTAB I
WHERE C.COURSENAMEENG='INFORMATION TECHNOLOGY'
AND I.INSTRUCTOR.FIRSTNAME='PRASERT'
/
-- RECORD2
INSERT INTO SUBJECTTAB(SUBJECTCODE,SUBJECTNAME,DESCRIPTION,CREDIT,TYPE,COURSEREF,INSTRUCTORREF) 
SELECT 'INT602' ,'DESIGN AND ANALYSIS OF ALGORITHM' ,'Introduction to analysis and design of algorithms, recursion, fundamental concepts of algorithm analysis' ,3 ,'1',REF(C),REF(I)
FROM COURSETAB C,INSTRUCTORTAB I
WHERE C.COURSENAMEENG='INFORMATION TECHNOLOGY'
AND I.INSTRUCTOR.FIRSTNAME='KITTICHAI'
/
-- RECORD 3
INSERT INTO SUBJECTTAB(SUBJECTCODE,SUBJECTNAME,DESCRIPTION,CREDIT,TYPE,COURSEREF,INSTRUCTORREF) 
SELECT 'INT603' ,'ORGANIZATION INFORMATION SYSTEM' ,'Managing the digital firm, introduction to information age, information systems in the enterprise, organization, management and strategy' ,3 ,'1',REF(C),REF(I)
FROM COURSETAB C,INSTRUCTORTAB I
WHERE C.COURSENAMEENG='INFORMATION TECHNOLOGY'
AND I.INSTRUCTOR.FIRSTNAME='NIPON'
/
--RECORD 4
INSERT INTO SUBJECTTAB(SUBJECTCODE,SUBJECTNAME,DESCRIPTION,CREDIT,TYPE,COURSEREF,INSTRUCTORREF)  
SELECT 'INT651' ,'NETWORKING' ,'Managing an instance, creating a database, database server Installation, architectural components' ,3 ,'1',REF(C),REF(I)
FROM COURSETAB C,INSTRUCTORTAB I
WHERE C.COURSENAMEENG='INFORMATION TECHNOLOGY'
AND I.INSTRUCTOR.FIRSTNAME='BORWORN'
/
-- RECORD 5
INSERT INTO SUBJECTTAB(SUBJECTCODE,SUBJECTNAME,DESCRIPTION,CREDIT,TYPE,COURSEREF,INSTRUCTORREF)
SELECT 'INT605' ,'SYSTEMS ANALYSIS AND DESIGN' ,'Introduction to systems analysis and design, information system building block, information system development' ,3 ,'1',REF(C),REF(I)
FROM COURSETAB C,INSTRUCTORTAB I
WHERE C.COURSENAMEENG='INFORMATION TECHNOLOGY'
AND I.INSTRUCTOR.FIRSTNAME='WICHIAN'
/
-- RECORD 6
INSERT INTO SUBJECTTAB(SUBJECTCODE,SUBJECTNAME,DESCRIPTION,CREDIT,TYPE,COURSEREF,INSTRUCTORREF) 
SELECT 'INT642' ,'DATABASE MANAGEMENT SYSTEM' ,'Introduction to database management system, entity-relationship model, relational model, relational algebra' ,3 ,'1',REF(C),REF(I)
FROM COURSETAB C,INSTRUCTORTAB I
WHERE C.COURSENAMEENG='INFORMATION TECHNOLOGY'
AND I.INSTRUCTOR.FIRSTNAME='KRIENGKRAI'
/
select subjectcode, s.courseref.coursenameeng,subjectname,s.instructorref.instructor.firstname,description,credit,type,s.prerequisiteref.subjectname
from subjecttab s;
-- INSERT INTO PREREQUISITE --
-- RECORD 1
INSERT INTO SUBJECTTAB(SUBJECTCODE,SUBJECTNAME,DESCRIPTION,CREDIT,TYPE,COURSEREF,INSTRUCTORREF,PREREQUISITEREF)
SELECT 'INT652' ,'DATABASE TECHNOLOGY' ,'Conceptual database design, object-oriented database model, object-relational database model', 3 ,'1',REF(C),REF(I),REF(S)
FROM COURSETAB C,INSTRUCTORTAB I,SUBJECTTAB S
WHERE C.COURSENAMEENG='INFORMATION TECHNOLOGY'
AND I.INSTRUCTOR.FIRSTNAME='KRIENGKRAI'
AND S.SUBJECTCODE='INT642'
/
-- RECORD 2
INSERT INTO SUBJECTTAB(SUBJECTCODE,SUBJECTNAME,DESCRIPTION,CREDIT,TYPE,COURSEREF,INSTRUCTORREF,PREREQUISITEREF)
SELECT 'INT648' ,'OBJECT-ORIENTED TECHNOLOGY' ,'Introduction to object oriented technology, software engineering, software process, object-oriented concepts', 3 ,'1',REF(C),REF(I),REF(S)
FROM COURSETAB C,INSTRUCTORTAB I,SUBJECTTAB S
WHERE C.COURSENAMEENG='INFORMATION TECHNOLOGY'
AND I.INSTRUCTOR.FIRSTNAME='WICHIAN'
AND S.SUBJECTCODE='INT605'
/
select subjectcode, s.courseref.coursenameeng,subjectname,s.instructorref.instructor.firstname,description,credit,type,s.prerequisiteref.subjectname
from subjecttab s;
-- INSERT INTO STUDENTTAB TABLE --
-- RECORD 1
INSERT INTO STUDENTTAB(studentid,studentname,gender,dateofbirth,contactaddress,contactphone,address,phoneno,
                       mobileno,startdate,enddate,ACADEMICYEAR,SEMESTER,citizenid,NATIONALITY,email,COURSEREF) 
SELECT 46432142,personobj('Mr.','SOMYOS','BONG'),'M',TO_DATE('05-03-1980','DD-MM-YYYY'),
                addressobj('120/84','M7','ABCDEF','EFG','BANGKOK','10140'),
        phonearray('02-4641061','06-6885660'),
        addressobj('120/84','M7','ABCDEF','EFG','BANGKOK','10140'),
        phonearray('02-4641061'),
        phonearray('06-6885660'),
        TO_DATE('01-10-2003','DD-MM-YYYY'),TO_DATE('01-10-2005','DD-MM-YYYY'),'2003','2',3110400969126,'THAI',
        emailarray('LEK_CPE@YAHOO.COM','LEK_CPE@HOTMAIL.COM'),REF(C)
FROM COURSETAB C
WHERE C.COURSENAMEENG='INFORMATION TECHNOLOGY'
/
-- RECORD 2
INSERT INTO STUDENTTAB(studentid,studentname,gender,dateofbirth,contactaddress,contactphone,address,phoneno,
                       mobileno,startdate,enddate,ACADEMICYEAR,SEMESTER,citizenid,NATIONALITY,email,COURSEREF) 
SELECT 46432082,personobj('Miss.','CHONLADA','TIRAWANITGUL'),'F',TO_DATE('01-05-1977','DD-MM-YYYY'),
                addressobj('87/97','M13','PRACHAUTIS','BANGKOK','THAILAND','10140'),
        phonearray('02-6337664','09-9998688'),
        addressobj('87/97','M13','PRACHAUTIS','BANGKOK','THAILAND','10140'),
        phonearray('02-6337664','09-9998688'),
        phonearray('09-9996966'),
        TO_DATE('01-10-2003','DD-MM-YYYY'),TO_DATE('01-10-2005','DD-MM-YYYY'),'2003','2',3110804568913,'THAI',
        emailarray('CHONLADA@GMAIL.COM','CHONLADA@MODIT16.COM'),REF(C)
FROM COURSETAB C
WHERE C.COURSENAMEENG='INFORMATION TECHNOLOGY'
/
-- RECORD 3
INSERT INTO STUDENTTAB(studentid,studentname,gender,dateofbirth,contactaddress,contactphone,address,phoneno,
                       mobileno,startdate,enddate,ACADEMICYEAR,SEMESTER,citizenid,NATIONALITY,email,COURSEREF) 
SELECT 46432149,personobj('Miss.','SURATCHANEE','SUVANPRADIT'),'F',TO_DATE('06-10-1980','DD-MM-YYYY'),
                addressobj('12/97','M1','PRACHAUTIS','BANGKOK','THAILAND','10140'),
        phonearray('02-8725864'),
        addressobj('12/97','M1','PRACHAUTIS','BANGKOK','THAILAND','10140'),
        phonearray('02-8725864'),
        phonearray('01-8759564'),
        TO_DATE('01-10-2003','DD-MM-YYYY'),TO_DATE('01-10-2005','DD-MM-YYYY'),'2003','2',3110804567812,'THAI',
        emailarray('SURACHANEE@GMAIL.COM','SURACHANEE@MODIT16.COM'),REF(C)
FROM COURSETAB C
WHERE C.COURSENAMEENG='INFORMATION TECHNOLOGY'
/
SELECT STUDENTID,S.COURSEREF.COURSENAMEENG,STUDENTNAME,GENDER,DATEOFBIRTH,
CONTACTADDRESS,CONTACTPHONE,ADDRESS,PHONENO,MOBILENO,STARTDATE,ENDDATE,ACADEMICYEAR,SEMESTER,CITIZENID,NATIONALITY,EMAIL
FROM STUDENTTAB S
/
-- INSERT INTO REGISTRATIONTAB TABLE --
-- RECORD 1
INSERT INTO REGISTRATIONTAB
  SELECT 1, '2003','2',REF(S),TO_DATE('16-10-2003','DD-MM-YYYY'),SUBJECTNTAB(),'1'
  FROM STUDENTTAB S
  WHERE S.STUDENTNAME.FIRSTNAME='SURATCHANEE';
-- NESTED RECORD 1
INSERT INTO TABLE(
  SELECT R.SUBJECTLIST
  FROM REGISTRATIONTAB R
  WHERE R.REGISSUBJECTID = 1)
SELECT 1, REF(S)
FROM SUBJECTTAB S
WHERE S.SUBJECTCODE='INT601';
-- NESTED RECORD 2
INSERT INTO TABLE(
  SELECT R.SUBJECTLIST
  FROM REGISTRATIONTAB R
  WHERE R.REGISSUBJECTID = 1)
SELECT 2, REF(S)
FROM SUBJECTTAB S
WHERE S.SUBJECTCODE='INT602';
-- NESTED RECORD 3
INSERT INTO TABLE(
  SELECT R.SUBJECTLIST
  FROM REGISTRATIONTAB R
  WHERE R.REGISSUBJECTID = 1)
SELECT 3, REF(S)
FROM SUBJECTTAB S
WHERE S.SUBJECTCODE='INT603';
-- RECORD 2
INSERT INTO REGISTRATIONTAB
  SELECT 2, '2004','1',REF(S),TO_DATE('10-03-2004','DD-MM-YYYY'),SUBJECTNTAB(),'1'
  FROM STUDENTTAB S
  WHERE S.STUDENTNAME.FIRSTNAME='SURATCHANEE';
-- NESTED RECORD 1
INSERT INTO TABLE(
  SELECT R.SUBJECTLIST
  FROM REGISTRATIONTAB R
  WHERE R.REGISSUBJECTID = 2)
SELECT 1, REF(S)
FROM SUBJECTTAB S
WHERE S.SUBJECTCODE='INT605';
-- NESTED RECORD 2
INSERT INTO TABLE(
  SELECT R.SUBJECTLIST
  FROM REGISTRATIONTAB R
  WHERE R.REGISSUBJECTID = 2)
SELECT 2, REF(S)
FROM SUBJECTTAB S
WHERE S.SUBJECTCODE='INT642';
-- NESTED RECORD 3
INSERT INTO TABLE(
  SELECT R.SUBJECTLIST
  FROM REGISTRATIONTAB R
  WHERE R.REGISSUBJECTID = 2)
SELECT 3, REF(S)
FROM SUBJECTTAB S
WHERE S.SUBJECTCODE='INT651';
-- RECORD 3
INSERT INTO REGISTRATIONTAB
  SELECT 3, '2004','2',REF(S),TO_DATE('16-02-2004','DD-MM-YYYY'),SUBJECTNTAB(),'0'
  FROM STUDENTTAB S
  WHERE S.STUDENTNAME.FIRSTNAME='SURATCHANEE';
-- NESTED RECORD 1
INSERT INTO TABLE(
  SELECT R.SUBJECTLIST
  FROM REGISTRATIONTAB R
  WHERE R.REGISSUBJECTID = 3)
SELECT 1, REF(S)
FROM SUBJECTTAB S
WHERE S.SUBJECTCODE='INT648';
-- NESTED RECORD 2
INSERT INTO TABLE(
  SELECT R.SUBJECTLIST
  FROM REGISTRATIONTAB R
  WHERE R.REGISSUBJECTID = 3)
SELECT 2, REF(S)
FROM SUBJECTTAB S
WHERE S.SUBJECTCODE='INT652';
SELECT SEMESTER || ' / ' || YEAR AS SEMESTER, R.STUDENTREF.STUDENTNAME.TITLENAME || ' ' || R.STUDENTREF.STUDENTNAME.FIRSTNAME || ' ' || R.STUDENTREF.STUDENTNAME.LASTNAME AS "STUDENT NAME", REGISDATE AS "REGISTER DATE",
CURSOR(
  SELECT REGISSUBJECTID,R.SUBJECTIDREF.SUBJECTNAME AS "REGISTER SUBJECT"
  FROM TABLE(R.SUBJECTLIST)R) AS SUBJECT
FROM REGISTRATIONTAB R
WHERE TYPE = '1';
SELECT SEMESTER || ' / ' || YEAR AS SEMESTER, R.STUDENTREF.STUDENTNAME.TITLENAME || ' ' || R.STUDENTREF.STUDENTNAME.FIRSTNAME || ' ' || R.STUDENTREF.STUDENTNAME.LASTNAME AS "STUDENT NAME", REGISDATE AS "RESERVED DATE",
CURSOR(
  SELECT REGISSUBJECTID,R.SUBJECTIDREF.SUBJECTNAME AS "RESERVED SUBJECT"
  FROM TABLE(R.SUBJECTLIST)R) AS SUBJECT
FROM REGISTRATIONTAB R
WHERE TYPE = '0';
5) Query information
--1 
select studentid,studentname,gender,dateofbirth,contactaddress,contactphone,address,
       phoneno,mobileno,startdate,enddate,citizenid,NATIONALITY,email,s.courseref.coursenameeng
from studenttab s
order by studentid
/
--2.
select i.instructorid,i.instructor.firstname,i.email
from instructortab i
order by value(i)
/
----------------------------------------------------------------- 
--3
select s.getStudentID(),s.studentname.firstname,s.getAge()
from studenttab s
order by s.getAge()
/
--4
-----------------------------------------------------------------
select distinct r.regiscost('SURATCHANEE','2003','2')
from registrationtab r
/
-----------------------------------------------------------------
 
6) Drop type and Table
drop table RegistrationTab;
drop table SubjectNTab;
drop table StudentTab;
drop table SubjectTab;
drop table CourseTab;
drop table CostTab;
drop table InstructorTab;
drop type RegistrationObj;
drop type SubjectNTab;
drop type RegisSubject;
drop type StudentObj;
drop type SubjectObj;
drop type CourseObj;
drop type CostObj;
drop type InstructorObj;
drop type PersonObj;
drop type AddressObj;
drop type EmailArray;
drop type PhoneArray;