Solved

Oracle SQL and PLSQL Inteview Questions

Posted on 2013-01-22
5
713 Views
Last Modified: 2013-02-12
Hi ALl,

  Can I have a list of very commonly asked SQL and PLSQL Interview QUestions & Answers please....

For Basic SQL:


How do you convert a date to a string? To_char. A bonus would be that they always include a format mask.


What is an aggregate function? I'm looking for "grouping", sums or counts, etc.


What is an interval? Specifies a period of time.


What is a nested subquery? A subquery in a where clause.


What is the dual table? A single row table provided by oracle for selecting values and expressions.




For Basic PL/SQL:



Describe the block structure of PLSQL. Declaration, Begin, exception, end.


What is an anonymous block? Unnamed PL/SQL block.


What is a PL/SQL collection? PL/SQL Table, Varray, PL/SQL Array, etc.


What is the difference between an explicit cursor and a select into. You might get something about performance but that's a myth. An explicit cursor is just more typing. A cursor for loop would be used to return more than a single row.


Why would you choose to use a package versus straight procedures and functions? I look for maintenance, grouping logical functionality, dependency management, etc. I want to believe that they believe using packages is a "good thing".

ThankS
Aman
0
Comment
Question by:amankhan2005
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 35

Assisted Solution

by:YZlat
YZlat earned 167 total points
ID: 38807175
0
 
LVL 15

Accepted Solution

by:
Devinder Singh Virdi earned 167 total points
ID: 38807280
I believe, you need to be confident and have clear picture of concept.
i.e.
Which table will you use to see user constraints? Normal question
Vs
I want to display data in one line showing Primary Key constraint not matching with child's foreign key.

Not 100% sure, I think this is against the policy of this website to provide that kind of information therefore I am not giving direct answer.

Learn any book and dig more what you can do with that.

i.e.
Can we call Sqlplus variable from PL/Block
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 38807870
Correction:-
I want to display data in one line showing Primary Key constraint not matching with child's foreign key INDEX columns ordering.
0
 
LVL 23

Assisted Solution

by:paquicuba
paquicuba earned 166 total points
ID: 38808533
I haven't had an interview like this in a few years. I'm the one now who sometimes has to help management in interviewing others and you kind of know when people answer certain questions the same way the read it online, so try to stay away from Google or any other search engine, 'cause chances are that the interviewer may have a printout of the same webpage you visited before going to the interview.

Learn Oracle as much as you can and go to the interview with what you know - what if you get the job and then you have no freaking idea how to code?

Try to impress the interviewers but giving them information that they don't have written down in the questionnaire, so you sound smarter than them.

For example, using your last question:

Why would you choose to use a package versus straight procedures and functions?

Instead of mentioning what everyone knows, mention that packages are great because contexts can only be associated with packages and contexts are excellent for database security. Also packages' execution section can initialize the package once per session and any program unit and/or variable in the executable section will be run and/or initialized once per session as well. Then talk about overloading, private global variables, encapsulation, etc, etc.

One quick question that most I have interviewed have failed is:

What's the default value a boolean variable in the declarative section of a program unit?
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 38808637
we need to learn by practising with code examples by writing/executing them to understand how they work as they will put stuff/answers into our heads which stay there for a long time rather than just reading all urls/links/books/forums as they stay for a short span of time.

Do not worry too much about the interview questions as no one in this world knows answers for all oracle questions. i mean prepare yourself with the required learning/skills and face it with confidence :)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question