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