Top 35 SQL Interview Questions for Oracle Apps

Frequently asked SQL questions for Oracle Apps technical interviews.

Article Overview

This collection of 35 SQL queries represents some of the most frequently asked questions in technical interviews for Oracle Apps R12 developers and techno-functional roles. Mastering these concepts, from basic data retrieval to more complex subqueries and joins, is essential for success.

1. Find all even-numbered rows

SQL/PLSQL Code

select * from emp where rowid in (select decode(mod(rownum,2),0,rowid, null) from emp);

2. Find all odd-numbered rows

SQL/PLSQL Code

select * from emp where rowid in (select decode(mod(rownum,2),0,null ,rowid) from emp);

3. Find the 5th highest salary

SQL/PLSQL Code

select distinct sal from emp e1 where 5 = (select count(distinct sal) from emp e2 where e1.sal <= e2.sal);

4. Find the 9th lowest salary

SQL/PLSQL Code

select distinct sal from emp e1 where 9 = (select count(distinct sal) from emp e2 where e1.sal >= e2.sal);

5. Select the first N rows using ROWNUM

SQL/PLSQL Code

select * from emp where rownum <= &Enter_row_num;

6. Select the last N rows

SQL/PLSQL Code

select * from emp minus select * from emp where rownum <= (select count(*) - &Enter_row_num from emp);

7. Find departments with no employees using NOT IN or NOT EXISTS

SQL/PLSQL Code

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. Find the top 21 highest salaries

SQL/PLSQL Code

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. Find the bottom 11 lowest salaries

SQL/PLSQL Code

select distinct sal from emp a where 11 >= (select count(distinct sal) from emp b where a.sal >= b.sal);

10. Find the Nth oldest hire date

SQL/PLSQL Code

select distinct hiredate from emp a where &n = (select count(distinct hiredate) from emp b where a.hiredate >= b.hiredate);

11. Example of a LEFT and RIGHT OUTER JOIN

SQL/PLSQL Code

-- Left Outer Join (returns all records from the right table, and matched records from the left)
select empno,ename,b.deptno,dname from emp a, dept b where a.deptno(+) = b.deptno and empno is null;

-- Right Outer Join (returns all records from the left table, and matched records from the right)
select empno,ename,b.deptno,dname from emp a, dept b where a.deptno = b.deptno(+) and empno is null;

12. Delete duplicate records from a table based on a column

SQL/PLSQL Code

delete from emp a where rowid != (select max(rowid) from emp b where a.empno=b.empno);

13. Count employees in each department using GROUP BY

SQL/PLSQL Code

select count(a.EMPNO), b.deptno, b.dname from emp a, dept b where a.deptno(+)=b.deptno group by b.deptno,dname;

14. Use a mathematical function in a query

SQL/PLSQL Code

select ename, sal/12 as monthly_sal from emp;

15. Select records using OR

SQL/PLSQL Code

select * from emp where deptno=90 or deptno=40;

16. Select records using less than and greater than

SQL/PLSQL Code

select * from emp where deptno=40 and sal > 1100;

17. Select records using NOT IN

SQL/PLSQL Code

select * from emp where job not in ('SALESMAN','CLERK');

18. Select records using IN

SQL/PLSQL Code

select * from emp where empid in('10','20','30');

19. Select records where name starts with 'Sonu' and is 7 characters long

SQL/PLSQL Code

select * from emp where ename like 'Sonu___';

20. Select records where name ends with 'Chauhan' using '%' wildcard

SQL/PLSQL Code

select * from emp where ename like '%Chauhan';

21. Use of COUNT function

SQL/PLSQL Code

select count(MGR), count(sal) from emp;

22. Calculate total salary including commission (handle NULLs with NVL)

SQL/PLSQL Code

select ename, (sal + nvl(comm,0)) as total_sal from emp;

23. Select salaries greater than ANY salary under 15000

SQL/PLSQL Code

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

24. Select salaries greater than ALL salaries under 5000

SQL/PLSQL Code

select * from emp where sal > all(select sal from emp where sal < 5000);

25. Order records by department, then by salary in descending order

SQL/PLSQL Code

select ename, deptno, sal from emp order by deptno, sal desc;

26. Create an empty table with the same structure as another table

SQL/PLSQL Code

Create table emp1 as select * from emp where 1=2;

27. Select records where salary is between 3000 and 7000

SQL/PLSQL Code

Select * from emp where sal between 3000 and 7000;

28. Select records from one table that have a matching record in another using EXISTS

SQL/PLSQL Code

select * from emp where exists(select 1 from dept where emp.deptno=dept.deptno);

29. Combine results from two tables using UNION

SQL/PLSQL Code

(Select * from emp) Union (Select * from emp1);

30. Find common records between two tables using INTERSECT

SQL/PLSQL Code

(Select * from emp) Intersect (Select * from emp1);

31. Find records in the first table but not in the second using MINUS

SQL/PLSQL Code

(Select * from emp) Minus (Select * from emp1);

32. Count total salary by department, but only for departments with more than 2 employees

SQL/PLSQL Code

SELECT deptno, sum(sal) As total_sal FROM emp GROUP BY deptno HAVING COUNT(empno) > 2;

33. Example of a Subquery to find expense reports for a specific email

SQL/PLSQL Code

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 to get IDs from different tables

SQL/PLSQL Code

SELECT PO_HEADER_ID FROM PO_headers_all union SELECT PO_HEADER_ID FROM PO_lines_all;

35. Example of aggregate functions like SUM, AVG

SQL/PLSQL Code

SELECT avg(RATE) from PO_headers_all;