self join in sql

                                                                                                                            edited by sukamal

create table dept(d_no varchar2(20) primary key,d_name varchar2(20),mg_no number);

insert into dept values('1','it','1');

insert into dept values('2','finance','3');

select *

from dept;

output:

D_NO	D_NAME	MG_NO
1	it	1
2	finance	3

2:code:

create table employee(e_id varchar2(20) primary key,e_name varchar2(20),d_no varchar2(20) references dept,city varchar2(20));

insert into employee values('1','ram','1','m.p');

insert into employee values('2','shyam','1','m.p');

insert into employee values('3','sita','2','u.p');

insert into employee values('4','gita','2','goa');

select *

from employee;

output:

E_ID	E_NAME	D_NO	CITY
1	ram	1	m.p
2	shyam	1	m.p
3	sita	2	u.p
4	gita	2	goa

3: Find the manager name of shyam.

Program code:

 select e_name

from employee

where e_id=(select mg_no from dept where d_no=(select d_no from employee where e_name='shyam'));

output:

E_NAME

ram

4:Find the employee city and manager city of all employee

Program code:

select e2.city,e1.city

from employee e1,dept,employee e2

where e2.d_no=dept.d_no and

dept.mg_no=e1.e_id;

output:

CITY	CITY
m.p	m.p
m.p	m.p
u.p	u.p
goa	u.p

5:Find the all employee name and their manager name.

Program code:

select e2.e_name,e1.e_name

from employee e1,dept,employee e2

where e2.d_no=dept.d_no and

dept.mg_no=e1.e_id;

output:

E_NAME		E_NAME
ram		ram
shyam		ram
sita		sita
gita		sita
6:Find all the employee  whose city is equal to their manager city program code: select e2.e_name from employee e1,dept,employee e2 where e2.d_no=dept.d_no and dept.mg_no=e1.e_id and e1.city=e2.city; output: E_NAME ram shyam sita