Interview questions for Oracle database administrator

1. Differentiate between TRUNCATE and DELETE
2. What is the maximum buffer size that can be specified using the DBMS_OUTPUT.ENABLE function?
3. Can you use a commit statement within a database trigger?
4. What is an UTL_FILE.What are different procedures and functions associated with it?
   
5. Difference between database triggers and form triggers?
6. What is OCI. What are its uses?
7. What are ORACLE PRECOMPILERS?
8. What is syntax for dropping a procedure and a function? Are these operations possible?
9. Can a function take OUT parameters. If not why?
10. Can the default values be assigned to actual parameters?
11. What is difference between a formal and an actual parameter?
12. What are different modes of parameters used in functions and procedures?
13. Difference between procedure and function.
14. Can cursor variables be stored in PL/SQL tables.If yes how. If not why?
15. How do you pass cursor variables in PL/SQL?
16. How do you open and close a cursor variable.Why it is required?
17. What should be the return type for a cursor variable.Can we use a scalar data type as return type?
18. What is use of a cursor variable? How it is defined?
19. What WHERE CURRENT OF clause does in a cursor?
20. Difference between NO DATA FOUND and %NOTFOUND
21. What is a cursor for loop?
22. What are cursor attributes?
23. Difference between an implicit & an explicit cursor.
24. What is a cursor?
25. What is the purpose of a cluster?
26. How do you find the numbert of rows in a Table ?
27. Display the number value in Words?
28. What is a pseudo column. Give some examples?
29. How you will avoid your query from using indexes?
30. What is a OUTER JOIN?
31. Which is more faster - IN or EXISTS?
32. When do you use WHERE clause and when do you use HAVING clause?
33. There is a % sign in one field of a column. What will be the query to find it?
34. What is difference between SUBSTR and INSTR?
35. Which datatype is used for storing graphics and images?
36. What is difference between SQL and SQL*PLUS?
37. What is difference between UNIQUE and PRIMARY KEY constraints?
38. What is difference between Rename and Alias?
39. What are various joins used while writing SUBQUERIES? 

Oracle Architecture Interview Question & Answers

1: What constitute an oracle instance?

SGA and oracle background processes constitute an oracle instance. 

(Or) 

Combination of memory structure and background process.

2: What is SGA?

The system global area is a shared memory region allocated by oracle that contains data and control information for one oracle instance.

3: What are the components of SGA?

Database buffers, redo log buffer, shared pool, cursors.

4: What do Database Buffers contain?

Database buffers store the most recently used blocks of database data.

It can also contain modified data that has not yet been permanently written to disk.

5: What do Redo Log Buffers contain?

Redo Log Buffer stores redo entries, a log of changes made to the database.

6: What is Shared Pool?

Shared Pool is a portion of the SGA that contains shared memory constructs such shared SQL areas.

7: What is Shared SQL Area?

A Shared SQL area is required to process every unique SQL statement submitted to a database and contains information such as the parse tree and execution plan for the corresponding statement.

8: What is cursor?

A cursor is a handle(a name or pointer) for the memory associated with a specific statement.

9: What is PGA?

Program global area is a memory buffer that contains data and control information for a server process.

10: What is User Process?

A user process is created and maintained to execute the software code of an application program.

It is a shadow process created automatically to facilitate communication between the

user and the server process.

11: What is server process?

Server process handle requests from connected user process.

A server process is in charge of communicating with the user process and interacting with oracle carry out requests of the associated user process.

12: What are the two types of server configurations?

Dedicated server configuration and multi-threaded server configuration.

13: What is dedicated server configuration?

In a dedicated server configuration a server process handles requests for a single user process.

14: What is a multi-threaded server configuration?

In a multi-threaded server configuration many user processes share a group of server process.

15: What is a parallel server option in oracle?

A configuration for loosely coupled systems where multiple instance share a single physical database is called parallel server.

16: Name the ORACLE Background Process ?

DBWR - Database Writer.

LGWR - Log Writer

CKPT - Check Point

SMON - System Monitor

PMON - Process Monitor

ARCH - Archiver

RECO - Recover

Dnnn - Dispatcher, and

LCKn - Lock

Snnn - Server

17: What Does DBWR do ?

Database writer writes modified blocks from the database buffer cache to the data files.

18: When Does DBWR write to the database ?

DBWR writes when more data needs to be read into the SGA and too few database buffers are free.

The least recently used data is written to the data files first.

DBWR also writes when CheckPoint occurs.

The DBWR writes the dirty buffers present in the database buffer cache to respective files.

(1)When a check point is issued.

