SQL PL SQL Oracle Database Questions Answers

EMP Table :

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

Dept :

DEPTNO DNAME LOC
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SALGrade:

GRADE LOSAL HISAL
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999

Q)How do I eliminate the duplicate rows ?

delete from table_name where rowid not in (select max(rowid) from table group by duplicate_values_field_name);
select max(rowid) from scott.emp group by job

Looks like 5 rows will remain after the deletions

MAX(ROWID)
AAAMgHAAEAAAAAeAAG
AAAMgHAAEAAAAAeAAM
AAAMgHAAEAAAAAeAAI
AAAMgHAAEAAAAAeAAJ
AAAMgHAAEAAAAAeAAN

Then get rid of the remaining duplicate rows.
Delete from emp where rowed not in(select max(rowid) from scott.emp group by job )

Q)How to identify the duplicate rows ?

select job, count(*) from scott.emp group by job having count(*)>1

JOB COUNT(*)
MANAGER 3
ANALYST 2
SALESMAN4
CLERK 4

Q)How do I display row number with records?

To achive this use rownum pseudocolumn with query, like SQL> SQL> select rownum, ename from emp;
Output:
1 Scott
2 Millor
3 Jiyo
4 Smith

Q)if commission is null then the text “Not Applicable” want to display, instead of blank space. How do I write the query?

select nvl(to_char(comm),'NA') from scott.emp;

NVL(TO_CHAR(COMM),'NA')
NA
300
500
NA
1400
NA
NA
NA
NA
0
NA
NA
NA
NA


Q)Display the records between two range

select rownum, empno, ename from scott.emp where rowid in
(select rowid from scott.emp where rownum <=14
minus
select rowid from scott.emp where rownum<7);

ROWNUM EMPNO ENAME
1 7782 CLARK
2 7788 SCOTT
3 7839 KING
4 7844 TURNER
5 7876 ADAMS
6 7900 JAMES
7 7902 FORD
8 7934 MILLER

Q)Oracle cursor : Implicit & Explicit cursors

Oracle uses work areas called private SQL areas to create SQL statements.
PL/SQL construct to identify each and every work are used, is called as Cursor.
For SQL queries returning a single row, PL/SQL declares all implicit cursors.
For queries that returning more than one row, the cursor needs to be explicitly declared.

Q)Explicit Cursor attributes
There are four cursor attributes used in Oracle
cursor_name%Found, cursor_name%NOTFOUND, cursor_name%ROWCOUNT, cursor_name%ISOPEN

Q)Implicit Cursor attributes
Same as explicit cursor but prefixed by the word SQL
SQL%Found, SQL%NOTFOUND, SQL%ROWCOUNT, SQL%ISOPEN

Q)What is the maximum number of triggers, can apply to a single table?
12 triggers

Q)What are PL/SQL Cursor Exceptions?
Cursor_Already_Open, Invalid_Cursor

Q)Any three PL/SQL Exceptions?
Too_many_rows, No_Data_Found, Value_Error, Zero_Error, Others

Q)Display Odd/ Even number of records

Odd number of records:

select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);
Even number of records:
select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp)

Q)Find out nth highest salary from emp table

SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal);

For explanation let a table emp with sal column like below
sal
1200
1300
1500
1200
1250
1700
1250
2000

See DISTINCT word is there in the query
So, you have to find the distinct sal first.
sal
1200
1300
1500
1250
1700
2000
Now see the condition a.sal<= b.sal

This condition compares a.sal and b.sal. The COUNT counts
how many times the a.sal is less than or equal to b.sal,
and gives this value as the output of sub query.
(N.B. comparing to same value means EQUAL SO count is 1).
This count is the value of N.

So after comparision the value of N for different salaries
are like
sal N
1200 6
1300 4
1500 3
1250 5
1700 2
2000 1

Now on querying when you give a value of N the
corresponding value of salary is selected.
Like if you are giving N=2 , then 1700 is displayed.

Q)What is correlated sub-query ?

Correlated sub query is a sub query which has reference to the main query

Q)What is a Non- corelated subquery

Non corelated subquery, where the subquery has been executed once for the entire parent statement.

Q)What are the usage of SAVEPOINTS ?

SAVEPOINTS are used to subdivide a transaction into smaller parts. It enables rolling back part of a transaction. Maximum of five save points are allowed.

