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 '_____';

23. 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);

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

27. select sum(sal), sum(nvl(comm,0)) from emp;
28. select max(sal) from emp;
29. select min(sal) from emp;
30. select avg(sal) from emp;
31. select max(sal) from emp where job='CLERK';
32. select max(sal) from emp where deptno=20;
33. select min(sal) from emp where job='SALESMAN';
34. select avg(sal) from emp where job='MANAGER';
35. select sum(sal)+sum(nvl(comm,0)) from emp where deptno=40;
36. select ename from emp order by sal;
37. select ename from emp order by sal desc;
38. select ename from emp order by ename;
39. select * from emp order by ename,deptno,sal;
40. Ans:select ename,sal,sal*12 "Annual Salary" from emp order by "Annual Salary" desc;

41. 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;
42. select deptno,count(*) from tvsemp group by deptno;
43. select job,count(*) from tvsemp group by job;
44. select deptno,sum(sal) from tvsemp group by deptno;
45. select deptno,max(Sal) from tvsemp group by deptno;
46. select job,sum(sal) from tvsemp group by job;
47. select job ,min(sal) from tvsemp group by job;
48. select deptno ,count(*) from tvsemp group by deptno having count(*)>3;
49. select job,sum(sal) from tvsemp group by job having sum(SAl)>40000;
50. select job,count(*) from tvsemp group by job having count(*)>3;
51. select ename, sal from tvsemp where sal>=(select max(sal) from tvsemp );
52. select ename,empno from tvsemp where sal=(select max(sal) from tvsemp where job='CLERK') and job='CLERK' ;

53. select ename,sal from tvsemp where sal>(select max(sal) from tvsemp where job='CLERK') AND job='SALESMAN';
54. select ename,sal from tvsemp where sal>(select min(sal) from tvsemp where job='SALESMAN') and job='CLERK';
55. select ename,sal from tvsemp where sal>all(select sal from tvsemp where ename='JONES' OR ename='SCOTT');
56. select ename,sal,deptno from tvsemp where sal in (select max(sal) from tvsemp group by deptno);
57. select ename,job from tvsemp where sal in (select max(sal) from tvsemp group by job);
58. select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno and d.dname='ACCOUNTING';
59. select e.ename,d.loc from emp e,tvsdept d where e.deptno=d.deptno and d.loc='CHICAGO';
60. select job ,sum(sal) from tvsemp group by job having sum(sal) >(select max(sal) from tvsemp where job='MANAGER');
61. select ename,deptno from tvsemp where sal>any(select min(sal) from tvsemp where deptno!=10 group by deptno) and deptno=10 ;
62. select ename,deptno from tvsemp where sal>all(select max(sal) from tvsemp where deptno!=10 group by deptno) and deptno=10 ;
63. select upper(ename) from tvsemp;
64. select Lower(ename) from tvsemp;
65. select InitCap(ename)from tvsemp;
66. select lentgh('RAMA') from dual;
67. select length(ename) from tvsemp;
68. select ename||' '||empno from tvsemp;
69. select substr('Oracle',3,2) from dual;
70. select lstr('Computer Maintenance Corporation','a' ) from dual;
71. select translate('Alliens','A','B') from Dual;
72. select ename ,replace(job,'MANAGER','BOSS') from tvsemp;
73. select empno,ename,deptno,Decode(deptno,10,'ACCOUNTING' ,20,'RESEARCH',30,'SALES','OPERATIONS')DName from tvsemp;
74. select sysdate-to_date('30-jul-1977') from dual;

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