(2)Every three seconds.

(3)When a log switch occurs.

(4)When dirty-blocks reaches to thresold value.

(5)When dirty-blocks are becoming LRU blocks.

(6)When database shuts down.

19: What does LGWR do ?

Log Writer (LGWR) writes redo log entries generated in the redo log buffer of the SGA to on-line Redo Log File.

20: When does LGWR write to the database?

LGWR writes redo log entries into an on-line redo log file when transactions commit and the log buffer files are full.

21: What is the function of checkpoint CKPT)?

The Checkpoint (CKPT) process is responsible for signaling DBWR at checkpoints and updating all the data files and control files of the database.

22: What are the functions of SMON ?

System Monitor (SMON) performs instance recovery at instance start-up.

In a multiple instance system (one that uses the Parallel Server), SMON of one instance can also perform instance recovery for other instance that have failed.

SMON also cleans up temporary segments that are no longer in use and recovers dead transactions skipped during crash and instance recovery because of file-read or off-line errors.

These transactions are eventually recovered by SMON when the tablespace or file is brought back on-line.

SMON also coalesces free extents within the database to make free space contiguous and easier to allocate.

23: What are functions of PMON ?

Process Monitor (PMON) performs process recovery when a user process fails.

PMON is responsible for cleaning up the cache and Freeing resources that the process was using.

PMON also checks on dispatcher and server processes and restarts them if they have failed.

24: What is the function of ARCH ?

Archiver (ARCH) copies the on-line redo log files to archival storage when they are full.

ARCH is active only when a database's redo log is used in ARCHIVELOG mode.

25: What is function of RECO ?

RECOver (RECO) is used to resolve distributed transactions that are pending due to a network or system failure in a distributed database.

At timed intervals, the local RECO attempts to connect to remote databases and automatically complete the commit or rollback of the local portion of any pending distributed transactions.

26: What is the function of Dispatcher (Dnnn) ?

Dispatcher (Dnnn) process is responsible for routing requests from connected user processes to available shared server processes and returning the responses back to the appropriate user processes.

27: How many Dispatcher Processes are created ?

Atleast one Dispatcher process is created for every communication protocol in use.

28: What is the function of Lock (LCKn) Process ?

Lock (LCKn) are used for inter-instance locking when the ORACLE Parallel Server option is used.

29: What is the maximum number of Lock Processes used ?

Though a single LCK process is sufficient for most Parallel Server Systems.

Upto Ten Locks (LCK0,....LCK9) are used for inter-instance locking.

Systems Architecture Interview Questions

Next week, I'm interviewing another architect, so I've gone over my list of "things to ask an architect candidate" for another time to see if I'd change anything.  Not much popped out, but I thought I'd share some exercises that I ask architecture candidates to run through. 
Note: I care more about experience than book learning, but I care very much that you know the actual names for things.   In other words, book learning does matter.  If you never got around to reading any of those 'patterns' books, don't bother to show up.  You wouldn't be considered a qualified medical intern if you referred to the Biceps Brachii as the 'upper arm muscle' and I won't consider you an architect if you can't tell that I'm using a Strategy pattern in my case study.
The things that a systems architect must do to survive an interview with me:

