Oracle -Interview questions

Find Managers and employees reporting to them

SELECT     LEVEL, empno, ename, mgr
FROM emp CONNECT BY PRIOR empno = mgr START WITH mgr IS NULL;


Number of occurrence of 'a' in the string

Select length(translate('saravana.','abcdefghijklmnopqrstuvwxyz., ','a')) from dual;


Find even records

SELECT k   FROM (SELECT empno k, MOD (ROWNUM, 2) t FROM emp)  WHERE t = 0

2nd max salary

Select max(sal) from emp where sal not in (select max(sal) from emp);

Maximum salary in each dept

SELECT e.ename, e.deptno, e.sal
  FROM emp e
 WHERE e.sal = (SELECT MAX (e2.sal)
                  FROM emp e2
                 WHERE e2.deptno = e.deptno);

n-th row selection

SELECT * FROM t1 a
 WHERE  n = (SELECT COUNT(rowid)
  FROM t1 b
  WHERE a.rowid >= b.rowid);
 or
SELECT rownum,empno FROM scott.emp a GROUP BY rownum,empno HAVING rownum = <rownumber>;

Fetch records between 2 ranges

SELECT   ROWNUM, empno
    FROM emp
GROUP BY ROWNUM, empno
  HAVING ROWNUM BETWEEN 2 AND 4;


Nth highest salary

SELECT * FROM emp a
 WHERE &n = (SELECT COUNT (DISTINCT sal) FROM emp   WHERE a.sal <= sal);

 eg:
 SELECT     LEVEL, MAX (sal)
       FROM emp
      WHERE LEVEL = 3
 CONNECT BY PRIOR sal > sal
   GROUP BY LEVEL

Nth Minimum salary

SELECT     LEVEL, MIN (sal)
      FROM emp
     WHERE LEVEL = 1--->1st level
CONNECT BY PRIOR sal >sal
  GROUP BY LEVEL

TOP 5 employees

SELECT *
  FROM (SELECT   ROWNUM, e.*
            FROM emp e
        ORDER BY sal DESC)
 WHERE ROWNUM < 6

About Oracle views

Once you create a view on some tables and if the referenced column is dropped from the table then view would have errors.

How Oracle processes a query

 How oracle processes a query
 ------------------------------
 1)The user executes a SQL statement and commits the transaction. For example,
 the user changes a name in a row of a table.
 2)The server process receives the statement and checks the shared pool for any
 shared SQL area that contains a similar SQL statement. If a shared SQL area is
 found, the server process checks the user’s access privileges to the requested
 data, and the previously existing shared SQL area is used to process the
 statement. If not, a new shared SQL area is allocated for the statement so it can
 be parsed and processed.
 3)The server process retrieves any necessary data values from the actual datafile
 (table) or those stored in the system global area.
 4)The server process modifies data in the system global area. The DBWn process
 writes modified blocks permanently to disk when doing so is efficient. Because
 the transaction is committed, the LGWR process immediately records the
 transaction in the online redo log file.
 5)If the transaction is successful, the server process sends a message across the
 network to the application. If it is not successful, an error message is
 transmitted.

Oracle-Find Even records

Find even records
-------------------------
SELECT k   FROM (SELECT empno k, MOD (ROWNUM, 2) t FROM emp)  WHERE t = 0

Oracle order by NULL

During ORDER BY sorts, NULL values always come first when the order is descending,
and last when the order is ascending. This default order can be changed by using the
clauses NULLS FIRST and NULLS LAST
E.g:
SELECT * FROM ACE ORDER BY 1 ASC NULLS FIRST
SELECT * FROM ACE ORDER BY 1 DESC NULLS LAST