Q)What is meant by Scrollable cursor

A scrollable cursor, however, can move forward and backward, and can seek any desired record in the cursor. Such operations are common in applications that present results sets in scrolling windows. With a scrollable cursor, application developers do not need to create and manage their own buffer for the records.

Q)What are the more common pseudo-columns

SELECT rownum, rowid FROM sometable
pseudo column is a column that is not present in the database.
it is used for unique identification of rows like
ROWNUM,ROWID,LEVEL,NEXTVAL,CURVAL,NULL etc.

Q)Where the integrity constraints are stored in Data Dictionary ?

The integrity constraints are stored in USER_CONSTRAINTS.

Q)What is ON DELETE CASCADE ?

When ON DELETE CASCADE is specified ORACLE maintains referential integrity by automatically removing dependent foreign key values if a referenced primary or unique key value is removed.

Q)What is a join ? Explain the different types of joins ?

A join is used to combine rows from multiple tables. A join is performed whenever two or more tables is listed in the FROM clause of an SQL statement.

The types of joins that you learn are

EQUIJOINS
NATURAL JOINS
NON-EQUIJOINS
OUTER JOINS
SELF JOINS

Equijoin : Perhaps the most used and important of the joins is the EQUIJOIN, also referred to as an INNER JOIN. The EQUIJOIN joins two tables with a common column in which each is usually the primary key.

SELECT EMPLOYEE_TBL.EMP_ID,
EMPLOYEE_PAY_TBL.DATE_HIRE
FROM EMPLOYEE_TBL,
EMPLOYEE_PAY_TBL
WHERE EMPLOYEE_TBL.EMP_ID = EMPLOYEE_PAY_TBL.EMP_ID;

Natural Join :
A natural join is a join statement that compares the common columns of both tables with each other. One should check whether common columns exist in both tables before doing a natural join.

Natural joins may cause problems if columns are added or renamed. Also, no more than two tables can be joined using this method. So, it is best to avoid natural joins as far as possible.

Examples
This is the same as an equi join on (emp.deptno = dept.deptno):

SELECT dname, ename FROM dept NATURAL JOIN emp
Same query on the HR sample schema:
SELECT department_name, first_name||' '||last_name
FROM departments NATURAL JOIN employees;

Nonequi join:

An nonequi (or theta) join is a join statement that uses an unequal operation (i.e: <>, >, <, !=, BETWEEN, etc.) to match rows from different tables. The converse of an nonequi join is a equi join operation.

Using Oracle join syntax:

SELECT e.ename, e.sal, s.grade
FROM emp e, salgrade s
WHERE e.sal BETWEEN s.losal and s.hisal;
Using ANSI join syntax:
SELECT e.ename, e.sal, s.grade
FROM emp e INNER JOIN salgrade s
ON e.sal BETWEEN s.losal and s.hisal;

Self join:

A self join is a join in which a table is joined with itself. For example, when you require details about an employee and his manager (also an employee).
Oracle join syntax:

SELECT e1.ename||' works for '||e2.ename "Employees and their Managers"
FROM emp e1, emp e2
WHERE e1.mgr = e2.empno;
ANSI join syntax (Oracle 9i and above):
SELECT e1.ename||' works for '||e2.ename "Employees and their Managers"
FROM emp e1
JOIN emp e2
ON (e1.mgr = e2.empno);

Outer Joins :

An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.

To write a query that performs an outer join of tables A and B and returns all rows from A (a left outer join), use the ANSI LEFT [OUTER] JOIN syntax, or apply the outer join operator (+) to all columns of B in the join condition. For all rows in A that have no matching rows in B, Oracle returns null for any select list expressions containing columns of B.

To write a query that performs an outer join of tables A and B and returns all rows from B (a right outer join), use the ANSI RIGHT [OUTER] syntax, or apply the outer join operator (+) to all columns of A in the join condition. For all rows in B that have no matching rows in A, Oracle returns null for any select list expressions containing columns of A.
To write a query that performs an outer join and and returns all rows from A and B, extended with nulls if they do not satisfy the join condition (a full outer join), use the ANSI FULL [OUTER] JOIN syntax.

For example the following query returns all the employees and department names and even those department names where no employee is working.

select e.empno,e.ename,e.sal,e.deptno,d.dname,d.city from emp e, dept d where e.deptno(+)=d.deptno;
That is specify the (+) sign to the column which is lacking values.

