You have EMP and dept table .how do you get the entire department and corresponding employee details and the department which are not alloted it?what is referential integrity constraint?wat is triggers,stored procedures,stored functions explain with examples

Interview Candidate
Jan 23rd, 2007
2
3063

Showing Answers 1 - 2 of 2 Answers

Nandakumar

Jan 28th, 2007

Yes, you can get all the departments along with the employee details, plus the department for which no employee exists. Here you go using outerjoin, select a.employee_id, a.email, a.salary, a.department_id, b.department_id, b.department_name from employees a, departments b where a.department_id(+) = b.department_id

deviji2000
Profile Answers by deviji2000

Oct 15th, 2007

hi, v use joins to get details from both the tables thz kind of joins r called outer joins to get the missed statements.

select e.eno, e.ename, e.deptno
from emp e, dept d
where (+)e.deptno=d.deptno;

hope ths will do..

the same query v rewrite to get the un- alloted dept nos.

select e.eno, e.ename, e.deptno
from emp e, dept d
where e.deptno=d.deptno(+);

bye
viji