Sql Question and answer 3

1. What is 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.

2. Which datatype is used for storing graphics and images

LONG RAW data type is used for storing BLOB's (binary large objects).


3. How will you delete duplicating rows from a base table
delete from table_name where rowid not in (select max(rowid) from table group by duplicate_values_field_name);

or

delete duplicate_values_field_name dv from table_name ta where rowid <(select min(rowid) from table_name tb where ta.dv=tb.dv);


4. What is difference between SUBSTR and INSTR

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 '-')

5. 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

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

'\' Should be used before '%'.