|
Tasks for new programmers
Can you help me with some tasks to check programmes experience in SQL Server development.
Thank you.
Jacob F
Saturday, November 15, 2003
Are you asking for some technical questions to guage the ability of a potential SQL Server programmer?
m
Saturday, November 15, 2003
Yes, my company want to hire SQL server programmers and i need to test they professional skills. I'm a programmer myself and do not have any experience in test writing. Can you help?
Jacob F
Saturday, November 15, 2003
Assuming they will not be doing DBA type work and only programming the DB, there are probably three areas to focus on:
Basic SQL Language.
1. If you have a customer table and an order table, write a join to pull back all the customer names and products.
2. Assuming they do a modern join (FROM A join B on A.CustID = B.CustID), ask them what other ways they can join two tables (you can use WHERE A.CustID = B.CustID).
3. Ask them how to select a set of data from one table and insert it into a different table.
4. Ask them to describe when one would use the HAVING keyword.
Transact-SQL Programming
5. If you are using SQL 2K, ask them what different triggers are supported and how they might be used.
6. Ask them the syntax for returned values in a stored procedure (in other words, you can pass stored procedures values, but you want some back after it executes).
Database Design
7. Ask what a bridge table is.
8. Ask for an example of when it would be a disadvantage of normalizing the data.
I'll post the answers later as I have to run, but here is a start. Also, there are many ways to skin a cat, so you may not get word for word answers.
m
Saturday, November 15, 2003
Don't ask language detail questions. Any fool can memorize. Ask questions that will tell you how much they understand about the concepts of databases and relational algebra.
- What is the ACID test?
- What are the six normal forms?
- Why should you normalize?
- When should you denormalize?
- Who is Codd?
- What is Conjunctive Normal Form? What is Disjunctive Normal Form?
- What are Karnaugh Maps?
- What is a correlated subquery?
- When should you use a sudquery as opposed to a join? Or a join as opposed to a subquery?
- What is a star schema? Or a snowflake schema?
- What is a trigger? When should you use one? When shouldn't you?
- What is declarative referntial integrity?
- Compare triggers with DRI.
- What are transactions?
- What are locks? What kind of locks are there? What are the issues in lock choice?
- What is a cursor? When should you use one? When shouldn't you?
etc....
Anonymous Coward
Saturday, November 15, 2003
I'd show them a partial schema of a database you use daily and ask them to write a query against it (or if they will be expected to do a report or flatfile, ask for that). Give them 30 minutes and answer schema questions as they arise.
They should ask about what is indexed (or they should make logical assumptions (*_id is likely to be indexed on most databases, etc). Ask them to handle a date comparison, a between statement, etc.
I'd say two or three queries covering correlated subqueries, formatting output, date comparisons, outer joins, counts, groupings, and maybe even an impossible scenario. The key is to test on things you actually do on the database you actually run against. Testing on anything else is a waste of everyone's time.
Lou
Saturday, November 15, 2003
Ahhhh ... These are bound to atract useless people
Don't ask language detail questions. Any fool can memorize. Ask questions that will tell you how much they understand about the concepts of databases and relational algebra.
>>- What is the ACID test?
I thought they only tested for Coke and Heroin!
Seriously though a transaction is a transaction either your DB supports it or it doesn't
>>- What are the six normal forms?
Pointless as your not supposed to use any but three of them
>>- Why should you normalize?
Becuase it saves disk space that costs what a buck a gig now
>>- When should you denormalize?
After you just fired the loser that thought disk space was more important than speed
>>- Who is Codd?
Some guy with a pony tail and a beard who doesn't sign my pay check
>>- What is Conjunctive Normal Form? What is Disjunctive Normal Form?
HUH?
>>- What are Karnaugh Maps?
HUH?*(2)
>>- What is a correlated subquery?
HUH?*(3)
>>- When should you use a sudquery as opposed to a join? Or a join as opposed to a subquery?
First Good Question out of 8 not bad
>>- What is a star schema? Or a snowflake schema?
Good Question 2
>>- What is a trigger? When should you use one? When shouldn't you?
Good question 3
>>- What is declarative referntial integrity?
Good question, but people may have read a different text book (I know what you mean) but a lot of people who know the answer would repsond HUH?
>>- Compare triggers with DRI.
Again what is DRI
>>- What are transactions?
Good
>>- What are locks? What kind of locks are there? What are the issues in lock choice?
Good you can also throw in Oracle vs Sybase locking models (if the weasel claims both on his resume)
>>- What is a cursor? When should you use one? When shouldn't you?
Good question
etc....
My advice is don't test a persons vocabulary or trivia knowledge test skills. Give him a problem and a set of requirements that he needs to normalize a databse to solve don't just ask him what 3rd normal form is.
i.e. don't ask what a "having" clause is ask I have a grouped a query of customers grouped by aggregate incomes how do I selected the ones whose aggregate incomes are over 120,000,000 per year?
the artist formerly known as prince
Saturday, November 15, 2003
">>- When should you denormalize?
After you just fired the loser that thought disk space was more important than speed
"
Although I laughed pretty loud when I read that, I still think there are good reasons to normalize. I've seen people over-normalize, and i'm sure we've definatly all seen someone denormalize way too much.
Vince
Saturday, November 15, 2003
I'm not saying don't normalize but sometimes it gets ridiculous (you get fifty tables to describe one small aspect of the problem).
the artist formerly known as prince
Sunday, November 16, 2003
Thanks to all who joined to this discussion. I think may it be a good idea to give competitors some test tasks to make it in home. What do you think about this? Any recommendation about tasks?
Jacob F
Sunday, November 16, 2003
Hi Jacob,
Here's the technical testing procedure that I use for the majority of my clients.
The first step is to filter out the hopeless candidates, especially in the current market where supply drastically exceeds demand in most cases. I do this by asking the candidate to complete a very basic technical test in orderto be considered for an interview.
This test consists of a set of 15 very basic questions, where any decent candidate should be able to score at least 80%. Most of the questions are open-ended, because you can learn a lot from a candidate by looking at the *way* the candidate answers a question, as well as what answer s/he gives.
A typical question from this basic test might be:
1) Please describe your two favourite SQL Server technical books and explain why you like them.
You might be astonished at the number of candidates who either can't or won't answer this simple question. A frequent scenario is for a candidate to claim that s/he finds most of his/her techie information on the Internet. Well, I'm sorry, but if you haven't read at least two books on some aspect of SQL Server, you aren't a professional SQL Server developer.
Another question might be:
2) What's the maximum number of clustered indexes that can be attached to a table?
Once again, if you don't understand the concept of a clustered index, then you're not a professional SQL Server developer.
And so on, for about 15 questions. I score 1 for a correct and complete answer, 0.5 for a correct but incomplete answer. Minimum score to be considered for an interview is 10, scoring 12 or more guarantees an interview.
Surprisingly, this basic test eliminates 80% of the candidates without having to go through the hassle of a formal interview. It's astonishing how many incompetent candidates are out there - it's almost as though many companies are so lax in their technical interviewing that a *lot* of crappy developers are still able to scratch a living.
During the formal interview, as well as looking for attitude and personality fit, I dig rather deeper into their technical skills. A typical question would be:
3) If you have stored procedure A calling stored procedure B, and you raise an error in stored procedure B while both procedures have an open transaction, what happens to the control flow and the open transactions?
I'm not looking so much for an absolutely correct answer, rather for the candidate to demonstrate the limits of his/her understanding and also how s/he performs under a fair amount of pressure from the interviewer.
HTH,
Mark
------
Author of "Comprehensive VB .NET Debugging"
http://www.apress.com/book/bookDisplay.html?bID=128
Mark Pearce
Sunday, November 16, 2003
I assume the original poster wants somebody to be proficient in SQL not somebody who can implement database engines. If this is the case then…
While I agree with “The Artist …”, it seems to me that most of the questions pertain to databases and their physical implementations and not SQL as a query language or programming. Why should a developer know how a clustered index is implemented and why you can have just one per table? In my opinion the DBA is the one paid to design, optimize and maintain a DB. A developer’s job comes only later when he or she the SQL to query whatever structure lies underneath, be it the 5 th normalized form or a flat file or a XML structure.
In the fact there are so many tricks when implementing an Oracle DB for instance that I would not ask a developer to have the knowledge to do it. It would suffice for me if the developer grasps the joins and knows what a full table scan is and how to avoid it. Everything else is for DBAs, including but not limited to stored procedures, RIs and triggers.
coresi
Sunday, November 16, 2003
Coresi,
>> Why should a developer know how a clustered index is implemented and why you can have just one per table? <<
*IMO* a candidate who doesn't understand the concept of a clustered index is not a competent SQL Server developer.
But you're lucky - my clients pay to ignore my opinions - you get to do it for free :)
Mark
Mark Pearce
Sunday, November 16, 2003
Mark,
What I’m saying is that a clustered index is a MS SQL physical implementation concept. Once the DBA decides to create a clustered index what is the developer to gain from knowing that a specific index is clustered or not? MS might not even support clustered indexes in the future for what we know…
Oracle does not implement clustered indexes as such. Filtering potential candidates on the base of “nice to have” knowledge won’t move the project forward. What if you have an extremely bright candidate with an Oracle background?
coresi
Monday, November 17, 2003
> Filtering potential candidates on the base of “nice to have” knowledge won’t move the project forward.
That's how I feel as well.
Too much filtering is basically trying to Fight City Hall (*).
Rather than bitching about (perenially) low standards (**), I'm interested in ways to make projects work with a wide range of skill levels, and this reflects what I'm seeing in the Real World.
* The really obvious filtering -- just not hiring anyone and keeping the team small -- is usually not considered.
** This often serves a dual purpose as a not-very-well-disguised form of one-upmanship
Portabella
Monday, November 17, 2003
Coresi,
>> What I’m saying is that a clustered index is a MS SQL physical implementation concept. Once the DBA decides to create a clustered index what is the developer to gain from knowing that a specific index is clustered or not? <<
In my clients' world (investment banking), the developers design and implement the entire database model. A DBA might check the implementation and give performance advice, but it's the developer's job to build the database.
And I must repeat my opinion that, regardless of who actually does the database implementation, a SQL Server developer who doesn't understand about clustered indexes is not competent.
Of course, your standards may vary. Investment banking pays top 10% percentile here in the UK, and my clients want that level of expertise. Typical rates are $450 - $1,000 per day.
>> What if you have an extremely bright candidate with an Oracle background? <<
For a contract job that emphasised SQL Server, I wouldn't normally be interested in an Oracle developer who didn't have SQL Server expertise. Developers at any decent level (say, top 25%) just aren't plug-and-play compatible.
A freelance developer who insists that he should be hired because he can pick up any required skill in a short time is telling me that he doesn't understand how much time is involved in developing real expertise, and therefore is probably not in the top 25%. Indeed, such a developer is playing into the hands of management who insist that software development can be outsourced easily because developers are "fundamentally a fungible resource".
For a permie job, where time is less of an issue, then I might well take on a really good Oracle developer to do a SQL Server role. But I would still give the same basic 15-question test, just oriented towards Oracle rather than SQL Server.
Mark
Mark Pearce
Monday, November 17, 2003
Portabella,
>> Rather than bitching about (perenially) low standards (**), I'm interested in ways to make projects work with a wide range of skill levels, and this reflects what I'm seeing in the Real World. <<
This is an interesting perspective that I hadn't really considered. The issue for my clients is that they're paying very good money for (contract) developers - so they only want the top 10-20%. Because of the money available, and the current economic climate, they don't need to hire developers at the lower skill levels.
Mark
Mark Pearce
Monday, November 17, 2003
Mark, I also work in the financial industry. I am quite involved in applications synchronizing MS SQL and Oracle schemas and in my experience someone who states that can master both DB design and application design did not encounter too many challenges. I would rather prefer to have each expert manage his own domain. I understand things are somehow different with MS SQL which does not pose the same challenges as Oracle, but there is a threshold in a team size somewhere around 5-10 developers where is a better idea (read: cheaper) to have a dedicated DBA than to have each developer implement his own ideas of DB structures.
I totally agree that for small applications one has to pull double duty. For medium to big size projects this is just not feasible.
coresi
Monday, November 17, 2003
Coresi,
>> I totally agree that for small applications one has to pull double duty. For medium to big size projects this is just not feasible. <<
I would agree that in a large team of developers working on a single project, it is often better to centralise your database modelling and implementation. The larger the team, the more likely it is that they'll trip over each other.
The original question concerned the testing of a single SQL Server developer. My assumption was that this means somebody who does database modelling and/or writes SQL for a living, not somebody who calls stored procs written by another developer or a DBA.
Mark
Mark Pearce
Monday, November 17, 2003
 Recent Topics
Fog Creek Home
| 
