Oracle SQL (IZO-007)

This exam covers the SQL in depth. First we will see the basics of SQL Language.

Lets see what all things should be covered for the same:
>> Limiting, Sorting and Manipulating return data.
>> Advanced Data Selection in Oracle
>> Subqueries
>> Creating Oracle Database Objects
>> Manipulating Oracle Data
>> Creating Other Database Objects in Oracle
>> User Access control in Oracle

Satetement Categories
The statement categories in SQL are classified into:
1. SELECT - Select is not considered as a DML Statement
2. DML Statements - This includes INSERT, UPDATE and DELETE.
3. DDL Statements - This includes CREATE, ALTER and DROP.
3. Transaction Control Statements - This includes COMMIT, ROLLBACK and SAVEPOINT.
4. DCL Statements - This includes GRANT and REVOKE.

SELECT statement is used to retrieve data from the database. Its a very powerful statement. We will see the same later.

NULL Value - There are many misconceptions about the presence of NULL in a column. NULL means the absence of data in the column and its not a field with a space / spaces in it.
NULL means "information missing".

The disadvantages of having null values are:
1. Salary + NULL = NULL (Scalar Expression)
2. Salary < NULL = UNKNOWN (Boolean Expression)

You can get a complete listing of NULL value and its problems at Beware of NULL Values.

Many times the NULL value causes a lot of problems.
Oracle SQL has got a very very good function that will take care of this. The NVL( ) Function is used to replace the NULL value in a column with a a value of your choice.

SQL> select NVL(Salary,'No Value') from emp;

This statement will return "No Value" whenever the value of the column Salary is NULL. You can give character values or numeric values in the NVL function.