Oracle Questions

A foreign key is a reference to another table. It is used to establish relationships between tables. For example, relationship between employee and professor table. One employee can have multiple professors. The Primary key of employee becomes foreign key of professor.
Example:
create table employee ( id number NOT NULL, professor_id NOT NULL, Name varchar(200) Constraint prim_id Foreign key(id) references professor(professor_id) );

- Sub Query also termed as Nested Query or Inner Query is used to get data from multiple tables.
- A sub query is added in the where clause of the main query.

There can be two types of subqueries:
a.) Correlated sub query :
- It can reference column in a table listed in the from list of the outer query but is not as independent as a query.
b.) Non-Correlated sub query :
- Results of this sub query are submitted to the main query or parent query.
- It is independent like a query

SQL is a structured query language while PL/SQL is an extension of SQL by introducing a procedural flow. PL/SQL has blocks of statements. PL/SQL works like other procedural languages and has concepts like control statements, sequential statements, exception handling etc.

Oracle Web Cache is a secure reverse proxy cache and a compression engine deployed between Browser and HTTP server, Browser and Content Management server to improve the performance of web sites by caching frequently accessed content. Oracle Web Cache supports:

- Static content caching

- Dynamic content caching

- Partial Page catching

- Request Filtering.

A data warehouse is a relational database that is designed for query and analysis rather than transaction processing. It usually contains historical data that is derived from transaction data, but it can include data from other sources. Data warehouses are designed to help you analyze your data.

A collection just like an array is an ordered group of elements of the same type. Each elements position is determined by a unique subscript.

Index by tables:- They are similar to hash arrays that allows to search for subscript values using arbitrary numbers and strings.

They can be declared as:

TYPE type_name IS TABLE OF element_type [NOT NULL]

INDEX BY [BINARY_INTEGER | PLS_INTEGER | VARCHAR2(size_limit)];

INDEX BY key_type;

Example:

TYPE studenttyp IS TABLE OF emp%ROWTYPE

INDEX BY BINARY_INTEGER;

stud_tab studenttyp;

Nested tables:- they hold random number of elements and use sequential numbers as sub scripts.

They can be declared as:

TYPE type_name IS TABLE OF element_type [NOT NULL];

Example: TYPE employee_type IS TABLE OF NUMBER INDEX BY VARCHAR2(64);

Varrays: Holds a fixed number of elements which can be changed in run time. 

They can be declared as:

TYPE type_name IS {VARRAY | VARYING ARRAY} (size_limit) OF element_type [NOT NULL];

Example: TYPE Calendar IS VARRAY(366) OF DATE;

- Because of the block nature, multiple statements are processed at once thereby improving performance. 

- PL/SQL handles exceptions on catching which, action can be taken. 

- PL/SQL is highly portable as it works with all procedural languages and is highly secured because of privileges.

We need to first login to the data base as: SQLPLUS followed by user_name/password. Then we need to execute the following query:

CREATE USER user_name IDENTIFIED BY abcd; in this query a user_name suggest the user that is created which has a password abcd which is required for login in database.