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?

SQL Basic Interview questions

Question: What type of joins have you used?

Answer: Joins knowledge is MUST HAVE. This interview question is quite nice because most people used inner join and (left/right) outer join which is rather mandatory knowledge but those more experienced will also mention cross join and self-join. In SQL Server you can also get full outer join.

Question: How can you combine two tables/views together? For instance one table contains 100 rows and the other one contains 200 rows, have exactly the same fields and you want to show a query with all data (300 rows). This sql interview question can get complicated.

Answer: You use UNION operator. You can drill down this question and ask what is the different between UNION and UNION ALL (the first one removes duplicates (not always desirable)… in other words shows only DISTINCT rows….Union ALL just combines so it is also faster). More tricky question are how to sort the view (you use order by at the last query), how to name fields so they appear in query results/view schema (first query field names are used). How to filter groups when you use union using SQL (you would create separate query or use common table expression (CTE) or use unions in from with ().

Question: What is the difference between where and having clause?

Answer: in SQL Where filters data on lowest row level. Having filters data after group by has been performed so it filters on "groups"

Question: How would apply date range filter?

Answer: This is tricky question. You can use simple condition >= and <= or similar or use between/and but the trick is to know your exact data type. Sometimes date fields contain time and that is where the query can go wrong so it is recommended to use some date related functions to remove the time issue. In SQL Server common function to do that is datediff function. You also have to be aware of different time zones and server time zone.

Question: What type of wildcards have you used? This is usually one of mandatory sql interview question.

Answer: First question is what is a wildcard? Wildcards are special characters that allow matching string without having exact match. In simple word they work like contains or begins with. Wildcard characters are software specific and in SQL Server we have % which represent any groups of characters, _ that represent one character (any) and you also get [] where we can [ab] which means characters with letter a or b in a specific place.

SQL interview questions and answers

1. What are two methods of retrieving SQL?
2. What cursor type do you use to retrieve multiple recordsets?
3. What is the difference between a "where" clause and a "having" clause? - "Where" is a kind of restiriction statement. You use where clause to restrict all the data from DB.Where clause is using before result retrieving. But Having clause is using after retrieving the data.Having clause is a kind of filtering command.
4. What is the basic form of a SQL statement to read data out of a table? The basic form to read data out of table is ‘SELECT * FROM table_name; ‘ An answer: ‘SELECT * FROM table_name WHERE xyz= ‘whatever’;’ cannot be called basic form because of WHERE clause.
   
5. What structure can you implement for the database to speed up table reads? - Follow the rules of DB tuning we have to: 1] properly use indexes ( different types of indexes) 2] properly locate different DB objects across different tablespaces, files and so on.3] create a special space (tablespace) to locate some of the data with special datatype ( for example CLOB, LOB and …)
6. What are the tradeoffs with having indexes? - 1. Faster selects, slower updates. 2. Extra storage space to store indexes. Updates are slower because in addition to updating the table you have to update the index.
7. What is a "join"? - ‘join’ used to connect two or more tables logically with or without common field.
8. What is "normalization"? "Denormalization"? Why do you sometimes want to denormalize? - Normalizing data means eliminating redundant information from a table and organizing the data so that future changes to the table are easier. Denormalization means allowing redundancy in a table. The main benefit of denormalization is improved performance with simplified data retrieval and manipulation. This is done by reduction in the number of joins needed for data processing.
9. What is a "constraint"? - A constraint allows you to apply simple referential integrity checks to a table. There are four primary types of constraints that are currently supported by SQL Server: PRIMARY/UNIQUE - enforces uniqueness of a particular table column. DEFAULT - specifies a default value for a column in case an insert operation does not provide one. FOREIGN KEY - validates that every value in a column exists in a column of another table. CHECK - checks that every value stored in a column is in some specified list. Each type of constraint performs a specific type of action. Default is not a constraint. NOT NULL is one more constraint which does not allow values in the specific column to be null. And also it the only constraint which is not a table level constraint.
10. What types of index data structures can you have? - An index helps to faster search values in tables. The three most commonly used index-types are: - B-Tree: builds a tree of possible values with a list of row IDs that have the leaf value. Needs a lot of space and is the default index type for most databases. - Bitmap: string of bits for each possible value of the column. Each bit string has one bit for each row. Needs only few space and is very fast.(however, domain of value cannot be large, e.g. SEX(m,f); degree(BS,MS,PHD) - Hash: A hashing algorithm is used to assign a set of characters to represent a text string such as a composite of keys or partial keys, and compresses the underlying data. Takes longer to build and is supported by relatively few databases.
11. What is a "primary key"? - A PRIMARY INDEX or PRIMARY KEY is something which comes mainly from
    database theory. From its behavior is almost the same as an UNIQUE INDEX, i.e. there may only be one of each value in this column. If you call such an INDEX PRIMARY instead of UNIQUE, you say something about
    your table design, which I am not able to explain in few words. Primary Key is a type of a constraint enforcing uniqueness and data integrity for each row of a table. All columns participating in a primary key constraint must possess the NOT NULL property.
12. What is a "functional dependency"? How does it relate to database table design? - Functional dependency relates to how one object depends upon the other in the database. for example, procedure/function sp2 may be called by procedure sp1. Then we say that sp1 has functional dependency on sp2.
13. What is a "trigger"? - Triggers are stored procedures created in order to enforce integrity rules in a database. A trigger is executed every time a data-modification operation occurs (i.e., insert, update or delete). Triggers are executed automatically on occurance of one of the data-modification operations. A trigger is a database object directly associated with a particular table. It fires whenever a specific statement/type of statement is issued against that table. The types of statements are insert,update,delete and query statements. Basically, trigger is a set of SQL statements A trigger is a solution to the restrictions of a constraint. For instance: 1.A database column cannot carry PSEUDO columns as criteria where a trigger can. 2. A database constraint cannot refer old and new values for a row where a trigger can.
14. Why can a "group by" or "order by" clause be expensive to process? - Processing of "group by" or "order by" clause often requires creation of Temporary tables to process the results of the query. Which depending of the result set can be very expensive.
15. What is "index covering" of a query? - Index covering means that "Data can be found only using indexes, without touching the tables" 

Database Analyst Interview Question

1. Do you have any experience as Database Analyst?
2. Are you aware about the responsibilities of Database Analyst?
3. How do you deal if you find any mistake in Database project?
4. How would you justify yourself if any error is found after your analytic process?
5. Which techniques will you use to analyse the data? How much time you will take to analyse single project?
6. How would you handle a client if he/ she ask for unreasonable change in database?
7. Explain the different processes and tools used in database management to analyse a database project?
8. How do you correlate sub quarries? Explain the difference between sub queries and join?
9. What is view? How is it useful to view a project? Explain integrity and import?
10. Explain Normal form? Why is it so important in database system?
11. How would you suggest a database to a customer? Which requirements do you consider to suggest a database project? 

Database analyst interview questions

Database analyst interview questions
You can use free interview questions and answers of Database analyst below to ask candidates or your job interview. Please note that you also use interview questions and answers, tips to answer Database analyst interview questions, types of job interview questions, Database analyst interview thank letters …at sidebar.

I. Interview questions samples of Database analyst

You can use free interview questions samples of Database analyst by links as below:
1. Tell me about yourself?
2. Why did you leave your last job?
3. What are your career goals for Database analyst?
4. What is your greatest weakness?
5. What kind of salary are you looking for Database analyst?
6. Why do you think you would do well Database analyst?
7. What motivates you to do your best on the Database analyst?
8. How would you know you were successful on this Database analyst?
9. Do you think you are overqualified for Database analyst?
10. Explain about normal forms?
11. Explain about primary key?
12. What are the three basic rules which are to be followed for relational model of database?
13. Explain about XML databases?
14. Explain about what a database is?
15. Explain about relational database management systems?
16. What have you learned from your past jobs that related to Database analyst?
17. Tell me about your last position and what you did?
18. Where would you like to be in 3 years? 5 years?
19. What made you choose to apply to Database analyst?
20. What are key tasks for Database analyst?
21. How to do each Database analyst position task/function?
22. How to control each task/function of Database analyst?
23. What are top 3 skills for Database analyst?
24. How to measure job performance of your position: Database analyst?
25. What tertiary qualifications have you attained that related to your Database analyst position?
26. What is the most recent skill you have learned that related to your Database analyst position?
27. What do you know about this company?
28. What do you know about the position of your Database analyst position?
29. Describe two or three major trends in your field?
30. Did you choose this profession/field?
31. What tertiary qualifications have you attained that related to Database analyst?
32. What is the most recent skill you have learned that related to Database analyst?
Other useful interview questions
• 40 Database interview questions and answers

II. Tips to answer Database analyst interview questions

1. Identify key duties, tasks, job specs, job standards of Database analyst positions then ask question: How to do, how to become, how to measure performance, how to monitor, how to control…
2. Always ask by your-self: what are things related to your position field in this interview questions before answering.
3. Research the company and its business carefully: company history, organization structure, your division structure, product/service list, competitive advantages and disadvantages…
4. Always ask by yourself: What are proofs that are needed for this interview questions/this job?

Database interview questions for beginner

1. Explain the difference between a database administrator and a data administrator.

Database Administrator :- A person (or group of people) responsible for the maintenance and performance of a database and responsible for the planning, implementation, configuration, and administration of relational database management systems.

Data Administrator :- The individual or organization responsible for the specification, acquisition, and maintenance of data management software and the design, validation, and security of files or databases. The DA is in charge of the data dictionary and data model.

2. Explain the difference between an explicit and an implicit lock.

Explicit Lock :- Lock is explicitly requested for a record or table.
Implicit Lock :- Lock is implied but is not acquired

3. What is lock granularity?

There are many locks available for the database system to have like
Intent Shared, Shared, Intent exclusive, exclusive and Shared Intent exclusive.
Locking granularity refers to the size and hence the number of locks used to ensure the consistency of a database during multiple concurrent updates.

4. In general, how should the boundaries of a transaction be defined?

A transaction ensures that one or more operations execute as an atomic unit of work. If one of the operations within a transaction fails, then all of them are rolled-back so that the application is returned to its prior state. The boundaries that define a group of operations done within a single transaction.

5. Explain the meaning of the expression ACID transaction.

ACID means Atomic, Consistency, Isolation, Durability, so when any transaction happen it should be Atomic that is it should either be complete or fully incomplete. There should not be anything like Semi complete. The Database State should remain consistent after the completion of the transaction. If there are more than one Transaction then the transaction should be scheduled in such a fashion that they remain in Isolation of one another.Durability means that Once a transaction commits, its effects will persist even if there are system failures.

6. Explain the necessity of defining processing rights and responsibilities. How are such responsibilities enforced?
One of the reason to define rights is the security in the database system. If any user is allowed to define the data or alter the data then the database would just be of no use and so processing rights and responsibilities are clearly defined in any database system. The resposibilities are enforced using the table space provided by the database system.

7. Describe the advantages and disadvantages of DBMS-provided and application-provided security.
DBMS provided security :- Any database system requires you to login and then process the data depending on the rights given by the DBA to the user who has logged in. The advatage of such a system is securing the data and providing the user and the DBA the secured platform. Any user who logs in cannot do whatever he want but his role can be defined very easily. There is no major disadvantage about the DBMS provided security apart from overhead of storing the rights and priviledges about the users.

Application-provided security :- It is much similar to the DBMS provided security but the only difference is that its the duty of the programmer creating the application to provide all the seurities so that the data is not mishandled.

8. Explain how a database could be recovered via reprocessing. Why is this generally not feasible?
If we reprocess the transaction then the database can be made to come to a state where the database is consistent and so reprocessing the log can recover the database. Reprocessing is not very feasible for a very simple reason that its very costly from time point of view and requires lots of rework and many transaction are even rollback giving more and more rework.

9. Define rollback and roll forward.
Rollback :- Undoing the changes made by a transaction before it commits or to cancel any changes to a database made during the current transaction
RollForward :- Re-doing the changes made by a transaction after it commits or to overwrite the chnaged calue again to ensure consistency

10. Why is it important to write to the log before changing the database values?
The most important objective to write the log before the database is changed is if there is any need to rollback or rollforward any transaction then if the log are not present then the rollback rollforward cannot be done accurately. 

Database Interview Questions

Q.1: What is the difference between an implicit lock and explicit lock?

An implicit lock is implied but not acquired while and explicit lock is requested for a record or table, explicitly.

Q.2: What is the difference between database administrator and a data administrator?

A database administrator is a person or a group of people responsible for the accuracy, maintenance and performance of the management systems. And a data administrator is and individual or an organization responsible for specification, acquisition and maintenance of date management. It does everything from the designing, validation and security of the files and databases.

Q.3: What are the two components of an enterprise class database system between which the DBMS works?

Database application and the database.

Q.4: What is rollback and roll forward?

Rollback means undoing the changes made by a transaction. It does before anything commits to cancel and changes to a database. Roll forward on the other hand means, re doing all the changes made by a transaction after it commits or overwrites the changed value to ensure consistency.

Q.5: What is one of the components of database out of these? User data, metadata, Reports, Indexes?
It is Reports. They are used to generally summarize and present the data in the form of tables. It generally answers a particular question.
Q.6: What do you think about an enterprise resource planning application? What is it an example of?
It is an example of Multiuser database application.
Q.7: Which of the following is not considered as a basic element of an enterprise class database system?
Options – User, Databse apps, DBMS, COBOL Programs.
The answer is COBOL programs.
Q.8: How can one recover the database via reprocessing?
Reprocessing is not very feasible. If we process the transaction, it is possible that the database is safe and accessible and so reprocessing can be done. It is not preferred by many for a simple reason that it is a costly process and it requires a lot of re-work, again and again. With many transactions calling for a rollback, it becomes tedious and involves loads of re-work.
Q.9: What is normalization?
It is the way of organizing data in a database by removing unneeded and not consistent data. The database normalization has some rules to be followed while creating databases-

• First Normal form is to – remove duplicate column first and identify each set of resembling and related data with a primary key.
• Second Normal from is to – create a relationship between master and master detail tablets by using a foreign key.
• Third Normal form is to – remove the fields that are not dependent on the primary key. They are independent.

Q.10: What is de-normalization?
De-normalization is a process of optimizing the performance of a database. It is a process of combining and organizing data in a single table. It is then used to access specific data needs according to the users.
Q.11: What is the meaning of flat file database?
It is a type of database where in there are no programs or languages which can be accessed by a user. There are no cross file capabilities but it is still user friendly and provides pretty good user friendly management.

Database interview questions

What is database?

A database is a logically coherent collection of data with some inherent meaning, representing some aspect of real world and which is designed, built and populated with data for a specific purpose.

2. What is DBMS?

It is a collection of programs that enables user to create and maintain a database. In other words it is general-purpose software that provides the users with the processes of defining, constructing and manipulating the database for various applications.

3. What is a Database system?

The database and DBMS software together is called as Database system.

4. What are the advantages of DBMS?

1. Redundancy is controlled.
2. Unauthorised access is restricted.
3. Providing multiple user interfaces.
4. Enforcing integrity constraints.
5. Providing backup and recovery.

5. What are the disadvantage in File Processing System?

1. Data redundancy and inconsistency.
2. Difficult in accessing data.
3. Data isolation.
4. Data integrity.
5. Concurrent access is not possible.
6. Security Problems.

6. Describe the three levels of data abstraction?

The are three levels of abstraction:

1. Physical level: The lowest level of abstraction describes how data are stored.
2. Logical level: The next higher level of abstraction, describes what data are stored in database and what relationship among those data.
3. View level: The highest level of abstraction describes only part of entire database. 

Oracle DBA Interview questions and Answers

Are you a Database Administrator? Need to update DBA technical knowledge or need to prepare for a job interview? Check out this collection of Oracle DBA Interview questions and Answers...

Oracle DBA Interview questions and Answers

• What is different between TRUNCATE and DELETE?
• What is the maximum buffer size that can be specified using the DBMS_OUTPUT.ENABLE function?
• Can you use a commit statement within a database trigger?
• What is an UTL_FILE? What are different procedures and functions associated with it?
• What are between database triggers and form triggers?
• What is OCI. What are its uses?
• What are ORACLE PRECOMPILERS?
• What is syntax for dropping a procedure and a function? Are these operations possible?
• How to check if application 11i System is Autoconfig enabled ?
• How to check if Oracle application 11i System is Rapid Clone enabled ?
• Whats is difference between two env files in <CONTEXT>.env and APPS<CONTEXT>.env under $APPL_TOP ?
• What is main concurrent Manager types.
• What is US directory in $AD_TOP or under various product TOP's .
• Where is Concurrent Manager log file location.
• Where would I find .rf9 file, and what it dose ?
• Where is appsweb.cfg or appsweb_$CONTEXT.cfg stored and why its used ?
• What is Multi Node System ?
• Can a function take OUT parameters. If not why?
• Can the default values be assigned to actual parameters?
• What is difference between a formal and an actual parameter?
• What are different modes of parameters used in functions and procedures?
• What is difference between procedure and function.
• Can cursor variables be stored in PL/SQL tables. If answer is yes, explain how? If not why?
• Can you clone from multi node system to single node system and vice versa ?
• Does rapid clone takes care of Updating Global oraInventory or you have to register manually in Global OraInventory after clone ?
• What is .dbc file , where its stored , whats use of .dbc file ?
• What are things you do to reduce patch timing ?
• How you put Applications 11i in Maintenance mode ?
• Can you apply patch without putting Applications 11i in Maintenance mode ?
• What are various options available with adpatch ?
• ADIDENT UTILITY is used for what ?
• How do you pass cursor variables in PL/SQL?
• How do you open and close a cursor variable. Why it is required?
• What should be the return type for a cursor variable. Can we use a scalar data type as return type?
• What is use of a cursor variable? How it is defined?
• What WHERE CURRENT OF clause does in a cursor?
• What is different between NO DATA FOUND and %NOTFOUND
• What is a cursor for loop?
• What is iAS Patch ?
• If we run autoconfig which files will get effected ?
• What is difference between .xml file and AutoConfig ?
• What is .lgi files ?
• How will you skip worker during patch ?
• Which two tables created at start of application Patch and drops at end of Patch ?
• How to compile an Oracle Reports file ?
• What is difference between AD_BUGS and AD_APPLID_PATCHES ?
• What is ADSPLICE UTILITY ?
• How can you licence a product after installation ?
• What is MRC ? What you do as application DBA for MRC ?
• What is access_log in apache , what entries are recored in access_log ? Where is default location of this file ?
• Where is Jserv configuration files stored ?
• Where is applications start/stop scripts stored ?
• What are main configuration files in Web Server (Apache) ?
• Can C driver in application patch create Invalid Object in database ?
• What is dev60cgi and f60cgi ?
• Why does a worker fails in Oracle Apps Patch and few scenarios in which it failed for you ?
• What is difference between mod_osso and mod_ose in Oracle HTTP Server ?
• What is difference between COMPILE_ALL=SPECIAL and COMPILE=ALL while compiling Forms ?
• What is GSM in Oracle application E-Business Suite ?
• What is FNDSM ?
• What are cursor attributes?
• What is difference between an implicit and an explicit cursor.
• What is a cursor?
• What is the purpose of a cluster?
• How do you find the number of rows in a Table ?
• What is a pseudo column. Give some examples?
• Whats is location of access_log file ?
• What is your Oracle application 11i Webserver Version and how to find it ?
• What is Location of Jserv configuration files ?
• What is plssql/database cache ?
• Where is DATABASE/PLSSQL cache stored ?
• What is *.DBC file and whats is location of DBC file ?
• What is content of DBC file and why its important ?
• What are few profile options which you update after cloning ?
• How to retrieve SYSADMIN password ?
• What is TWO_TASK in Oracle Database ?
• What is GWYUID ?
• Where GWYUID defined and what is its used in Oracle Applications ?
• If APPS_MRC schema is not used in 11.5.10 and higher then How MRC is working ?
• When you apply C driver patch does it require database to be Up and Why ?
• How you will avoid your query from using indexes?
• What is a OUTER JOIN?
• Which is more faster - IN or EXISTS?
• When do you use WHERE clause and when do you use HAVING clause?
• There is a % sign in one field of a column. What will be the query to find it?
• Where will you find forms configuration details apart from xml file ?
• What is forms server executable Name ?
• What are different modes of forms in which you can start Forms Server and which one is default ?
• How you will start Discoverer in Oracle Applications 11i ?
• How many ORACLE HOME are Oracle Applications and whats significance of each ?
• Where is HTML Cache stored in Oracle Applications Server ?
• Where is plssql cache stored in Oracle Applications ?
• What happens if you don't give cache size while defining Concurrent Manager ?
• There are lot of DBC file under $FND_SECURE, How its determined that which dbc file to use from $FND_SECURE ?
• What is RRA/FNDFS ?
• What is PCP is Oracle Applications 11i ?
• Why I need two Concurrent Processing Nodes or in what scenarios PCP is Used ?
• Output and Logfiles for requests executed on source Instance not working on cloned Instance
• How to confirm if Report Server is Up and Running ?
• What is difference between ICM, Standard Managers and CRM in Concurrent Manager ?
• What is difference between SUBSTR and INSTR?
• Which data type is used for storing graphics and images?
• What is difference between SQL and SQL*PLUS?
• What is difference between UNIQUE and PRIMARY KEY constraints?
• What is difference between Rename and Alias?
• What are various joins used while writing SUBQUERIES?
• What is use of Applications listener ?
• How to start Applications listener ?
• How to confirm if Apps Listener is Up and Running ?
• What is Web Listener ?
• How will you find Invalid Objects in database ?
• How to compile Invalid Objects in database ?
• How to compile JSP in Oracle Applications ?
• What is difference between ADPATCH and OPATCH ?
• Can you use both ADPATCH and OPATCH in application ?

Database admin interview questions

1. What is a major difference between SQL Server 6.5 and 7.0 platform wise? SQL Server 6.5 runs only on Windows NT Server. SQL Server 7.0 runs on Windows NT Server, workstation and Windows 95/98.
   
   
2. Is SQL Server implemented as a service or an application? It is implemented as a service on Windows NT server and workstation and as an application on Windows 95/98.
3. What is the difference in Login Security Modes between v6.5 and 7.0? 7.0 doesn’t have Standard Mode, only Windows NT Integrated mode and Mixed mode that consists of both Windows NT Integrated and SQL Server authentication modes.
4. What is a traditional Network Library for SQL Servers? Named Pipes.
5. What is a default TCP/IP socket assigned for SQL Server? 1433
6. If you encounter this kind of an error message, what you need to look into to solve this problem?
   

   [Microsoft][ODBC SQL Server Driver][Named Pipes]Specified SQL Server not found.

   1. Check if MS SQL Server service is running on the computer you are trying to log into
   2. Check on Client Configuration utility. Client and Server have to in sync.
7. What is new philosophy for database devises for SQL Server 7.0? There are no devises anymore in SQL Server 7.0. It is file system now.
8. When you create a database how is it stored? It is stored in two separate files: one file contains the data, system tables, other database objects, the other file stores the transaction log.
9. Let’s assume you have data that resides on SQL Server 6.5. You have to move it SQL Server 7.0. How are you going to do it? You have to use transfer command.
10. Do you know how to configure DB2 side of the application? Set up an application ID, create RACF group with tables attached to this group, attach the ID to this RACF group.
11. What kind of LAN types do you know? Ethernet networks and token ring networks.
12. What is the difference between them? With Ethernet, any devices on the network can send data in a packet to any location on the network at any time. With Token Ring, data is transmitted in ‘tokens’ from computer to computer in a ring or star configuration. Token ring speed is 4/16 Mbit/sec , Ethernet - 10/100 Mbit/sec.
13. What protocol both networks use? TCP/IP. Transmission Control Protocol, Internet Protocol.
14. How many bits IP Address consist of?An IP Address is a 32-bit number.
15. How many layers of TCP/IP protocol combined of? Five. (Application, Transport, Internet, Data link, Physical).
16. How do you define testing of network layers? Reviewing with your developers to identify the layers of the Network layered architecture, your Web client and Web server application interact with. Determine the hardware and software configuration dependencies for the application under test.
17. How do you test proper TCP/IP configuration Windows machine? Windows NT: IPCONFIG/ALL, Windows 95: WINIPCFG, Ping or ping ip.add.re.ss  

Oracle Interview Questions

1. A tablespace has a table with 30 extents in it. Is this bad? Why or why not.
Multiple extents in and of themselves aren?t bad. However if you also have chained rows this can hurt performance.

2. How do you set up tablespaces during an Oracle installation?
You should always attempt to use the Oracle Flexible Architecture standard or another partitioning scheme to ensure proper separation of SYSTEM, ROLLBACK, REDO LOG, DATA, TEMPORARY and INDEX segments.

3. You see multiple fragments in the SYSTEM tablespace, what should you check first?
Ensure that users don?t have the SYSTEM tablespace as their TEMPORARY or DEFAULT tablespace assignment by checking the DBA_USERS view.

4. What are some indications that you need to increase the SHARED_POOL_SIZE parameter?
Poor data dictionary or library cache hit ratios, getting error ORA-04031. Another indication is steadily decreasing performance with all other tuning parameters the same.

5. What is the general guideline for sizing db_block_size and db_multi_block_read for an application that does many full table scans?
Oracle almost always reads in 64k chunks. The two should have a product equal to 64 or a multiple of 64.

6. What is the fastest query method for a table
Fetch by rowid

7. Explain the use of TKPROF? What initialization parameter should be turned on to get full TKPROF output?
The tkprof tool is a tuning tool used to determine cpu and execution times for SQL statements. You use it by first setting timed_statistics to true in the initialization file and then turning on tracing for either the entire database via the sql_trace parameter or for the session using the ALTER SESSION command. Once the trace file is generated you run the tkprof tool against the trace file and then look at the output from the tkprof tool. This can also be used to generate explain plan output.

8. When looking at v$sysstat you see that sorts (disk) is high. Is this bad or good? If bad -How do you correct it?
If you get excessive disk sorts this is bad. This indicates you need to tune the sort area parameters in the initialization files. The major sort are parameter is the SORT_AREA_SIZe parameter.

9. When should you increase copy latches? What parameters control copy latches
When you get excessive contention for the copy latches as shown by the "redo copy" latch hit ratio. You can increase copy latches via the initialization parameter LOG_SIMULTANEOUS_COPIES to twice the number of CPUs on your system.

10. Where can you get a list of all initialization parameters for your instance? How about an indication if they are default settings or have been changed
You can look in the init.ora file for an indication of manually set parameters. For all parameters, their value and whether or not the current value is the default value, look in the v$parameter view.

11. Describe hit ratio as it pertains to the database buffers. What is the difference between instantaneous and cumulative hit ratio and which should be used for tuning
The hit ratio is a measure of how many times the database was able to read a value from the buffers verses how many times it had to re-read a data value from the disks. A value greater than 80-90% is good, less could indicate problems. If you simply take the ratio of existing parameters this will be a cumulative value since the database started. If you do a comparison between pairs of readings based on some arbitrary time span, this is the instantaneous ratio for that time span. Generally speaking an instantaneous reading gives more valuable data since it will tell you what your instance is doing for the time it was generated over.

12. Discuss row chaining, how does it happen? How can you reduce it? How do you correct it
Row chaining occurs when a VARCHAR2 value is updated and the length of the new value is longer than the old value and won?t fit in the remaining block space. This results in the row chaining to another block. It can be reduced by setting the storage parameters on the table to appropriate values. It can be corrected by export and import of the effected table.

Database administrator interview questions

Although the labor market is not as tight as it was a couple of years ago, recruiting top Oracle talent is still a challenge—especially finding the right blend of technical skill and people skills. Besides thoroughly checking technical references, here are four questions that you should ask a DBA job candidate.

What is the most challenging aspect of your job?
This question will give you clues about the mindset of the DBA. When asked what is challenging about the DBA job, the DBA’s answer will reveal a great deal about his or her personality and ability to work well with other people. The DBA who talks about the technical challenges of the job and difficulties administering and maintaining the database may not be ideally suited for a position where contact for the development staff is critical. On the other hand, the DBA who speaks openly of the challenges associated with getting the developers fast answers and accurate information might be a better consideration.

How do you perceive the relationship between the DBA and the development staff?
The answer to this question is critical when evaluating a DBA candidate. Many DBAs, by virtue of their high pay and product-specific knowledge, tend to think of developers as underlings; in some cases, DBAs view developers with outright contempt, believing their queries to be naive. On the other hand, DBAs with the proper attitude will respond to this question by talking about the developers as clients to whom they provide data services essential to the application. In some shops, the DBAs may be responsible for code, reviewing SQL queries, or DML statements written by developers; so, a good relationship is vital.

Do you treat your test and production systems differently? If so, why?
The answer to this question will often provide insight into the priorities of the candidate. In many shops, the DBA doesn’t perceive the test databases as being as important as the production database. The appropriate DBA candidate will note that the data integrity of the test databases is crucial to the development staff because it is their “production” environment in many cases.

What experience have you had analyzing new systems?
The answer to this question will reveal a great deal about the breadth of the DBA’s background. Although technical proficiency is an absolute requirement for the DBA, you must remember that the DBA needs to have some tangential knowledge about the functional areas within the business that they are supporting: for example, networking; Java syntax; or business-side concerns, like finance or accounting. DBAs with a strong business background will often be very useful to the initial design and implementation of new business functionality within your organization.

Balance, grasshopper
A balance between technical aptitude and effective communication skills is essential for any top-notch IT professional, and a DBA is no exception. Check technical references and drill down on their certifications and work experience first. After gauging those skills, the open-ended questions provided in this article should give you a good idea if the candidate is a nice cultural fit for your team.

Junior DBA Interview Questions

I noticed that Brent Ozar wrote a post on how to get a Junior DBA job. His post reminded me about some earlier posts I had done regarding how to do a job search as well as the interview itself. And as great as his post was, I think it was missing one particular detail on the types of interview questions you may want to ask a potential Junior DBA. I mean, if you have done all that work to get yourself an interview, you should know what types of questions you may be asked, right? And if you are doing the interview you should have an idea of what to ask someone with little to no practical DBA experience.

Before we go any further let me make one thing clear. Yes, this is exactly like dating. You should know that by now. But since I have already explained that to you once, I won’t be doing that again. Instead, we will take a different approach, and give you the top ten questions you should be asking potential new Junior DBA’s.

Oh, who am I kidding? Of course I will continue the dating theme. So, here are my top questions to ask during an interview. Are these the best interview questions? Probably not, you really need to tailor your set of questions for your own shop, but these can hopefully set you on the right path. If you keep in mind that you are looking for someone that you can work closely with, and mentor, then the conversation you have should flow naturally during the interview. So, here are the best interview questions, in no particular order.

What is a database?

Let’s start with the basics. This would be like asking your date “so, what do you do for work?” While it may seem silly to ask this question keep one thing in mind: there are no silly interview questions. No matter how ridiculous a question may seem to both the person asking it and the person responding, they will always serve a purpose. In this case you may find the candidate talking about MS Access, or Filemaker, or whatever they have been exposed to previously. Let their response be an indication of their fundamental knowledge of some RDBMS system. See if they can explain the difference between a data and a log file, for example.

How do people connect to a database?

This is about the same as asking them “where do you usually go to meet up with others?” What you want here is to gauge if the candidate knows the basic difference between Windows Authentication and SQL logins. Perhaps they do, in which case you might want to see if they understand connection strings, or how different apps might try to connect to a database, or even some basics of ODBC connectivity. You are not looking for a expert, but you do want someone to understand that different people and applications have different connectivity needs. or, at the very least, someone you can train to understand the differences.

Who is the most important user of a database?

This can be considered identical to the line “seen any good movies lately?” What you want here is to get an understanding if they candidate knows who the stars are. And who are the stars? Well, everyone. That’s right. Every connection to a database server is as important as any other. What’s that? You say it is only a development server, so it is not that important? Well, a development server is considered a production server to a developer. In other words, every person and every connection is important, no matter if they sit in the corner office or not. If the candidate lists out only a handful of people or groups as important and fails to understand that everyone is important, consider that a red flag.

Are you active in any social networks? Do you blog?

It should be clear that this is the same as asking them “are you seeing anyone?” What you want to find out here is if the candidate is sexually active in the community in some way. Why? Well, it’s simple. You need to know if they are amenable to working with others, either getting help or giving help. As a DBA you will often need to work with a wide range of people, sometimes they will need your help, sometimes you will need theirs. If they are not used to interacting with others then you may have an issue should they join your team and continue to work in a silo. So, unlike a real date, here you are looking for someone that admits to spending time with others.

DBA Interview Questions - Part 1

1. What is Log Switch? - The point at which ORACLE ends writing to one online redo log file and begins writing to another is called a log switch.
2. What is On-line Redo Log? - The On-line Redo Log is a set of tow or more on-line redo files that record all committed changes made to the database. Whenever a transaction is committed, the corresponding redo entries temporarily stores in redo log buffers of the SGA are written to an on-line redo log file by the background process LGWR. The on-line redo log files are used in cyclical fashion.
3. Which parameter specified in the DEFAULT STORAGE clause of CREATE TABLESPACE cannot be altered after creating the tablespace? - All the default storage parameters defined for the tablespace can be changed using the ALTER TABLESPACE command. When objects are created their INITIAL and MINEXTENS values cannot be changed.
4. What are the steps involved in Database Startup? - Start an instance, Mount the Database and Open the Database.
5. What are the steps involved in Instance Recovery? - Rolling forward to recover data that has not been recorded in data files, yet has been recorded in the on-line redo log, including the contents of rollback segments. Rolling back transactions that have been explicitly rolled back or have not been committed as indicated by the rollback segments regenerated in step a. Releasing any resources (locks) held by transactions in process at the time of the failure. Resolving any pending distributed transactions undergoing a two-phase commit at the time of the instance failure.
6. Can Full Backup be performed when the database is open? - No.
   
7. What are the different modes of mounting a Database with the Parallel Server? - Exclusive Mode If the first instance that mounts a database does so in exclusive mode, only that Instance can mount the database. Parallel Mode If the first instance that mounts a database is started in parallel mode, other instances that are started in parallel mode can also mount the database.
8. What are the advantages of operating a database in ARCHIVELOG mode over operating it in NO ARCHIVELOG mode? - Complete database recovery from disk failure is possible only in ARCHIVELOG mode. Online database backup is possible only in ARCHIVELOG mode.
9. What are the steps involved in Database Shutdown? - Close the Database, Dismount the Database and Shutdown the Instance.
10. What is Archived Redo Log? - Archived Redo Log consists of Redo Log files that have archived before being reused.
11. What is Restricted Mode of Instance Startup? - An instance can be started in (or later altered to be in) restricted mode so that when the database is open connections are limited only to those whose user accounts have been granted the RESTRICTED SESSION system privilege.
12. What is Partial Backup? - A Partial Backup is any operating system backup short of a full backup, taken while the database is open or shut down.
13. What is Mirrored on-line Redo Log? - A mirrored on-line redo log consists of copies of on-line redo log files physically located on separate disks, changes made to one member of the group are made to all members.
14. What is Full Backup? - A full backup is an operating system backup of all data files, on- line redo log files and control file that constitute ORACLE database and the parameter.
15. Can a View based on another View? - Yes.
16. Can a Tablespace hold objects from different Schemes? - Yes.
17. Can objects of the same Schema reside in different tablespaces? - Yes.
18. What is the use of Control File? - When an instance of an ORACLE database is started, its control file is used to identify the database and redo log files that must be opened for database operation to proceed. It is also used in database recovery.
19. Do View contain Data? - Views do not contain or store data.
20. What are the Referential actions supported by FOREIGN KEY integrity constraint? - UPDATE and DELETE Restrict - A referential integrity rule that disallows the update or deletion of referenced data. DELETE Cascade - When a referenced row is deleted all associated dependent rows are deleted.
21. What are the type of Synonyms? - There are two types of Synonyms Private and Public
22. What is a Redo Log? - The set of Redo Log files YSDATE,UID,USER or USERENV SQL functions, or the pseudo columns LEVEL or ROWNUM.
23. What is an Index Segment? - Each Index has an Index segment that stores all of its data.
24. Explain the relationship among Database, Tablespace and Data file.? - Each databases logically divided into one or more tablespaces one or more data files are explicitly created for each tablespace
25. What are the different type of Segments? - Data Segment, Index Segment, Rollback Segment and Temporary Segment.
26. What are Clusters? - Clusters are groups of one or more tables physically stores together to share common columns and are often used together.
27. What is an Integrity Constrains? - An integrity constraint is a declarative way to define a business rule for a column of a table.
28. What is an Index? - An Index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.
29. What is an Extent? - An Extent is a specific number of contiguous data blocks, obtained in a single allocation, and used to store a specific type of information.
30. What is a View? - A view is a virtual table. Every view has a Query attached to it. (The Query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.)
31. What is Table? - A table is the basic unit of data storage in an ORACLE database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns.
32. What is a Synonym? - A synonym is an alias for a table, view, sequence or program unit.
33. What is a Sequence? - A sequence generates a serial list of unique numbers for numerical columns of a database’s tables.
34. What is a Segment? - A segment is a set of extents allocated for a certain logical structure.
35. What is schema? - A schema is collection of database objects of a User.
36. Describe Referential Integrity? - A rule defined on a column (or set of columns) in one table that allows the insert or update of a row only if the value for the column or set of columns (the dependent value) matches a value in a column of a related table (the referenced value). It also specifies the type of data manipulation allowed on referenced data and the action to be performed on dependent data as a result of any action on referenced data.
37. What is Hash Cluster? - A row is stored in a hash cluster based on the result of applying a hash function to the row’s cluster key value. All rows with the same hash key value are stores together on disk.
38. What is a Private Synonyms? - A Private Synonyms can be accessed only by the owner.
39. What is Database Link? - A database link is a named object that describes a “path” from one database to another.
40. What is a Tablespace? - A database is divided into Logical Storage Unit called tablespaces. A tablespace is used to grouped related logical structures together
41. What is Rollback Segment? - A Database contains one or more Rollback Segments to temporarily store “undo” information.
42. What are the Characteristics of Data Files? - A data file can be associated with only one database. Once created a data file can’t change size. One or more data files form a logical unit of database storage called a tablespace.
43. How to define Data Block size? - A data block size is specified for each ORACLE database when the database is created. A database users and allocated free database space in ORACLE datablocks. Block size is specified in INIT.ORA file and can’t be changed latter.
44. What does a Control file Contain? - A Control file records the physical structure of the database. It contains the following information. Database Name Names and locations of a database’s files and redolog files. Time stamp of database creation.
45. What is difference between UNIQUE constraint and PRIMARY KEY constraint? - A column defined as UNIQUE can contain Nulls while a column defined as PRIMARY KEY can’t contain Nulls. 47.What is Index Cluster? - A Cluster with an index on the Cluster Key 48.When does a Transaction end? - When it is committed or Rollbacked.
46. What is the effect of setting the value “ALL_ROWS” for OPTIMIZER_GOAL parameter of the ALTER SESSION command? - What are the factors that affect OPTIMIZER in choosing an Optimization approach? - Answer The OPTIMIZER_MODE initialization parameter Statistics in the Data Dictionary the OPTIMIZER_GOAL parameter of the ALTER SESSION command hints in the statement.
47. What is the effect of setting the value “CHOOSE” for OPTIMIZER_GOAL, parameter of the ALTER SESSION Command? - The Optimizer chooses Cost_based approach and optimizes with the goal of best throughput if statistics for atleast one of the tables accessed by the SQL statement exist in the data dictionary. Otherwise the OPTIMIZER chooses RULE_based approach.
48. What is the function of Optimizer? - The goal of the optimizer is to choose the most efficient way to execute a SQL statement.
49. What is Execution Plan? - The combinations of the steps the optimizer chooses to execute a statement is called an execution plan.
50. What are the different approaches used by Optimizer in choosing an execution plan? - Rule-based and Cost-based. 

Database Interview Questions & Answers

Below we have listed all the Database Interview Questions and answers. Feel free to comment on any Database Interview Questions or answer by the comment feature available on the page. Get Your Own Copy of Oracle Magazine directly from USA to your home, ABSOULUTELY FREE OF COST, CLICK HERE..

Subcategories for Database Interview Questions - The following are sub categories for which Interview Questions are available under this category. Please select the appropriate sub-category:
Clipper Interview Questions (51)

DBA Interview Questions (487)

Firebird Interview Questions (136)

Hierarchical Interview Questions (25)

Informix Interview Questions (28)

Microsoft Access Interview Questions (35)

MS SqlServer Interview Questions (573)

MYSQL Interview Questions (174)

Network Interview Questions (90)

Object Relational Interview Questions (43)

PL/SQL Interview Questions (141)

PostgreSQL Interview Questions (127)

Progress Interview Questions (107)

Relational Interview Questions (77)

SQL Interview Questions (546)

SQL Server Interview Questions (486)

Stored Procedures Interview Questions (48)

Sybase Interview Questions (274)

Teradata Interview Questions (71)