Sql Useful For etl Developer

Sql Useful For etl Developer

NVL Function Usage in ...

CREATE TABLE test
(
id number(5),
name varchar2(30),
dob DATE
);

SELECT * FROM test;
INSERT INTO test (id) values(5)
Id      name    dob
5    null    null
null    null    null                   

Convert into null values to user defined values

Integer Values Converstion
select nvl(id,101) as Id----->101

String Formats Always use
NVL(TO_CHAR(name), 'Not Avaible') AS Name,

Date Formats
NVL(to_date(DOB),'25-MAY-1987') AS dob

SELECT
    NVL(ID,101)AS ID,
    NVL(TO_CHAR(name), 'Not Avaible') AS Name,
    NVL(to_date(DOB),'25-MAY-1987') AS dob
FROM
    test

101    Not Avaible    1987-05-25 00:00:00

===============================================================================================

Remove The Duplicate Data From Table using Differnt Ways...

CREATE TABLE duplicate
(id NUMBER(5),
name varchar2(20),
job varchar2(20));

INSERT INTO duplicate values(101,'king','admin')
INSERT INTO duplicate values(102,'king','admin')
INSERT INTO duplicate values(103,'blake','admin')
INSERT INTO duplicate values(104,'blake','sales')
INSERT INTO duplicate values(104,'blake','admin')

Data
Id    Name    Job
101    king    admin
102    king    admin
103    blake    admin
103    blake    sales
104    blake    admin
104    blake    admin
103    blake    sales

Remove The Duplicate From Table Based On Name Colum;

Method1:-
SELECT column_name, COUNT(column_name)
FROM TABLE
GROUP BY column_name
HAVING COUNT (column_name) > 1;

Ex:-
SELECT name,COUNT(name)
FROM    duplicate
GROUP BY name
having COUNT(name)>1;
Name Count
king    2
blake    3

Delete The Duplicate Values From Table:-