Cartesian Products :

If two tables in a join query have no join condition, Oracle returns their Cartesian product. Oracle combines each row of one table with each row of the other. A Cartesian product always generates many rows and is rarely useful. For example, the Cartesian product of two tables, each with 100 rows, has 10,000 rows. Always include a join condition unless you specifically need a Cartesian product

Q)What is the Subquery ?

Sub query is a query whose return values are used in filtering conditions of the main query. Subqueries will be executed once for the entire parent statement.

Q)Can a view be updated/inserted/deleted? If Yes under what conditions ?
A View can be updated/deleted/inserted if it has only one base table if the view is based on columns from one or more tables then insert, update and delete is not possible.

Q)How many LONG columns are allowed in a table ? Is it possible to use LONG columns in WHERE clause or ORDER BY ?

Only one LONG columns is allowed. It is not possible to use LONG column in WHERE or ORDER BY clause.

Q)What is Referential Integrity ?

Maintaining data integrity through a set of rules that restrict the values of one or more columns of the tables based on the values of primary key or unique key of the referenced table.

Q)What is ROWID ?

ROWID is a pseudo column attached to each row of a table. It is 18 character long, blockno, rownumber are the components of ROWID.

Q)Drop the column
There are 2 ways of doing it :
alter table"table name" drop column "column name";

This drops the column immediately. However if there is huge data and you would like to postpone the task of dropping the columns you can make the columns unused and drop the unused columns during the weekend or less peak activity time.

Q)UNION,INTERSECT,MINUS,UNION ALL ?

Then, a few values are inserted:
alter session set nls_date_format='dd.mm.yyyy';

insert into table_1 values ( 3, 'hello' , to_date('28.08.1970'));
insert into table_1 values ( 42, 'galaxy', to_date('01.01.2001'));
insert into table_1 values (100, 'bye' , to_date('09.02.2004'));
insert into table_2 values ( 3, 'bye' , to_date('28.08.1970'));
insert into table_2 values ( 42, 'galaxy', to_date('01.01.2001'));
insert into table_2 values ( 60, 'bye' , to_date('09.02.2004'));
insert into table_2 values ( 3, 'hello' , to_date('05.05.2002'));

union all:

union all selects all rows from all select statements:

select col_1, col_2, col_3 from table_1 union all
select col_1, col_2, col_3 from table_2;

As can be seen, all records of both tables are returned:
COL_1 COL_2 COL_3
---------- ---------- ----------
3 hello 28.08.1970
42 galaxy 01.01.2001
100 bye 09.02.2004
3 bye 28.08.1970
42 galaxy 01.01.2001
60 bye 09.02.2004
3 hello 05.05.2002

union :

union all is very similar to union, however, it dismisses duplicate rows found across different select statements:

select col_1, col_2, col_3 from table_1 union
select col_1, col_2, col_3 from table_2;

The galaxy record is a duplicate. Hence, it is returned only once:
COL_1 COL_2 COL_3
---------- ---------- ----------
3 bye 28.08.1970
3 hello 28.08.1970
3 hello 05.05.2002
42 galaxy 01.01.2001
60 bye 09.02.2004
100 bye 09.02.2004

intersect:

intersect only returns the rows that are found in all select statements:

select col_1, col_2, col_3 from table_1 intersect
select col_1, col_2, col_3 from table_2;

Only the galaxy record is returned. It's the only record that is stored in both tables:
COL_1 COL_2 COL_3
---------- ---------- ----------
42 galaxy 01.01.2001

minus:

minus returns all rows from the first select statements except those who are duplicated in a following select statement:

select col_1, col_2, col_3 from table_1 minus
select col_1, col_2, col_3 from table_2;

As the galaxy record is found in both tables, it is removed from the first table's record set:
COL_1 COL_2 COL_3
---------- ---------- ----------
3 hello 28.08.1970
100 bye 09.02.2004

The following query is valid:
SELECT 3 FROM DUAL
INTERSECT
SELECT 3f FROM DUAL;

This is implicitly converted to the following compound query:
SELECT TO_BINARY_FLOAT(3) FROM DUAL
INTERSECT
SELECT 3f FROM DUAL;

The following query returns an error:
SELECT '3' FROM DUAL
INTERSECT
SELECT 3f FROM DUAL;

