add

About Me

My photo
Oracle Apps - Techno Functional consultant

Sunday, October 30

Oracle9i - SQL Queries / Answers


  1. select * from dept;
  2. select * from emp;
  3. select ename,job from emp;
  4. select ename,sal from emp;
  5. select empno,sal+comm from emp;
  6. select empno,ename,12*sal+nvl(comm,0) annualsal from emp;
  7. select ename from emp where deptno = 10;
  8. select ename from emp wher job = 'CLERK' and sal > 3000;
  9. select empno,ename from emp where comm is not null and comm > 0;
  10. select empno,ename from emp where comm is null and comm = 0;
  11. select ename from emp where (job='CLERK' or job='SALESMAN' or job='ANALYST') and sal>3000;
  12. select ename from emp where sysdate - hiredate > 5*365;
  13. select * from emp where hiredate between '30-jun-1990' and '31-dec-1990';
  14. select sysdate from dual;
  15. select * from dba_users;
  16. select * from tab;
  17. show user;
  18. select ename from emp where deptno in (10,20,40) or job in ('CLERK','SALESMAN','ANALYST');
  19. select ename from emp where ename like 'S%';
  20. select ename from emp where ename like '%S';
  21. select ename from emp where ename like '_S%';
  22. select ename from emp where length(ename)=5;
or
select ename from emp where ename like '_____';
  1. select * from emp minus (select * from emp where empno in (select mgr from emp));
