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.

1 comment:

  1. Nice work keep it up.. Visit for more oracle/OBIEE interview questions http://obiee-basics.blogspot.in/

    ReplyDelete