Q)What is CYCLE/NO CYCLE in a Sequence ?

CYCLE specifies that the sequence continues to generate values after reaching either maximum or minimum value. After pan ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum.NO CYCLE specifies that the sequence cannot generate more values after reaching its maximum or minimum value.

Q)How will you a activate/deactivate integrity constraints ?

The integrity constraints can be enabled or disabled by ALTER TABLE ENABLE constraint/DISABLE constraint.

Q)What is difference between CHAR and VARCHAR2 ? What is the maximum SIZE allowed for each type ?

CHAR pads blank spaces to the maximum length. VARCHAR2 does not pad blank spaces. For CHAR it is 255 and 2000 for VARCHAR2.

Q)Database link

Database Link is a named path through which a remote database can be accessed.

Q)What are the data types allowed in a table ?

CHAR,VARCHAR2,NUMBER,DATE,RAW,LONG and LONG RAW

Q)What is the maximum number of triggers, can apply to a single table?

Insert/Update/Delete :- 3
Before/After:- 2
Row Level/Statement Level:-2

Hence 3*2*2

Q)What is an integrity constraint?

Integrity constraint restricts values of a column so that only meaningful values as per the business logic of the table be entered into the column.

Integrity constraints are of the following types:

Not null
Unique
Primary Key
Foreign Key (referential integrity)
Check -- custom constraints e.g. check sal > 0, check joiningdate > date()

constraints; which enforces rules on a table columns which stops to enter unnecessary data

while inserting data or updating the data there are 5 types of constraints;
1) not null;This constraint doesn't allow null values.

2)Unique; The constraint associated with unique column keeps allow uniquely.

3)Primary key(primary+notnull) This constraint doesn't allow repeated values & null values.

4)check;This constraint checks the condition weather satisfied or not.

5)Foreign key;This constraint is used as reference key between the columns of two tables

Q)How do I replace a comma (,) with a blank in a select statement?

select empno||' '||ename from emp;

Display the number value in Words
select sal, (to_char(to_date(sal,'j'), 'jsp'))
from emp;

Q)If a view on a single base table is manipulated will the changes be reflected on the base table
an Insert,Update,delete can be done through views is
1. inserts/updates/deletes done only in one base table at a time
2. primary key columns should be part of view
3. columns which are going to be updated must be part of vew def.
4. it shouldn't voielet the contraints

Q)How to access the current value and next value from a sequence
I would like to give you a small example to use the sequence.currval and sequence.nextval

create sequence seq_name

start with 1

minvalue 1

maxvalue 999

increment by 1

nocycle

insert into table_name (sno,name) values (seqname.nextval,'abc');

select seqname.currval from dual

Q)If unique key constraint on DATE column is created, will it validate the rows that are inserted with SYSDATE

If there is a unique key defined on a column which has date as the data type then one can insert the same date more than once.The date is always stored in the format dd-mon-yyyy hh:mi:ss.The reason why it will accept the same date again is that the time taken for you to enter the date for the first time and entering the date again for the second time there will be a gap of atleast one secon which makes that date value unique. ONE CAN INSERT THE SAME DATE VALUE IN A UNIQUE COLUMN MORE THAN ONCE.

Q)Where the integrity constraints are stored in data dictionary

The integrity constraints are stored in USER_CONSTRAINTS.

Q)Is it possible to use LONG columns in WHERE clause or ORDER BY
No

Q)Difference between SQL and SQL* Plus

SQL*PLUS is a command line tool where as SQL and PL/SQL language interface and reporting tool. Its a command line tool that allows user to type SQL commands to be executed directly against an Oracle database. SQL is a language used to query the relational database(DML,DCL,DDL). SQL*PLUS commands are used to format query result, Set options, Edit SQL commands and PL/SQL.

Q)Difference between procedure and function.

Functions are named PL/SQL blocks that return a value and can be called with arguments procedure a named block that can be called with parameter. A procedure all is a PL/SQL statement by itself, while a Function call is called as part of an expression.

Q)What is an UTL_FILE.What are different procedures and functions associated with it?

