Top 35 SQL Interview Question asked in Oracle Apps R12 Technical Interview
or
SQL TOP 35 Frequently asked question in Technical Interview
Frequently asked Technical (SQL) question in Oracle apps r12 -:
1. To find EVEN NUMBER
select * from emp where rowid in (select decode(mod(rownum,2),0,rowid, null) from emp);
2. To find EVEN NUMBER
select * from emp where rowid in (select decode(mod(rownum,2),0,null ,rowid) from emp);
3. To find the 5th MAX salary
select distinct sal from emp e1 where 5 = (select count(distinct sal) from emp e2 where e1.sal <= e2.sal);
4. To find the 9th MIN salary in the emp table.
select distinct sal from emp e1 where 9 = (select count(distinct sal) from emp e2 where e1.sal >= e2.sal);
5. How can we use Rownum function/How can get output for first 10 row output
select * from emp where rownum <= &Enter_row_num;
6. How can get output for Last 10 row output
select * from emp minus select * from emp where rownum <= (select count(*) - &Enter_row_num from emp);
7. Use of Not in,Not Exists and /Department name without allocation of any Employee in it .
select * from dept where deptno not in (select deptno from emp);
OR
select * from dept a where not exists (select * from emp b where a.deptno = b.deptno);
8. To find 21 Max salaries ?
select distinct sal from emp a where 21 >= (select count(distinct sal) from emp b where a.sal <= b.sal) order by a.sal desc;
9. To find 11 Min salaries ?
select distinct sal from emp a where 11 >= (select count(distinct sal) from emp b where a.sal >= b.sal);
10. To find nth maximum salary of employee with distinct function ?
select distinct hiredate from emp a where &n = (select count(distinct sal) from emp b where a.sal >= b.sal);
11. Example of Left or right join
select empno,ename,b.deptno,dname from emp a, dept b where a.deptno(+) = b.deptno and empno is null;
select empno,ename,b.deptno,dname from emp a, dept b where a.deptno = b.deptno(+) and empno is null;
12. To delete duplicate Records from Table
delete from emp a where rowid != (select max(rowid) from emp b where a.empno=b.empno);
13. Use of Group by Function
select count(EMPNO), b.deptno, dname from emp a, dept b where a.deptno(+)=b.deptno group by b.deptno,dname;
14. Use of Mathmatical Function in query
select ename,sal/12 as monthlysal from emp;
15. Select all record "OR"
select * from emp where deptno=90 or deptno=40;
16. Select all record Using Less then and greater then.
select * from emp where deptno=40 and sal>1100;
17. Use of "Not in" by example
select * from emp where job not in ('SALESMAN','CLERK');
18. Use of "IN" by example
select * from emp where empid in('10','20','30');
19. Select all records where ename starts with 'Sonu' and its lenth is 8 char.
select * from emp where ename like'Sonu___';
20. Select all records where ename end with 'Chauhan'/Use of "%"
select * from emp where ename like'%Chauhan';
21. Use of Count function
select count(MGR),count(sal) from emp;
22. Use Formula in select
select ename,(sal+nvl(comm,0)) as totalsal from emp;
23. Select any salary <15000 from emp table using subquery
select * from emp where sal> any(select sal from emp where sal<15000);
24. Select all salary <5000 from emp table using subquery
select * from emp where sal> all(select sal from emp where sal<5000);
25. Select all the employee group by deptno and sal in descending order.
select ename,deptno,sal from emp order by deptno,sal desc;
26. How can I create an empty table emp1 with same structure as emp?
Create table emp1 as select * from emp where 3=5;
27. How to retrive record where sal between 3000 to 7000? / Use of "Between"
Select * from emp where sal>=3000 And sal<7000
28. Select all records where dept no of both emp and dept table matches.
select * from emp where exists(select * from dept where emp.deptno=dept.deptno)
29. How to use Union between two table
(Select * from emp) Union (Select * from emp1)
30. To find Intersect/Common attribute value
(Select * from emp) Intersect (Select * from emp1)
31. Use of "Minus"
(Select * from emp) Minus (Select * from emp1)
32. Count the total sal deptno wise where more than 2 employees exist.
SELECT deptno, sum(sal) As totalsal FROM emp GROUP BY deptno HAVING COUNT(empno) > 2
33. Example of Subquery
SELECT * FROM AP_EXPENSE_REPORT_HEADERS_ALL WHERE EMPLOYEE_ID IN ( SELECT PERSON_ID FROM PER_ALL_PEOPLE_F WHERE EMAIL_ADDRESS = &Email_id )
34. Example of Union
SELECT PO_HEADER_ID FROM PO_headers_all
union
SELECT PO_HEADER_ID FROM PO_lines_all
35 Example of Function Like Sum ,Avg etc
SELECT avg(RATE) from PO_headers_all
“A GUIDE FOR ORACLE E-BUSINESS SUITE” – A complete Solution and Learning platform For Oracle Apps R12/11i, Training, India Localization, Technical, P2P, O2C, Drop-shipment, AR/AP Cycle, Implementation, Bug or error in Oracle Apps R12/11i, Up-gradation, Support, Alert, Personalization, Discoverer management, OracleApps R12/11i Module, Finance, SCM, MRP, ASCP, WIP, BOM, FA, AP, AR, OM, INV, AOL, MOAC, TCA structure, Project Accounting, CM, Functional,Oracle DBA, ERP, Techno-functional, OAF, Oracle Apps Interview Question and Error in Oracle apps R12/11i. For More Information Visit on www.OracleAppsGuide.com Or Subscribe your email-id on OracleAppsGuide
