Oracle Interview Questions And Answers

oracle interview questions and answers   Top 21 Oracle Interview Questions And Answers

1) What is Oracle?

Oracle is a relational database management system which is designed for enterprise grid computing.


2) What are the different editions of Oracle database?

Following are the four editions of the Oracle database:

● Enterprise Edition
● Standard Edition
● Express Edition (XE)
● Oracle Lite


3) How to create table in Oracle?

Here is the command which is used to create table in oracle:

CREATE TABLE table_name  
(   
  column1 datatype [ NULL | NOT NULL ],  
  column2 datatype [ NULL | NOT NULL ],  
  ...  
  column_n datatype [ NULL | NOT NULL ]  
);  


4) How to create table with primary key in Oracle?

Here is the command which is used to create table in oracle:

CREATE TABLE customers  
( customer_id number(10) NOT NULL,  
  customer_name varchar2(50) NOT NULL,  
  city varchar2(50),  
  CONSTRAINT customers_pk PRIMARY KEY (customer_id)  
);  


5) How to alter table in Oracle?

Here is the command which is used to create table in oracle:

ALTER TABLE table_name  
  ADD column_name column-definition;  


6) How to drop column of a table in Oracle?

Here is the command which is used to create table in oracle:

ALTER TABLE table_name  
  DROP COLUMN column_name;  


7) How to drop table in Oracle?

Here is the command which is used to create table in oracle:

DROP [schema_name].TABLE table_name  
[ CASCADE CONSTRAINTS ]  
[ PURGE ];


8) What is the major difference between ordinary and global temporary tables in oracle?

The major difference between ordinary and global temporary tables is that temporary tables can't have foreign keys related to other tables.


9) What is oracle view?

It is virtual table which stores in Oracle data dictionary and do not store any data.

Here is the command which is used to create view in oracle:

CREATE VIEW view_name AS  
SELECT columns  
FROM tables  
WHERE conditions;  


10) How to truncate records from table in oracle?

Here is the command which is used to truncate records from table in oracle:

truncate table customers; 


11) How to insert records in table in Oracle?

Here is the command which is used to insert records in table in oracle:

 INSERT INTO table  
(column1, column2, ... column_n )  
VALUES  
(expression1, expression2, ... expression_n );   


12) How to update records in table in Oracle?

Here is the command which is used to update records in table in oracle:

UPDATE table  
SET column1 = expression1,  
    column2 = expression2,  
    ...  
    column_n = expression_n  
WHERE conditions; 


13) How to delete record in table in Oracle?

Here is the command which is used to delete record in table in oracle:

DELETE FROM table_name  
WHERE conditions;   


14) Can we roll back if the table is truncated in Oracle?

No, we can't roll back if the table is truncated in oracle.


15) What are the procedures in Oracle?

Procedures are nothing but the group of PL/SQL statements that can be called. Here is the syntax of the procedure.

CREATE [OR REPLACE] PROCEDURE procedure_name  
    [ (parameter [,parameter]) ]  
IS  
    [declaration_section]  
BEGIN  
    executable_section  
[EXCEPTION  
    exception_section]  
END [procedure_name];  


16) What are the different type of parameters that are defined in procedure in Oracle?

Following are the types of parameters that are defined in procedure in Oracle:

IN - It is the default parameter which passes the value to subprogram.
OUT - It returns the value from subprogram to the caller.
IN OUT - It passes the initial value to the subprogram and returns the updated value to the caller.


17) How to drop procedure in Oracle?

Here is the command which is used to drop procedure in oracle:

DROP PROCEDURE procedure_name; 


18) What is function in oracle?

It is a sub program that is used to return single value. Here is the command which is used to create function in oracle:

CREATE [OR REPLACE] FUNCTION function_name  
   [ (parameter [,parameter]) ]  
RETURN return_datatype  
IS | AS  
 [declaration_section]  
BEGIN  
   executable_section  
[EXCEPTION  
   exception_section]  
END [function_name];  


19) How to drop function in Oracle?

Here is the command which is used to drop function in oracle:

DROP FUNCTION function_name;  


20) What is oracle cursor?

A cursor is a pointer which stores information about the processing of SQL statements. Here is the command which is used to declare cursor in oracle:

CURSOR cursor_name  
IS  
  SELECT_statement;


21) How to open cursor in oracle?

Here is the command which is used to open cursor in oracle:

OPEN cursor_name;