89. select e.ename, d.loc from tvsemp e ,tvsdept d where d.loc like('%K') and ename like('S%')
90. select e.ename Superior, e1.ename Subordinate from tvsemp e,e1 where e.empno=e1.mgr and e.ename='JONES';
91. select ename, sal, (sal+(sal*0.20)) from tvsemp where (sal+(sal*0.20))>3000;
92. select e.ename, d.dname from tvsemp e, tvsdept d where e.deptno=d.deptno
93. select e.ename, d.dname from emp e, dept d where e.deptno=d.deptno and d.dname='SALES';
94. 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;
95. Select e.ename, e.sal, e1.ename,e1.sal from tvsemp e,e1 where e.mgr=e1.empno and e.sal>e1.sal;
96. select e.ename,e.deptno,e1.ename,e1.deptno from tvsemp e,e1 where e.mgr=e1.empno and e.deptno=e1.deptno;
97. select ename from tvsemp where mgr is null;
98. 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';

99. update tvsemp set sal= (sal+(sal*0.10)) where comm is null;
100. 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');
101. 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);
102. select e.ename sub,e1.ename from tvsemp e,e1 where e.mgr=e1.empno and e1.ename='JONES';
103. 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);

104. 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;

105. 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;
106. 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 ;
107. Select level , ename, job, mgr from emp where level <= 5 connect by prior empno = mgr start with mgr is null ;
108. select ename,sal from tvsemp where sal = (select max(sal) from tvsemp);
109. select * from tvsemp where sal =(select (max(sal)+min(sal))/2 from tvsemp;
110. select count(*) from tvsemp group by deptno having count(*)>3
111. select d.dname from tvsdept d, tvsemp e where e.deptno=d.deptno group by d.dname having count(*)>3;
112. 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);
113. 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);
114. select ename,sal,NVL(comm,0), sal+NVL(comm,0) from tvsemp where sal+NVL(comm,0) >any (select e.sal from tvsemp e );
115. 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);
116. Select ename, (select sum(sal) from emp) total_sal from emp;
117. 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;
118. 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;
119. select e2.ename from emp e1,emp e2,emp e3 where e1.mgr=e2.empno and e2.mgr=e3.empno and e3.job!='PRESIDENT';
120. delete from tvsemp where empno is null;
121. delete from tvsemp e where e.deptno not in (select deptno from tvsdept);
122. select empno,sal from tvsemp where sal<(select min(LOSAL) from salgrade ) ;

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

123. select ename,sal,comm,sal+comm from tvsemp where sal+comm> any (select sal+comm from tvsemp);
124. select empno, hiredate,sysdate, to_char(sysdate,'yyyy') - to_char(hiredate,'yyyy') from tvsemp where to_char(sysdate,'yyyy') - to_char(hiredate,'yyyy')=30;
125. select ename ,sal from tvsemp where mod(sal,2)!=0;
126. select ename,sal from tvsemp where length(sal)=3;
127. Select empno, ename from tvsemp where trim(to_char(hiredate,'Mon')) =trim('DEC');
128. select ename from tvsemp where ename like('%A%');
129. select ename,sal from tvsemp where deptno in (select distinct sal from tvsemp);
130. 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))

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

138. select deptno from tvsemp where empno is null;
139. select e2.ename from tvsemp e1,e2 where e1.mgr=e2.empno and e2.empno is not null
140. 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)

141. select a.ename,b.ename from tvsemp a,tvsemp b where a.hiredate=b.hiredate and a.empno!=b.empno
142. 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

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

intersect

select b.mgr from tvsemp b)

144. select a.ename,b.ename from tvsemp a,tvsemp b where a.hiredate=b.hiredate and a.empno!=b.empno;
145. 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)
146. select ename,sal,lpad(translate(sal,sal,((sal +(sal*0.15))/50)),5,'$') from tvsemp
147. select ename"EMPLOYEE_AND",job"JOB" FROM TVSEMP;
148. select ename,to_char(hiredate,'Month dd yyyy') from tvsemp;
149. 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 ;

150. ;
151. 
     
152. 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 ;

