rem Build It for the Real World: A Database Workbook Second Edition
rem by Mary J. Hoferek & Susan C. Wilson

rem Chapter 1 Getting Started
rem Chapter 2 Basic Database Creation Exercises
rem Chapter 3 Intermediate Exercises
rem Chapter 4 Beginning Maintenance
rem Chapter 5 Object-Relational Exercises

rem Milestone II - Create a Type

drop type my_type;
drop table test_type;

create type my_name as object
(
 first_name	varchar2(10),
 last_name	varchar2(10)
);
/

create table test_type
(
 id		number,
 emp_name	my_name
);

insert into test_type values(10, my_name('elms', 'mike'));
insert into test_type values(20, my_name('elms', 'karen'));

rem	SQL> select * from test_type;

rem     ID EMP_NAME(FIRST_NAME, LAST_NAME)
rem	-- -----------------------------------------------------------------
rem     10 MY_NAME('elms', 'mike')
rem     20 MY_NAME('elms', 'karen')

rem Milestone III - Create Variable Array

drop type my_phonel
drop table person;

create type my_phone as varray(3) of varchar2(14);
/

create table person
(
 name		varchar2(10),
 phone		my_phone
);

insert into person values('Mike', my_phone('(410)-480-8422', '(410)-966-3251'));


rem	SQL> select * from person;

rem	NAME	PHONE
rem	------- --------------------------------------------
rem	Mike    MY_PHONE('(410)-480-8422', '(410)-966-3251')

rem Milestone IV - Create a BLOB Data Type
drop table test_blob;

create table test_blob
(
 id	number,
 text	blob
);

rem 	SQL> desc test_blob
rem	  Name                                      Null?    Type
rem	  ----------------------------------------- -------- -------
rem	  ID                                                 NUMBER
rem	  TEXT                                               BLOB