or
select * from emp where empno not in (select mgr from emp where mgr is not null);
or
select * from emp e where empno not in (select mgr from emp where e.empno=mgr);
  1. select job from emp where job not in ('CLERK','ANALYST','SALESMAN');
  2. set pause on;
  3. select count(*) from emp;
  1. select sum(sal), sum(nvl(comm,0)) from emp;
  2. select max(sal) from emp;
  3. select min(sal) from emp;
  4. select avg(sal) from emp;
  5. select max(sal) from emp where job='CLERK';
  6. select max(sal) from emp where deptno=20;
  7. select min(sal) from emp where job='SALESMAN';
  8. select avg(sal) from emp where job='MANAGER';
  9. select sum(sal)+sum(nvl(comm,0)) from emp where deptno=40;
  10. select ename from emp order by sal;
  11. select ename from emp order by sal desc;
  12. select ename from emp order by ename;
  13. select * from emp order by ename,deptno,sal;
  14. Ans:select ename,sal,sal*12 "Annual Salary" from emp order by "Annual Salary" desc;
  1. 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;
  2. select deptno,count(*) from tvsemp group by deptno;
  3. select job,count(*) from tvsemp group by job;
  4. select deptno,sum(sal) from tvsemp group by deptno;
  5. select deptno,max(Sal) from tvsemp group by deptno;
  6. select job,sum(sal) from tvsemp group by job;
  7. select job ,min(sal) from tvsemp group by job;
  8. select deptno ,count(*) from tvsemp group by deptno having count(*)>3;
  9. select job,sum(sal) from tvsemp group by job having sum(SAl)>40000;
  10. select job,count(*) from tvsemp group by job having count(*)>3;
  11. select ename, sal from tvsemp where sal>=(select max(sal) from tvsemp );
  12. select ename,empno from tvsemp where sal=(select max(sal) from tvsemp where job='CLERK') and job='CLERK' ;
  1. select ename,sal from tvsemp where sal>(select max(sal) from tvsemp where job='CLERK') AND job='SALESMAN';
  2. select ename,sal from tvsemp where sal>(select min(sal) from tvsemp where job='SALESMAN') and job='CLERK';
  3. select ename,sal from tvsemp where sal>all(select sal from tvsemp where ename='JONES' OR ename='SCOTT');
  4. select ename,sal,deptno from tvsemp where sal in (select max(sal) from tvsemp group by deptno);
  5. select ename,job from tvsemp where sal in (select max(sal) from tvsemp group by job);
  6. select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno and d.dname='ACCOUNTING';
  7. select e.ename,d.loc from emp e,tvsdept d where e.deptno=d.deptno and d.loc='CHICAGO';
  8. select job ,sum(sal) from tvsemp group by job having sum(sal) >(select max(sal) from tvsemp where job='MANAGER');
  9. select ename,deptno from tvsemp where sal>any(select min(sal) from tvsemp where deptno!=10 group by deptno) and deptno=10 ;
  10. select ename,deptno from tvsemp where sal>all(select max(sal) from tvsemp where deptno!=10 group by deptno) and deptno=10 ;
  11. select upper(ename) from tvsemp;
  12. select Lower(ename) from tvsemp;
  13. select InitCap(ename)from tvsemp;
  14. select lentgh('RAMA') from dual;
  15. select length(ename) from tvsemp;
  16. select ename||' '||empno from tvsemp;
  17. select substr('Oracle',3,2) from dual;
  18. select lstr('Computer Maintenance Corporation','a' ) from dual;
  19. select translate('Alliens','A','B') from Dual;
  20. select ename ,replace(job,'MANAGER','BOSS') from tvsemp;
  21. select empno,ename,deptno,Decode(deptno,10,'ACCOUNTING' ,20,'RESEARCH',30,'SALES','OPERATIONS')DName from tvsemp;
  22. select sysdate-to_date('30-jul-1977') from dual;
  1. select months_between(sysdate,to_date('30-jul-1977')) from dual;
  2. select To_char(sysdate,'ddth Month Day year') from dual;

  3. select empno,ename,to_char(Hiredate,' Day ddth Month year') from tvsemp;
  4. select next_day(sysdate,'Saturday') from dual;
  5. select To_Char(sysdate,'HH:MI:SS') from dual;
  6. select Add_months(sysdate,-3) from dual
  7. select job from tvsemp where job in (select job from tvsemp where deptno=20) and deptno=10;
  8. select Distinct job from tvsemp where deptno in(10,20);
  9. select job from tvsemp where deptno=10;
  10. select empno,ename,job from tvsemp where empno not in (select mgr from tvsemp where mgr is not null );
  11. select e.ename,d.dname, grade from emp e,dept d ,salgrade where e.deptno=d.deptno and dname='SALES' and grade=3;
  12. select ename from tvsemp where job!='MANAGER';
  13. select ename from tvsemp where length(ename)>=4 ;

  1. select e.ename, d.loc from tvsemp e ,tvsdept d where d.loc like('%K') and ename like('S%')
  2. select e.ename Superior, e1.ename Subordinate from tvsemp e,e1 where e.empno=e1.mgr and e.ename='JONES';
  3. select ename, sal, (sal+(sal*0.20)) from tvsemp where (sal+(sal*0.20))>3000;
  4. select e.ename, d.dname from tvsemp e, tvsdept d where e.deptno=d.deptno
  5. select e.ename, d.dname from emp e, dept d where e.deptno=d.deptno and d.dname='SALES';
  6. 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;
  7. Select e.ename, e.sal, e1.ename,e1.sal from tvsemp e,e1 where e.mgr=e1.empno and e.sal>e1.sal;
  8. select e.ename,e.deptno,e1.ename,e1.deptno from tvsemp e,e1 where e.mgr=e1.empno and e.deptno=e1.deptno;
  9. select ename from tvsemp where mgr is null;
  10. 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';

  1. update tvsemp set sal= (sal+(sal*0.10)) where comm is null;
  2. 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');
  3. 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);
  4. select e.ename sub,e1.ename from tvsemp e,e1 where e.mgr=e1.empno and e1.ename='JONES';
  5. select ename, grade, hisal, sal from emp, salgrade where ename='FORD' and sal=hisal;
OR
select grade, sal, hisal from tvsemp, salgrade where ename='FORD' and sal
between losal and hisal;
OR
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);
  1. 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;
