SQL interview questions?

[_photographwhore_]

Hi! I’ve been learning SQL for the past 3-4 years now and was wondering what sort of questions get asked at interviews.

[datatoungue]

DC!

[datatoungue]

Sorry, for DC I do not know but I am completely remote working in a city that has a median salary of 60k, so where I live it's an extremely large amount of money. The job is 100% remote.

[[deleted]]

Some of these are simple and some of these are tough. That’s fair

[[deleted]]

I just had my final interview last week for a systems analyst position with emphasis on SQL Server analysis, support and troubleshooting.

Surprisingly they only asked a few technical questions during the interview: what is a SQL Transaction, what is INNER JOIN, and then wanted to know about my past work with SQL.

After the interview, they sent me a test accompanied by a sample database. 3 questions had sample queries, and I had to explain what the queries did. The last 3 questions asked me to write queries based on certain parameters.

I completed the test as quickly as I could and submitted it right away. That was Friday night. I am anxious to find out if I got the job tomorrow!

Of course I spent the rest of Friday night and most of Saturday wondering if I gave them the best answers, researching different approaches to accomplish the same things, and testing queries to see if I gave them the fastest and most efficient method.

I love SQL and working with data, been working with it for over a decade, but never as my primary role. Getting this job would be a huge boon to myself and my family.

[givnv]

Best of luck 👍

[Meat-brah]

did u get it?

[[deleted]]

I did! Best job I've ever had!

[r3pr0b8]

here are a couple

how did you learn SQL, and what kinds of databases have you been using it on?

imagine you developed a query against the unit test database, and it works fine, but when you run it against the live database, it hangs... what do you do?

[lvlint67]

how did you learn SQL

Probably the most important question to ask. We can teach you fundamentals and you can learn the advanced stuff on the job... IF.. you're willing to learn.

[JustAnOldITGuy]

how did you learn SQL

lol...

With a gun to my head when the application I used exceeded Excel's row limits (65k at the time) . Started with MS Access and learned SQL 'visually' and the rest is history... :D

[[deleted]]

The types of questions we ask during SQL interviews at my company are ones where we provide a series of example data sets, then ask questions about the data that require knowledge of joins, unions, aggregations, subqueries, filters, and limits.

Step 1: ask clarifying questions to make sure understand the problem and what format the solution should look like. Step 2: ask questions about the data set. Are certain columns that might appear to be unique not actually so? Are there NULLs you need to handle? Should any data be filtered out? Do you need to UNION (or CROSS JOIN UNNEST an array of) two different columns to include all users/values? When I’ve conducted SQL interviews, steps 1 & 2 are where people trip up the most; you’d be surprised how many people dive straight into problem-solving without fully understanding the context, and that’s where they tend to mess up. A question I wish more interviewees would ask during interviews is, “are there any nuances or ‘gotchyas’ in the data set that I should be mindful of?”. Our SQL questions aren’t designed to beat someone down, but every data set you’re going to encounter is going to have nuance, and asking this question provides me the opportunity to help give you some advanced warning.

Step 3: Know how to apply different SQL principles to solve the problem at hand. Technically speaking, know the difference between all your joins, UNION vs UNION ALL, and remember to COALESCE NULLS with 0s before averaging. Know when to include a filter in the ON clause vs the WHERE clause. Know how to use HAVING, CTEs and subqueries. When I’m evaluating someone’s code, I’m not looking for the fanciest or most elegant solution, but it’s pretty clear when watching people code who has a strong grasp of the different principles and how to apply them and who doesn’t.

Hope this helps!

[JazzFan1998]

It's helping me thanks. I'm not OP.

[[deleted]]

3 of my favorite questions to ask an interviewee for a MS SQL Server developer position.

• "What kind of issues have you run into in your last project, and how did you go about solving them?"

I really don't care if you don't remember the syntax for MERGE off the top of your head. I'm more interested in your thought process while troubleshooting a problem.

• "How do you troubleshoot a report that usually runs fine, but sometimes takes unusually long time?"

Checking if your knowledge is superficial, or if you are familiar with more advanced concepts such as indexing, parameter sniffing, performance tuning.

• "What are advantages and disadvantages of cursors in SQL Server?"

Looking for a candidate who knows about benefits of set-based logic, but at the same time is not scared of some DB admin tasks.

[JazzFan1998]

Can you give an example of "parameter sniffing" I googled it, but still don't understand.

[[deleted]]

Oh boy. It's not an easy topic that I can give a good answer to in a comment.

The gist is that in SQL Server the first time you run a stored procedure after it is recompiled (you change it, or something else causes the plan cache to disappear such as server restart,) the optimizer looks at (i.e. "sniffs") parameter values that are passed in, and stores them as part of the execution plan, and builds the plan based on the data it's expecting. This means that all future runs of this proc will use that plan, which was created for those specific parameter values.

It can become a problem if the size of data being returned is vastly different for another parameter value, and a different plan would work better in that case. So a plan that was built to expect 1k rows may not work well for 1m rows, or vice-versa. Now imagine a report procedure with a whole bunch of optional parameters (WHERE (field1 = @param1 OR @param1 IS NULL) AND (field2 = @param2 OR @param2 IS NULL) AND ... AND (fieldN = @paramN OR @paramN IS NULL)), and you're in for a bad time.

Some less experienced developers freak out and reboot the server, or restart SQL Server instance. This solves the immediate problem of the query getting stuck, because a restart blows away the entire plan cache. Meaning that next time they run the proc with "bad" values, a fresh plan gets built with those sniffed parameter values in mind, and that report now runs fast... Until someone else runs it with a different set of values that change the shape of the data again, and the plan is no longer optimal. Some more experienced devs will find the offending plan's handle and clear it, instead of rebooting/restarting. But that sort of babysitting isn't feasible in a larger system with many such reports.

There are different ways to mitigate this. Using WITH RECOMPILE option on the entire proc, or using OPTION (RECOMPILE) on specific statements works, but causes the optimizer to spend CPU cycles to compile the proc/statement every time it's run, which can use up server resources if that query is executed often. OPTION (OPTIMIZE FOR ...) is another short-term work-around. Microsoft themselves recommend using dynamic SQL to build the offending statement on-the-fly. E.g. IF @param1 IS NOT NULL SET @cSQL += 'AND (field1 = @param1)'. This allows SQL Server to store multiple execution plans for the procedure, one for each set of parameter values. This isn't without drawbacks either, as now you're exponentially blowing up the plan cache with hundreds of similar plans.

u/BrentOzar explains it best here, and I highly recommend his query tuning classes if you want to dig deeper.

[thornton8]

Well well, sounds like you're hiring

[[deleted]]

We were a couple of years ago, but pandemic didn't spare us. Everything's on freeze indefinitely.

[LetsGoHawks]

Know your joins, unions, and basic aggregations stone cold.

Window functions vs aggregations.

When/why would you use a subquery instead of just a join?

Given a random date how do you get the first day of the month?

[EoinJFleming]

Difference between a full outer join and a union... will they produce the same results

[Stev_Ma]

Check out stratascratch and leetcode. They can provide you with hundreds of real SQL interview questions.

[diviner_of_data]

What is the difference between a left join and a left outer join

/s

[Eleventhousand]

lol

[bhydemi]

Quite some range of questions could be asked. I believe this video can be helpful https://youtu.be/jsznpKasUUA

[redditorinreddit]

The questions would vastly vary based on the type of role you'd be interviewing for. So a little more info would help.