oracle interview questions and answers

Oracle Interview Questions and Answers

1.   WHAT IS DATA OR INFORMATION?

Ans: The Matter that we feed into the Computer is called Data or Information.

2.   WHAT IS DATABASE?

Ans: The Collection of Interrelated Data is called Data Base.

3.   WHAT IS A DATABASE MANAGEMENT SYSTEM (DBMS) PACKAGE?

Ans: The Collection of Interrelated Data and some Programs to access

the Data is Called Data Base Management System (DBMS).

4.   WHEN CAN WE SAY A DBMS PACKAGE AS RDBMS?

Ans: For a system to Qualify as RELATIONAL DATABASE MANAGEMENT system,

it must use its RELATIONAL facilities to MANAGE the DATABASE.

5.   WHAT IS ORDBMS?

Ans: Object (oriented) Relational Data Base Management System is one

that can store data, the relationship of the data, and the behavior of the data

(i.e., the way it interacts with other data).

6.  NAME SOME CODD'S RULES?

Ans: Dr. E.F. Codd presented 12 rules that a database must obey if it

is to be considered truly relational. Out those,  some are as follows

     a)         The rules stem from a single rule- the ‘zero rule’: For a system to Qualify as RELATIONAL DATABASE MANAGEMENT system, it must use its RELATIONAL facilities

to MANAGE the DATABASE.

     b)         Information Rule: Tabular Representation of Information.

     c)         Guaranteed Access Rule: Uniqueness of tuples for guaranteed accessibility.

     d)         Missing Information Rule: Systematic representation of missing information as NULL   Values.

      e)         Comprehensive Data Sub-Language Rule: QL to support Data definition,

View definition, Data manipulation, Integrity, Authorization and Security.

7.  WHAT ARE HIERARCHICAL, NETWORK, AND RELATIONAL DATABASE MODELS?

Ans: 

a)  Hierarchical Model: The Hierarchical Model was introduced in

the Information Management System (IMS) developed by IBM in 1968. In this data is organized as a tree structure. Each tree is made of nodes and branches.

The nodes of the tree represent the record types and it is a collection

of data attributes entity at that point. The topmost node in the structure is called the root. Nodes succeeding lower levels are called children.

b)  Network Model: The Network Model, also called as the CODSYL database

structure, is an improvement over the Hierarchical mode, in this model concept of parent and child is expanded to have multiple parent-child relationships, i.e. any child can be subordinate to many different parents (or nodes). Data is represented by

collection of records, and relationships among data are represented by

links. A link is an association between precisely two records. Many-to-many relationships can exists between the parent and child.

c)  Relational Model: The Relational Database Model eliminates the need

for explicit parent-child relationships. In RDBMS, data is organized in two-dimensional tables consisting of relational, i.e. no pointers are maintained between tables.

8.  WHAT IS DATA MODELING?

Ans: Data Modeling describes relationship between the data objects. The

relationships between the collections of data in a system may be graphically represented using data modeling.

  

9.   DEFINE ENTITY, ATTRIBUTE AND RELATIONSHIP?

Ans: Entity: An Entity is a thing, which can be easily identified. An entity is any object, place, person, concept or activity about which an enterprise records data.

Attribute: An attribute is the property of a given entity.

Relationship: Relationship is an association among entities.

10.  WHAT IS ER-MODELING?

Ans: The E-R modeling technique is the Top Down Approach. Entity

relationship is technique for analysis and logical modeling of a system’s data requirements. It is the most widely used and has gained acceptance as the ideal database design. It uses three basic units: entities, their attributes and the relationship that exists between

 the entities. It uses a graphical notation for representing these.

11.  WHAT IS NORMALIZATION?

Ans: Normalization is a step-by-step decomposition of complex records

into simple records.

12.  WHAT ARE VARIOUS NORMAL FORMS OF DATA?

Ans: The First Normal Form   1NF,

The Second Normal Form       2NF,

The Third Normal Form          3NF,

The Boyce and Codd Normal Form     BC NF.

13.  WHAT IS DENORMALIZATION?

Ans: The intentional introduction of redundancy to a table to improve

performance is called DENORMALIZATION.

14.  WHAT ARE 1-TIER, 2-TIER, 3-TIER OR N-TIER DATABASE ARCHITECTURES?

Ans: 1-Tier Database Architecture is based on single system, which acts as both server and client.

     2-Tier Architecture is based on one server and client.

     3-Tier Architecture is based on one server and client out that on client act as a remote system.

     N-Tier Architecture is based on N no. Of servers and N no. Of clients.

15. WHAT ARE A TABLE, COLUMN, AND RECORD?

Ans: Table:  A Table is a database object that holds your data. It is

made up of many columns. Each of these columns has a data type associated with it.

Column: A column, referred to as an attribute, is similar to a field in

the file system.

Record: A row, usually referred to as tuple, is similar to record in

the file system.

16.  WHAT IS DIFFERENCE BETWEEN A PROCEDURAL LANGUAGE AND A NON-PROCEDURAL LANGUAGE?

Ans:    Procedural Language  NON-Procedural Language

A program in this implements a step-by-step algorithm to solve the

problem. It contains what to do but not how to do .

17. WHAT TYPE OF LANGUAGE  "SQL" IS?

Ans: SQL is a Non-procedural, 4th generation Language,/ which concerts

what to do rather than how to do any process.

18. CLASSIFICATION OF SQL COMMANDS?

Ans:

DDL (Data Definition Language)             DQL [Data Querying Lnaguage ]

DML (Data Manipulating Language)       DCL (Data Control Language)

TCL(Data Transaction Language)

Create  Alter Drop Truncate Rename, Select , Insert  Update Delete Merge , Grant Revoke , Rollback Commit savepoint

19.  WHAT IS DIFFERENCE BETWEEN DDL AND DML COMMANDS?

Ans: For DDL commands autocommit is ON implicitly whereas For DML

commands autocommit is to be turned ON explicitly.

20. WHAT IS DIFFERENCE BETWEEN A TRANSACTION AND A QUERY?

Ans: A Transaction is unit of some commands where as Query is a single

line request for the information from the  database.

21. WHAT IS DIFFERENCE BETWEEN TRUNCATE AND DELETE COMMANDS?

Ans: Truncate Command will delete all the records where as Delete

Command will delete specified or all the records depending only on the condition given.

22.  WHAT IS DIFFERENCE BETWEEN UPDATE AND ALTER COMMANDS?

Ans: Alter command is used to modify the database objects where as the

Update command is used to modify the values of a data base objects.

23.  WHAT ARE COMMANDS OF DCL CATEGORY?

Ans: Grant and Revoke are the two commands belong to the DCL Category.

24.  WHICH IS AN EFFICIENT COMMAND - TRUNCATE OR DELETE? WHY?

Ans: Delete is the efficient command because using this command we can

delete only those records that are not really required.

25. WHAT ARE RULES FOR NAMING A TABLE OR COLUMN?

Ans:  1) Names must be from 1 to 30 bytes long.

         2) Names cannot contain quotation marks.

         3) Names are not case sensitive.

         4) A name must begin with an alphabetic character from your database

              character set and the characters $ and #.

              But these characters are discouraged.

        5) A name cannot be ORACLE reserved word.

        6) A name must be unique across its namespace. Objects in the name

            space must have different names.

        7) A name can be enclosed in double quotes.