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