Oracle Questions

1. What is the difference between DBMS and RDBMS

DBMS:

1)In dbms there is no relationship concept
2) DBMS supports Single User only
3) DBMS treats Data as Files internally
5) DBMS requires low Software and Hardware Requirements.
6)FoxPro, IMS are Examples

RDBMS:
1)It is used to establish the relationship concept between two database objects, i.e, tables
2) RDBMS supports multiple users
3) RDBMS treats data as Tables internally
5) RDBMS requires High software and hardware requirements.
6) SQL-Server, Oracle are examples

2. In which version of oracle u r working?

SQL> select banner from v$version;

3. How u connect oracle from UNIX?

$ su orcale10g

$sql plus

username/password-scott/tiger

4. Difference between group functions and single row functions.

      Group Function                                         Single Row Function                               

A group function operates                              A single row function operates

on multiple rows returns one                           on one row and returns one result.

single result.

Ex: SUM(),AVG,MIN,MAX, etc                               Ex: UPPER,LOWER,CHR...

5. Difference between TRUNCATE and DELETE

TRUNCATE   is much faster than DELETE.

We can not use truncate and delete if integrity constraint is available. It shows the following error         àintegrity constraint violated - child record found

                Truncate                                      Delete

Truncate is a DDL statement            Delete is a DML statement

Truncate is a one way trip, cannot    Delete can Rollback

ROLLBACK

Doesn't have selective                     Has where clause

features (where clause)     

database triggers doesn't fire           database triggers  fire for delete

for truncate.                                                            

Truncate releases memory.              Delete does not release memory                                                                                             

6. When you use WHERE clause and when you use HAVING clause

          WHERE clause is used to restrict a single row. Where clause is used to specify a condition for columns or for single row functions. Where clause is written before GROUP BY clause.

          HAVING clause is use to restrict a groups of rows. Having clause is use to specify a condition for a group function. Having is written after GROUP BY clause.

7. What is the data type in oracle? What is the difference between char and varchar2 data type?

In oracle there is following data types ie

     Data type                          minimum size            maximum size

varchar2                             1                           4000

char                                   1                           2000

     number                               1                           38                         

     date                                   jan-1-4712 BC          dec-31-9999

     long                                                                2gb

      

char à when u declare a column with a specified size of character data type, and if the data in the column is less than the specified size then the unused space is not released.

varchar2à when u declare a column with a specified size of  varchar2 data type, and if the data in the column is less than the specified size then the unused space is released.

8. Which aggregation functions are used for any type of data type?

The general functions can work with any type of data type. They are,

NVL

NVL2

NVLIF (EXPRE1, EXPRE2)   {if the value of expre1 and exp2 is same then it return null

COALESCE (EXPRS1, EXPRS2, EXPRS3,- - - - - - - - - )

CASE

DECODE

9. What is the difference between NVL and NVL2?

NVLà

NVL convert the null value to actual value.

NVL take two arguments.

Syntaxà NVL (nullcolumn, required actual value)

Exà NVL (comm,100)

NVL2à

NVL2 convert the null value to the actual value else gives the specified value.

NVL2 take three arguments.

Syntax à NVL2(null column , specify value for not null, required actual value)

Exà NVL2(comm., sal+comm.,sal);

10. What is the syntax of replace and what is its use?

Syntax of replaceà

          Replace (text, change_string, replacement_string)

Using replace function we can replace a string by another string in a text.