Questions Every Good Database/SQL Developer Should Be Able to Answer

Questions every good Database/SQL developer should be able to answer

The different types of JOINs:

INNER JOIN
LEFT and RIGHT OUTER JOIN
FULL JOIN
CROSS JOIN

See Jeff Atwood's Visual Explanation of JOINs

What is a key? A candidate key? A primary key? An alternate key? A foreign key?
What is an index and how does it help your database?
What are the data types available and when to use which ones?

Fundamental SQL Knowledge?

The importance of Indexes and Primary Keys / Foreign Keys. Join Syntax and an understanding of the various types of Joins (including table aliasing). Data Type Selection (what column type to assign for an example of the input data set) would make sense. Sub Queries (esp correlated). Stored Procedures. Creation of variables. Aggregation functions and Grouping. Backup and Restoration practices.

That's a short list to start, I'm curious to see what other people reply with as well.

I thought of a few more. Temporary tables and Views are both good topics. Possibly Pivot Tables.

Fundamental SQL Knowledge?

That's a short list to start, I'm curious to see what other people reply with as well.

I thought of a few more. Temporary tables and Views are both good topics. Possibly Pivot Tables.

SQL & Postgres Interview Concepts

It depends on how much of the role is based around database development and design. For your SQL syntax, you should also understand the difference between the types of joins, and be able to use GROUP BY, ORDER BY, HAVING as well as the aggregate functions that can be used in conjunction with them.

In terms of performance monitoring, I would be looking at execeution plans (not sure about the Postgres equivalent) and how they can provide tips on increasing performance, as well as using SQL Profiler to see what instructions the server is executing in real time.

Transactions can be useful for rolling back, well, transactions (stored procs, ad-hoc queries etc.) that require queries to complete in a certain way to maintain data consistency. Some people (myself included) have a practice of placing any statements that make any changes to data into a transaction that automatically rolls back (BEGIN TRAN ... ROLLBACK TRAN) to check that the correct amount of data is manipulated before pushing changes to a live server. Have a look at the ACID model - Atomicity, Consistency, Isolation, Durability.

Normalization is something that can take a little time to go through, but just know and partially understand up to 3rd form normalization and that will get you started.

Optimisation can be a huge topic. Just remember to try and do things like UPDATE using set based queries, rather than row based (updating in a WHILE loop is an example of row based updating, but it CAN have its uses).

I hope this helps a little.

Questions every good .NET developer should be able to answer?

Basic questions include:

Whats the difference between an abstract class and interface? When would you want to use them?
What's the difference between a left join and an inner join?
What's the difference between viewstate and sessionstate?
What's the difference between overriding and overloading a method? Explain how both are done.
What's the difference between protected and internal? What about "protected internal"?
How do short-circuited operators work?
Explain what the StringBuilder class is and why you'd want to use it?
What's the difference between a static method and a non-static method?
What does the "volatile" keyword in C# mean?
Explain what happens when you pass a "ref" or "out" parameter into a method. What's the difference between those two keywords?
What's a weakreference? When would you want to use one?
What's the difference between a DataTable and a DataReader?
What's the difference between a value-type and a reference type?
What does the "readonly" keyword in C# mean?

I think it usually helps to ask your applicants to complete a simple coding exercise such as:

Write your own linked list class without using the built-in classes.
Write your own hashtable class without using the built-in classes.
Write a class that represents a binary tree. Write a method that traverses all nodes of the tree.
Write a method to perform a binary search on an array without using built-in methods.
Draw a database schema for a blog. Each user only has one blog, each blog has many categories, each category has many posts, and each post can belong to more than one category. Ask your applicant to write queries to pull specific information out.

Next, look for specific technical know-how:

(Event handlers) Create a class with a custom event handler, create another class which hooks onto the custom event handler.
(XML) Load an XML document and select all of the nodes with properties x, y, and z.
(Functional programming) Create a function that accepts another function as a parameter. A Map or Fold function works really good for this.
(Reflection) Write a function which determines if a class has a particular attribute.
(Regex) Write a regular expression which removes all tags from a block of HTML.

None of these are particularly difficult questions for a proficient C# programmer to answer, and they should give you a good idea of your applicants particular strengths. You may also want to work in a few questions/code sample that make use of specific design patterns.

[Edit for clarification]:

