I am a Java developer and most of my experience is on Java. But sometimes I have face some database related questions in the interviews. Hence I wanted to ask that from the interview point of view, which questions a Software Developer/Sr. Software Developer (non-DBA, non-Database Enigneer) should be able to answer about databases?

I know that this is a very subjective question with no definite answer. But I have come across a similar question about J2EE with some really great answers. Hence some constructive suggestions like specific topics, areas, questions, even links to resources will be greatly appreciated.

  • 1
    Hi Maverick, the question you linked to is on Stack Overflow, where such questions were at one point allowed. Here, list questions like this aren't what the system is designed for: trying to capture everything one needs to know about a subject is way too broad a scope. If there's something specific about software development you'd like someone to explain to you, feel free to ask about that, instead. – user8 Dec 5 '11 at 3:47

Each organization have certain expectations from their developers, these expectations depend on the project at hand and the seniority of the developer. Here are some of the topics that cover all developer roles:

0-Build database scheme, tables, procedures and permissions.

1-Index design basics and when to use each type of index.

2-Query performance analysis and tuning - How and what tools/commands to use.

3-SQL and common SQL functions.

4-Database scripting language (T-SQL, PL/SQL) and when to use and when not to use.

5-Primary Keys/Foreign Keys design.

6-At least the first 3 Normal Forms ormalization (pros and cons).

7-Triggers types and when to use and not use.

8-Loading database from files.

9-Database environment parameters that needs to be set and why.

10-Logical database modeling, and some physical database modeling although, DBAs would know about that more. If applying for ETL job, need to know about star schema.

11-Referential Integrity constraint types and their effects.

12-Key design/selection best practices.

13-How to use the database management environment.

14-How to invoke the database commands from console.

15-How to write basic DDL (Create Table, etc.).

16-What is a dead lock and how to avoid it.

17-Views and their usage

18-What is a RDBMS!


There are several references for the above knoweldge, The following were added to address some of the comments:

Index Types - MSDN

Index Types and Query Optimaization

Database Engine Advisor - MSDN

SQL Server Profiler

Edit-2 FYI - See related question: software-developer-interview-question-fair-or-unfair

  • Thanks Emmand, but my question was from the non-DBA and non-Database Engineer's point-of-view. – Bhushan Dec 5 '11 at 0:22
  • @Maverick, thanks for your comment, the post was edited as per question. – NoChance Dec 5 '11 at 0:37
  • 1
    I've been a developer for decades, first used SQL more than 20 years ago, and I don't even understand some of those points, let alone have that knowledge. For example - "when to use each type of index"... as far as I was aware, the good old ordered key-base lookup (inverted table or whatever, often implemented as a B tree or B+ tree) was the only kind of index even most beginner database specialists will be taught about. Sure, you may have hash-table, r-tree and other options, but is that really what you're referring too? – Steve314 Dec 5 '11 at 1:02
  • And query performance analysis and tuning? As far as I'm aware, queries should be written to describe their intent. Performance issues are the responsibility of the query optimisation engine and of the database admin (who should add extra indexes where needed, which the optimiser should use automatically where appropriate). – Steve314 Dec 5 '11 at 1:04
  • @Steve314, thanks for your comments. You are correct about index types in general. In some database systems you could tell the database what type of index you want. I added 2 links in the answer body for this. Also, regarding query performance, there are basic rules such as you should avoid using "NOT" but there are cases where the query does not perform well because of the indexes currently built on the table or because the Optimizer is taking a different path than what you expect. It may help the developer to use some of the freely provided tools with the DBMS to identify such cases. – NoChance Dec 5 '11 at 8:39

Not the answer you're looking for? Browse other questions tagged or ask your own question.