I’ve put together my best questions to ask during an interview when I’m hiring a senior database administrator for Microsoft SQL Server or Oracle. I don’t expect any DBA to ace all of these – I’m not looking for easy questions, but rather for the top ten questions, tough challenges to let the really good DBAs shine during the job interview.
10. Solve the FizzBuzz problem with SQL code.
Here’s a quote of the FizzBuzz problem:
Write a program that prints the numbers from 1 to 100. But for multiples of three print “Fizz” instead of the number and for the multiples of five print “Buzz”. For numbers which are multiples of both three and five print “FizzBuzz”.
Challenge the DBA to do this on a whiteboard with pseudocode. The code doesn’t have to be perfect syntax, but we’re looking for the ability to solve this problem clearly and quickly. I’d ask this of any type of DBA, production or development.
There’s countless ways to do this, but I’m almost not as concerned with their technical accuracy as I am with the way they approach the problem itself. Do they get excited? Do they scribble out a few different ideas? Are they stunned at the thought of having to actually write T-SQL? Do they make excuses?
9. I’m a manager, and you’re my Senior DBA. Explain to me why we shouldn’t switch to MySQL or Oracle.
A senior DBA should have a basic grasp of the advantages and disadvantages of the major database platforms. They’ve probably answered this question before, too – if not from a manager, then from a developer who’s whining because they think Platform X is better than Platform Y.
I also want to see senior DBAs that can clearly explain a very political concept without taking it personally. My ideal DBA knows when his platform isn’t the right fit, and has no problem suggesting other ideas.
8. I’m a developer. Explain why I need a unique key on my table.
And really pretend that you’re a developer. If you’re a DBA manager, bring in one of your toughest developers to play bad cop. Challenge them – does it really improve performance or manageability? How do you know? Is it just your opinion, or where’s the proof?
If they can explain it in clear, easy-to-comprehend terms, that bodes well for their ability to communicate with other teams. Speaking of inter-team communication…
7. A project manager needs a new SQL Server. What do you ask her?
Use a brochure from any third party application (like Microsoft Project Server or Blackberry Enterprise Server) and say the project manager wants to set this up. I want the DBA to ask questions like:
- How big will the database be? (Leading to questions about whether we can add the database to an existing server)
- How critical is the database? (Leading to questions about clustering, disaster recovery, high availability)
- What’s the company standard on virtualization? (Can we save money by using a virtual server)
If the senior DBA candidate comes back with a shocked look and doesn’t know where to begin, then they haven’t done a lot of deployments. That might be fine if your shop rarely does new deployments, or if you’re hiring a development DBA, but they still should have some basic knowledge about sizing.
6. When and where does the local user group meet?
I’m not asking if they regularly attend, I’m not asking if they speak, I’m not asking if they run for office. At the senior DBA level, I’d just be thankful if they were at least vaguely aware that user groups existed. Huge massive bonus points if they’ve been involved with the community, and I’d skip the next question.
5. Can you give me references from other DBAs and developers who aren’t at your company?
I want to know that they’ve got at least a couple of people they can call when the going gets rough and the servers catch fire. These other references could be mentors, or could be people they’ve mentored or just worked with along the way.
I expect to get terrified looks, and I’d answer those by saying, “I don’t want to raise any red flags by calling people at your current employer, and I’m sure you know people who’ve left your company and moved on. I just want to talk to people who’ve worked with you on projects or on problems. I won’t ask for your level of technical competency, because these other guys can’t judge that. I just want to know you’ve interacted with them.”
I know, it’s creepy, but here’s the problem: there’s a lot of fakers out there with all the right answers, but no actual experience. At the senior level, for somebody with five or more years of experience, they have to have met other people who can at least verify they’re database administrators.
4. How do you learn new things?
When new versions of database servers come out, how do you prefer to learn how to use ’em?
Ideally, I want to hear a DBA say they build their own server under the desk when the beta comes out, and start hammering it and getting their arms around it long before it gets released. Not everybody has that much time, though – they might be stretched to the breaking point at their current job, with barely enough time to get their work done, let alone train on new versions. In that case, I like to level-set them by saying, “You’re coming to work here because we’re not that kind of shop. I want you to keep your knowledge current. How much time per month do you need to keep current, and how would you do it?”
Some DBAs learn best by going to offsite training classes, and can’t budget their time well enough or maintain a train of thought to learn inside the office. As a manager, you want to know that before you hire the person, so you can build that training budget into their salary. (You don’t want unqualified employees, do you? Yeah, you probably do – I’ve worked for you, ha ha ho ho.)
3. What third party database tools are your favorites?
If somebody’s been doing database administration long enough to claim the title Senior DBA, they’ve built up a little wish list of database management tools they’ve seen along the way. Tool types might include:
- Data modeling
- Change management
- Backup compression
- Performance monitoring
- Alerting
If they had a $X tool budget for their workstation, how would they spend it? Forget corporate standards – I want to know what tools they’d use if they could pick on their own. I’m not asking what software they’ve had experience using, because they might work somewhere so cheap that they’re restricted to native tools only. They have to have at least seen some ads for products that looked cool, though.
I might follow up with questions about tools we were using in-house already by saying things like, “We’re currently using Product X for monitoring. Have you seen it? What’d you think of it?”
This does two things: it gives me an outside opinion about other tools out there that my DBAs could be using to do a better job, and it tells me how much the job candidate has seen.
2. Ask stressful questions.
In one of the earlier questions, you probably found an area of weakness for the DBA’s knowledge – heck, nobody’s perfect. Go after that weakness. Challenge it, make them uncomfortable, and really push their buttons.
This is your one chance to see how they handle stress before the brown stuff actually hits the fan. Ideally, a senior DBA is someone who’s had their cage rattled more than once, and they’re comfortable under the gun.
No, don’t use an actual gun during the interview – but that reminds me of a plastic slot machine I used to keep on my desk at the office. When you pulled the lever, it shot water straight at you like a squirt gun. Always fun to see how somebody handled that one, but I wouldn’t let job candidates pull the lever. That’s a little overboard!
1. Why are you here?
Zoom out to the big picture. Tell me why you’re in this chair right now.
Is this some sort of career plan? Have they heard good things about the environment from another employee? Are they looking for an employee discount on products? (It doesn’t work at wine & spirits companies, just for the record.) Or are they just desperate? Nothing wrong with desperation, but remember that desperate employees are desperate for something, and you’d better find out what it is. If they’re desperate for money, it’s going to color the judgements they make. I’m not worried about someone stealing data and selling it as much as I’m worried about someone taking the job and then bailing out shortly thereafter for just a little more money from somebody else.
Top DBA Interview Questions That You Don’t See Here
I’ve seen DBA managers asking questions about tough challenges that had come up recently in the shop, such as, “Pretend you have horrible TempDB contention, really seriously bad load, and you have to fix it. What do you do?” The candidate would then name off all kinds of answers while the DBA manager sat back and said, “Nope. Didn’t work. Tried that too. Nope.” The DBA manager feels more and more smug while the candidate feels more and more frustrated, and the end result is a DBA manager that thinks the candidate isn’t good enough for the job.
The problem with that scenario is that the DBA manager expects the candidate to be able to answer a question that had already stumped their own staff. I always want to grab that manager and say, “Look, if I was interviewing YOU with this question, you’d have failed, because you couldn’t figure it out in sixty seconds either!”
If you’re going to ask a candidate a technical question, my rule of thumb is that it should take your existing staff the same length of time to answer the question. If a technical challenge took your staff three days to figure out, then you should expect the job candidate to take that same length of time to answer the question!
Need to practice for your next interview?
We can help! We’ve got an online course that teaches you how to ace DBA job interview questions.
100 Comments. Leave new
Well Done once again. You'd be amazed how many interviewees I tripped up with some simple questions about backing up the tail of the log and not detaching suspect databases. These 10 are great!
Thanks man! You're totally right – I'm putting together a list for junior DBAs too, and that'll cover some of those backup questions. Backup has to be priority numero uno.
FYI Brent,
http://dbwhisperer.blogspot.com/2009/01/technical…
Great read Brent! Dunno if I'll qualify but the thought stimulation was great.
I really want to make sure that a senior DBA understands how the db works under the hood. Do they understand a statement's plan? What's a nested-loop join, what's a hash-join? When would you expect the optimizer to use an index and when would you not? How does skewed data prevent a single "best" statement plan? Are they familiar with query factoring ("with") and window functions ("over" "partition by" "order by")? How is MVCC implemented and what does it mean for read-consistent isolation?
I think that depends on the type of work the DBA is doing. I've worked with very senior production DBAs who wouldn't know the answer to anything related to MVCC because they live in server troubleshooting mode all day – building clusters, setting up log shipping, etc. I've worked with senior development DBAs who wouldn't know the first thing about SAN array setup for clusters, but they could talk MVCC all day long. There's a few different flavors of "senior DBA" (data warehousing, production, development, etc), and that determines the technical questions you ask.
I based my question list here not on in-depth technical questions (because it varies so dramatically on the job function) but on things I want every DBA to be at least peripherally aware of.
Brent,
Very few hiring managers understand that as a DBA we become familiar with a subset of sqlserver functionality and tools, determined by the environments we work in. A DBA can answer questions without hesitation in regards to those things we deal with daily, those things we deal with rarely , not so much. What is important is that a senior DBA has been around for awhile, when the going get’s tough it’s a senior dba that is needed, that is most likely to smooth everything out. Interviewing is difficult, at least for me, if I’m interviewing with the company, it means I really want to work for them, so, I’m dealing with being nervous and answering questions, knowing, I may not get to work for the company if I answer incorrectly. I have also found that terminology being used can be an issue during interviews, the interviewer asks a question which I don’t understand due to the wording they used , later I discovered what they were asking and that I knew the answer.
Anyways, Thanks. Hopefully hiring mangers can learn from this as well as job candidates.
Hi Mindy,
Just jumping in with a suggestion based on one part of your comment. I don’t mean to ignore the rest, I thinks it’s great, but the sentence ” if I’m interviewing with the company, it means I really want to work for them” really struck me.
It’s important to interview for some jobs where you don’t feel strongly about it, and you aren’t super-invested. Interviewing is very much like playing a sport — if you only play games where you feel like everything is on the line, you won’t build up important core skills. And even when you’re playing and you feel like you’ve lost the game, you need to follow through (and that can save the game). Doing interviews for jobs that on paper don’t sound incredible can really help you out.
And, oddly enough, some of those jobs where you don’t feel like a raving fan coming in can lead to amazing things. Either the job is better than it seems, or you make a connection that leads to something important.
Basically, being too picky about where you interview can be a disadvantage to you as a candidate. Interviewing is hard work and it’s time consuming and frustrating, but it’s one of our most important job skills even if we don’t use it all the time.
Kendra
Great post. I have just recently discovered this very thing – one’s interviewing “muscles” definitely benefit from exercising them as much as possible!
Very Perfect Point. I have worked with Oracle and Sql Server for about 6 years, But Reading through his article, that is exactly what i came up with as well. There are Interviewers out there that simply want to trick you into a mistake or to get uncomfortable and i never understand why. When i participate in an interview, i ask questions simply to get the candidate thinking and see their train of thought. To have a person write a query on a white board is absurd. You immediately put the candidate out of their conform zone and they begin to fumble. Like i said i have done DBA for abut 6 years and i always do bad with interviewers that do this. Also some that expect you to answer the question exactly how they either know it or how they researched it online before your interview. Since all DBAs use google now-a-days, i think it’s unrealistic to expect DBAs to have everything stored in memory to regurgitate during a one hour interview for a job. Some they may not know, but they can google to get the job done if it came to it.
Very Good questions. I was never asked on the questions on the FizzBuzz problem, So I decided to do it myself to evaluate my skills:
Below is the SQL code, I was able to get the output of the FizzBuzz problem :
declare @var as int ;
declare @num as char(10) ;
set @var = 1 ;
while @var <101
begin
set @num =
case
when @var%3 = 0 and @var%5 = 0 then 'FizzBuzz'
when @var%3 = 0 then 'Fizz'
when @var%5 = 0 then 'Buzz'
else cast(@var as char(10))
end
Print @num ;
set @var = @var+1 ;
end
Re: fizzbuzz: I came up with the following single statement. Interestingly, the statement is not good for a list that's much bigger than 100 because of CTEs built-in recursion limit of 100.
with tmp(id) as
(
select 1 id
union all
select id + 1 from tmp where id <= 99
)
select
case
when id % 15 = 0 then 'fizzbuzz'
when id % 3 = 0 then 'fizz'
when id % 5 = 0 then 'buzz'
else CAST (id as varchar(100))
end
from tmp
I always ask DBA to write a sales report proc on the whiteboard with typical date time formatting in parameters and name concatenation with grouping issues. That may seem like a dev question, but I want DBA to be able to write a proc and talk about all the performance issues that come up based on this example. Indexes, clustering, filegroups, etc. It is amazing how deep you can go with a simple proc that a dev would care less about, but a DBA would.
Interesting. I’ve been on both sides of the interview chair and only some of your list is also on my list.
Man, I don’t mean to sound harsh but..you’re an expert DBA? I’m guessing you wouldn’t survive one of my interviews. I only expect the candidates to get 50%. I’ve had them all too. PhDs, Masters in blah-blah-blah, Captain of the Patterns Team at Yale majoring in C#, writing joins since she was in Pampers. Uh huh.
My goal isn’t to knock any candidate out, but if you show up at my door looking for a job and talkin’ tough about ‘how long it is’ you better be able to back that up because this is gym class and it’s time for a shower…
All kidding aside–asking who I know will get you nothing. Why would I share my contact list with the likes of you? If you’re nice you might get to know some of my crew but only after I get to know you.
Please note that I actually like your style in a lot of ways. Hell it’d probably be fun to have a beer.
AngryDBA – great question! I’ll answer it in tomorrow’s blog post (will be visible around 9am EST):
https://www.brentozar.com/archive/2009/07/what-i-want-versus-what-i-can-afford/
Great questions Brent. I especially love question #6.
I accidently wen to this website. I am a programmer developer and there is a DBA opening at my work. I want to prepare on some common dba questions. do yuo have any?
I’ve had several interview question posts on this site – if you search for “interview” here, you’ll find a few posts. Good luck!
Thank you Brent. I’m preparing for a DBA interview. Hopefully, your questions will pop up.
declare @num int
, @fizz char(4)
, @buzz char(4)
set @num = 1
set @fizz = ‘Fizz’
set @buzz = ‘Buzz’
while @num <= 100
begin
if (@num % 3 =0)
begin
if (@num % 5 = 0)
begin
print @fizz + @buzz
end
print @fizz
end
else if (@num % 5 = 0)
begin
print @buzz
end
else
begin
print @num
end
set @num = @num + 1
end
Good luck with the interview!
I wonder why do you wanna be a psycologist? Your questions and answer approach sometimes looks quite personal to me. Sort of: play by my rules or don’t play at all. You have good information here. Some times I think there is to much “you”, one has to filter out. For example, question 7 A project manager needs a new SQL Server. What do you ask her?… I think you have a particular scenario in mind you are not revealing. I would ask her, why does she need a new database. This would lead to the questions you are suggesting. Also this would partialy answer them, as she is the project manager and you are the DBA.
I read you Top 10 Interview Questions to Ask Senior DBAs I want to know do you have the answer to these question so I can learn from this experince
Yes, but I’m not giving them here. That’s the point of providing interview questions for DBAs – the real DBAs already know the answers through experience.
Hello Brent,
I’ve just found this post and must say that it was an interesting read (even though I’m not really a DBA). As I hadn’t heard of the FizzBuzz problem either so I thought it would be fun to give it a go. My answer to that one would have been either very similar to the one of Michael Swart, using a recursive CTE. Or it would have been the following:
select
case
when number % 5 = 0 and number % 3 = 0 then ‘FizzBuzz’
when number % 3 = 0 then ‘Fizz’
when number % 5 = 0 then ‘Buzz’
else cast(number as varchar(8))
end TheFizzBuzzAnswer
from master..spt_values
where number between 1 and 100 and type = ‘P’
PS: I’ve got your blog in my reader now, still catching up on some posts though 🙂
Regards,
Valentino.
These are your top 10 DBA’s questions? #9 is silly. #8 is junior question. Hardly a senior question. For #7 How big? you think a project manager knows how big a database will be? How critical? all production databases are critical. It more of a budget question…#6 can pass, but for a senior the question should be what conferences have they presented at, not just attended…#5 is silly. #4 is okay. #3 thru #1 are better. You should change your title to “possible interview questions with some good ones coming later…”
ThomW – that’s certainly not the first time I’ve heard criticism like that. We all have different standards, and it’s great that you can afford to hire top-notch people who present at conferences. Not everyone can be so lucky, and I blogged about that here:
https://www.brentozar.com/archive/2009/07/what-i-want-versus-what-i-can-afford/
Brent,
Perhaps the “senior” in your title got my underwear up in bunch. Seems somewhere in your list you should have had a strong backup and recovery question. Its all a company would really care about should disaster strikes. Anyone can call themselves senior and it seems your questions _should be testing to see if they are indeed senior. Though the B&R topic probably has its own 10 things to ask in an interview…
Thom
ThomW – you could say the same things about security, high availability, and so on. Unfortunately, there’s only 10 questions. You should blog your own 10 questions too. Take care, and have a great weekend.
how to add dynamic controls in Dotnet control,Panel,Placeholder,table,view.Which one is CUrrect
all of your responses were not practical guys, I experience to an interviewd was:
1 . Manager wants to do everything within 8 hours if can not achive it them stay till you;re done yr job.. not OT.
2. Minimum pay if they can
3. They don’t want to train or pay for training..
4. Finally, get rid of you ASAP project is completed.. C’est la vie..
String msg = “”;
int x=0;
while (x<99){
x = x + 1;
if ((x % 3 == 0) & (x % 5 == 0))
msg = msg + "FizzBuzz" + "\n";
else
if (x % 3 == 0)
msg = msg + "Fizz" + "\n";
else
if (x % 5 == 0)
msg = msg + "Buzz" + "\n";
else
msg = msg + x + "\n";}
jTextArea1.setText(msg);
Please do not use RBAR to solve fizzbuzz.
;with e1(N)
as
( select 1
union all select 1
union all select 1
union all select 1
union all select 1
union all select 1
union all select 1
union all select 1
union all select 1
union all select 1
), e2(n)
as
( select ROW_NUMBER() over( order by x.n)
from e1 x, e1 y
)
select
case when n%3 = 0 and n%5 = 0 then ‘fizzbuzz’
when n%3 = 0 then ‘buzz’
when n%5 = 0 then ‘fizz’
else cast(n as varchar(8))
end x
from e2
declare @n int, @oprint varchar(20)
set @n=1
while @n<=100
begin
set @oprint=null
if @n%3=0
set @oprint='Fizz'
if @n%5=0
set @oprint=isnull(@oprint,'')+'Buzz'
if @oprint is null
print @n
else
print @oprint
set @n=@n+1
end
First i will thanx for giving this kind of information …. but i dint did dba course yet…so i too also not have much knowledge about that ….but i want ot do dba course……how can i start the dba coursee from the begning ….i need ur help …..pls would u help me…..
regard
sahassan
Sahassan – thanks, glad you liked the post. I’ve got a lot of info about how to become a DBA here:
https://www.brentozar.com/sql/becoming-a-dba/
well i will thanx for givin tis kind of informatin….. but jst i completed my mca and also i completed sql server course…then i want to do dba course..now i can strt directly are i must learn any other course…. i need ur help
Rajitha – you can take training anytime you like, but do you have a job doing database work? If not, I’d start looking for the job first. I’d hate to have you end up with a bunch of expensive training and no job.
i want to ask that do u know any companies providing training or let me gain experience in database admin…. i really need that info…
reply me on my mail if possible…
thank u
Subhan – yes, there’s lots of companies that provide SQL Server training. You can Google for SQL Server training and find dozens of web sites and online courses. Can you be more specific?
declare @Loop int; set @Loop = 1
declare @MaxLoop int; set @MaxLoop = 100
while @Loop @MaxLoop
begin
if @loop % 5 = 0 and @loop % 3 = 0
Print ‘FizzBuzz’
else
begin
if @loop % 3 = 0
Print ‘Fizz’
else
begin
if @loop % 5 = 0
Print ‘Buzz’
else
Print @Loop
end
end
set @Loop = @Loop + 1
end
Declare @num as Int
set @num = 1
While (@num <=100)
begin
If @num%3 = 0
Print 'Fizz'
if @num%5 = 0
Print 'Buzz'
if @num%5 = 0 and @num%3 = 0
Print 'FizzBuzz'
if @num%5 0 and @num%3 0
Print right(‘0000’ + cast(@num as Varchar(3)),4)
set @num = @num + 1
Continue
End
DECLARE @n INT = 25
;WITH MyCTE (Num)
AS
(
SELECT 1 AS [Num]
UNION ALL
SELECT Num + 1 AS [Num]
FROM MyCTE
WHERE Num < @n
)
–SELECT * FROM MyCTE
SELECT Num
, CASE
WHEN (Num % 3 = 0) AND (Num % 5 = 0) THEN 'FizzBuzz'
WHEN Num % 3 = 0 THEN 'Fizz'
WHEN Num % 5 = 0 THEN 'Buzz'
ELSE CONVERT(VARCHAR, Num)
END AS [Mod3And5]
FROM MyCTE
set nocount on
declare @n int
set @n=1
while @n<=100
begin
if @n%5 = 0 and @n %3 = 0
begin
select convert (char (3),@n) + ' modulo of 3 and 5 = FizzBuzz'
if @@error != 0
begin
select 'failed to calculate modulo of 3 and 5'
end
end
else if @n %3 = 0
begin
select convert (char (3),@n) + ' modulo of 3 = Fizz'
if @@error != 0
begin
select 'failed to calculate modulo of 3'
end
end
else if @n%5 = 0
begin
select convert (char (3),@n) + ' is modulo of 5 = Bizz'
if @@error != 0
begin
select 'failed to calculate modulo of 5'
end
end
else
begin
print @n
end
set @n = @n+1
end
declare @i varchar(100) = 1
while @i < 101
begin
select
Case
when ((@i%3) =0) AND (@i%5 = 0) Then 'FizzBuzz'
when (@i%3) = 0 Then 'Fizz'
when (@i%5) = 0 Then 'Buzz'
Else @i
End
set @i = @i +1
End
declare @b as integer
set @b=1
while @b <= 100
begin
if(@b%3)=0 AND (@b%5) = 0)
print 'FizzBuzz'
else if(@b%3) = 0
print 'Fizz'
else if (@b%5) = 0
print 'Buzz'
else
print @b
set @b = @b +1
End
Thanks for providing good information…
I have complete my MCA, i dont want to do a job on programming so that i decided to do any non program course then my principal advise me to do DBA or Oracle so i join in DBA.. Now i have complete my DBA oracle course in this month. but where ever am applying for job there are specifying experiences. what should i do? Plz suggest me.
Hi. I’ve written about that here: https://www.brentozar.com/archive/2009/04/how-to-get-a-junior-dba-job-part-1/
DECLARE @a int
SET @a=1
WHILE @a <= 100
BEGIN
IF ((@a % 3 = 0) AND (@a % 5 =0))
PRINT ('FIZZBUZZ')
ELSE IF (@a % 3 = 0)
PRINT ('FIZZ')
ELSE IF (@a % 5 = 0 )
PRINT ('BUZZ')
ELSE
PRINT (@a)
SET @a=@a+1
END
If you want to get a feel for his or her’s query optimization skills I’ve found asking what a bookmark lookup seems to be a good one that trips a lot of people up.
declare i,a,b,y as integer
set i=1
while i <= 100
begin
if(i%3)=0 then a =1
else a=0
end if
if(i%5)=0 then b =2
else b=0
end
y=a+b
case y
y=0 print (i)
y=1 print ("Fizz")
y=2 print ("Buzz")
y=3 print ("FizzBuzz")
end case
end
i=i+1
loop
end
Great intuitive information. Keep them coming Brent. I’m a person that is getting my feet wet on SQL Server and looking to get certified and then move up to Oracle. I think this site will definitely help me. Thanks again
Just to be different. I’ve already got a tally table of sequential ints set up. So…
CREATE FUNCTION [dbo].[ufnGetFizzBuzz]
(
@Number int
)
returns nvarchar(50) as
begin
declare @retStr varchar(50) = NULL
if @Number % 3 = 0
set @retStr = N’Fizz’
if @Number % 5 = 0
set @retStr = N’Buzz’
if @Number % 3 = 0 and @Number % 5 = 0
set @retStr = N’FizzBuzz’
if @retStr = null
set @retStr = cast(@Number as nvarchar(50))
return @retStr
END
GO
select dbo.ufnGetFizzBuzz(number)
from WorkDB..TallyTable
where number <= 100
Re-reading your great post in anticpation of possible job interview next week. I’ve been lucky in that I’ve only had one interview like you describe in the last section.Interviewer got slightly offended after my 3rd course of action was rejected and I asked how long did I have to solve this problem. I feel confident in all my answers to the others, thanks to my great #sqlfamily!
Ask them what sargable means.
i comleted my b tech … my spesalization is i did a course of oracle dba . i do leave in dubai (sharja) bt the problem is m nt getting the job of my feild .that meanss my secilization course oracle dba job . so iwld lyk to inform people that do not depend on any course just gve a start up nd move onn . whatever u get thae job just do it ..
set nocount on
declare @counter int
set @counter = 1
while (@counter < 100)
BEGIN
if((@counter % 3) = 0 and (@counter % 5 ) = 0 ) select 'FizzBuzz'
else if((@counter % 3) = 0)
Sqltimes: provided very optimized solution. It is faster than loop through 100 times.
Son of a gun, I think the company I’m working for actually read this article. I didn’t get the fizzbuzz question, but I got 1,2,3,4 and 7.
For fun I did the fizzbuzz problem myself. Not as elegant as some of the ones above, but there’s more than one way to skin a cat in T-sql.
declare @a int
declare @fizz varchar(4)
declare @buzz varchar(4)
set @a =1
while @a <=100
BEGIN
set @fizz = ''
set @buzz = ''
If (cast(@a as float)/3.0 = @a/3) SET @fizz = 'FIZZ'
If (cast(@a as float)/5.0 = @a/5) SET @buzz = 'BUZZ'
If @fizz ” OR @buzz ”
SELECT @Fizz + @buzz
ELSE SELECT @a
set @a = @a + 1
END
Interesting questions. Fizzbuzz took me five minutes but I did figure out a t-sql solution.
declare @num int
set @num = 1
while @num <= 100
BEGIN
IF @num % 3 = 0 PRINT 'fizz'
IF @num % 5 = 0 PRINT 'buzz'
IF @num % 3 = 0 AND @num % 5 = 0 PRINT 'fizzbuzz'
ELSE PRINT @num
SET @num = @num + 1
END
Just realized my first solution was wrong, should have double checked the results 🙁
declare @num int
set @num = 1
while @num <= 100
BEGIN
IF @num % 3 = 0 AND @num % 5 0 PRINT ‘fizz’
IF @num % 5 = 0 AND @num % 3 0 PRINT ‘buzz’
IF @num % 3 = 0 AND @num % 5 = 0 PRINT ‘fizzbuzz’
IF @num % 3 0 AND @num % 5 0 PRINT @num
SET @num = @num + 1
END
DECLARE @Digit table (I int)
INSERT INTO @Digit VALUES (0), (1),(2),(3),(4),(5),(6),(7),(8),(9)
DECLARE @pop table (j int, JLabel sysname)
INSERT INTO @pop VALUES (3, ‘Fizz’), (5,’Buzz’)
/*To get fizzbuzz on same row: use concatenation
assembly of for xml path(”) trick */
SELECT COALESCE(p.JLabel, cast(sq.Num as sysname))
FROM
(
SELECT dp.i * 10 + d.i as Num
FROM @Digit as D
INNER JOIN @Digit as dp on 1 = 1 ) as sq
LEFT OUTER JOIN @pop as p on sq.Num % p.J = 0
Where Num > 0
Order by sq.Num
Hi
IYHO – does a Senior DBA need to know VB?
VB coding is normally a developer type task I’d have thought
Yes I guess it could be useful, but would you see it as essential?
Thanks
hi Brent,
These are great questions, as are the Top 10 for Developers, but what about the DBD. We have a DBA who maintains our servers, but then we have a team of SQL Developers who create the SQL Objects, work with the applicaiton developers, and create reports. What questions would you have for them?
thanks,
Mickey
I am a senior enterprise DBA for a company where an HR employee asks the initial phone screen interviews. I had to come up with a list of phone screen questions (see below). I ask the HR person to preface this section with – Please answer the questions in 3 sentences or less. If they can’t succintly answer each of these questions – they fail. I believe that a good DBA has it in his blood (or psyche, or DNA). They tend to be precise, systematic, somewhat obsessed with efficiency, and detail oriented. If they don’t know or if they ramble on about these answers, they are unlikely to be a good fit. And I also want them to have a solid understanding of basic concepts.
At the same time I believe good DBAs need to be able to think creatively about hard-to-solve problems. So a little rambling on about truly difficult problems is a good sign and would hopefully show the breadth of their knowledge. But I would not expect an HR, and to be honest other IT employees to effectively analyze a more in-depth response.
And of course, I will always ask about their real-world experiences, what they did, what projects they worked on recently, and what problems arose. And then how they handled it. But again, not sure an HR person could know a good response.
I added your question #8 to my list.
1. What’s the difference between DELETE TABLE and TRUNCATE TABLE commands?
2. What is an Audit table?
3. Explain the difference between an OLTP (or transactional) database and an OLAP (or analytical) database?
4. I’m a developer. Explain why I need a unique key on my table?
5. What’s the difference between a trigger and a function?
6. Why are SQL queries so fundamental to database performance?
7. What is an execution plan and what is it used for?
And it’s amazing how many wanna-be DBAs fail on the above 7 questions.
Illegal but easy to solve this way
select top 100 row_number() over (order by name ) as value into #numbers from sys.columns
SELECT CASE
WHEN value%5=0 and value%3 = 0 THEN ‘FizzBuzz’
WHEN value%3=0 THEN ‘FUZZ’
WHEN value%5= 0 THEN ‘Buzz’
Else cast(value as varchar)
END
FROM #numbers
drop table #numbers
sqlfrndz – and if you used that in an interview, do you think you’d have a good shot at the job?
This is a bunch of junk. Your trying to play amateur psych. Your taking away 99% of the resources people will have at work. “Good cop bad cop” business man please. Most nerds cant even stand the pressure to talk to a woman. Learn how to probably interview people.
Hi, Mike. Sorry you feel that way. Out of curiosity, would you mind listing a few interview questions you’d recommend instead?
Here you go. http://www.inc.com/suzanne-lucas/do-you-turn-into-a-jerk-when-you-conduct-an-interview.html
The way I see it is. One I spend years in the military so that I can pay for a computer science degree. In order to work for your company so I can help it turn a profit. Knowing this please do not disrespect me by playing good cop bad cop. Two, ask about my previous projects that I did in school/professionally Three do not play stress games with people. There are a lot of different cultures and background in America. We handle stress differently, not everyone is a white guy from the burbs who handle stress by bluffing his way through. Four the science/knowledge industry needs to stop pattern matching people.
Mike – thanks, but the question I asked you was to list interview questions that you’d use instead. You didn’t answer the question. Thanks for your contribution anyway.
Yes I did.
He indeed did, which was #2, to ask questions based purely on prior experience.
I don’t necessarily disagree with Mike. A more straight forward approach is to just ask about the worst production issue that the candidate stressed out over, when was the last time he was in the trenches and how did he go about handling it. If he can’t recall then likely he’s never gotten his fingernails dirty, never in the trenches. Though none of Brent’s questions were ego driven “gotcha” questions, they are the typical “behaviorial” ones.
To add on to this, one of my favorite questions (multi-part) i have seen asked and now use myself is ‘Tell me the biggest mistake you made, when you realized it how did you handle it, what did you learn’. If the candidate looks concerned, I let them know this can be anything and not necessarily they brought production down.
I love this question because depending on level of experience we have all made some mistake in our careers minor or major. This is how we all learn. I’m looking for can this person admit to when they make a mistake and not hide it. The folks that are worth hiring will explain how they implemented a procedure to avoid the mistake again. As a bonus, if they answer all the questions in the right order it gives me an idea of their thought process in recalling items.
SHOW THE STRUCTURE OF EMP TABLE. CREATE A QUERY TO DISPLAY THE (ENAME, JOB, HIREDATE, AND EMPLOYEE NUMBER FOR EACH EMPLOYEE, WITH EMPLOYEE NUMBER APPEARING FIRST. SAVE YOUR STATEMENT WITH YOUR FIRST NAME.
Just for kicks, I wanted to try to do this via one statement, no funky system tables to generate my number sequence and no recursion. So, cross products. Though this question says “Print”, this doesn’t actually do “Print”. Only really needed 2 cross joins here but added a 3rd. I want to add that I don’t like “clever” code that is not readily readable or universally understandable.
SELECT
CASE
WHEN (Ordinal % 3 = 0) AND (Ordinal % 5 = 0) THEN ‘FizzBuzz’
WHEN (Ordinal % 3 = 0) THEN ‘Fizz’
WHEN (Ordinal % 5 = 0) THEN ‘Buzz’
ELSE CAST(Ordinal AS VARCHAR)
END AS Output
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Ordinal, a.digit
FROM (VALUES(0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS A(digit)
CROSS JOIN (VALUES(0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS B(digit)
CROSS JOIN (VALUES(0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS C(digit)
) AS NumberSet
WHERE Ordinal <= 100
Here is my rendition, I am not sure if it is the first one but got it to 10 lines of code.
DECLARE @number INT = 0
BEGIN
WHILE @number <= 99
BEGIN
SET @number = @number + 1
if @number % 3=0 PRINT 'Fizz'
if @number % 5=0 PRINT 'buzz'
if @number % 15=0 PRINT 'Fizzbuzz'
else print @number
END
END
Pasted wrong copy:
DECLARE @number INT = 0
BEGIN
WHILE @number <= 99
BEGIN
SET @number = @number + 1
if @number % 3=0
PRINT 'Fizz'
if @number % 5=0
PRINT 'buzz'
if @number % 15=0
PRINT 'Fizzbuzz'
if @number % 3!=0 and @number % 5!=0 and @number % 15!=0
print @number
END
END
One of the best article, I have read in long time. very practical, every word is piece of advice to developer, manger and DBA.
Declare @var int = 1
declare @prt VARCHAR(10) = ”
While @var < 101
begin
Select @prt = IIF(@var % 3 = 0 AND @var % 5 = 0, 'Fizzbuzz', IIF(@var % 3 = 0, 'Fizz', CONVERT(VARCHAR, @var)))
print CONcAT('Number is ' , @var, ' ' , @prt)
Set @var = @var + 1
end
–>…and now for something completely different…
create table #x (word varchar(9), rowId int identity(1,1))
insert #x (word) select ”
GO 100
update #x set word = ‘Fizz’ where rowId % 3 = 0
update #x set word = ‘Buzz’ where rowId % 5 = 0
update #x set word = ‘FizzBuzz’ where rowId % 5 = 0 and rowId % 3 = 0
select * from #x
I will submit that Jr/Sr DBA is often a “depends” and will further submit such titles exist because they support the HR methodology in place at an organization and not much else.
I like the idea of the FizzBuzz question, although I have never been asked to perform such a task during an interview. I saw an interesting Oracle (yes, I support Oracle too) question the other day that led to a similar programming “test” where a man had three daughters and given some seemingly odd bits of information the test was to create the code to calculate the daughters’ ages. BTW the decision to bring in Oracle was because the software in question had not been written for SQL Server. In my experience someone on the “management team” and not the DBA more often than not selects the RDBMS to be used.
Most of us, unless we are joining an organization as the first-ever DBA and have been asked to implement DBA best-practices, will have to do some adapting as we fit into the new organization and its way of doing business. Then as time passes and the organization’s hiring decision is validated by our daily job performance we will have an opportunity to introduce those things that have served us well over time and perhaps challenge some of the status quo. I have found this to be true the last several moves I have made.
Today I ask to see a generic org chart (I want to know how the DBA function is viewed organizationally and if there is no DBA box that tells me a lot ), I want to know about the technical pond in which I will live (dev/test/prod- what those environments look like), what is the migration path for moving from dev to prod and how that works, what are their standards and best practices (goes back to knowing in advance how they do business) among other things.
BTW, if I don’t know an answer I say so and tell them where I would look to find it answer.
Well done what a bunch of F-ing A-holes your all are, Why oh why would anyone want to bother working with any of you, your heads are so far up you collective asses that you should give up the DBA gig and apply for a job in cirque du soleil let’s face it you would fit in well with the rest of the Clowns.
Nah, I got fired from Cirque du Soleil. It cost too much for the clown makeup to cover my nose.
I know it’s an old post, but I wanted to try my hand:
with cteNumbers as (
select top 100 row_number() over(order by name) as number from msdb.sys.tables
)
select
number
,case
when number % (3*5) = 0 then ‘FizzBuzz’
when number % 3 = 0 then ‘Fizz’
when number % 5 = 0 then ‘Buzz’
end as FizzBuzz
from cteNumbers
Declare @i int = 1
while @i <= 100
Begin
If @i % 3 = 0 AND @i % 5 = 0
Print 'FizzBuzz'
ELSE If @i % 3 = 0
Print 'Fizz'
ELSE If @i % 5 = 0
Print 'Buzz'
ELSE IF @i % 3 0 AND @i % 5 0
Print @i
set @i = @i + 1
END
I probaly would get up and walk out on some of you guys interviews. I have been on both sides of the table. Most dba’s today rare write code on a daily basis. If a person shows confident that they can write code or diagnostics a sql queries that’s enough for me. A good interviewer knows when a person is good and confident at their job. You don’t have to try to trip someone up during a interview. I’m not looking for a book worm. I’m looking for a confident dba that has the dedication to get the job done. Most the crap your asking people can be found in 5 minutes on the internet.
RDBA – if you don’t know where the local user group meets, then yes, you should probably get up and walk out on the interview to save time for everyone involved.
I’m curious, which questions can you find answers to in five minutes on that there internet?
1st my user group meet in Columbia, Md. The Baltimore Sql Server User Group. Basiscally all the questions I have ever been ask in a interview can be found on the internet. I just find it funny when people ask me trick questions in a interview. I don’t have time for games. I’m about making moves. I either know or don’t know the answers. Missing a question in a interview doesn’t change my work ethic.
Just so I understand you right, you’re saying you don’t need any knowledge for a job because there’s Google.
Gotcha. Good luck with that. And hey, while you’re at it – what’s stopping you from being a brain surgeon? No reason you couldn’t just Google what you need to know, right?
Really! I never said that..I just don’t care for the trick questions in a interview. Oh well enjoy your weekend.
You too, and good luck on the upcoming job interview.
Declare @i int = 1
While (@i <= 100)
Begin
IF ((@i%3) = 0 AND (@i%5)=0)
Begin
Print 'FizzBuzz'
End
Else IF (@i%3) = 0
Begin
Print 'Fizz'
End
Else IF (@i%5) = 0
Begin
Print 'Buzz'
End
Else
Begin
Print @i
End
SET @i = @i + 1
End
Sridhar — What if I ask you to write set based code to do this? If I ask you why you’re writing procedural code in a database, what would your response be?
[…] Turns out this also answers Brent Ozar’s (t|b) FizzBuzz interview question! […]
1. How would you restore a Database that does backups every 5 minutes to 12:55 PM without using the GUI?
2. When would you use a Rowstore Index vs a Columnstore Index and why?
3. Would you use Columnstores underneath an MD SSAS Cube’s OLAP tables?
4. How would you tune a Query with a plan that looks like a Christmas tree?
5. Do you have any PowerBi, R or Powershell Experience?
6. Describe the benefits of on-prem over the Cloud.
7. How would you get a suspect Database working again?
8. Where and when is the local SQL Saturday?
9. How would you setup a readable secondary reporting Database without kicking out the users?
10. Name something SQL you didnt know last week.
Brent,
I’m a newcomer but I think the comments section was more of an eye opener than the original questions. I was unaware of the local users group importance. So, after googling all the answers in five minutes (laugh) I found an article that might help others.
https://www.mssqltips.com/sqlservertip/949/how-do-i-find-a-local-sql-server-user-group/
Keep up the good work. Anything that keeps generating comments since 2009 has struck a cord.
Cheers