OR
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;
  1. 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;
  2. 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 ;
  3. Select level , ename, job, mgr from emp where level <= 5 connect by prior empno = mgr start with mgr is null ;
  4. select ename,sal from tvsemp where sal = (select max(sal) from tvsemp);
  5. select * from tvsemp where sal =(select (max(sal)+min(sal))/2 from tvsemp;
  6. select count(*) from tvsemp group by deptno having count(*)>3
  7. select d.dname from tvsdept d, tvsemp e where e.deptno=d.deptno group by d.dname having count(*)>3;
  8. 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);
  9. 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);
  10. select ename,sal,NVL(comm,0), sal+NVL(comm,0) from tvsemp where sal+NVL(comm,0) >any (select e.sal from tvsemp e );
  11. 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);
  12. Select ename, (select sum(sal) from emp) total_sal from emp;
  13. 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;
  14. 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;
  15. select e2.ename from emp e1,emp e2,emp e3 where e1.mgr=e2.empno and e2.mgr=e3.empno and e3.job!='PRESIDENT';
  16. delete from tvsemp where empno is null;
  17. delete from tvsemp e where e.deptno not in (select deptno from tvsdept);
  18. select empno,sal from tvsemp where sal<(select min(LOSAL) from salgrade ) ;
OR sal>(select max(hisal) from salgrade);
  1. select ename,sal,comm,sal+comm from tvsemp where sal+comm> any (select sal+comm from tvsemp);
  2. select empno, hiredate,sysdate, to_char(sysdate,'yyyy') - to_char(hiredate,'yyyy') from tvsemp where to_char(sysdate,'yyyy') - to_char(hiredate,'yyyy')=30;
  3. select ename ,sal from tvsemp where mod(sal,2)!=0;
  4. select ename,sal from tvsemp where length(sal)=3;
  5. Select empno, ename from tvsemp where trim(to_char(hiredate,'Mon')) =trim('DEC');
  6. select ename from tvsemp where ename like('%A%');
  7. select ename,sal from tvsemp where deptno in (select distinct sal from tvsemp);
  8. select empno, hiredate, sal from tvsemp where trim(substr(hiredate,1,2)) = trim(substr(sal,-2,2));
