oracle sql interview questions for 3 years experience

 

http://www.sgraphinfotech.com/blog/oracle-sql-interview-questions-and-answers.html

 Q1.) What is the difference between DML and DDL?

Ans: DDL (Data Definition Language) is used mainly for defining the database schema. It consists of SQL statements which give an idea about the structure of the database. Some examples of DDL statements are:

CREATE: Used to create new database objects
ALTER: Used to alter characteristics of already existing database objects
DROP: Used to remove database objects
TRUNCATE: Used to remove all records including all spaces allocated to record from the table

RENAME: Used to rename any database object
DML (Data Manipulation Language) are responsible for managing and manipulating various data schemas or database objects. They cannot alter the characteristics of a database object unlike DDL statements. Most common examples are:

SELECT: Used to retrieve data from the database
INSERT: Used to insert new data/records into the database
UPDATE: Used to update/modify the existing data/record from the database
DELETE: Used to remove the existing data/record from the database

Q2.) What does NVL function does?

Ans: NVL function is mainly used to replace NULL values with some other value provided the data type of the NULL value to be replaced with the given value is same.

Q3.) What is a BLOB data type?

Ans: BLOB is a binary string of varying length used to store data up to 2 gigabytes. Images, videos and other documents can be stored in the database using BLOB data type.

Q4.) What is the difference between varchar and varchar2?

Ans: The data type varchar is used to stored data up to 2000 bytes while varchar2 is used to store data up to 4000 bytes. However, the prominent difference between varchar and varchar2 is that varchar can occupy NULL values while varchar2 cannot.

Q5.) What are the different Oracle SQL database objects?

Ans:    Tables - Database object used to store various database records
           Views - It is a virtual table derived from one or more tables
           Indexes - Used to improve efficiency and performance of the database
           Synonyms - Aliases used for database tables

Q6.) What are various types of integrity constraints in Oracle SQL?

Ans: Unique: It ensures that the given column or entity is unique and cannot     be duplicated.

Not NULL: It ensures that the given column cannot be empty.

Foreign: It ensures that the two entities/tables share a common key with the one in parent table being a primary key and the one in child table being a foreign key.

Q7.) What is a hash cluster?

Ans: It is used to store table in a manner that query fetching results in faster data retrieval by using the hashing technique.

Q8.) What is a sub query?

Ans: Sub query refers to a query written with in a query. It is often referred to as nested query or inner query. It is used to apply filters on selected data retrieved by the main query.

Q9.) What is an alert?

Ans: A window appearing in the center of the screen overlaying the current screen to provide some form of information is termed as an alert.