Oracle Interview Questions Page 6

1.What is a CO-RELATEDSUBQUERY

A CO-RELATED SUBQUERY is one that has a correlation name as table or viewdesignator in the FROM clause of the outer query and the same correlation nameas a qualifier of a search condition in the WHERE clause of the subquery.

SELECTfield1 from table1 X

WHEREfield2>(select avg(field2) from table1 Y 

where

field1=X.field1);

(The subquery in a correlated subquery is revaluated for every row of the tableor view named in the outer query.)

2.What are various joinsused while writing SUBQUERIES

Self join-Its a join foreign key of a table references the same table.

Outer Join--Its a join condition used where One can query all the rows of oneof the tables in the join condition even though they don't satisfy the joincondition.

Equi-join--Its a join condition that retrieves rows from one or more tables inwhich one or more columns in one table are equal to one or more columns in thesecond table.

3.What are variousconstraints used in SQL

NULL
NOT NULL
CHECK
DEFAULT

4.What are different Oracledatabase objects

TABLES
VIEWS
INDEXES
SYNONYMS
SEQUENCES
TABLESPACES etc

5.What is difference betweenRename and Alias

Rename is a permanent name given to a table or column whereas Alias is atemporary name given to a table or column which do not exist once the SQLstatement is executed.

6.What is a view

A view is stored procedure based on one or more tables, its a virtual table.

7.What are variousprivileges that a user can grant to another user

SELECT
CONNECT
RESOURCE

8.What is difference betweenUNIQUE and PRIMARY KEY constraints

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

9.Can a primary key containmore than one columns
Yes

10.How you will avoidduplicating records in a query

By using DISTINCT

11.What is difference betweenSQL and SQL*PLUS

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

12.Which datatype is used forstoring graphics and images

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

13.How will you deleteduplicating rows from a base table

DELETE FROM table_name A WHERE rowid>(SELECT min(rowid) from table_name Bwhere B.table_no=A.table_no);

CREATE TABLE new_table AS SELECT DISTINCT * FROM old_table;

DROP old_table RENAME new_table TO old_table DELETE FROM table_name A WHERErowid NOT IN (SELECT MAX(ROWID) FROM table_name GROUP BY column_name)

14.What is difference betweenSUBSTR and INSTR

SUBSTR returns a specified portion of a string eg SUBSTR('BCDEF',4) output BCDEINSTR provides character position in which a pattern is found in a string.

eg INSTR('ABC-DC-F','-',2) output 7 (2nd occurence of '-')

15.There is a string '12000012 0 .125' ,how you will find the position of the decimal place

INSTR('120000 12 0 .125',1,'.') output 13

16.There is a '%' sign in onefield of a column. What will be the query to find it.

'\' Should be used before '%'.

17.When you use WHERE clauseand when you use HAVING clause

HAVING clause is used when you want to specify a condition for a group functionand it is written after GROUP BY clause The WHERE clause is used when you wantto specify a condition for columns, single row functions except group functionsand it is written before GROUP BY clause if it is used.

18.Which is more faster - INor EXISTS

EXISTS is more faster than IN because EXISTS returns a Boolean value whereas INreturns a value.

19.What is a OUTER JOIN

Outer Join--Its a join condition used where you can query all the rows of one ofthe tables in the join condition even though they dont satisfy the joincondition.

20.How you will avoid yourquery from using indexes

SELECT * FROM emp Where emp_no+' '=12345;

i.e you have to concatenate the column name with space within codes in thewhere condition.

SELECT /*+ FULL(a) */ ename, emp_no from emp where emp_no=1234;

i.e using HINTS

Posted in: Oracle