Oracle Points - Part-1

1.   Aggregate functions like Min, max, count, avg ignores null values in the columns

select count( department_id) from hr.employees

Gives 106 records where as we actually have 107 records.

2.   On external table we can’t perform the following.

a.   DML operations

b.   Index, Constraints, Trigger creations

Error “ORA-30657: operation not supported on external organized table”

3.   Single row functions

a.   They can be used in SELECT, WHERE, and ORDER BY clauses.

b.   They can modify the data type of the argument that is referenced.

c.    They can accept a column name, expression, variable name, or a user-supplied constant as arguments.

4.   Error - ORA-25154: column part of USING clause cannot have qualifier

5.   Correlated Sub Query

a.   The nested query executes after the outer query returns the row.

b.   Each row returned by the outer query is evaluated for the results returned by the inner query.

6.   GROUPING function is used to identify if the NULL value in an expression is a stored NULL value or created by ROLLUP or CUBE.

7.   In the same GRANT statement we can’t put more than one table

8.   Flashback version queries provide a mechanism for viewing data as it existed at a particular point in time (a timestamp or SCN). With 10g Release 1, Oracle has extended flashback query to enable us to view different versions of our data in a given range between two timestamps or SCNs.

1.   ROLLUP produces higher-level subtotals, moving from right to left through the list of grouping columns specified in the GROUP BY clause.

2.   Error - ORA-02261: such unique or primary key already exists in the table occurs when we try to create primary key as well as unique key constraint on the same table column