or
select hiredate, sal from tvsemp where to_Char(hiredate,'dd')=trim(substr(sal,- 2,2))
  1. select ename ,sal,0.10*sal from tvsemp where 0.10*sal=trim(to_char(hiredate,'yy'));
  2. select e.ename from tvsemp e, tvsdept d where e.deptno=d.deptno and d.dname in('SALES','RESEARCH');
  3. select ename, grade from tvsemp, salgrade where ( grade,sal) = (select grade, sal from salgrade, tvsemp where sal between losal and hisal and ename='JONES')
  4. select ename ,hiredate from tvsemp where hiredate<'15-Jul-02' and hiredate >='01-jul-02';
  5. Select ename ,hiredate from tvsemp where hiredate<'15-Jul-02'
  6. delete from tvsemp where deptno in (select deptno from tvsemp group by deptno having count(*) <3 ;
  7. delete from tvsemp where empno in (select empno from tvsemp where to_char(sysdate,'yyyy')- to_char(hiredate,'yyyy')>=10)
  1. select deptno from tvsemp where empno is null;
  2. select e2.ename from tvsemp e1,e2 where e1.mgr=e2.empno and e2.empno is not null
  3. 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)
  1. select a.ename,b.ename from tvsemp a,tvsemp b where a.hiredate=b.hiredate and a.empno!=b.empno
  2. 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
  1. Select count(empno) from tvsemp where empno in (select a.empno from tvsemp a
intersect
select b.mgr from tvsemp b)
  1. select a.ename,b.ename from tvsemp a,tvsemp b where a.hiredate=b.hiredate and a.empno!=b.empno;
  2. 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)
  3. select ename,sal,lpad(translate(sal,sal,((sal +(sal*0.15))/50)),5,'$') from tvsemp
  4. select ename"EMPLOYEE_AND",job"JOB" FROM TVSEMP;
  5. select ename,to_char(hiredate,'Month dd yyyy') from tvsemp;
  6. 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 ;
  1. ;

  2. 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 ;
  1. 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
  2. select a.empno,a.ename ,b.ename from tvsemp a, tvsemp b where a.mgr=b.empno and b.ename='BLAKE'
  3. select * from emp where empno in (select mgr from emp);
  4. 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');
  5. select * from emp where &emp_ann_sal>30000;
  6. select count(*) from emp where empno in (select mgr from emp);
  7. select count(empno), count(distinct(empno)) from emp having count(empno) = (count(distinct(empno));
  8. select e.ename,e.mgr,e.sal from emp e where sal in (select min(sal) from emp where mgr=e.mgr) and
  9. e.sal>1000 order by sal;
  10. 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
  11. 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';

  12. select * from emp e where hiredate < (select hiredate from emp where empno=e.mgr);
  13. select e.empno,e.ename,m.empno Manager,m.ename ManagerName from emp e,emp m where e.mgr=m.empno;
  14. select * from emp e where sal=(select max(sal) from emp where job=e.job);
  15. select * from emp e where sal=(select min(sal) from emp where job=e.job) order by sal;
  16. select deptno, max(hiredate) from emp group by deptno order by hiredate desc;
  17. select ename,sal,deptno from emp e where sal>(select avg(sal) from emp where deptno=e.deptno) order by deptno;
  18. select deptno,dname from dept where deptno not in (select distinct(deptno) from emp);
  19. select deptno,sum(sal) from emp group by deptno having sum(sal)=(select max(sum(sal)) from emp group by deptno);
  20. select count(*),to_char(hiredate,'yyyy') from emp group by to_char(hiredate,'yyyy');
  21. select deptno, avg(sal) from emp group by deptno;
  22. select empno, hiredate from emp wher hiredate=(select max(hiredate) from emp);
  23. select * from emp where sal > (select min(sal) from emp where deptno=30);
  1. select * from emp where sal>(select max(sal) from emp where deptno=30);
  1. select * from emp where sal>all(select sal from emp where deptno=30);
  2. select deptno from emp group by deptno having count(*)>3;
  3. select * from emp where rownum < 11 minus select * from emp where rownum< 10;
  4. select ename, upper(substr(ename,0,length(ename)/2)) || lower(substr(ename,length(ename)/2+1, length(ename))) UP_lo from emp
  5. create table emp1 as select * from emp;
  6. delete from emp where empno = ( select empno from emp where rownum < 11 minus select empno from emp where rownum< 10);
  7. create table copyEMP as select * from emp;
  8. select distinct(ename) from emp e where ename in (select ename from emp where e.empno<>empno);
  9. select ename from emp order by ename desc;
  10. select empno,ename from emp e,salgrade s where e.sal between s.losal and s.hisal and to_char(hiredate, 'mm')=grade;
  11. select * from emp where to_char(hiredate,'dd') =deptno;
  12. select substr(ename,1,1)||''||ename from emp;
  13. select ename,sal,sal*15/100 pf from emp;
  14. .
  15. create table emp (empno number(5));
  16. alter table emp add ename varchar2(20) not null;
  17. alter table emp add constraint emp_empno primary key (empno);
  18. alter table emp modify ename varchar2(30);
  19. alter table emp add sal number(7,2);
  20. alter table emp add constraint emp_sal_check check(sal<10000);
  21. alter table emp disable constraint emp_sal_check;
  22. alter table emp enable constraint emp_sal_check;
  23. alter table emp add mgr number(5);
  24. Alter table emp add constraint emp_mgr foreign key (empno;
  25. alter table emp add deptno number(3);
  26. alter table emp1 add constraint emp1_deptno foreign key (deptno) references dept(deptno);
  27. create table newemp as select * from emp;
  28. create table newemp as select empno,ename,dname from emp e,dept d where e.deptno=d.deptno;
  29. delete from emp where floor(sysdate-hiredate)>2*365;
  30. select emp set comm=300 where comm is null;
  31. update emp set comm=comm*10/100 where comm is not null;
  32. select ename,dname from emp e,dept d where e.deptno=d.deptno;
  33. select empno,ename,loc from emp e,dept d where e.detpno=d.deptno;
  34. select ename,dname from emp e,dept d where e.deptno(+)=d.deptno;
  35. select e.ename,m.ename from emp e,emp m where e.mgr=m.empno;
  36. select deptno,sum(sal) from emp group by deptno;
  37. select deptno,count(*) from emp group by deptno;
  38. select table_name from user_constraints where R_constraint_name IN (select constraint_name FROM USER_CONSTRAINTS WHERE TABLE_NAME = '&PARENTTABLENAME’) .

No comments: