Oracle Applications: Oracle9i - SQL Queries / Answers

select * from dept;
select * from emp;
select ename,job from emp;
select ename,sal from emp;
select empno,sal+comm from emp;
select empno,ename,12*sal+nvl(comm,0) annualsal from emp;
select ename from emp where deptno = 10;
select ename from emp wher job = 'CLERK' and sal > 3000;
select empno,ename from emp where comm is not null and comm > 0;
select empno,ename from emp where comm is null and comm = 0;
select ename from emp where (job='CLERK' or job='SALESMAN' or job='ANALYST') and sal>3000;
select ename from emp where sysdate - hiredate > 5*365;
select * from emp where hiredate between '30-jun-1990' and '31-dec-1990';
select sysdate from dual;
select * from dba_users;
select * from tab;
show user;
select ename from emp where deptno in (10,20,40) or job in ('CLERK','SALESMAN','ANALYST');
select ename from emp where ename like 'S%';
select ename from emp where ename like '%S';
select ename from emp where ename like '_S%';
select ename from emp where length(ename)=5;

select ename from emp where ename like '_____';

select * from emp minus (select * from emp where empno in (select mgr from emp));

select * from emp where empno not in (select mgr from emp where mgr is not null);

select * from emp e where empno not in (select mgr from emp where e.empno=mgr);

select job from emp where job not in ('CLERK','ANALYST','SALESMAN');
set pause on;
select count(*) from emp;

select sum(sal), sum(nvl(comm,0)) from emp;
select max(sal) from emp;
select min(sal) from emp;
select avg(sal) from emp;
select max(sal) from emp where job='CLERK';
select max(sal) from emp where deptno=20;
select min(sal) from emp where job='SALESMAN';
select avg(sal) from emp where job='MANAGER';
select sum(sal)+sum(nvl(comm,0)) from emp where deptno=40;
select ename from emp order by sal;
select ename from emp order by sal desc;
select ename from emp order by ename;
select * from emp order by ename,deptno,sal;
Ans:select ename,sal,sal*12 "Annual Salary" from emp order by "Annual Salary" desc;

select ename,sal SA,sal*0.15 HRA,sal*0.10 DA,sal*5/100 PF, sal+(sal*0.15)+(sal*0.10)-(sal*.05) TOTALSALARY from emp ORDER BY TOTALSALARY DESC;
select deptno,count(*) from tvsemp group by deptno;
select job,count(*) from tvsemp group by job;
select deptno,sum(sal) from tvsemp group by deptno;
select deptno,max(Sal) from tvsemp group by deptno;
select job,sum(sal) from tvsemp group by job;
select job ,min(sal) from tvsemp group by job;
select deptno ,count(*) from tvsemp group by deptno having count(*)>3;
select job,sum(sal) from tvsemp group by job having sum(SAl)>40000;
select job,count(*) from tvsemp group by job having count(*)>3;
select ename, sal from tvsemp where sal>=(select max(sal) from tvsemp );
select ename,empno from tvsemp where sal=(select max(sal) from tvsemp where job='CLERK') and job='CLERK' ;

select ename,sal from tvsemp where sal>(select max(sal) from tvsemp where job='CLERK') AND job='SALESMAN';
select ename,sal from tvsemp where sal>(select min(sal) from tvsemp where job='SALESMAN') and job='CLERK';
select ename,sal from tvsemp where sal>all(select sal from tvsemp where ename='JONES' OR ename='SCOTT');
select ename,sal,deptno from tvsemp where sal in (select max(sal) from tvsemp group by deptno);
select ename,job from tvsemp where sal in (select max(sal) from tvsemp group by job);
select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno and d.dname='ACCOUNTING';
select e.ename,d.loc from emp e,tvsdept d where e.deptno=d.deptno and d.loc='CHICAGO';
select job ,sum(sal) from tvsemp group by job having sum(sal) >(select max(sal) from tvsemp where job='MANAGER');
select ename,deptno from tvsemp where sal>any(select min(sal) from tvsemp where deptno!=10 group by deptno) and deptno=10 ;
select ename,deptno from tvsemp where sal>all(select max(sal) from tvsemp where deptno!=10 group by deptno) and deptno=10 ;
select upper(ename) from tvsemp;
select Lower(ename) from tvsemp;
select InitCap(ename)from tvsemp;
select lentgh('RAMA') from dual;
select length(ename) from tvsemp;
select ename||' '||empno from tvsemp;
select substr('Oracle',3,2) from dual;
select lstr('Computer Maintenance Corporation','a' ) from dual;
select translate('Alliens','A','B') from Dual;
select ename ,replace(job,'MANAGER','BOSS') from tvsemp;
select empno,ename,deptno,Decode(deptno,10,'ACCOUNTING' ,20,'RESEARCH',30,'SALES','OPERATIONS')DName from tvsemp;
select sysdate-to_date('30-jul-1977') from dual;

select months_between(sysdate,to_date('30-jul-1977')) from dual;
select To_char(sysdate,'ddth Month Day year') from dual;
select empno,ename,to_char(Hiredate,' Day ddth Month year') from tvsemp;
select next_day(sysdate,'Saturday') from dual;
select To_Char(sysdate,'HH:MI:SS') from dual;
select Add_months(sysdate,-3) from dual
select job from tvsemp where job in (select job from tvsemp where deptno=20) and deptno=10;
select Distinct job from tvsemp where deptno in(10,20);
select job from tvsemp where deptno=10;
select empno,ename,job from tvsemp where empno not in (select mgr from tvsemp where mgr is not null );
select e.ename,d.dname, grade from emp e,dept d ,salgrade where e.deptno=d.deptno and dname='SALES' and grade=3;
select ename from tvsemp where job!='MANAGER';
select ename from tvsemp where length(ename)>=4 ;

select e.ename, d.loc from tvsemp e ,tvsdept d where d.loc like('%K') and ename like('S%')
select e.ename Superior, e1.ename Subordinate from tvsemp e,e1 where e.empno=e1.mgr and e.ename='JONES';
select ename, sal, (sal+(sal*0.20)) from tvsemp where (sal+(sal*0.20))>3000;
select e.ename, d.dname from tvsemp e, tvsdept d where e.deptno=d.deptno
select e.ename, d.dname from emp e, dept d where e.deptno=d.deptno and d.dname='SALES';
Select e.ename, d.dname, e.sal, e.comm from tvsemp e, dept d where e.deptno=d.deptno and sal between 2000 and 5000;
Select e.ename, e.sal, e1.ename,e1.sal from tvsemp e,e1 where e.mgr=e1.empno and e.sal>e1.sal;
select e.ename,e.deptno,e1.ename,e1.deptno from tvsemp e,e1 where e.mgr=e1.empno and e.deptno=e1.deptno;
select ename from tvsemp where mgr is null;
select ename,grade,deptno,sal from tvsemp ,salgrade where ( grade,sal) in

(select grade, sal from salgrade, tvsemp where sal between losal and hisal)

and grade!=4 and deptno in (10,30) and hiredate<'31-Dec-82';

update tvsemp set sal= (sal+(sal*0.10)) where comm is null;
select e.ename, e.hiredate, d.loc from tvsemp e,tvsdept d where e.deptno=d.deptno and hiredate<'31-Dec-82' and d.loc in ('NEWYORK','CHICAGO');
select e.ename,e.job,d.dname,d.loc from tvsemp e,tvsdept d where e.deptno=d.deptno and e.empno in (select mgr from tvsemp where mgr is notnull);
select e.ename sub,e1.ename from tvsemp e,e1 where e.mgr=e1.empno and e1.ename='JONES';
select ename, grade, hisal, sal from emp, salgrade where ename='FORD' and sal=hisal;

select grade, sal, hisal from tvsemp, salgrade where ename='FORD' and sal

between losal and hisal;