153. 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
154. select a.empno,a.ename ,b.ename from tvsemp a, tvsemp b where a.mgr=b.empno and b.ename='BLAKE'
155. select * from emp where empno in (select mgr from emp);
156. 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');
157. select * from emp where &emp_ann_sal>30000;
158. select count(*) from emp where empno in (select mgr from emp);
159. select count(empno), count(distinct(empno)) from emp having count(empno) = (count(distinct(empno));
160. select e.ename,e.mgr,e.sal from emp e where sal in (select min(sal) from emp where mgr=e.mgr) and
161. e.sal>1000 order by sal;
162. 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
163. 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';
164. 
     
165. select * from emp e where hiredate < (select hiredate from emp where empno=e.mgr);
166. select e.empno,e.ename,m.empno Manager,m.ename ManagerName from emp e,emp m where e.mgr=m.empno;
167. select * from emp e where sal=(select max(sal) from emp where job=e.job);
168. select * from emp e where sal=(select min(sal) from emp where job=e.job) order by sal;
169. select deptno, max(hiredate) from emp group by deptno order by hiredate desc;
170. select ename,sal,deptno from emp e where sal>(select avg(sal) from emp where deptno=e.deptno) order by deptno;
171. select deptno,dname from dept where deptno not in (select distinct(deptno) from emp);
172. select deptno,sum(sal) from emp group by deptno having sum(sal)=(select max(sum(sal)) from emp group by deptno);
173. select count(*),to_char(hiredate,'yyyy') from emp group by to_char(hiredate,'yyyy');
174. select deptno, avg(sal) from emp group by deptno;
175. select empno, hiredate from emp wher hiredate=(select max(hiredate) from emp);
176. select * from emp where sal > (select min(sal) from emp where deptno=30);

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

178. select * from emp where sal>all(select sal from emp where deptno=30);
179. select deptno from emp group by deptno having count(*)>3;
180. select * from emp where rownum < 11 minus select * from emp where rownum< 10;
181. select ename, upper(substr(ename,0,length(ename)/2)) || lower(substr(ename,length(ename)/2+1, length(ename))) UP_lo from emp
182. create table emp1 as select * from emp;
183. delete from emp where empno = ( select empno from emp where rownum < 11 minus select empno from emp where rownum< 10);
184. create table copyEMP as select * from emp;
185. select distinct(ename) from emp e where ename in (select ename from emp where e.empno<>empno);
186. select ename from emp order by ename desc;
187. select empno,ename from emp e,salgrade s where e.sal between s.losal and s.hisal and to_char(hiredate, 'mm')=grade;
188. select * from emp where to_char(hiredate,'dd') =deptno;
189. select substr(ename,1,1)||''||ename from emp;
190. select ename,sal,sal*15/100 pf from emp;
191. .
192. create table emp (empno number(5));
193. alter table emp add ename varchar2(20) not null;
194. alter table emp add constraint emp_empno primary key (empno);
195. alter table emp modify ename varchar2(30);
196. alter table emp add sal number(7,2);
197. alter table emp add constraint emp_sal_check check(sal<10000);
198. alter table emp disable constraint emp_sal_check;
199. alter table emp enable constraint emp_sal_check;
200. alter table emp add mgr number(5);
201. Alter table emp add constraint emp_mgr foreign key (empno;
202. alter table emp add deptno number(3);
203. alter table emp1 add constraint emp1_deptno foreign key (deptno) references dept(deptno);
204. create table newemp as select * from emp;
205. create table newemp as select empno,ename,dname from emp e,dept d where e.deptno=d.deptno;
206. delete from emp where floor(sysdate-hiredate)>2*365;
207. select emp set comm=300 where comm is null;
208. update emp set comm=comm*10/100 where comm is not null;
209. select ename,dname from emp e,dept d where e.deptno=d.deptno;
210. select empno,ename,loc from emp e,dept d where e.detpno=d.deptno;
211. select ename,dname from emp e,dept d where e.deptno(+)=d.deptno;
212. select e.ename,m.ename from emp e,emp m where e.mgr=m.empno;
213. select deptno,sum(sal) from emp group by deptno;
214. select deptno,count(*) from emp group by deptno;
215. select table_name from user_constraints where R_constraint_name IN (select constraint_name FROM USER_CONSTRAINTS WHERE TABLE_NAME = '&PARENTTABLENAME’) .