Discussing pl/sql interview questions.

      Discussing pl/sql interview questions 

How to generate row number in SQL Without ROWNUM

SQL> SELECT ename, sal, (SELECT COUNT(*)  FROM EMP i WHERE o.ename >= i.ename) row_num

  2  FROM EMP o

  3  order by row_num;

ENAME             SAL    ROW_NUM

---------- ---------- ----------

ADAMS            1100          1

ALLEN            1600          2

BLAKE            2950          3

CLARK            2550          4

FORD             3000          5

JAMES             950          6

JONES            3075          7

KING             5000          8

MARTIN           1250          9

MILLER           1300         10

SCOTT            3000         11

SMITH             800         12

TURNER           1500         13

WARD             1250         14

14 rows selected.

What is the difference between WHERE clause and HAVING clause?

WHERE and HAVING both filters out records based on one or more conditions. The difference is, WHERE clause can only be applied on a static non-aggregated column whereas we will need to use HAVING for aggregated columns.

What is the difference between JOIN and UNION?

SQL JOIN allows us to “lookup” records on other table based on the given conditions between two tables. For example, if we have the department ID of each employee, then we can use this department ID of the employee table to join with the department ID of department table to lookup department names.

UNION operation allows us to add 2 similar data sets to create resulting data set that contains all the data from the source data sets. Union does not require any condition for joining. For example, if you have 2 employee tables with same structure, you can UNION them to create one result set that will contain all the employees from both of the tables.

SQL> SELECT DEPTNO FROM EMP

  2  UNION

  3  SELECT DEPTNO FROM DEPT;

 DEPTNO

----------

        10

        20

        30

        40

What is the difference between ROWNUM pseudo column and ROW_NUMBER() function?

ROWNUM is a pseudo column present in Oracle database returned result set prior to ORDER BY being evaluated. So ORDER BY ROWNUM does not work.

ROW_NUMBER() is an analytical function which is used in conjunction to OVER() clause wherein we can specify ORDER BY and also PARTITION BY columns.

Suppose if you want to generate the row numbers in the order of ascending employee salaries for example, ROWNUM will not work. But you may use ROW_NUMBER() OVER() like shown below:

SQL> SELECT Ename, sal, row_number() over(order by sal desc) rownum_by_sal

  2  FROM EMP;

ENAME             SAL ROWNUM_BY_SAL

---------- ---------- -------------

KING             5000             1

JONES            3075             2

FORD             3000             3

SCOTT            3000             4

BLAKE            2950             5

CLARK            2550             6

ALLEN            1600             7

TURNER           1500             8

MILLER           1300             9

WARD             1250            10

MARTIN           1250            11

ADAMS            1100            12

JAMES             950            13

SMITH             800            14

14 rows selected.

What are the differences among ROWNUM, RANK and DENSE_RANK?

ROW_NUMBER assigns contiguous, unique numbers from 1.. N to a result set.

RANK does not assign unique numbers—nor does it assign contiguous numbers. If two records tie for second place, no record will be assigned the 3rd rank as no one came in third, according to RANK. See below:

SQL> SELECT ename, sal, rank() over(order by sal desc) rank_by_sal

  2  FROM EMP;

ENAME             SAL RANK_BY_SAL

---------- ---------- -----------

KING             5000           1

JONES            3075           2

FORD             3000           3

SCOTT            3000           3

BLAKE            2950           5

CLARK            2550           6

ALLEN            1600           7

TURNER           1500           8

MILLER           1300           9

WARD             1250          10

MARTIN           1250          10

ADAMS            1100          12

JAMES             950          13

SMITH             800          14

14 rows selected.

DENSE_RANK, like RANK, does not assign unique numbers, but it does assign contiguous numbers. Even though two records tied for second place, there is a third-place record. See below:

SQL> SELECT ename, sal, dense_rank() over(order by sal desc) dense_rank_by_sal

  2  FROM EMP;

ENAME             SAL DENSE_RANK_BY_SAL

---------- ---------- -----------------

KING             5000                 1

JONES            3075                 2

FORD             3000                 3

SCOTT            3000                 3

BLAKE            2950                 4

CLARK            2550                 5

ALLEN            1600                 6

TURNER           1500                 7

MILLER           1300                 8

WARD             1250                 9

MARTIN           1250                 9

ADAMS            1100                10

JAMES             950                11

SMITH             800                12

14 rows selected.