UTL_FILE is a package that adds the ability to read and write to operating system files. Procedures associated with it are FCLOSE, FCLOSE_ALL and 5 procedures to
output data to a file PUT, PUT_LINE, NEW_LINE, PUTF, FFLUSH.PUT, FFLUSH.PUT_LINE,FFLUSH.NEW_LINE. Functions associated with it are FOPEN, ISOPEN
Cursor Oracle uses work area to execute SQL statements and store processing information PL/SQL construct called a cursor lets you name a work area and access its stored information A cursor is a mechanism used to fetch more than one row in a Pl/SQl block.
Cursor is a variable.

it is similar to a 2D array .
used for processing multiple rows.
used for storing data temporarily.

Q)Which is more faster - IN or EXISTS?
EXISTS is more faster than IN because EXISTS returns a Boolean value whereas IN returns a value.

Q)What are different Oracle database objects?

TABLES
VIEWS,
INDEXES,
SYNONYMS,
SEQUENCES,
TIGGERS,
PACKAGES,
PROCEDURE,
FUNCTION,

Q)Can a primary key contain more than one columns?

primary key not allow null values,unique key allow only one null value.In table only one primary is allowed,unique key as many as it can allow. In each table each column it can allow only one primary key the same table more than one primary key then it is called
composite primary key

Q)What are different modes of parameters used in functions and procedures?
-IN
-OUT
-INOUT

Q)Difference between NO DATA FOUND and %NOTFOUND

NO DATA FOUND is an exception raised only for the SELECT....INTO statements when the where clause of the query does not match any rows. When the where clause of the explicit cursor does not match any rows the %NOTFOUND attribute is set to TRUE instead.

________________________________________
NO_DATA_FOUND is pre defind exception of oracle.
when we can't get any data
from the table for any query.
In that case we will get error.
%NOT FOUND is one of the attribute
of explicit cursor.
when can't get any data from explicit cursor in that case
%NOT FOUND will returns true
otherwise it returns false.
Outer Join
OUTER join means fetched all the rows that is based on condition,as well as retrieve all rows that is not based on condition.

Q)What is the purpose of a cluster?

Oracle does not allow a user to specifically locate tables, since that is a part of the function of the RDBMS. However, for the purpose of increasing performance, oracle allows a developer to create a CLUSTER. A CLUSTER provides a means for storing data from different tables together for faster retrieval than if the table placement were left to the RDBMS.
it's a temperory and logical memory allocatiopn where we can put several tables.

Q)What is difference between SUBSTR and INSTR?

SUBSTR:-
returns a specified portion of a string
eg:- SUBSTR('GANGAA',5)
->output GANGA

INSTR:-
provides character position
found in a string. eg INSTR('KHAIRATABAD','A',3)
->output 8 (3RD occurrence of 'A')

SUBSTR returns a specified portion of a string eg SUBSTR('BCDEF',4) output BCDE
INSTR provides character position in which a pattern is found in a string. eg INSTR('ABC-DC-F','-',2) output 7 (2nd occurence of '-')

Q)What is difference between UNIQUE and PRIMARY KEY constraints?

A table can have only one PRIMARY KEY whereas there can be any number of UNIQUE keys. The columns that compose PK are automatically define NOT NULL, whereas a column that compose a UNIQUE is not automatically defined to be mandatory must also specify the column is NOT NULL.

Q)There is a '%' sign in one field of a column. What will be the query to find it ?

'\' Should be used before '%'.
You need to use INSTR function in the WHERE clause as follows :

select ename from emp
where instr(ename,'%',1,1) > 1

Q)What is OCI. What are its uses?

Oracle Call Interface is a method of accesing database from a 3GL program. Uses--No precompiler is required,PL/SQL blocks are executed like other DML statements. The OCI library provides
--functions to parse SQL statemets
--bind input variables
--bind output variables
--execute statements
--fetch the results

Q)Can a function take OUT parameters. If not why?

No. A function has to return a value,an OUT parameter cannot return a value.

Q)How you open and close a cursor variable.Why it is required?

OPEN cursor variable FOR SELECT...Statement
CLOSE cursor variable In order to associate a cursor variable with a particular SELECT statement OPEN syntax is used. In order to free the resources used for the query CLOSE statement is used.

Q)What a SELECT FOR UPDATE cursor represent.

SELECT......FROM......FOR......UPDATE[OF column-reference][NOWAIT] The processing done in a fetch loop modifies the rows that have been retrieved by the
cursor. A convenient way of modifying the rows is done by a method with two parts: the FOR UPDATE clause in the cursor declaration, WHERE CURRENT OF CLAUSE
in an UPDATE or declaration statement.

