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: 
  | 
  
   | 
  ||||||