Interview Questions Part 2

1) How to count the no of records of a table without using COUNT function?

Ans: using rownum we can get this.

SQL> select max(rownum) from emp;
MAX(ROWNUM)
-----------
         14

2)  How to print ,output as below?
*
**
***
****
*****
******
 
Ans:
SQL>  select lpad('*',level,'*') from dual
   connect by level <=6

LPAD('*',LEVEL,'*')
-----------------------
*
**
***
****
*****
******
6 rows selected

3) select 'Venky'  from dual where null=null .. What is the output ?

Ans: we need to remember that in oracle, we can't compare null with another null using equal operator and we always need to use "IS NULL" to compare so for above query, we get output as NULL.

SQL> select 'Venky'  from dual where null=null;
'VENKY'
--------------------------------
 
we ill get output as 'Venky' when we use is null as below.


SQL> select 'Venky'  from dual where null is null;
'VENKY'
--------------------------------
Venky


SQL> select * from dual where null is null; --> Defaut value will be 'X' for dual table
DUMMY
-----
X


4) I have one table EMP with columns :Empno,Depno,ename,sal.
Now i want to have max(sal) in each department?

Sol1: using Group by
SQL>  select max(sal),deptno from emp    group by deptno
 
  MAX(SAL) DEPTNO
---------- ------
      2850     30
      3000     20
      5000     10

Sol2: Using Analytical function

SQL>  select distinct deptno, max(sal)over(partition by deptno order by 1) from emp;
DEPTNO MAX(SAL)OVER(PARTITIONBYDEPTNO
------ ------------------------------
    10                           5000
    20                           3000
    30                           2850

5)  Addition to above question, i have table "DEPT" with columns deptno and deptno so now i need 
max(sal) in each department along with deptname ?

SQL>  select max(sal),e1.deptno,d1.dname from emp e1 join dept d1 on d1.deptno = e1.deptno
   group by e1.deptno,d1.dname;

  MAX(SAL) DEPTNO DNAME
---------- ------ --------------
      5000     10 ACCOUNTING
      3000     20 RESEARCH
      2850     30 SALES

6) Addition to above question, i need ename whose salary is maximum in each deptno along with deptname?



SQL>  with my_dummy as (
    select max(sal) sal1,e1.deptno,d1.dname from emp e1 join dept d1 on d1.deptno = e1.deptno
     group by e1.deptno,d1.dname)
   select e2.empno,e2.ename,m1.sal1,m1.dname,m1.deptno from my_dummy m1 join emp e2 on e2.sal = m1.sal1;

EMPNO ENAME            SAL1 DNAME          DEPTNO
----- ---------- ---------- -------------- ------
 7839 KING             5000 ACCOUNTING         10
 7698 BLAKE            2850 SALES              30
 7788 SCOTT            3000 RESEARCH           20
 7902 FORD             3000 RESEARCH           20



Interview Questions Part1: Check out here ---> Interview Questions Part1

Interview Questions Part3: Check out here ---> Interview Questions Part-3