Q)Difference between foreign key and primary key?

1)Primary key is unique key but foriegn key always refers to
primary key.

2)Primary keys enforce entity integrity by uniquely
identifying entity instances. Foreign keys enforce.

3)Primary key is unique
primary key is not NULL and

foreign key is NULL
foreign key reference as Primary key in another table.

4) primary key is used to identify a row and it doesnot allow
null values. It avoids duplication of rows.

whereas foreign key refers to a column or combination of
columns included in the defenition of referential integrity.


referential integrity by completing an association between
two entities.

Q)What should be the return type for a cursor variable.Can we use a scalar data type as return type?

The return type for a cursor must be a record type.It can be declared explicitly as a user-defined or %ROWTYPE can be used. eg TYPE t_studentsref IS REF CURSOR
RETURN students%ROWTYPE

Q)Can you use a commit statement within a database trigger?
no,we can't use commit statement in the trigger,we are using trigger for checking purpose.

Q)What is difference between a formal and an actual parameter?

The variables declared in the procedure and which are passed, as arguments are called actual, the parameters in the procedure declaration. Actual parameters contain the values that are passed to a procedure and receive results. Formal parameters are the placeholders for the values of actual parameters

Q)What is a cursor for loop?

Cursor For Loop is a loop where oracle implicitly declares a loop variable, the loop index that of the same record type as the cursor's record.

________________________________________
When we use for loops,there is no need to declare explicit cursor.
Only case of for loop,cursor is implicitly open and after fetching the data cursor is implicitly closed.
Example given below
SET SERVEROUTPUT ON
BEGIN
FOR emp_record IN (SELECT last_name,department_id FROM employees) LOOP
IF emp_record.department_id=80 THEN
DBMS_OUTPUT.PUT_LINE('employees'||'emp_record.last_name'||'works for sales department');
END IF;
END LOOP;--implicit cursor closed
END;

Q)Difference between an implicit & an explicit cursor.

PL/SQL declares a cursor implicitly for all SQL data manipulation statements, including quries that return only one row. However,queries that return more than one row you must declare an explicit cursor or use a cursor FOR loop. Explicit cursor is a cursor in which the cursor name is explicitly assigned to a SELECT statement via the CURSOR...IS statement. An implicit cursor is used for all SQL
statements Declare, Open, Fetch, Close. An explicit cursors are used to process multirow SELECT statements An implicit cursor is used to process INSERT, UPDATE, DELETE and single row SELECT. .INTO statements.

________________________________________
The implicit cursor is used to process INSERT, UPDATE,
DELETE, and SELECT INTO statements. During the processing of
an implicit cursor,Oracle automatically performs the OPEN,
FETCH, and CLOSE operations.

Where as in explicit cursors,the process of its working is
done in 4 steps namely DECLARE a cursor,OPEN a cursor,
FETCH from cursor and CLOSE a
cursor.

IMPLICT CURSOR:- Automatically porvide by oracle which
perform DML statements. queries returns only one row.

EXPLICT CURSOR:- Defined by user. queries returns more
than rows.

Explicit Cursor:-We are not able to Handle NO_DATA_FOUND
Exception.

Implicit Cursor:-We are able to Handle NO_DATA_FOUND
Exception.

Q)There is a string 120000 12 0 .125 , how you will find the position of the decimal place?
INSTR('120000 12 0 .125',1,'.')

output 13

Q)Which datatype is used for storing graphics and images?
LONG RAW data type is used for storing BLOB's (binary large objects).


Q)When do you use WHERE clause and when do you use HAVING clause?

HAVING clause is used when you want to specify a condition for a group function and it is written after GROUP BY clause. The WHERE clause is used when you want to specify a condition for columns, single row functions except group functions and it is written before GROUP BY clause if it is used.
1.where clause is used to restrict the row ,basically where clause operate on single row function

2.Having clause is used to restrict the groups that means which groups to be displayed.
where is used to restrict the rows
having is used to restrict the group


Q)What is difference between Rename and Alias?

Rename is a permanent name given to a table or column whereas Alias is a temporary name given to a table or column which do not exist once the SQL statement is executed.
Rename;This is a DDL command which is used to the change the name of an object whereas alias is the alternate name given to the table while selecting this alias is used generally to join a same table.