#### 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 the N

^{th}maximum salary of an employee with a 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 Mathematical Function in the 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 than and greater than.

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 length 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 a subquery

select * from emp where sal> any(select sal from emp where sal<15000);

24. Select all salary <5000 from emp table using a 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 the same structure as emp?

Create table emp1 as select * from emp where 3=5;

27. How to retrieve 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**
## 0 comments:

## Post a Comment