Tutorial Downloads .com

Home

Oracle interview questions and answers

These are some common oracle interview questions with answers.

1.††††† What is the command to To see current user name
†Sql> show user;

2.††††† How to Change the SQL prompt name
†SQL> set sqlprompt “Manimara > “
Manimara >
Manimara >

3.††††† Command Switch to DOS prompt
†SQL> host

4.††††† How do I eliminate the duplicate rows in a table ?
†SQL> delete from table_name where rowid not in (select max(rowid) from table group by duplicate_values_field_name);
or
SQL> delete duplicate_values_field_name dv from table_name ta where rowid <(select min(rowid)† from table_name tb where ta.dv=tb.dv);


5.††††† How do I display row number with records?
To achive this use rownum pseudocolumn with query, like SQL> SQL> select rownum, ename from emp;


6.††††† Display the records between two range
select rownum, empno, ename† from emp† where† rowid in
†(select rowid from emp where rownum <=&upto
†minus
†select rowid from emp where rownum<&Start);
Enter value for upto: 10
Enter value for Start: 7

7.††††† I know the nvl function only allows the same data type(ie. number or char or date Nvl(comm, 0)), if commission is null then the text “Not Applicable”† want to display, instead of blank space. How do I write the query?

†SQL> select nvl(to_char(comm.),'NA') from emp;

Output :

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

8.††††† 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.

9.††††† Explicit Cursor attributes
†There are four cursor attributes used in Oracle
cursor_name%Found, cursor_name%NOTFOUND, cursor_name%ROWCOUNT, cursor_name%ISOPEN

10.† Implicit Cursor attributes
†Same as explicit cursor but prefixed by the word SQL

SQL%Found, SQL%NOTFOUND, SQL%ROWCOUNT, SQL%ISOPEN

Tips : 1. Here SQL%ISOPEN is false, because oracle automatically closed the implicit cursor after executing SQL statements.
†††††† : 2.† All are† Boolean attributes.

11.† 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);

Enter value for n: 2
††††† SAL
---------
†††† 3700

12.† To view installed Oracle version information
†SQL> select banner from v$version;

13.† Display the number value in Words
†SQL> select sal, (to_char(to_date(sal,'j'), 'jsp'))
from emp;
the output like,

††††† SAL (TO_CHAR(TO_DATE(SAL,'J'),'JSP'))
--------- -----------------------------------------------------
††††† 800 eight hundred
†††† 1600 one thousand six hundred
†††† 1250 one thousand two hundred fifty
If you want to add some text like,
Rs. Three Thousand only.
SQL> select sal† "Salary ",
†(' Rs. '|| (to_char(to_date(sal,'j'), 'Jsp'))|| ' only.'))
†"Sal in Words" from emp
/
Salary† Sal in Words
------- ------------------------------------------------------
††† 800† Rs. Eight Hundred only.
†† 1600† Rs. One Thousand Six Hundred only.
†† 1250† Rs. One Thousand Two Hundred Fifty only.

14.† Display Odd/ Even number of records
†Odd number of records:
select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);
1
3
5
Even number of records:
select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp)
2
4
6

15.† Which date function returns number value?
†months_between

16.† Any three PL/SQL Exceptions?
†Too_many_rows, No_Data_Found, Value_Error, Zero_Error, Others

17.† What are PL/SQL Cursor Exceptions?
†Cursor_Already_Open, Invalid_Cursor

18.† Other way to replace query result null value with a text
†SQL> Set NULL ‘N/A’
to reset SQL> Set NULL ‘’

19.†† What are the more common pseudo-columns?
†SYSDATE, USER , UID, CURVAL, NEXTVAL, ROWID, ROWNUM

20.†† What is the output of SIGN function?
†1 for positive value,
0 for Zero,
-1 for Negative value.

21.† What is the maximum number of triggers, can apply to a single table?
†12 triggers.


22)†† †What is a relational database management system?
When answering these types of questions you need to answer them in a very detailed manner because everyone in your group can answer them without any difficulty. There should be difference between your approach and the other candidates. Interview candidate should try to present all the information he has in a very detailed and step by step manner. A professional approach should be present.

