How to get distinct values without using Distinct Keyword - Interview Questions Part 3

How to get distinct values without using Distinct Keyword:-

Generally we use DISTINCT keyword to get distinct values/to filter duplicate values.

Alternative for DISTINCT:

Lets take following table with data for this scenario:

SQL>  select * from dept1;
DEPTNO DNAME          LOC
------ -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
    40 OPERATIONS     BOSTON
    40 medicine       LONDON

We generally use as below.

SQL>  select distinct deptno from dept1;
DEPTNO
------
    30
    20
    40
    10

1st Alternative: Using Group By 

SQL>  select deptno from dept1 group by deptno;
DEPTNO
------
    30
    20
    40
    10

2ndAlternative: Using Row_number analytical function

 SQL> select * from (select deptno, row_number()over(partition by deptno order by 1) rn from dept1) where rn=1 ;

DEPTNO         RN
------ ----------
    10          1
    20          1
    30          1
    40          1

 3rd Method: Using SET operator (i.e UNION)

SQL>  select deptno from dept1
       union
      select deptno from dept1
 
DEPTNO
------
    10
    20
    30
    40


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

Interview Questions Part2: Check out here ---> Interview Questions Part-2

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

Different ways to Delete Duplicate Records - Interview Questions Part 1

1) How to find duplicated records and write all possible queries to find duplicate records?

Method 1: Using Rowid

Ans: When someone ask you to get duplicate then you need to give preference write query using ROWID as its faster to retrieve records from data file.

SQL> select * from dept_1;
DEPTNO DNAME          LOC
------ -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
    40 OPERATIONS     BOSTON
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
6 rows selected


we have taken above table for this scenario and  here we could see dept no 10 and 20 are repeated so lets find out using query.



First lets understand about rowid here,

ROWID: rowid is pseudocolumn and it can be used as Data type same as integer, varchar2.

Oracle server will assign unique address for each record in each table to identify row in table.

we can see in above screenshot that 10,20 having different rowid it means we can understand that there are more than 1 record for deptno 10 and 20.

Below query is showing our required output and if you NOT IN in below query then you will get only duplicate records.

SQL> select * from dept_1 where rowid in (select min(rowid) from dept_1 group by deptno);
 
DEPTNO DNAME          LOC
------ -------------- -------------
    30 SALES                  CHICAGO
    20 RESEARCH         DALLAS
    40 OPERATIONS     BOSTON
    10 ACCOUNTING   NEW YORK


Method 2: Using Group by and having clause.

SQL> select deptno from dept_1    group by deptno having count(deptno) >1;
DEPTNO
------
    20
    10


Method 3: Using Count Analytical function and Inline view.

Here we are using count function and then finally with inline view we are getting output.

SQL> select * from (select  deptno, count(*)over(partition by deptno order by deptno) rn from dept_1)   where rn>1
 
DEPTNO         RN
------ ----------
    10          2
    10          2
    20          2
    20          2

Method 4: Using Row_number Analytical function and Inline view.

SQL> select * from
   (select deptno, row_number()over(partition by deptno order by deptno) r_number, rowid from dept_1)    where r_number <>1

  DEPTNO   R_NUMBER ROWID
------ ---------- ------------------------------------
    10          2 ABE8AUAF7AAAx4fAAA
    20          2 ABE8AUAF7AAAx4fAAB


Method 5: Using Dense_rank Analytical function and Inline view.

Note: in below query we are using "Order by rowid" to get sequential values.

 SQL> select * from
    (select deptno, dense_rank()over(partition by deptno order by rowid) r_number, rowid from dept_1)     where r_number <>1
 
DEPTNO   R_NUMBER ROWID
------ ---------- --------------------
    10          2 ABE8AUAF7AAAx4fAAA
    20          2 ABE8AUAF7AAAx4fAAB



Interview Questions Part2: Check out here ---> Interview Questions Part-2

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