Please Visit Our New Website for Further Communication

Amazon

Showing posts with label SQL Queries. Show all posts
Showing posts with label SQL Queries. Show all posts

28 Mar 2013

Getting Random Rows from Oracle Table Using DBMS_RANDOM

To fetch random rows from Oracle table use DBMS_RANDOM.

SELECT column FROM( SELECT column FROM table ORDER BY dbms_random.value ) WHERE rownum <= 10

5 Feb 2013

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