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.