23)†† †What do you know about normalization? Explain in detail?

24)†† †Explain the difference between a procedure and a function? What do you understand by those terms?

25)†† †Explain about functional dependency and its relation with table design?

26)†† †Explain the different normalization forms?
Remember how you answer shortens your technical interview you are trying to convince the interviewer that you are a suitable candidate for the job.

27)†† †What are the different types of trigger and explain its various uses and functions?

28)†† †Are truncate and delete commands same? If so why?

29)†† †Compare and contrast between SQL and SQL server and explain its various functions?

30)†† † What is a query and state the different types of queries and their uses?

31)†† † Explain about your project and its relation the current job position you are applying to?

32)†† † How different is MS Access and Oracle?

33)†† † What is a cluster and non cluster index?

34)†† † State the difference between a primary key and foreign key?

35)†† † State all possible different index configurations a table can possibly have?

36)†† † What is a cursor and what are the steps need to be taken?
If possible while answering this question try to explain him the various steps in an order.

37)†† † Why do you use stored procedures and state some of its disadvantages?

38)†† † State the various uses of DBCC command?

39)†† † What is a collation and state the different types of collation sensitivity?

40)†† † What are the uses of linked server and explain it in detail?

41)†† † State and explain about the different types of data models?

42)†† † State and explain about oracle instance?

43)†† † Explain about achiever in SQL?

44)†† † What are the different Pseudo commands? Explain in general?

45)†† † State some uses of redo log files?
46)†† †Explain what you understand of a primary key and a unique key and explain the differences between them?
47)†† † Explain about the commands Truncate and Delete?
48)†† † Which operating system you are comfortable to use for databases?
49)†† † Do you like command language or GUI?
50)†† † State and explain some of the properties of Sub-Query?
51)†† † What are the different types of user defined functions you can create?
52)†† † What do you understand by log shipping explain the process?
53)†† † State and explain the different types of replication?
54)†† † Explain the cases or situations where you use de-normalization?


Resource: http://www.pdftutorials.com/FAQ/oracle-interview-questions.html
Posted By : Oracle interview
On date : 09.25.09

Most used ORACLE Database Tutorials