• Demonstrate the ability to work on a project team with developers productively
• Describe a time when you created a vision for a systems architecture, communicate it, and others made it come into existence
• Tell me how you know when it is time to review code for compliance and when you are better off getting out of a developer's hair
• Be able to guide and direct the team in the art and science of visual modeling (it's not enough to know... you must also teach).
• Detect the gaps in the early design artifacts of a project and determine what risks are going unmanaged.
• Be able to convince me that you understand, in your bones, the concepts of coupling, abstraction, and encapsulation. 
• Be able to describe at least five GoF patterns in fine detail.  Be able to describe at least five messaging patterns in fine detail.
• Pick a single system quality attribute that should be paramount in a particular situation, and then explain why.

Some exercises to run folks through:
A. James is an IT architect working with a team of 5 developers and 5 testers on a new system.  He has a situation with his development team.  He presented the high-level design for their new system at a dev meeting and the team listened politely.  Then, after reviewing his diagrams, some of the members of the dev team concluded that he has created a great deal of complexity in one area that they think is overkill.  James hears about it from the project manager three days later.  He feels pretty strongly that the complexity is called for.  Evaluate the situation.  What dynamics are at play?  James comes to you for advice: what do you tell him?
B. Let's say that you have 10 systems in an infrastructure.  One of them provides the list of all customers, while another provides the list of all orders.  You want to keep the systems as decoupled as possible but they need to share data in real time.  What mechanisms do you put in place to keep the systems independent, yet fully integrated?  Describe the steps you would follow to replace the system that holds orders.
C. Draw the architecture of one of your most complex systems on the white board.  Now:

• What made you partition the system in this way?
• What data entities are mastered by your system?  Which components are responsible for actually writing the key data about each entity?
• What changes do you predict the business will want in the next 2-5 years in this system?  How will the architecture cope with those changes?
• What system quality attributes did you consider most paramount when designing this system?  What attributes did you sacrifice?

D. The architectural council has invited you to join.  They perform periodic reviews on major projects.  You attend and a project comes before the council that has not been reviewed before.  It is a medium-sized project for your company (your company will perform between three and six projects of this size each year on average).  It consumes data from other systems in real time, creates transactions in other systems, and produces data for reporting.  The following artifacts are provided.  What additional information do you ask for?  What risks are you concerned about? 

• Logical Data Model showing every data entity in the system's database
• Deployment Diagram
• Class-level architectural diagram illustrating the use of design patterns

E. You review the code of a junior developer, and you see a call where the developer is passing a structure.  The structure contains 25 different values as parameters to the method being called.  The structure is used only to pass parameters to this one method call.  You ask why the call needs so many parameters, and it turns out that it is used in about eight different places in the code, and each one has a slightly different need, so the routine will look to see what parameters are passed to decide what to do.  The code for the method is complex, and contains many examples of code like this:
   if (paramx != null)
         do_something_specific(paramx);
What patterns do you discuss with your junior developer?  What options do you consider?  Do you advise the developer to change the code?  Why?
F.  Pick one of the following practice areas and describe the ideas and concerns that led to this area of software development practice, what the practice entails, the benefits that can be achieved, and the negative consequences that may be observed by teams engaged in the practice.  Note: I may pick one for you... be prepared to be asked about any of them. (If you were to ask: "why this particular list?" I'd turn that around and ask you why I picked this list... )

• Service Oriented Architecture
• Test Driven Development
• Aspect Oriented Programming
• Dependency Injection / Inversion of Control
• Software Factories

G. Mary is a fellow architect with a problem and she has come to you for advice.  She is producing an impact analysis on a system we will call "Charlie".  The Charlie system gets an hourly feed of data from another system called "Bravo."  The Bravo system is scheduled to be replaced by an altogether new system called "Romeo" that will organize the data in a completely different way.  Unfortunately, this will radically affect the data feed from Bravo to Charlie.  Not only will it vanish, but any feed from Romeo will be structured and organized in a completely different manner than it was before.  Mary needs to develop a roadmap to allow this change to occur.  What advice do you give her?

Data Analyst Interview Questions

One of the major skills required for the post of a data analyst is problem solving. Hence, there are a number of questions, which test this aspect in a candidate. The problem solving methods and clear process used should be the highlight of the answers to the problem solving questions. Some set of questions are aimed at understanding the knowledge of software solutions. Understanding of business processes and tools are also tested. We will now turn to see some sample interview questions for a data analyst.

• When a problem is encountered, how will you obtain information to find solution to the problem? Give Examples.
• After obtaining the information, how will you filter out the unnecessary information? Give Examples.
• Give an example, where a problem is solved by mapping and tracing informational data within one system and between two systems.
• Which are the domains in which you have worked previously? Which of them was the most challenging domain?
• Till date, which was the most difficult problem you solved? What measures did you take to solve the problem?
• Have you performed statistical analysis of corporate data? What tools did you use for the same?
• In your previous assignments, has there been a time, when you had to analyze and mine information for identifying prototypes?
• How should business information be documented? Do you have any particular method that you use?
• Has there been a time, where you managed data accounts, created data accounts? How did you go about the entire process?
• If you are given a database and you are asked to analyze the same for unusual and interesting trends, what are the steps you will take?
• Tell me in short about IDOC's.
• Define dataset with an example.
• In ABAP, can a subroutine from program A be called from program B?
• Which different tools in SAP can be used for reporting data? Which of the tools have you previously worked on?
• Which steps are a part of data analysis procedure?
• What according to you are the tasks of a data analyst in relation to the database.?
• Can you give some examples of data report formats?

Before you prepare the answers to data analyst interview questions, it is important you study a few data analyst job descriptions. This will prove to be of help, when you are answering the questions. In case your resume does mention particular tools, be prepared to answer questions related to those tools in details. Whenever you give an answer, it is best to supplement it with an example. Having a sample database format ready comes in handy. You may also want to prepare some database schema, which will prove to be useful while answering questions.

Along with the preparation for the database related question, you may also want to be well versed with the domain knowledge. Domain knowledge will help you to answer the questions more convincingly. When you are answering questions, it is best to stick to the point and have factual, yet detailed answer. Do not leave any open ends in your answers. When there are open ends in the answers the interviewer will find more areas to grill you and you may not always be comfortable answering the questions.

Data Analyst Interview Questions and Answers

Generally speaking, data analysts are busy analyzing information and solving problems. The type of information and problems depends on the industry: there are various business industries (software, alternative energy, etc), healthcare industries, entertainment, and so on.
Refer to the following sample Data Analyst job interview questions and answers for you to get ready for the upcoming interview.

Job Interview Questions and Answers for Data Analysts

Question: What is involved in typical data analysis?
Answer: Data analysis involves collection and organization of data, correlation between analyzed data and the rest of the company and market, and the ability to then creatively think of solutions to existing problems, or point out problems and initiate preventive measures.
Question: Do you have any experience with corporate websites?
Answer: Relevant experience might involve creating the rules for various corporate consumer websites, depending on market conditions and company goals. Analysts might also help design website features, so that they align with their analysis of company data and performance predictions.
Question: How do you handle database as a data analyst?
Answer: Data analysts may be responsible for database design and security. They are responsible for upgrading the database on a regular basis so that meets the demands of the market and company needs. And they make sure database runs smoothly in general.
Question: Are you comfortable spending prolonged periods of time seated in front of a desktop computer?
Answer: All data analysts spend much time seating and using computers. The job requires no physical strain except the ability to seat and focus on the job of analyzing and offering creative solutions.
Question: Have you ever developed any programs or products relevant to your profession?
Answer: As a business analyst working in the software and technology industry, you might have experience actually developing information-related products for the company, to be used within the company, products which can be used for data analysis and data presentation.

MS Access Interview Questions

What is the size limitation of an Access database?
 2. how do you add a developer to a trusted publishers list?
3. Would you like to easily automate your MS Access database through menu driven selections?
 4. Explain about managing data tasks?
5. When I assigned a primary key to my table in Microsoft Access, I was expecting my 11112 to be sorted. Am I using the correct procedure?
 6. How do you import data from another access database?
7. What happens if the both source and destination are named the same?
8. What format should my database be saved in to allow different versions of Microsoft Access to open the database?
 9. What is the maximum size of a database that can be opened in Microsoft Access?
 10. In MS Access the long and short date option does not show 4 digit years. how do I achieve the mm/dd/yyyy format?
11. how can I open an MS Access database that has been converted to a current version?
12. Explain about relationships and look up fields?
13. How do you create an append query?
14. State some of the uses append query?
15. Detail about how you can import a sharepoint list?
16. Explain the steps for this message could not delete from the specified tables?
17. Explain about creating a report tool by using the report tool and report wizard?
18. Explain about Microsoft access?
19. Explain about the various features present in MS Access?
20. State some of the uses of MS Access?

Microsoft Access Interview Questions

Explain about creating a report tool by using the report tool and report wizard?

Explain the steps for this message "could not delete from the specified tables"?

Detail about how you can import a sharepoint list?

State some criteria`s and their effects on a ms access database?

State some of the uses append query?

How do you create an append query?

Explain about relationships and look up fields?

What happens if the both source and destination are named the same?

How do you import data from another access database?

Explain about managing data tasks?

How do you add a developer to a trusted publishers list?

State the criteria which a trusted publisher should meet before adding him?

Name at least six file extensions of Microsoft Access?

Explain about the protection features present in MS Access?

State some of the uses of MS Access?

Explain about the various features present in MS Access?

Explain about Microsoft access?

I am using the Switchboard Manager in MS Access. I am getting an error message "There was an error executing this command."

When I assigned a primary key to my table in Microsoft Access, I was expecting my data to be sorted. Am I using the correct procedure?

Would you like to easily automate your MS Access database through menu driven selections?

What is the size limitation of an Access database?

How can I open an MS Access database that has been converted to a current version?

In MS Access, the long and short date option does not show 4 digit years. How do I achieve the mm/dd/yyyy format?

I am designing an Access database form. How can I get my next control in the form to be automatically selected?

What is the maximum size of a database that can be opened in Microsoft Access?

What format should my database be saved in to allow different versions of Microsoft Access to open the database?

Can I use ExecuteReader() in if-else. Because in my code which is in C# ExecuteReader() in if is executing and in else it is not please tell me the solution.

how to insert and retrive images on MSACCESS using java

What is Replication Manager?

How does Microsoft Access database recover free space?