Oracle Interview Questions

  • Updated
  • Posted in Programming
  • 3 mins read


The oralce inteview questions are categorised into

  1. SQL Interview Questions
  2. PL/SQL Interview Questions

SQL Interview Questions:

1. Write a question to search out the best wage earned by an worker in every division and likewise the variety of staff who earn the best wage?

SELECT DEPARTMENT_ID,
MAX(SALARY) HIGHEST_SALARY,
COUNT(1) KEEP(DENSE_RANK LAST ORDER BY SALARY) CNT_HIGH_SAL
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID;

2. Write a question to get the highest 2 staff who’re incomes the best wage in every division?

SELECT DEPARTMENT_ID,
EMPLOYEE_ID,
SALARY
FROM
(
SELECT DEPARTMENT_ID,
EMPLOYEE_ID,
SALARY,
ROW_NUMBER() OVER(PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC ) R
FROM EMPLOYEES
) A
WHERE R <= 2;

3. Write a question to delete the duplicate data from staff desk?

DELETE  FROM EMPLOYEES
WHERE ROWID NOT IN
(SELECT MAX(ROWID) FROM EMPLOYEES GROUP BY EMPLOYEE_ID);

4. Write a question to search out the workers who’re incomes greater than the common wage of their division?

SELECT EMPLOYEE_ID,
SALARY
FROM EMPLOYEES E_O
WHERE SALARY >
( SELECT AVG(SALARY) FROM EMPLOYEES E_I
WHERE E_I.DEPARTMENT_ID = E_O.DEPARTMENT_ID );

5. How do you show the present date in oracle?

SELECT SYSDATE FROM DUAL;

6. What is a correlated Query?

It is a type of sub question, the place the sub question makes use of the values from the outer question in its WHERE clause. The sub question runs for every row processed within the outer question. Question 4 is an instance for a correlated sub question.

PL/SQL Interview Questions:

1. What is a cursor?

A cursor is a reference to the system reminiscence when an SQL assertion is executed. A cursor comprises the details about the choose assertion and the rows accessed by it.

2. What is implicit cursor and express cursor?

  • Implicit Cursors: Implicit cursors are created by default when DML statements like INSERT, UPDATE and DELETE are executed in PL/SQL objects.
  • Explicit Cursors: Explicit cursors have to be created by you when executing the choose statements.

3. What are the attributes of a cursor?

Cursor attributes are:

  • %FOUND : Returns true if a DML or SELECT assertion impacts a minimum of one row.
  • %NOTFOUND: Returns true if a DML or SELECT assertion doesn’t have an effect on a minimum of one row.
  • %ROWCOUNT: Returns the variety of rows affected by the DML or SELECT assertion.
  • %ISOPEN: Returns true if a cursor is in open state.
  • %BULK_ROWCOUNT: Similar to %ROWCOUNT, besides it’s utilized in bulk operations.

4. What is a personal and public process?

  • Public process: In a bundle, the signature of the process is specified within the bundle specification. This process could be referred to as outdoors of the bundle.
  • Private process: For personal process, there gained’t be any signature within the bundle specification. So, these procedures could be referred to as solely contained in the bundle and can’t be referred to as outdoors of the bundle.

5. Create a pattern delete set off on staff desk?

CREATE OR REPLACE TRIGGER EMPLOYEES_AD" 
AFTER DELETE ON EMPLOYEES
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
INSERT INTO
employees_changes (employee_id,
change_date
)
VALUES (:OLD.photo_tag_id,
SYSDATE
);
END;

6. What is the distinction between a process and a operate?

A operate returns a worth. However a process doesn’t return a worth.

Recommended Reading:


SQL Interview Questions and Answers
Oracle Analytical Functions
Oracle Complex Queries
Oracle Query to split the delimited data in a column to multiple rows
Min and Max values of contiguous rows – Oracle SQL Query

If you want this text, then please share it or click on on the google +1 button.

Leave a Reply