Method2:
DELETE FROM TABLE A
WHERE ROWID > (SELECT MIN(ROWID) FROM TABLE B
WHERE A.COLUMN=B.COLUMN

Using union Operator Dispaly the unique Values in Table

SELECT id,name FROM DUPLICATE
union
SELECT id,name FROM DUPLICATE
ID    Name
101    king
102    king
103    blake
104    blake
===================================================================================================================================

Dispaly Max and Min  Salary From Table:-

CREATE TABLE test3
(id NUMBER(5),
sal number(10));

INSERT INTO test3 values(101,6500);
INSERT INTO test3 values(102,6500);
INSERT INTO test3 values(103,5500);
INSERT INTO test3 values(104,4500);
INSERT INTO test3 values(105,3500);

Find The 1St Higest Salary:-

SELECT max(sal)FROM test3;

Show Only Sal:-6500

iwant Dispaly Row Of ThatSalary Id Also

SELECT * FROM test3 WHERE sal= (SELECT max(sal)FROM test3);
SELECT * FROM test3 WHERE sal= (SELECT min(sal)FROM test3);
Second to Nth Salary Display:-

SELECT    DISTINCT(sal)FROM test3  t1
WHERE    3 >(SELECT COUNT(DISTINCT(sal))FROM test3  t2
        WHERE t1.sal<t2.sal);

Using Row_Number:-

WITH T1 AS(SELECT id,sal,ROW_NUMBER()over( ORDER BY sal desc) AS RNK FROM test3)
SELECT * FROM t1 WHERE RNK=2;
===================================================================================================================

Joins:-

CREATE TABLE emp
(empid NUMBER(5),
empname varchar2(30),
empjob varchar2(30),
deptno number(2)
);

INSERT INTO emp values(103,'Adams','Accounts',30)
INSERT INTO emp values(102,'blake','Assiate',20)
INSERT INTO emp values(101,'king','software',10)
INSERT INTO emp values(105,'Miller','Accounts',null);
INSERT INTO emp values(104,'Adams','Operations',40)

CREATE TABLE DEPT
(deptno NUMBER(5),
 dname varchar2(5),
 dloc varchar2(6));

INSERT INTO dept values(10,'IT','HYD');
INSERT INTO dept values(20,'IT','BLR');
INSERT INTO dept values(30,'HR','HYD');
INSERT INTO dept values(40,'OP','HYD');
INSERT INTO dept values(NULL,'IT','HYD');

Inner Join:-

How To Dispaly matched Row Form Two Tables:-
SELECT
    E.EMPID,
    E.EMPNAME,
    E.EMPJOB,
    E.DEPTNO,
    D.DNAME,
    D.DLOC
FROM
    EMP E
INNER JOIN DEPT D ON
    E.DEPTNO = D.DEPTNO;

Ans:-
EMPID    EMPNAME   JOB          DEPTNO    DNAME    DLOC
101    king    software    10    IT    HYD
102    blake    Assiate        20    IT    BLR
103    Adams    Accounts    30    HR    HYD
104    Adams    Operations    40    OP    HYD

inner Join Perform Matched records from Both Table Based Equal Condition

Left Outer Join:-

Mathed +UnMatched Records From Left Side Table based on condition

SELECT
    E.EMPID,
    E.EMPNAME,
    E.EMPJOB,
    E.DEPTNO,
    D.DNAME,
    D.DLOC
FROM
    EMP E
LEFT OUTER JOIN DEPT D ON
    E.DEPTNO = D.DEPTNO;

Result:-

EMPID    EMPNAME   JOB          DEPTNO    DNAME    DLOC
101    king    software    10    IT    HYD
102    blake    Assiate        20    IT    BLR
103    Adams    Accounts    30    HR    HYD
104    Adams    Operations    40    OP    HYD
105    Miller    Accounts    Null    null    null   

In above Result All The records form left side table    

Right Outer Join:-

Mathed +UnMatched Records From Right Side Table based on condition

SELECT
    E.EMPID,
    E.EMPNAME,
    E.EMPJOB,
    E.DEPTNO,
    D.DNAME,
    D.DLOC
FROM
    EMP E
RIGHT OUTER JOIN DEPT D ON
    E.DEPTNO = D.DEPTNO;

Result:-
EMPID    EMPNAME   JOB          DEPTNO    DNAME    DLOC
101    king    software    10    IT    HYD
102    blake    Assiate    20    IT    BLR    null
103    Adams    Accounts    30    HR    HYD
104    Adams    Operations    40    OP    HYD
Null    Null    Null        null    IT    null   

In above Result All The records form right side table

Full Outer Join:-

Right outer join +left outer join = Full Outer Join

SELECT
    E.EMPID,
    E.EMPNAME,
    E.EMPJOB,
    E.DEPTNO,
    D.DNAME,
    D.DLOC
FROM
    EMP E
full outer JOIN DEPT D ON
    E.DEPTNO = D.DEPTNO;

Result:-

EMPID    EMPNAME   JOB          DEPTNO    DNAME    DLOC
101    king    software    10    IT    HYD
102    blake    Assiate    20    IT    BLR    null
103    Adams    Accounts    30    HR    HYD
104    Adams    Operations    40    OP    HYD
Null    null    null        null    IT    null   
105    Miller    Accounts    null    null    null   

   

Unmatched Records form Left Side Table:-

Simple leftOuter join

What Is The umanthed record means The values Null Then umanthed record of Table

Use where condition on Right Table values null

SELECT
    E.EMPID,
    E.EMPNAME,
    E.EMPJOB,
    E.DEPTNO,
    D.DNAME,
    D.DLOC
FROM
    EMP E
left outer JOIN DEPT D ON
    E.DEPTNO = D.DEPTNO WHERE d.DEPTNO IS null;
Results
EMPID    EMPNAME   JOB          DEPTNO    DNAME    DLOC
105    Miller    Accounts    null    null    null

Unmatched Records form right Side Table:-

SELECT
    E.EMPID,
    E.EMPNAME,
    E.EMPJOB,
    E.DEPTNO,
    D.DNAME,
    D.DLOC
FROM
    EMP E
right outer JOIN DEPT D ON
    E.DEPTNO = D.DEPTNO WHERE e.DEPTNO IS null;
EMPID    EMPNAME   JOB          DEPTNO    DNAME    DLOC
null     null      null        null    IT      null