Minimize
Untitled Document
Phone: (888) 551-6343 Fax: (678) 495-1302
Click Here to View Our Jobs

Centrics Group  >  Career Center  >  Sample Interview Questions  >  Oracle Interview Questions

Oracle Interview Questions

Oracle Interview Questions

Common Oracle Questions and Answers

What is a CO-RELATED SUBQUERY
  1. A CO-RELATED SUBQUERY is one that has a correlation name as table or view designator in the FROM clause of the outer query and the same correlation name as a qualifier of a search condition in the WHERE clause of the subquery.
2. eg
3. SELECT field1 from table1 X
4. WHERE field2>(select avg(field2) from table1 Y
5. where
field1=X.field1);
(The subquery in a correlated subquery is revaluated for every row of the table or view named in the outer query.)

What are various joins used 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 one of the tables in the join condition even though they don’t satisfy the join condition.
Equi-join–Its a join condition that retrieves rows from one or more tables in which one or more columns in one table are equal to one or more columns in the second table.

What are various constraints used in SQL?
NULL
NOT NULL
CHECK
DEFAULT

What are different Oracle database objects?
TABLES
VIEWS
INDEXES
SYNONYMS
SEQUENCES
TABLESPACES etc

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.

What is a view?
A view is stored procedure based on one or more tables, its a virtual table.
What are various privileges that a user can grant to another user?
SELECT
CONNECT
RESOURCE

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.

Can a primary key contain more than one columns?
Yes

How you will avoid duplicating records in a query?
By using DISTINCT

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.

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

How will you delete duplicating rows from a base table?
DELETE FROM table_name A WHERE rowid>(SELECT min(rowid) from table_name B where 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 WHERE rowid NOT IN (SELECT MAX(ROWID) FROM table_name GROUP BY column_name)

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 ‘-’ )

Latest Tweets

Follow us @centricsgroup
Twitter Updates
Loyalty Program

Loyalty Program

At the Centrics Group, our IT staffing solutions are guaranteed to earn your long-term business; we want you to be satisfied.

More  

Referral Program

Referral Program

Earn money for referring candidates to The Centrics Group. This IT recruiting cash is yours when they're hired!


More  

Email Signup

Email Signup

Subscribe to our email newsletter to receive updates on the latest IT job opportunities straight to your inbox!

Blog

Blog

Stay up-to-date on the current trends and topics in IT recruiting and IT staffing solutions, written by our IT recruiters.

More  

Copyright (c) 2017 Centrics Group > Privacy Policy > Terms of Use > Employee Login

Text/HTML

Minimize

Text/HTML

Minimize
The Centrics Group
Web Design by AtlantaSky.