Oracle SQL Developer 1.5.5 download and installation
To install Oracle SQL Developer 1.5.5 download the file and unzip into an empty folder. Select the "Use folder names" checkbox when unzipping the file. A Check for Updates facility is available to install third-party database drivers, if required.
Oracle Tutorials ( Ebooks , pdf's )
Oracle 8i the Complete ReferenceMcGrawHill-Oracle_Database_10g_New_Features.chm Database.10g..High.Availablity.with.RAC.Flashback.&.Data.Guard McGraw Hill - Oracle Database 10g, A Beginner's Guide.chm
Mastering Oracle PL/SQL: Practical Solutions - pdf tutorial
This book offers a wealth of tips, techniques, and complete strategies for maximizing the benefits of PL/SQL within your organization. By the end of this book, youíll be as convinced as we are that PL/SQL isnít just a useful toolóitís an integral part of any Oracle application youíll ever develop.
Oracle 10g Database PL/SQL User's Guide and Reference study material - pdf tutorial
Anyone developing PL/SQL-based applications for Oracle should read this book. This book is intended for programmers, systems analysts, project managers, database administrators, and others who need to automate database operations. People developing applications in other languages can also produce mixed-language applications with parts written in PL/SQL.
Oracle interview questions and answers
These are some common oracle interview questions with answers.
Oracle Database SQL Quick Reference tutorial
This quick reference contains a high-level description of the Structured Query Language (SQL) used to manage information in an Oracle database.
New Features in Oracle Database 10g
This tutorial provides the details of New Features in Oracle Database 10g
Oracle SQL Parallel Execution tutorial
This Oracle tutorial focuses on SQL parallel execution only, which consists of parallel query, parallel DML (Data Manipulation Language) and parallel DDL (Data Dictionary Language). While this pdf study material focuses on Oracle Database 11g, the information in this paper also applies to Oracle Database 10g and higher, unless explicitly stated.
Oracle SQL Developer Data Modeler tutorial and reference
Oracle SQL Developer Data Modeler is a new, graphical data modeling tool that facilitates and enhances communication between data architects, database administrators, application developers and users, and simplifies the data modeling development process itself.
Oracle Rdb 7 Guide to SQL Programming - pdf tutorial
This manual describes how to design and develop host language application programs that use SQL (structured query language) to store, modify, and retrieve data from Oracle Rdb databases.
Oracle TimesTen In-Memory database Extension for Oracle SQL Developers - pdf tutorial
Oracle TimesTen In-Memory database Extension for Oracle SQL Developers is a free extension to Oracle SQL Developer, a graphical tool that enhances productivity and simplifies database development tasks.
Oracle Database SQL Developer Installation Guide - pdf tutorial
This Oracle SQL installation guide is helpful for those who plan to download SAL Developer kit (.zip ) file and install it as a freestanding tool
SQL Apply Best Practices: Oracle Data Guard 10g study guide and reference
With Oracle Data Guard SQL Apply in Oracle Database 10g, Oracle is addressing the requirements of the business community for an online disaster-recovery solution that also provides a means to offload reporting and decision support operations from he primary database
Oracle SQL Model Clause version 10g - study guide and reference tutorial
Oracle Database 10g introduces an innovative approach to complex SQL calculations: the SQL Model clause.
Best Practices for Minimal Downtime Migration to ASM Oracle 10g tutorial
This oracle tutorial describes procedures to dramatically reduce downtime during the process of migrating Oracle databases residing on raw partitions, volumes or conventional file systems to Automatic Storage Management (ASM) by using Oracle Recovery Manager (RMAN) and Oracle Data Guard.

Latest added ORACLE Database Tutorials

New Features in Oracle Database 10g
This tutorial provides the details of New Features in Oracle Database 10g
Oracle Rdb 7 Guide to SQL Programming - pdf tutorial
This manual describes how to design and develop host language application programs that use SQL (structured query language) to store, modify, and retrieve data from Oracle Rdb databases.
Oracle interview questions and answers
These are some common oracle interview questions with answers.
Oracle SQL Developer Data Modeler Naming Standardization reference guide
SQL Developer Data Modeler provides and uses different elements when applying naming standards to models. You can define and then apply these standards to the different models available in SQL Developer Data Modeler.
Oracle SQL Developer 1.5.5 download and installation
To install Oracle SQL Developer 1.5.5 download the file and unzip into an empty folder. Select the "Use folder names" checkbox when unzipping the file. A Check for Updates facility is available to install third-party database drivers, if required.
Oracle SQL Model Clause version 10g - study guide and reference tutorial
Oracle Database 10g introduces an innovative approach to complex SQL calculations: the SQL Model clause.
SQL Apply Best Practices: Oracle Data Guard 10g study guide and reference
With Oracle Data Guard SQL Apply in Oracle Database 10g, Oracle is addressing the requirements of the business community for an online disaster-recovery solution that also provides a means to offload reporting and decision support operations from he primary database
Oracle Database SQL Developer Installation Guide - pdf tutorial
This Oracle SQL installation guide is helpful for those who plan to download SAL Developer kit (.zip ) file and install it as a freestanding tool
Best Practices for Minimal Downtime Migration to ASM Oracle 10g tutorial
This oracle tutorial describes procedures to dramatically reduce downtime during the process of migrating Oracle databases residing on raw partitions, volumes or conventional file systems to Automatic Storage Management (ASM) by using Oracle Recovery Manager (RMAN) and Oracle Data Guard.
Oracle SQL Developer Data Modeler tutorial and reference
Oracle SQL Developer Data Modeler is a new, graphical data modeling tool that facilitates and enhances communication between data architects, database administrators, application developers and users, and simplifies the data modeling development process itself.