4 Apr

Oracle Interview Questions

auto_adminComments off.

 

1) What is Oracle and what are its different editions?

Ans: Oracle is one of the popular databases provided by Oracle Corporation, which works on relational management concepts and hence it is referred to as Oracle RDBMS as well.

It is widely used for online transaction processing, data warehousing, and enterprise grid computing.

2) How will you identify Oracle Database Software Release?

Ans: Oracle follows a number of formats for every release.

3) How will you differentiate between VARCHAR & VARCHAR2?

Ans: Both VARCHAR & VARCHAR2 are Oracle data types that are used to store character strings of variable length.

VARCHAR can store characters up to 2000 bytes while VARCHAR2 can store up to 4000 bytes.

VARCHAR will hold the space for characters defined during declaration even if all of them are not used whereas VARCHAR2 will release the unused space.

4) What is the difference between TRUNCATE & DELETE command?

Ans: Both the commands are used to remove data from a database.

The finer differences between the two include:

  • TRUNCATE is a DDL operation while DELETE is a DML operation.
  • TRUNCATE drops the structure of a database and hence cannot be rolled back while DELETE command can be rolled back.
  • The TRUNCATE command will free the object storage space while the DELETE command does not.

5) What is meant by RAW data type?

Ans: RAW data type is used to store variable-length binary data or byte strings.

The difference between RAW & VARCHAR2 data type is that PL/SQL does not recognize this data type and hence, cannot do any conversions when RAW data is transferred to different systems. This data type can only be queried or inserted in a table.

Syntax: RAW (precision)

6) What is meant by Joins? List out the types of joins.

Ans: Joins are used to extract data from multiple tables using some common column or condition.

There are various types of Joins as listed below:

  • INNER JOIN
  • OUTER JOIN
  • CROSS JOINS or CARTESIAN PRODUCT
  • EQUI JOIN
  • ANTI JOIN
  • SEMI JOIN

7) What is the difference between SUBSTR & INSTR functions?

Ans: SUBSTR function returns the sub-part identified by numeric values from the provided string.

Example: [Select SUBSTR (‘India is my country’, 1, 4) from dual] will return “Indi”.

INSTR will return the position number of the sub-string within the string.

Example: [SELECT INSTR (‘India is my country’, ‘a’) from dual] will return 5.

8) How can we find out the duplicate values in an Oracle table?

Ans: We can use the below example query to fetch the duplicate records.

SELECT EMP_NAME, COUNT (EMP_NAME)
FROM EMP
GROUP BY EMP_NAME
HAVING COUNT (EMP_NAME) > 1;

9) How does the ON-DELETE-CASCADE statement work?

Ans: Using ON DELETE CASCADE will automatically delete a record in the child table when the same is deleted from the parent table. This statement can be used with Foreign Keys.

We can add ON DELETE CASCADE option on an existing table using the below set of commands.

Syntax:

ALTER TABLE CHILD_T1 ADD CONSTRAINT CHILD_PARENT_FK REFERENCES
PARENT_T1 (COLUMN1) ON DELETE CASCADE;

10) What is an NVL function? How can it be used?

Ans: NVL is a function, which helps the user to substitute a value if null is encountered for an expression.

It can be used as the below syntax.

[NVL (Value_In, Replace_With)]

11) What is the difference between a Primary Key & a Unique Key?

Ans:  Primary key is used to identify each table row uniquely, while a Unique Key prevents duplicate values in a table column.

Given below are a few differences:

  • The primary key can be only one on the table while unique keys can be multiple.
  • The primary key cannot hold null value at all while a unique key can hold one null value per column.
  • The primary key is a clustered index while a unique key is a non-clustered index.

12) How TRANSLATE command is different from REPLACE?

Ans: TRANSLATE command translates characters one by one in the provided string with the substitution character. REPLACE will replace a character or a set of characters with a complete substitution string.

Example:

TRANSLATE (‘Missisippi’,’is’,’15) => M155151pp1
REPLACE (‘Missisippi’,’is’,’15) => M15s15ippi

13) How can we find out the current date and time in Oracle?

Ans: We can find the current Date & Time using SYSDATE in Oracle.

Syntax:

SELECT SYSDATE into CURRENT_DATE from dual;

14) Why do we use COALESCE function in Oracle?

Ans: COALESCE function is used to return the first non-null expression from the list of arguments provided in the expression. Minimum two arguments should be there in an expression.

Syntax:

COALESCE (expr 1, expr 2, expr 3…expr n)

15) How will you write a query to get a 5th RANK student from a table STUDENT_REPORT?

Ans: The Query will be as follows:

SELECT TOP 1 RANK
FROM (SELECT TOP 5 RANK
FROM STUDENT_REPORT
ORDER BY RANK DESC) AS STUDENT
ORDER BY RANK ASC;

16) When do we use GROUP BY clause in a SQL Query?

Ans: GROUP BY clause is used to identify and group the data by one or more columns in the query results. This clause is often used with aggregate functions like COUNT, MAX, MIN, SUM, AVG etc.

Syntax:

SELECT COLUMN_1, COLUMN_2
FROM TABLENAME
WHERE [condition]
GROUP BY COLUMN_1, COLUMN_2

17) What is the quickest way to fetch the data from a table?

Ans: The quickest way to fetch the data would be to use ROWID in the SQL Query.

18) Where do we use DECODE and CASE Statements?

Ans: Both DECODE & CASE statements will function like IF-THEN-ELSE statement and they are the alternatives for each other. These functions are used in Oracle to transform the data values.

19) Why do we need integrity constraints in a database?

Ans: Integrity constraints are required to enforce business rules so as to maintain the integrity of the database and prevent the entry of invalid data into the tables. With the help of the below-mentioned constraints, relationships can be maintained between the tables.

Various integrity constraints available include Primary Key, Foreign Key, UNIQUE KEY, NOT NULL & CHECK.

20) What do you mean by MERGE in Oracle and how can we merge two tables?

Ans: MERGE statement is used to merge the data from two tables. It selects the data from the source table and inserts/updates it in the other table based on the condition provided in the MERGE query.

 

http://www.automationminds.com/oracle-training-in–omr-sholinganallur-chennai/

Posted in: news
Previous Post
Next Post
© 2018 Automation Minds. All rights reserved..