Oracle Basics Interview Questions

select * from employees where hiredate in (1995,1996,1997,1998);

Exact query you required..... Try it out if you have any doubts.

Code
SELECT count(*),to_char(hire_date,'yyyy') FROM employees WHERE to_char(hire_date,'yyyy') BETWEEN 1995 AND 1998 GROUP BY to_char(hire_date,'yyyy');

SQL> select max(sysdate)-min(sysdate) from dual;

MAX(SYSDATE)-MIN(SYSDATE)
-------------------------
0

SQL>

result of Max(sysdate)-Min(sysdate),it means, it shows the date which is in between last date n first date of the month according to the system information.

SQL> select ceil(14.1) ,round(14.1),ceil(14.01),round(14.4),trunc(14.6) from dual; CEIL(14.1) ROUND(14.1) CEIL(14.01) ROUND(14.4) TRUNC(14.6) ---------- ----------- ----------- ...

Round function will increase the value by 1 if decimal value is greater than or equal to 5 else it will return same integer value without decimal ex. select round(8.34) from dual; will return 8; sel...

mutating error is nothing but when we update a table at the same time when we retrieve the data from the same table then the mutating table error occurs.......to overcome we use autonomous transaction...

it occurs when ever we try to perform either select or dml operations on the same table in the trigger created for same table

select last_name,hiredate,to_char(hiredate,'day') day

eg. if use scott/tiger schema:

select ename, TO_CHAR(hiredate, 'DAY') as DAY from emp;

Oracle is developed using C and C++

Till 9i it was developed in using "C" and Java langauge. But from 10g it is fully developed in Java.

Data Integrity is maintained by Triggers and various other constraints in Oracle.

Oracle Database enables you to define and enforce each type of data integrity rule defined in the previous section. Most of these rules are easily defined using either integrity constraints or datab...

It should be (sysdate,'day')

Use

trim(dayofweek) in IF Condition

Revoke insert,update,delete privileges from the user

LOCK TABLE  tablename IN EXCLUSIVE MODE;

EX:
LOCK TABLE emp IN EXCLUSIVE MODE;

To convert a LMT to DMT,

SQL> exec dbms.space_admin.Tablespace_Migrate_From_local('Tablespace name');

By  creating all rollback segments in locally-managed tablespaces.

As rows are added to tables, the table expands into unused space within the space. Conversely, when rows are deleted, a table may coalesce extents, releasing unused space back into the tablespace. As ...

In two circumstances, the data for a row in a table may be too large to fit into a single data block. In the first case, called chaining, the row is too large to fit into an empty data block. In this ...

The Oracle architecture has five mandatory background processes Mandatory background processes: DBWR (Database Writer) PMON (Process Monitor) CKPT (Check Point) LGWR (Log Writer) SMON (System Monitor)...

Four types of segments are found in an Oracle database:1- Data segment every table in the database resides in a single data segment.2- Index segment each index is stored in its own index segment.3- Te...

There are following types of segments in Oracle

1. Data segments stores the data
2. Index segment stores the index data
3. Rollback segment : Stores undo data information for data consistency.

The question is: " is the attribute instance." The attribute itself is not an instance but the value "Exforsys" stored in the attribute is an instance.

An entity represents a real world object where as attribut reprsents the characteristics of an entity

When a user issues a update statement the server process checks for weather the same type of command is executed recently or not if that command is executed very recently then it directly proceed to t...

During the execution of an update statement, the server process looks into library cache whether same kind of query is recently executed or not i.e. explain plan is ready for execution or no...

------Funtions------DBMS_PIPE.CREATE_PIPE:- for creating a pipeDBMS_PIPE.SEND_MESSAGE:- For sending messageDBMS_PIPE.REMOVE_PIPE:- for removing messageDBMS_PIPE.RECEIVE_MESSAGE:- For receiving message...

dbms_pipe is a IPC methodology which allows processes to communicate with each other using pipes. The important functions/procedures in this package are(1) CREATE_PIPE to create a new pipe ==> dbms...

The sys_context function can be used to retrieve information about the Oracle environment.The syntax for the sys_context function is:sys_context( namespace, parameter, [ length ] )namespace is an Orac...

One time procedure, Its actually not a procedure but its called as one time procedure, Why because this used to initialize variable, calling function or procedure once the scope of package for user se...

And the primary purpose of one time procedure is for one time initialisation of package variables