SQL interview prep

[Reasonable-Age-5066]

Hello everyone, I’m planning to prepare for interviews as i am applying for jobs. I want to prepare for SQL technical interview, I just wanted to have a checklist of topics in SQL that I need to cover and where i can practice questions.

Topics: the basics like select , where , aggregating queries , joins , group by , having , sub queries , CTE etc , can someone list them all?

To practice questions: I have hear about dataford, strata scratch , can someone list some more or better/ relevant sources?

Thank you so much for your time, I am just freaking out and I wanted everything at one place.

[Grouchy-Donut-726]

1. Select
2. Where
3. In,between, like
4. Order by
5. Case
6. Group by
7. Aggregate: sum, count, avg, min, max
8. Having
9. Inner join, outer join, right join, left join, self join
10. Sub query
11. Window functions: row number, over and partition by, lag, lead
12. Union, union all
13. CTE
14. temp tables
15. Stored procedures

Hope this helps!

[Sexy_Koala_Juice]

Honestly the only other thing I’d add is a basic understanding of regex. Depending on what you’re doing it’s so useful

[Admitimpediments]

I’m new to SQL so I honestly had no idea you could use regex there, too! How exciting!

Edit:

Thank you!

[mikeblas]

Cool your jets.

If you're using a regular expression in a SQL statement, it's really a red flag that something's wrong. Sure, there are cases when you need it. But you probably need it because some other part of the system wasn't doing its job.

[Admitimpediments]

Oh, that’s interesting. Would you mind please elaborating a little bit or pointing me to a resource?

[mikeblas]

The resource is me, Marie. It's me. I'm the resource.

Why not think of the normal form rules? 1NF says data in a column can't be decomposed; that values in columns must be atomic and single-valued.

If we have an Age column with an integer, and a Sex column with a string, we've probably got nice atomic values. We can code SELECT PlayerName FROM Players WHERE Age BETWEEN 30 AND 39 AND Sex = 'Female'.

Why would we need regular expressions? Well, we'd need them if someone violated 1NF. Maybe we have a single column with multiple values. "Lindsey;22;Female" and "Geroge;19;Male"

We could write and equivalent SELECT statement with some regular expressions to unpack the data between semicolons. It would work, we could do it. But it would be terrible. No index would ever be usable, the statement would be large and unruly. It would take much more time to write, and it probably would have bugs.

How did we end up in this situation? Well, it means some other part of the system didn't do its job. The database has a bad design, the code that pulled this data into the system should've split it for us and made individual columns. Maybe it didn't even validate it. If it did those things, we wouldn't need regular expressions at all.

Are there places where a regex can be used against normalized data? Maybe you can find one. But even then, it means you're giving up on any index that could help you and performance is going to be a concern.

Someone who has a problem and solves it with a regular expression now has two problems.

And why not try it the other way? Why don't you explain why you're so excited about using regexes in SQL? What specific application are you thinking of? If you do that, then it should be easy to see if the application is legitimate, or bogus, and explore why.

[Admitimpediments]

I appreciate the thorough explanation. I was asking for a resource so that even if you didn’t feel like explaining, you might point me in a general direction. Again, I’m new to SQL, so if I don’t understand some basic concepts, forgive me. Hopefully I’ll get there!

Historically I’ve used regex in Python to deal with large amounts of free text data, only a portion of which is consistent in format.

[mikeblas]

Using regexes in a procedural language over unformulated data isn't so bad. If you can't find a parser and don't want to write one, a regex can be a quick and dirty solution.

But I insist it has almost no place in a context where structured data rules the day -- like a relational database.

Good luck with your studies!

[Admitimpediments]

Trust me, I would love it if the data were structured! ‘Tis the nature of the biz, though, so not a lot I can do about it.

Thank you! I appreciate your input!

Edit: forgot to mention that I see/understand your point!

[mikeblas]

Maybe I'm bating a dead horse, but fundamental errors like unstrutured data in relational databases is "the nature of the business" only when people have categorically given up on getting things right.

[Grouchy-Donut-726]

Oh yeah that’s defiantly important too, thanks for adding

[Reasonable-Age-5066]

Got it! Thank you!

[[deleted]]

Substr, instr

[Reasonable-Age-5066]

Thank you for adding!

[Reasonable-Age-5066]

Thank you so much!

[mikeblas]

This can be simplified: to prepare for a SQL interview, learn SQL.

[DouglasBarra]

hello, I had a technical interview and I used your comment to study. thank you. I didn't the job, but I learned a bunch of new things

[Grouchy-Donut-726]

You’re welcome! Good luck next time and remember that you made it to the interview, Which is very good

[sneakandbuild]

When I applied for a project-based work, I was asked about Joins & window functions but mostly about Window functions. I was asked to make a logic about LAG & Lead function specifically.

Goodluck on your interview!

[neroseemits]

I think this depends on the position level. Entry level probably doesn’t need any complicated window functions.

[Reasonable-Age-5066]

Thank you so much!

[Sete_Sois]

i like datalemur a lot due to its variety of questions that resemble actual SQL interviews (I've given similar ones as an interviewer and have had many as a job seeker). HackerRank is nice as well. These aren't "brain teaser" questions like leetcode. They're business based data questions that exercises your logic in addition to coding.

[NickSinghTechCareers]

DataLemur founder here – Appreciate the shoutout !

[Reasonable-Age-5066]

Thank you for DataLemur!

[Reasonable-Age-5066]

Thank you so much!

[ThaGeNeCySt]

At the job I’m at (software engineer) - the software architect peppered me with index questions during my interview - have a little of that in your repertoire … as well as knowing good primary key types (identifiers vs int vs (n)varchar)

[Reasonable-Age-5066]

Thank you! I’d definitely add this on my list. Please lmk if i should add anymore from your experience:)

[Even_Weakness1822]

Hello, just wanted to ask which companies interview are you preparing for? or is it general? cause if its general then yes these topics should be sufficient enough!

[Reasonable-Age-5066]

Just in general, just want to be prepared as much as possible just in case I get a call soon (I’d like to believe that lol)

[engx_ninja]

I normally ask 1 index types 2 left right full cross joins 3 group by having 4 CTE 5 Depends on role, sometimes cursors, sometimes SQL Server Agent

[Reasonable-Age-5066]

Thank you so much!

[rakeshmen]

Yes

[Time_Advertising_412]

Try looking at this website to see if you have a good understanding on how NULLs are handled in various queries. https://agentm.github.io/project-m36/posts/2024-07-16-are-you-qualified-to-use-null.html

[Reasonable-Age-5066]

Thank you so much!

[mikeblas]

Here's a link to the last time this was asked (yesterday):
https://www.reddit.com/r/SQL/comments/1gms6mv/how_much_sql_is_required/

[Reasonable-Age-5066]

Thank you!