Seems that a lot of people don't understand why I'd ask these types of questions. Let me touch on a few peoples comments (I'm not quoting directly, but paraphrasing instead):

Q: When was the last time anyone used volatiles or weak references?

A: When I give technical interviews, I look to see whether a person understands the high-level and low-level features of .NET. Volatiles and weak references are two low-level features provided by .NET -- even if these features aren't used often in practice, answers to these questions are extremely revealing:

A good understanding of volatiles demonstrates that a person understands how compiler optimizations change the correctness of code, how threads keep local copies of shared state which may be out of sync at any given time, and is minimally aware of some of the complexities of multithreaded code.
A good understanding of weak references demonstrates that a person knows about the intimate details of the garbage collector and how it decides when to free memory. Sure, you could ask candidates "how does a garbage collector work", but asking about weak references gets a much better, more thoughtful reply.

.NET is a fairly abstract language, but star developers almost always have a deep understanding of the CLR and the low-level details of .NET's runtime.

Q: Why would anyone need to implement their own hashtable or linked list?

A: I'm not implying that the Dictionary class is inferior or that people should roll their own hashtable. This is a basic question which tests whether a person has a minimal understanding of datastructures. Thats what these questions test for: bare minimum understanding.

You learn about these hashtables and linked lists on the first day of Data Structures 101. If someone can't write a hashtable or a linked list from scratch, then they have a huge gap in their technical knowledge.

Q: Why are these questions so crud-oriented?

A: Because the title of this thread is "questions every good .NET developer should know". Every .NET developer begins their career writing crud apps, and 90% of all application development people do for a living is concerned with line-of-business applications.

I think questions testing a persons knowledge of line-of-business apps are appropriate in most cases, unless you're looking for developers in very specific niches, such as compiler development, game-engine development, theorem-proving, image processing, etc.

What is a good 'FizzBuzz' question for a SQL programmer?

We typically use something like this as a bare minimum for SQL:

Given the tables:

Customers: CustomerID, CustomerName

Orders: OrderID, CustomerID, ProductName, UnitPrice, Quantity

Calculate the total value of orders for each customer showing CustomerName and TotalPrice.

In our view, this is a pretty simple question requiring a join on two tables, grouping, and an aggregate function. We're amazed at how many people we talk to that presumably write database code in their job can't remember join syntax (and we never care which syntax they use, MSSQL style or Oracle style or something else).

What I like about this question is it lends itself to follow up questions like

How would you find all customers that ordered more than $1000 total?
How would you normalize these tables?
How would you optimize the queries?

Questions every good PHP Developer should be able to answer

Admittedly, I stole this question from somewhere else (can't remember where I read it any more) but thought it was funny:

Q: What is T_PAAMAYIM_NEKUDOTAYIM?

A: Its the scope resolution operator (double colon)

An experienced PHP'er immediately knows what it means.
Less experienced (and not Hebrew) developers may want to read this.

But more serious questions now:

Q: What is the cause of this warning: 'Warning: Cannot modify header information - headers already sent', and what is a good practice to prevent it?

A: Cause: body data was sent, causing headers to be sent too.

Prevention: Be sure to execute header specific code first before you output any body data. Be sure you haven't accidentally sent out whitespace or any other characters.

Q: What is wrong with this query: "SELECT * FROM table WHERE id = $_POST[ 'id' ]"?

A: 1. It is vulnarable to SQL injection. Never use user input directly in queries. Sanitize it first. Preferebly use prepared statements (PDO) 2. Don't select all columns (*), but specify every single column. This is predominantly ment to prevent queries hogging up memory when for instance a BLOB column is added at some point in the future.

Q: What is wrong with this if statement: if( !strpos( $haystack, $needle ) ...?

A: strpos returns the index position of where it first found the $needle, which could be 0. Since 0 also resolves to false the solution is to use strict comparison: if( false !== strpos( $haystack, $needle )...

Q: What is the preferred way to write this if statement, and why?

if( 5 == $someVar ) or if( $someVar == 5 )

A: The former, as it prevents accidental assignment of 5 to $someVar when you forget to use 2 equalsigns ($someVar = 5), and will cause an error, the latter won't.

Q: Given this code:

function doSomething( &$arg )
{
    $return = $arg;
    $arg += 1;
    return $return;
}

$a = 3;
$b = doSomething( $a );

...what is the value of $a and $b after the function call and why?

A: $a is 4 and $b is 3. The former because $arg is passed by reference, the latter because the return value of the function is a copy of (not a reference to) the initial value of the argument.

OOP specific

Q: What is the difference between public, protected and private in a class definition?

A: public makes a class member available to "everyone", protected makes the class member available to only itself and derived classes, private makes the class member only available to the class itself.

Q: What is wrong with this code:

class SomeClass
{
    protected $_someMember;

    public function __construct()
    {
        $this->_someMember = 1;
    }

    public static function getSomethingStatic()
    {
        return $this->_someMember * 5; // here's the catch
    }
}

A: Static methods don't have access to $this, because static methods can be executed without instantiating a class.

Q: What is the difference between an interface and an abstract class?

A: An interface defines a contract between an implementing class is and an object that calls the interface. An abstract class pre-defines certain behaviour for classes that will extend it. To a certain degree this can also be considered a contract, since it garantuees certain methods to exist.

Q: What is wrong with classes that predominantly define getters and setters, that map straight to it's internal members, without actually having methods that execute behaviour?

A: This might be a code smell since the object acts as an ennobled array, without much other use.

Q: Why is PHP's implementation of the use of interfaces sub-optimal?

A: PHP doesn't allow you to define the expected return type of the method's, which essentially renders interfaces pretty useless. :-P

SQL Server interview questions

Sql Server is a massive topic. You're not going to know everything, especially as a developer. You'll be expected to know about PKeys, foreign keys, design etc. Just be honest when you don't know something. Don't guess and don't try to blls&t your way through anything. It never plays well. I've seen it from both angles (interviewee and interviewer.)

Honesty gets a lot of respect from the interviewer. It makes you a real person and it makes their job easier. Not necessarily to say yes or no to your qualifications but because they have less guessing to do and they will remember that when it comes time to cull through the applicants.

Questions Every Good Database/SQL Developer Should Be Able to Answer