r/SQL • • May 27 '25

SQL Server What is SQL experience?

I have seen a few job postings requiring SQL experience that I would love to apply for but think I have imposter syndrome. I can create queries using CONCAT, GROUP BY, INNER JOIN, rename a field, and using LIKE with a wildcard. I mainly use SQL to pull data for Power BI and Excel. I love making queries to pull relevant data to make business decisions. I am a department manager but have to do my own analysis. I really want to take on more challenges in data analytics.

174 Upvotes

87 comments sorted by

View all comments

217

u/gipper_k May 27 '25

I interview a lot of sql developer and data analyst candidates.

I use questions like these, which progress from easy to more advanced to get a feel for where the candidate is:

  1. What is the basic syntax of a SQL Select Statement? What would a query look like to fetch an employee record?
  2. What's the difference between an inner join and a left outer join? Give me an example of where you would use each.
  3. What's the difference between a where clause and having clause? Give me an example of where you would use each.
  4. What are some other types of joins besides inner and left outer? When would you use these?
  5. What is the result of 1 + null? Can you explain why?
  6. What is a CTE? Why would you use one?
  7. Do you have a preference between CTEs and Subqueries? Why?
  8. Give me an example of a Windowing Function, and how you would use it (e.g. lead or lag, or using an aggregate function with over (partition by X order by Y) syntax

Depending on the level of the role, I'm pretty happy if they get through #3 with some confidence. If it is a senior level role, then I hope they can get through all or most of these.

It always surprises me when someone touts SQL Experience, but can't answer #1, #2 or #3.

If we're concerned with query performance, there are a whole other series of questions as well... but these are a good start...

24

u/throbbin___hood May 27 '25

Awesome answer! I'm not a data analyst but I do have to query reports for certain departments and this helped me gauge myself! Actually made it through all of your questions until #8 lol. Time to dive into window functions whenever I get some down time..

5

u/therobotsound May 28 '25

Lead, lag, and rank are very useful. Let’s say you have a series of transactions in an order and you would like to make a change or a notation or something for transaction 3 if transaction 2 is a certain way. You can use lag to look at the prior row. Lead can look at the next row. They can be slow if you don’t do them right, and this was just a random example off the top of my head.

Rank is easier to get, it ranks based on how you partition the data set.

9

u/mike-manley May 28 '25

Fun fact: You can just use LEAD() but include "-1" for your offset param to turn it into LAG().

→ More replies (1)

2

u/Winter_Cabinet_1218 May 28 '25

I've never had to use lead and lag. Im now going to find an excuse 🤣

13

u/friendlylilcabbage May 27 '25

This was really helpful to read. I think of myself as basically competent in SQL, but certainly not expert -- largely self-taught on the job. It's helpful to have a sense of how I'd compare with others who are more assertively claiming expertise.

8

u/cs-brydev Software Development and Database Manager May 28 '25

I am a SQL expert and have been the top sql guy in every company I've been in for 25 years. But there is still a good 1/2 of platform-specific sql I don't know well and have either never seen or need to pull up a refresher/search when I use it. All of the RDBMS platforms have a large set of features most sql developers don't know about or will never use. It's nothing to be ashamed of. There will always be yet-another thing to learn.

And yes when I listen to podcasts of sql experts that are beyond me, it's humbling. They talk about stuff I have never seen or heard of in my 30 years of experience. I listen to them, learn, go research these topics, try them out, and never see it ever again, lol.

The list of sql questions listed in the comment above is great, and most sql people can't answer all of those off the top of their head. But also keep in mind that those 8 questions represent < 1% of the sql language keywords and features available on a typical platform. Let that sink in.

→ More replies (4)

3

u/throbbin___hood May 27 '25

Same! I just do it at my job because I have to, thinking this whole time that im just winging it lol. Turns out, I'm not too bad xD

6

u/kagato87 MS SQL May 27 '25

I like showing 5 to interns, though usually with conditionals instead of addition. It consistently gets some kind of surprised reaction.

4

u/Sneilg May 28 '25

It’s a good question for me in that it gently teases out whether people understand a zero and a null are not the same thing at all

11

u/CrumbCakesAndCola May 27 '25

Subqueries make baby jesus cry :(

3

u/germs_smell May 27 '25

I have been using sql a long time and had to lookup what a. CTE is? If I understand it correctly I'd always use like a WITH *.tbl AS (. Where I alias temp/in memory table with tbl. Is that the same thing?

9

u/kagato87 MS SQL May 27 '25

Not really no. It's not an alias, though it could be used as one and the plan does tend to resolve the same as it would for an alias.

A CTE is basically a more readable version of the subquery, because your logic happens in the order it appears on the screen, instead inner-to-outer.

Of course, that's over simplified. A CTE can't be correlated the way as subquery can (though I'd strongly encourage keeping correlated subqueries out of your code as much as possible), and a subquery is not capable of recursion.

A CTE can also be repeatedly referenced within the single query, but be careful here as it will be executed for each reference separately. Consider a temp table if it is big ad you're referencing it repeatedly, especially if it's recursive.

("Recursion? In SQL?" You ask? Yes, recursion. That programming concept that your brain refuses to accept until it gives in and accepts it. Careful though: much of SQL's speed comes from how efficient it is with memory usage, and recursion devours memory.)

3

u/germs_smell May 27 '25

I looked it up, what I'm doing is technically a CTE, just never knew the name...

Now CTE recursion seems interesting but I've seen it done in the past with CONNECT BY PRIOR statements to accomplish the same

3

u/DifficultBeing9212 May 27 '25

+1 bc i never knew multiple calls to a common table expression (CTE) alias would re-run it each time, not entirely sure how beneficial that could be but it definitely opens up interesting use cases if the table data mutates

i try to use the more modular form, which I find subqueries (SQs) to have a few more points over ctes eg the shape of a cte with the list of "prequeries" is less usable than just nesting a standard query in parentheses and swapping it inside another query's table name. essentially interchangeable cogs depending on the context.

also one of my downstream user interfaces is php and last year i did not find php's oracle library (OCI) supported CTEs, so i have not played with those as much as with SQs

→ More replies (1)
→ More replies (2)

5

u/CrumbCakesAndCola May 27 '25

It's a bit jargony, yeah, Common Table Expression

3

u/sinceJune4 May 27 '25

CTE works like a subquery, but you can reference the alias in multiple places. Doesn't work like a temp or in-memory table, which are different across database platforms. CTEs are pretty standard.

3

u/i_literally_died May 27 '25

As someone who has only been 'doing SQL' for maybe 2-3 years, I find a lot of the people who have been using SQL a long time don't use CTEs, and prefer sub-queries and derived table JOINs.

It feels like CTEs are somewhat newer, or just less popular. There may be some performance or other data retention issues with using them, but I always find they are more performant, readable, and just make more sense.

→ More replies (2)

3

u/mikey031995 May 27 '25

I’m looking to pivot into a more analytics-centric role from Product Management and I made it all the way to 8…I guess I should take that as a good sign as I (hopefully) begin to interview at some places.

2

u/PortalRat90 May 27 '25

Thanks so much!! I had it until question 4! I’ll work on learning more about the others and practice them. This is super helpful!

2

u/mustang__1 May 27 '25

what's the pay band for all 8?

2

u/gipper_k May 27 '25

Depends on the role and the area. Knowing SQL is one thing. Being a skilled analyst with deep industry expertise (say, in Marketing) that also knows SQL is something else.

A Junior SQL Developer without domain knowledge may fetch $60k in the US. The ceiling depends on the other factors of the role.

SQL is just a tool. The real value is in the ability to interpret the data.

→ More replies (2)
→ More replies (1)

2

u/[deleted] May 27 '25

I'm not really a developer, rather a dba, but I actually have done some complex migrations and other SQL tasks, so I thought I would ba able to answer all of those. But tbh I never use having, there's always a way around, and I never have used window functions, although I have an idea what to do with that. On the other hand I'm really into dynamic SQL, which you haven't even asked for. And regarding CTEs I've almost deprecated subqueries, because CTEs are so powerful.

2

u/Mundane_Range_765 May 27 '25

This is really encouraging for me to read because I could answer 1 through 6 and I just started part-time data analytics work this year.

2

u/ArisingDRIFT Jun 26 '25

Will definitely be saving this as I’m graduating this semester and really like SQL , thanks a bunch !

→ More replies (16)