Interview questions for Database Administrator

Database Administrators (DBA) ensure the company’s databases are secure and run smoothly, and that information flows seamlessly between all employees, including front-end and back-end users.
During your hiring process, look for candidates with experience in database administration who are familiar with data privacy and recovery procedures. An academic background in Computer Science and knowledge of statistical analysis packages are pluses

1. 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.

2. Do you have experience with on-premises databases, cloud databases or both?

Many organizations are moving from a fully on-premises infrastructure to the cloud. You can discover which environment your candidate works the best in. If your organization intends on changing away from your current configuration, you can find out whether your interviewee can support your long-term goals.
What to look for:
• Strong understanding of infrastructure differences
• Flexibility
• Willingness to learn

3. Diffrence between a “where” clause and a “having” clause?

The order of the clauses in a query syntax using a GROUP BY clause is as follows:
select …where..group by…having…order by…
Where filters, group by arranges into groups, having applies based on group by clause. Having is applied with group by clause.

Answer2

In SQL Server, procedures and functions can return values. (In Oracle, procedures cannot directly return a value).
The major difference with a function is that it can be used in a value assignment. Such as:
–system function
Declare @mydate datetime
Set @mydate = getdate()

–user function (where the user has already coded the function)
Declare @My_area
Set @My_area = dbo.fn_getMy_area(15,20)

Answer3

1.”where” is used to filter records returned by “Select”
2.”where” appears before group by clause
3.In “where” we cannot use aggregate functions like where count(*) >2 etc
4.”having” appears after group by clause
5.”having” is used to filter records returned by “Group by”<
6.In”Having” we can use aggregate functions like where count(*) >2 etc there are two mor

4. Can you explain what ODBC is?

This question tests the technical knowledge of your applicant and is directed at people you’re hiring for mid-level positions. Look for an answer that covers the basic concept and goes into some detail about its role in a database environment.
What to look for:
• Thorough understanding of this acronym
• Good communication
• Use case examples
Example:
“Open Database Connectivity is a method used by application front ends when they’re communicating with a database backend. I’ve helped software developers implement ODBC so they can pull the right data sources into custom enterprise apps.

5. Describe Your Previous Experience with Database Servers?

The interviewer wants to know if you have any experience working with database servers and how that experience will help the company.
Describe your experience of working with database servers.
Tip #2: Explain anything that stood out for you.
Sample Answer
I have worked with different databases including oracle for the past five years. Whenever failure occurs, I capitalize on my skills and knowledge to identify the problem and rectify it promptly. To overcome certain security and functionality issues, I make sure to upgrade a database to a more recent and better version. I have been involved in setting up databases as well as migrating data from one platform to another.

6. Describe How You Would Use SQL Agent?

The interviewer is testing your depth of experience using SQL server
Tip#1: Clearly explain the purpose of the SQL agent.
Tip#2: Describe a situation where you used it.
SQL agents enable you to perform commands at a specific time repeatedly. For example, backups are done frequently at particular times. I use SQL agents to perform backups daily at a time when no one is using the system. I do my backups at 3:00 am using SQL agents.

7. Shall we create procedures to fetch more than one record?

Yes. We can create procedures to fetch more than a row. By using CURSOR commands we could able to do that.
Ex:
CREATE OR REPLACE PROCEDURE myprocedure IS
CURSOR mycur IS select id from mytable;
new_id mytable.id%type;
BEGIN
OPEN mycur;
LOOP
FETCH mycur INTO new_id;
exit when mycur%NOTFOUND;
–do some manipulations–
END LOOP;
CLOSE mycur;
END myprocedure;
In this example iam trying to fetch id from the table mytable. So it fetches the id from each record until EOF.

(EXIT when mycur%NOTFOUND-is used to check EOF

8. What is SQL?

Structured Query Language is the basic way of asking a database server to talk to you. Whether that is in the context of asking it a question, giving it answers to questions it is asking you, or updating answers that have already been stored in the database. The art of asking the right question is critical to getting back the right data you need, which is incredibly valuable when dealing with databases, as it is very easy to receive far more data than you know what to do with, or nothing at all.

9. What does ‘SELECT’ do?

SELECT in the terms of an SQL query triggers a question to the database. It looks across the specified table(s), finds the data you are looking for and then presents it to the user for consideration. Depending on the query, this can be an awful lot of data, so again, asking the right question is critical.

10. What is a primary key?

A primary key is usually used as the index for a particular table — a value that the table can depend upon to be a reliable unique value in every row. When trying to pull data for a particular row, the primary key will normally be used to pull that information, usually a numeric value. For example, if you are trying to pull up data on a specific person, and that database is using their unencrypted ssn as the primary key (naughty), then that could be used in the query to identify that particular row since there could be other people present in the database with that specific name or other identifying characteristics.

11. What is a query?

A query in normal terms is a question, simple enough. It is the statement that is talking to the database in order to Create, Read, Update or Delete (CRUD) data. While many times a query is an actual question asking for an answer, it can also be the statement to modify, insert, or remove data in the database as well.