To Find Duplicate Data Quarter-Wise in Oracle

Query to fetch duplicate data entered Quarter-wise in Oracle:

Step 1: Create a Table “TEMP” as shown below:

CREATE TABLE TEMP

(PRODUCT_ID NUMBER PRIMARY KEY,

STOCK_NO VARCHAR2(30),

PRICE VARCHAR2(30),

QTY NUMBER,

CREATION_DATE DATE);

Step 2: Insert data into “TEMP” Table.

INSERT INTO TEMP VALUES (1,'MM001','45.55',5,'22-JAN-2012');

INSERT INTO TEMP VALUES (2,'MM002','55.00',6,'2-FEB-2012');

INSERT INTO TEMP VALUES (3,'MM003','60.00',8,'20-APR-2012');

INSERT INTO TEMP VALUES (4,'MM001','46.00',5,'29-MAR-2012');

INSERT INTO TEMP VALUES (5,'MM004','40.00',9,'2-JUL-2012');

INSERT INTO TEMP VALUES (6,'MM003','61.00',65,'3-MAY-2012');

INSERT INTO TEMP VALUES (7,'MM005','69.00',23,'22-AUG-2012');

INSERT INTO TEMP VALUES (8,'MM006','56.00',2,'22-SEP-2012');

INSERT INTO TEMP VALUES (9,'MM007','80.00',6,'22-OCT-2012');

INSERT INTO TEMP VALUES (10,'MM008','75.00',7,'22-NOV-2012');

INSERT INTO TEMP VALUES (11,'MM003','75.00',8,'22-NOV-2014');

Commit;

Step 3: Select the “TEMP” Table.

SELECT * FROM TEMP;

PRODUCT_ID	STOCK_NO	PRICE	QTY	CREATION_DATE
1	MM001	45.55	5	22-Jan-12
2	MM002	55	6	2-Feb-12
3	MM003	60	8	20-Apr-12
4	MM001	46	5	29-Mar-12
5	MM004	40	9	2-Jul-12
6	MM003	61	65	3-May-12
7	MM005	69	23	22-Aug-12
8	MM006	56	2	22-Sep-12
9	MM007	80	6	22-Oct-12
10	MM008	75	7	22-Nov-12
11	MM003	75	8	22-Nov-14

Step 4: Run the below query to fetch duplicate Stock Numbers entered Quarter-wise from given set of data. 

SELECT * FROM TEMP WHERE (STOCK_NO,TRUNC(CREATION_DATE,'Q')) IN(SELECT STOCK_NO,TRUNC(CREATION_DATE,'Q') FROM TEMP GROUP BY STOCK_NO,TRUNC(CREATION_DATE,'Q') HAVING COUNT( STOCK_NO)>1)

ORDER BY STOCK_NO;

Output:

PRODUCT_ID	STOCK_NO	PRICE	QTY	CREATION_DATE
1	MM001	45.55	5	22-Jan-12
4	MM001	46	5	29-Mar-12
3	MM003	60	8	20-Apr-12
6	MM003	61	65	3-May-12

To Find Missing Values from Set of Values in Oracle

Query to fetch missing numeric values from given set of values in oracle table:

Step 1: Create a Table “TEMP” as shown below:

CREATE TABLE TEMP (A NUMBER);

Step 2: Insert numeric values into “TEMP” Table.

INSERT INTO TEMP VALUES(1);

INSERT INTO TEMP VALUES(3);

INSERT INTO TEMP VALUES(9);

INSERT INTO TEMP VALUES(12);

INSERT INTO TEMP VALUES(15);

INSERT INTO TEMP VALUES(16);

INSERT INTO TEMP VALUES(20);

INSERT INTO TEMP VALUES(19);

Commit;

Step 3: Select the “TEMP” Table.

SELECT * FROM TEMP;

A
19
1
3
9
12
15
16
20

Step 4: Run any of the below query to fetch missing numeric values from given set of values.

Note: Query1, Query2 and Query3 all will give the same output.

Query1:

SELECT ROWNUM FROM ALL_TABLES WHERE ROWNUM<=(SELECT MAX(A) FROM TEMP)

MINUS

SELECT A FROM TEMP;

Query2:

SELECT  (SELECT MIN(A) FROM TEMP)+ ROWNUM

FROM    DUAL

CONNECT BY

LEVEL   <=(SELECT MAX(A) - MIN(A) FROM TEMP)

MINUS

SELECT A FROM TEMP;

Query3:

SELECT ROWNUM FROM DUAL CONNECT BY LEVEL<=20

MINUS

SELECT A FROM TEMP

Output:

A
2
4
5
6
7
8
10
11
13
14
17
18

To Compare “NULL” Values Use “NVL” Function in Oracle

Query to compare “Null” values in oracle tables:

Step1: Create a Table “Test1” as shown below:

CREATE TABLE TEST1 (A NUMBER,B NUMBER);

Step 2: Insert few random data into “TEST1” Table.

INSERT INTO TEST1 VALUES (1,25);

INSERT INTO TEST1 VALUES (2,20);

INSERT INTO TEST1 VALUES (3,NULL);

INSERT INTO TEST1 VALUES (4,30);

INSERT INTO TEST1 VALUES (5,NULL);

Step 3: Select the “TEST1” Table.

SELECT * FROM TEST1;

A	B
1	25
2	20
3
4	30
5

Step4: Create a Table “Test2” as shown below:

CREATE TABLE TEST2 (A NUMBER,B NUMBER);

Step 5: Insert few random data into “TEST2” Table.

INSERT INTO TEST2 VALUES (1,25);

INSERT INTO TEST2 VALUES (2,NULL);

INSERT INTO TEST2 VALUES (3,20);

INSERT INTO TEST2 VALUES (4,NULL);

INSERT INTO TEST2 VALUES (5,NULL);

Step 6: Select the “TEST2” Table.

SELECT * FROM TEST2;

A	B
1	25
2
3	20
4
5

The column “A” of table “TEST1” and “TEST2” is same and can be act as a joining condition for both tables.

Run the below query to fetch column B’s data from both the table that is not equal.

SELECT TEST1.A,TEST1.B,TEST2.B FROM TEST1,TEST2 WHERE TEST1.A=TEST2.A AND TEST1.B!=TEST2.B

Output: No Rows Returned. (It is because null values cannot be equal to or unequal to any value)

Now, run the below query to fetch the actual result using NVL function.

SELECT TEST1.A,TEST1.B,TEST2.B FROM TEST1,TEST2 WHERE TEST1.A=TEST2.A AND NVL(TEST1.B,0)!=NVL(TEST2.B,0)

Expected Output:

A	B	B_1
2	20
3		20
4	30