ORACLE DATABASE

1). What is difference between UNIQUE constraint and PRIMARY KEY constraint ?


Primary Key Column(s) cannot contain null values but UNIQUE key column(s) can contain null values.


2). Describe Referential Integrity ?


Referential integrity in a relational database is consistency between coupled tables. Referential integrity is usually enforced by the combination of a primary key or candidate key and a foreign key. For referential integrity to hold, any field in a table that is declared a foreign key can contain only values from a parent table's primary key or a candidate key. For instance, deleting a record that contains a value referred to by a foreign key in another table would break referential integrity. The relational database management system (RDBMS) enforces referential integrity, normally either by deleting the foreign key rows as well to maintain integrity, or by returning an error and not perfoming the delete. Which method is used would be defined by the definition of the referential integrity constraint.


Eg: An employee database stores the department in which each employee works. The field "DepartmentNumber" in the Employee table is declared a foreign key, and it refers to the field "Index" in the Department table which is declared a primary key. Referential integrity would be broken by deleting a department from the Department table if employees listed in the Employee table are listed as working for that the department.



3). What are the Referential actions supported by FOREIGN KEY integrity constraint ?


The referential actions supported by the FOREIGN KEY integrity constraints of Oracle are UPDATE and DELETE NO ACTION, and DELETE CASCADE.



4). What is self-referential integrity constraint ?


Self-referential integrity constraint is a type of foreign key that references the parent key in the same table. For example if in the Employee table we have a column called manager_id then the manager would also be an employee. So the manager should be defined first before defining the employees under him.i.e the employee_id u enter in manager_id field should have been already defined as an employee. So manager_id points to employee_id.

1. What constitute an ORACLE Instance ?


An Oracle Instance:
• Is a means to access an Oracle database
• Always opens one and only one database
• Consists of memory and background process structures


2. What is SGA ?


The System Global Area (SGA) is a shared memory region allocated by ORACLE that contains data and control information for one ORACLE instance.SGA is allocated at instance startup, and is a fundamental component of an Oracle Instance.


3. What are the components of SGA ?


The SGA consists of several memory structures:
– Shared Pool
– Database Buffer Cache
– Redo Log Buffer
– Other structures (for example, lock and latch
management, statistical data).



4. What do Database Buffers contain ?


Database buffers store the most recently used blocks of database data. It can also contain modified data that has not yet been permanently written to disk.

What is the difference between VARCHAR, VARCHAR2 and CHAR data types?

Varchar and varchar2 ... the only difference being the naming convention varchar followed by database vendors and varchar2 which is set by ansi and has been incorporated by almost all database vendors.
CHAR .. would add spaces and use the entire size allocated to it by filling the rest with spaces. eg gender CHAR(10) .. gender:='MALE' would store as 'MALE ' -- notice blank spaces

What is the use of DESC in SQL

1. To Describe the table structure.

2. To sort the column in descending order


How does one add a column to the middle of a table?
Anytime u add a new column .. its added after last column, You can only alter the way u want to access a column using SQL query

What is the advantage of specifying WITH GRANT OPTION in the GRANT command?

with the help of GRANT option the grantee can also grant the privileges that they hav got from the granter to the other users