Introduction to Oracle9i: SQL (6)

Subqueries
##CONTINUE##

• ‘Rollback’ rolls back the entire transaction. ‘Rollback to savepoint’ rollback the transaction only upto the save point. ‘Rollback work’ is the equvalent of ‘Rollback’, though it is not often used.
• Exclusive Locks and Share Locks are two basic locking modes in Oracle. Share Locks prevent other exclusive locks but allow other share locks. Exclusive Locks prevent other exclusive as well as share locks.
• U can inserted Unlimited tables via a single INSERT use INSERT ALL
• Lexical substitution variable can be used to replace values in the WHERE caluse
• You want all unmatched data from both tables then u use a FULL OUTER JOIN
• A multiple row subquery can be compared by using the “>” operator. IN, ANY, ALL
• You can use IN operator in a condition that involves an outer join.
• The PROCEDURE, SYNONYMS and VIEWS dependent on the TABLE become invalid when you drop it. When you recreate the TABLE, SYNONYMS and VIEWS become valid but the PROCEDURE will have to be recompiled. INDEXES and TRIGGERS are dropped with table itself. SEQUENCE is a separate database object, and is not dependent on any one.
• SELECT e.last_name, e.dept_id, d.name FROM employee e
  RIGHT OUTER JOIN departments d
  ON (e.dept_id = d.dept_id);
  ->
  SELECT e.last_name, e.dept_id, d.name FROM employee e, departments d WHERE d.dept_id = e.dept_id (+);
• A subquery can be used in the CREATE VIEW statement, regardless of the number of rows it returns.
• EXISTS is used in a correlated subquery.
• CURRVAL pseudocolumn is used to get successive sequence numbers from a particular sequence. It is mostly used in the SET clause of an UPDATE command and the VALUES clause of an INSERT statement.
• Indexes are frequently created for columns that are likely to be used in WHERE clauses for simple equalities or join conditions.Arithmetic operators can be used in any clause of SELECT statement except ‘FROM’ Clause.