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

Oracle PL/SQL Interview Questions

• What is difference between SQL and PL/SQL?
• Difference between Truncate and Delete commands.
• What is a Merge command?
• Difference between Implicit and Explicit Locking
•  Types of Joins in Oracle
•  Types of Integrity Constraints in Oracle
• How many different types of NULL functions are there in Oracle?
• Difference between Union and Union ALL
• What are Synonyms?
• What are materialized View? Syntax? How to create Primary key on Materialized Views?
• What are Indexes? Different types of Indexes?
• Types of Cursors?
•  Explain Cursors Attribute?
• Difference between Procedures and Functions
• Difference between Packages and Stored Procedures
• What are Packages?
• Name any In-built Oracle Packages
• Types of Triggers
• Difference between Before and After Trigger
• Difference between Package Specification and Package Body
• Types of Exceptions in PLSQL
•  What are Ref Cursors?
• Explain Optimization Techniques?
• What is Explain Plan?
•  Difference between Normalization and Denormalization
• Explain SDLC Life cycle and name few different Models
•  Explain AGILE Model
• What is Group BY and having clause?
• Oracle 11g new features
• Difference between VARCHAR and VARCHAR2
• Name parameter modes for stored procedures
• What is DB LINK?
• What is an Autonomous Transaction?
• What is a Savepoint
•  Explain Rollup and Cube statement
•  Write a query to find even rows from a Table
• Write a query to find duplicate rows from a Table
• Write a query to find nth highest salary from employee Table
• Write a query to find first 10 rows from a table
• Write a query to find random 7 rows out of 47 rows in a Table

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