Oracle Questions

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.

- 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.

The CACHE option means how many sequences will be stored in memory for access by the application objects. The performance is faster. However in case of the database is down the data is memory is lost for the sequence.

The NO CACHE option means values are not stored in memory. So there might be some performance issue. 

The number of rows returned by a select query can be restricted by the LIMIT clause.
Example:
SELECT * from employee
LIMIT 20

Even though both PL/SQL and Java (or) .NET can be used, PL/SQL stands above these two in terms of integration overhead. This is because Java is an open source proprietary and Data manipulation is slightly faster in PL/SQL than in Java. 

Oracle NOT NULL is used on a column to ensure that the value for that column can never be NULL.
Example:
Below, the constraint is that the id should never be NULL. If it is, oracle throws an error.
create table employee ( id number NOT NULL, Name varchar(200) );

Joins are used to combine data of one or more tables. Joins should be used when there is abundant data. Joins can be LEFT, RIGHT, OUTER, INNER or even SELF JOIN. The purpose is to bind data from multiple tables without any receptivity

Oracle database comprises of three kinds of files:
- Datafiles
- Redo log files
- Control files