Top 35 SQL Interview Questions for Oracle Apps

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

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

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

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

select * from emp where rownum <= &Enter_row_num;

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

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

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;

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

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

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

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

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

select ename, sal/12 as monthly_sal from emp;

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

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

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

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

select * from emp where ename like 'Sonu___';

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

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

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

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

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

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

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

Select * from emp where sal between 3000 and 7000;

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

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

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

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

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

SELECT * FROM AP_EXPENSE_REPORT_HEADERS_ALL WHERE EMPLOYEE_ID IN (SELECT PERSON_ID FROM PER_ALL_PEOPLE_F WHERE EMAIL_ADDRESS = '&Email_id');

SELECT PO_HEADER_ID FROM PO_headers_all union SELECT PO_HEADER_ID FROM PO_lines_all;

SELECT avg(RATE) from PO_headers_all;