Oracle FAQs

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 onethat 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 toQualify as RELATIONAL DATABASE MANAGEMENT system, it must use its RELATIONAL facilitiesto 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 recordsinto 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: A program in this implements a step-by-step algorithm to solve the problem.
NON-Procedural Language: 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) :Create Alter Drop
DML (Data Manipulating Language): Select Insert Update Delete
DCL (Data Control Language): Rollback Commit
DTL(Data Transaction Language): Grant Revoke

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:
DELETE:
a)If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.
b)Use DELETE statement without a WHERE clause.
c)Activates Trigger.
d)Can Rollback.
e)DML Command

TRUNCATE:
a)Removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain. The counter used by an identity for new rows is reset to the seed for the column.
b)You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint.
c)Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
d)No Roll back.
e)DDL Command


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 TCL CATEGORY?
Ans: Grant and Revoke are the two commands belong to the TCL 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.

26. HOW MANY COLUMNS CAN A TABLE HAVE?
Ans: A Table can have 1000 columns.

27. WHAT ARE DIFFERENT DATATYPES SUPPORTED BY SQL?
Ans: Char (size), Nchar (size), Varchar2 (size), Nvarchar2 (size) data types for character values, Number (precision, scale), Number, Number (n), Float, Float (binary precision) data types for numerical values, Date data type for date values, Long, Raw (size), Long Raw, Clob, Blob, Nclob, Bfile for large objects.

28. WHAT IS DIFFERENCE BETWEEN LONG AND LOB DATATYPES?
Ans:
LOB LONG
1) The maximum size is 4GB.
2) LOBs (except NCLOB) can be attributes of an object type.
3) LOBs support random access to data.
4) Multiple LOB columns per table or LOB attributes in an object type.
1) The maximum size is 2GB. 2) LONGs cannot. 3) LONGs support only sequential access.
4) Only one LONG column was allowed in a table

29. WHAT IS DIFFERENCE BETWEEN CHAR AND VARCHAR2 DATATYPES?
Ans: Varchar2 is similar to Char but can store variable no. Of characters and while querying the table varchar2 trims the extra spaces from the column and fetches the rows that exactly match the criteria.

30. HOW MUCH MEMORY IS ALLOCATED FOR DATE DATATYPE? WHAT IS DEFAULT DATE FORMAT IN ORACLE?
Ans: For Date data type oracle allocates 7 bytes Memory. Default Date Format is: DD-MON-YY.

31. WHAT IS RANGE FOR EACH DATATYPE OF SQL?
Ans: Datatype Range
Char Varchar2 Number Float LONG, RAW, LONGRAW Large Objects
(LOB's) 2000 bytes 4000 bytes
Precision 1 to 38 Scale -84 to 127 Precision 38 decimals Or 122 binary
precision 2 GB 4GB

32. HOW TO RENAME A COLUMN?
Ans: We can't rename a Column of a table directly. So we follow the following steps.
To Rename a Column:
a) Alter the table specifying new column name to be given and data type.
b) Then copy the values in the column to be renamed into new column.
c) drop the old column.

More Methods:
SQL> ALTER TABLE test RENAME COLUMN col1 TO id; (Applicable from 9i)


33. HOW TO DECREASE SIZE OR CHANGE DATATYPE OF A COLUMN?
Ans: To Decrease the size of a Data type of a column
i. Truncate the table first.
ii. Alter the table column whose size is to be decreased using the same name and data type but new size.

34. WHAT IS A CONSTRAINT? WHAT ARE ITS VARIOUS LEVELS?
Ans: Constraint: Constraints are representators of the column to enforce data entity and consistency.There r two levels
1)Column-level constraints
2)Table-level constraints.

35. LIST OUT ALL THE CONSTRAINTS SUPPORTED BY SQL.
Ans: Not Null, Unique, Check, Primary Key and Foreign Key or Referential Integrity.

36. WHAT IS DIFFERENCE BETWEEN UNIQUE+NOT NULL AND PRIMARY KEY?
Ans: Unique and Not Null is a combination of two Constraints that can be present any number of times in a table and can't be a referential key to any column of an another table where as Primary Key is single Constraint that can be only once for table and can be a referential key to a column of another table becoming a referential integrity.

37. WHAT IS A COMPOSITE PRIMARY KEY?
Ans: A Primary key created on combination of columns is called Composite Primary Key.

38. HOW TO DEFINE A NULL VALUE?
Ans: A NULL value is something which is unavailable, it is neither zero nor a space and any mathematical calculation with NULL is always NULL.

39. WHAT IS NULL? A CONSTRAINT OR DEFAULT VALUE?
Ans: It is a default value.

40. WHAT IS DEFAULT VALUE FOR EVERY COLUMN OF A TABLE?
Ans: NULL.