Oracle / SQL Questions

Oracle
Q1. What is PL/SQL ?
A1. A programming language available in Oracle. It is a way of wrapping SQL with 3GL constructs such as IF..THEN..ELSE, WHILE etc.
Q2. What is locking and what is it used for ?
A2 Locking is a mechanism to restrict access to data. It prevents one user updating data whilst another user is looking at it.
Q3. What types of locking are available with Oracle?
A3. Row locking and table locking.
Q4. What happens if a tablespace clause is left off of a primary key constraint clause?
A4. This results in the index that is automatically generated being placed in the users default tablespace. Since this will usually be the same tablespace as the table is being created in, this can cause serious performance problems.
Q5. What packages (if any) has Oracle provided for use by developers?
A5. Oracle provides the DBMS_ series of packages. There are many which developers should be aware of such as DBMS_SQL, DBMS_PIPE, DBMS_TRANSACTION, DBMS_LOCK, DBMS_ALERT, DBMS_OUTPUT, DBMS_JOB, DBMS_UTILITY, DBMS_DDL, UTL_FILE.
Q6. When should more than one DB writer process be used? How many should be used?

A6. If the UNIX system being used is capable of asynchronous IO then only one is required, if the system is not capable of asynchronous IO then up to twice the number of disks used by Oracle number of DB writers should be specified by use of the db_writers initialisation parameter.


SQL
Q1. Explain the use of the WHERE clause.
A1. It directs SQL to extract data from rows where the value of the column is the same as the current value of the WHERE clause variable.
Q2. What technique is used to retrieve data from more than one table in a single SQL statement?
A2. The Join statement combines data from more that two tables
Q3. What is a foreign key?
A3. It identifies a related row in another table and establishes a logical relationship between rows in two tables.
Q4. What are the reasons for adding an index to a table ?
A4. To increase lookup performance. For a unique key to guarantee the uniqueness of the values for a column on a table.
Q5. What is a primary key on a table used for ?
A5. Guaranteeing the uniqueness of a column on a table.
Q6. What is the purpose of a view?
A6. Views can be used to join and display data from multiple tables and to limit the number of columns and/or rows that are selected and displayed.