Oracle Real Time Examples

Here You can see the solutions for Problem which may face...

Table Emp1

ename varchar2(10)-->primary key

eadd varchar2(10)

Table Company

cname varchar2(10)--->primary key

cadd varchar2(10)

Table Sal

ename varchar2(10)---> foreign key of emp1

canme varchar2(10)--->foreign key of emp1

salary number(6)

Question:display the names of employee  with in the company whose sal is grater
than the company avg sal?

solutions-1:

select s.ename from sal s,emp1 e,com c where s.ename=e.ename and c.cname=s.cname and s.sa>=(select avg(sa) from sal group by cname having cname=c.cname)




solutions-2:(By Using Inline view)


select s.ename from sal s,(select avg(sa) a,cname from sal group by cname) i where s.cname=i.cname and s.sa>=i.a

solutions-3:

create view tem as select cname,avg(sa) avge from sal group by cname;

 select s.ename from sal s,tem t where s.cname=t.cname and s.sa>=t.avge;