oracle_sql_tute1

create type dept_t;
/

create type emp_t as object(
eno number(4),
ename varchar2(15),
edept ref dept_t,
salary number(8,2)
);
/

create type proj_t as object(
pno number(4),
pname varchar2(15),
pdept ref dept_t,
budget number(10, 2)
);
/

create type dept_t as object(
dno number(2),
dname varchar2(12),
mgr ref emp_t
);
/

create table emp of emp_t(
eno primary key
);

create table dept of dept_t(
dno primary key,
constraint fk_dept foreign key(mgr) references emp
);

alter table emp
add constraint fk_emp foreign key(edept) references dept;

create table proj of proj_t(
pno primary key,
constraint fk_proj foreign key(pdept) references dept
);

insert into emp values(
emp_t(1111, 'Kamal', null, 100000.00)
)
/

insert into dept values(
dept_t(11, 'Eng', (select ref(s) from emp s where eno=1111))
)
/

update emp
set edept = (select ref(a) from dept a where dno=11)
/

select a.edept.dno
from emp a;