select ename, sal, hisal, grade from tvsemp, salgrade where ename= 'FORD'

and (grade,sal) in (select grade, hisal from salgrade,tvsemp where

sal between losal and hisal);

select e.ename sub,e1.ename sup, e.job, d.dname ,grade from tvsemp e,e1,salgrade,tvsdept d where e.mgr=e1.empno and e.sal between losal and hisal and e.deptno=d.deptno group by d.deptno,e.ename,e1.ename,e.job,d.dname,grade;

select e.ename sub,e1.ename sup,e.job,d.dname ,grade from tvsemp e,e1,salgrade,tvsdept d where e.mgr=e1.empno and e.sal between losal and hisal and e.deptno=d.deptno;

select e.ename, e.job, e.sal, d.dname ,grade from tvsemp e,salgrade, tvsdept d where (e.deptno=d.deptno and e.sal between losal and hisal ) order by e.sal desc;
select e.ename ,e1.ename,e.job,e.sal,d.dname from tvsemp e, e1,tvsdept d where e.mgr=e1.empno(+) and e.deptno=d.deptno ;
Select level , ename, job, mgr from emp where level <= 5 connect by prior empno = mgr start with mgr is null ;
select ename,sal from tvsemp where sal = (select max(sal) from tvsemp);
select * from tvsemp where sal =(select (max(sal)+min(sal))/2 from tvsemp;
select count(*) from tvsemp group by deptno having count(*)>3
select d.dname from tvsdept d, tvsemp e where e.deptno=d.deptno group by d.dname having count(*)>3;
select distinct e1.ename,e1.sal from tvsemp e,e1,dept d where e.deptno=d.deptno and e.mgr=e1.empno and e1.sal> (select avg(sal) from tvsemp);
select distinct e1.ename,e1.sal from tvsemp e,e1,dept d where e.deptno=d.deptno and e.mgr=e1.empno and e1.sal > any (select avg(sal) from tvsemp group by deptno);
select ename,sal,NVL(comm,0), sal+NVL(comm,0) from tvsemp where sal+NVL(comm,0) >any (select e.sal from tvsemp e );
select e.ename sub, e.sal from tvsemp e,e1,tvsdept d where e.deptno=d.deptno and e.mgr=e1.empno and e.sal<e1.sal and e.sal >any (select e2.sal from tvsemp e2, e,tvsdept d1 where e.mgr=e2.empno and d1.deptno=e.deptno);
Select ename, (select sum(sal) from emp) total_sal from emp;
select rn, lev,empno,sal,job from (Select rownum rn, lev, empno,sal, job, mgr from ( Select level lev , empno, ename, sal, job, mgr from emp connect by prior empno = mgr start with mgr is null order by level desc) ) where rn <=5;
Select e.ename,e.sal,e1.ename,e1.sal from tvsemp e,e1,tvsdept d where e.deptno=d.deptno and e.mgr=e1.empno and e.sal>e1.sal;
select e2.ename from emp e1,emp e2,emp e3 where e1.mgr=e2.empno and e2.mgr=e3.empno and e3.job!='PRESIDENT';
delete from tvsemp where empno is null;
delete from tvsemp e where e.deptno not in (select deptno from tvsdept);
select empno,sal from tvsemp where sal<(select min(LOSAL) from salgrade ) ;

OR sal>(select max(hisal) from salgrade);

select ename,sal,comm,sal+comm from tvsemp where sal+comm> any (select sal+comm from tvsemp);
select empno, hiredate,sysdate, to_char(sysdate,'yyyy') - to_char(hiredate,'yyyy') from tvsemp where to_char(sysdate,'yyyy') - to_char(hiredate,'yyyy')=30;
select ename ,sal from tvsemp where mod(sal,2)!=0;
select ename,sal from tvsemp where length(sal)=3;
Select empno, ename from tvsemp where trim(to_char(hiredate,'Mon')) =trim('DEC');
select ename from tvsemp where ename like('%A%');
select ename,sal from tvsemp where deptno in (select distinct sal from tvsemp);
select empno, hiredate, sal from tvsemp where trim(substr(hiredate,1,2)) = trim(substr(sal,-2,2));

select hiredate, sal from tvsemp where to_Char(hiredate,'dd')=trim(substr(sal,- 2,2))

select ename ,sal,0.10*sal from tvsemp where 0.10*sal=trim(to_char(hiredate,'yy'));
select e.ename from tvsemp e, tvsdept d where e.deptno=d.deptno and d.dname in('SALES','RESEARCH');
select ename, grade from tvsemp, salgrade where ( grade,sal) = (select grade, sal from salgrade, tvsemp where sal between losal and hisal and ename='JONES')
select ename ,hiredate from tvsemp where hiredate<'15-Jul-02' and hiredate >='01-jul-02';
Select ename ,hiredate from tvsemp where hiredate<'15-Jul-02'
delete from tvsemp where deptno in (select deptno from tvsemp group by deptno having count(*) <3 ;
delete from tvsemp where empno in (select empno from tvsemp where to_char(sysdate,'yyyy')- to_char(hiredate,'yyyy')>=10)

select deptno from tvsemp where empno is null;
select e2.ename from tvsemp e1,e2 where e1.mgr=e2.empno and e2.empno is not null
select d.dname from tvsdept d where length(d.dname) in (select count(*) from tvsemp e where e.deptno!=d.deptno group by e.deptno)

select a.ename,b.ename from tvsemp a,tvsemp b where a.hiredate=b.hiredate and a.empno!=b.empno
select ename,sal,grade ,substr(sal,grade,1) from tvsemp,salgrade where

grade!=substr(sal,1,1) and grade = substr(sal,grade,1) and

sal between losal and hisal

Select count(empno) from tvsemp where empno in (select a.empno from tvsemp a

intersect

select b.mgr from tvsemp b)

select a.ename,b.ename from tvsemp a,tvsemp b where a.hiredate=b.hiredate and a.empno!=b.empno;
select e2.ename,count(*) from tvsemp e1,e2 where e1.mgr=e2.empno group by e2.ename Having count(*)=(select max(count(*)) from tvsemp e1,e2 where e1.mgr=e2.empno group by e2.ename)
select ename,sal,lpad(translate(sal,sal,((sal +(sal*0.15))/50)),5,'$') from tvsemp
select ename"EMPLOYEE_AND",job"JOB" FROM TVSEMP;
select ename,to_char(hiredate,'Month dd yyyy') from tvsemp;
select ename,sal,

(

case when sal < 1500 then

'Below_Target'

when sal=1500 then

'On_Target'

when sal > 1500 then

'Above_Target'

else

'kkkkk'

End ) from tvsemp ;

;
select ename,hiredate, LAST_DAY ( next_day(hiredate,'Friday')),

case when to_char(hiredate,'dd') <=('15') then

LAST_DAY ( next_day(hiredate,'Friday'))

when to_char(hiredate,'dd')>('15') then

LAST_DAY( next_day(add_months(hiredate,1),'Friday'))

end case from tvsemp ;

select a.empno,a.ename ,a.sal,b.sal,b.empno,b.ename from tvsemp a, tvsemp b where a.mgr=b.empno and a.sal>b.sal
select a.empno,a.ename ,b.ename from tvsemp a, tvsemp b where a.mgr=b.empno and b.ename='BLAKE'
select * from emp where empno in (select mgr from emp);
select * from emp where mgr=(select empno from emp where ename='JONES') union select * from emp where empno = (select mgr from emp where ename='JONES');
select * from emp where &emp_ann_sal>30000;
select count(*) from emp where empno in (select mgr from emp);
select count(empno), count(distinct(empno)) from emp having count(empno) = (count(distinct(empno));
select e.ename,e.mgr,e.sal from emp e where sal in (select min(sal) from emp where mgr=e.mgr) and
e.sal>1000 order by sal;
select e.ename,e.job,(e.sal+nvl(e.comm,0))*12,e.deptno,d.dname,s.grade from emp e,salgrade s,dept d
where e.sal between s.losal and s.hisal and e.deptno=d.deptno and (e.sal+nvl(comm,0))*12 > 30000 and e.job<>'CLERK';
select * from emp e where hiredate < (select hiredate from emp where empno=e.mgr);
select e.empno,e.ename,m.empno Manager,m.ename ManagerName from emp e,emp m where e.mgr=m.empno;
select * from emp e where sal=(select max(sal) from emp where job=e.job);
select * from emp e where sal=(select min(sal) from emp where job=e.job) order by sal;
select deptno, max(hiredate) from emp group by deptno order by hiredate desc;
select ename,sal,deptno from emp e where sal>(select avg(sal) from emp where deptno=e.deptno) order by deptno;
select deptno,dname from dept where deptno not in (select distinct(deptno) from emp);
select deptno,sum(sal) from emp group by deptno having sum(sal)=(select max(sum(sal)) from emp group by deptno);
select count(*),to_char(hiredate,'yyyy') from emp group by to_char(hiredate,'yyyy');
select deptno, avg(sal) from emp group by deptno;
select empno, hiredate from emp wher hiredate=(select max(hiredate) from emp);
select * from emp where sal > (select min(sal) from emp where deptno=30);

select * from emp where sal>(select max(sal) from emp where deptno=30);

select * from emp where sal>all(select sal from emp where deptno=30);
select deptno from emp group by deptno having count(*)>3;
select * from emp where rownum < 11 minus select * from emp where rownum< 10;
select ename, upper(substr(ename,0,length(ename)/2)) || lower(substr(ename,length(ename)/2+1, length(ename))) UP_lo from emp
create table emp1 as select * from emp;
delete from emp where empno = ( select empno from emp where rownum < 11 minus select empno from emp where rownum< 10);
create table copyEMP as select * from emp;
select distinct(ename) from emp e where ename in (select ename from emp where e.empno<>empno);
select ename from emp order by ename desc;
select empno,ename from emp e,salgrade s where e.sal between s.losal and s.hisal and to_char(hiredate, 'mm')=grade;
select * from emp where to_char(hiredate,'dd') =deptno;
select substr(ename,1,1)||''||ename from emp;
select ename,sal,sal*15/100 pf from emp;
.
create table emp (empno number(5));
alter table emp add ename varchar2(20) not null;
alter table emp add constraint emp_empno primary key (empno);
alter table emp modify ename varchar2(30);
alter table emp add sal number(7,2);
alter table emp add constraint emp_sal_check check(sal<10000);
alter table emp disable constraint emp_sal_check;
alter table emp enable constraint emp_sal_check;
alter table emp add mgr number(5);
Alter table emp add constraint emp_mgr foreign key (empno;
alter table emp add deptno number(3);
alter table emp1 add constraint emp1_deptno foreign key (deptno) references dept(deptno);
create table newemp as select * from emp;
create table newemp as select empno,ename,dname from emp e,dept d where e.deptno=d.deptno;
delete from emp where floor(sysdate-hiredate)>2*365;
select emp set comm=300 where comm is null;
update emp set comm=comm*10/100 where comm is not null;
select ename,dname from emp e,dept d where e.deptno=d.deptno;
select empno,ename,loc from emp e,dept d where e.detpno=d.deptno;
select ename,dname from emp e,dept d where e.deptno(+)=d.deptno;
select e.ename,m.ename from emp e,emp m where e.mgr=m.empno;
select deptno,sum(sal) from emp group by deptno;
select deptno,count(*) from emp group by deptno;
select table_name from user_constraints where R_constraint_name IN (select constraint_name FROM USER_CONSTRAINTS WHERE TABLE_NAME = '&PARENTTABLENAME’) .

Pages

Sunday, October 30, 2011

Oracle9i - SQL Queries / Answers

No comments:

Post a Comment