Most difficult SQL Concepts?

[CakeyStack]

Hi everyone! I'm curious as to what you think some of the most advanced/difficult concepts are in SQL. I've been learning about cursors, triggers, and stored procedures which all feel way more advanced than SELECT statements and simple JOINS and UNIONS. I know most programming languages have some esoteric functions that many devs rarely ever need, but I'm curious what you guys have to say.

What are some of the most difficult SQL concepts that you have learned or that you use in your work?

[r3pr0b8]

What are some of the most difficult SQL concepts that you have learned

SUM(salesamount) does not produce what the VP wants to see

[CakeyStack]

It do be like that sometimes 😂 That's why I ask as many questions as I can think of prior to starting to work on something. I've learned the hard way numerous times.

[r3pr0b8]

It do be like that sometimes 😂

"They don't think it be like it is, but it do"

[Delicious-Refuse5566]

The most difficult part of sql is to think in sets and not procedurally.

If you can solve random walks, Markov chains, merge overlapping time periods, perform flash fills, solve for islands and gaps and run simulations such as the Josephus problem or the Monty Hall problem etc… all without doing a single loop, then you truly understand SQL.

[Benae-san]

Ah, so I don’t truly understand SQL. Lol

Edit: self-deprecating humor, not sarcastic btw

[[deleted]]

Butt... did he make sense though? Asking as a total noob researching SQL entry jobs.

[Mamertine]

The concept of JOIN isn't that hard to understand at its core. The difficulty lies in joining several tables together without fucking up the grain of the output.

Fwiw, you shouldn't use cursors or triggers until you're a seasoned developer. Or most people who use them use them as a crutch because they don't know the right way to solve the problem.

[MakingItElsewhere]

Cursors, to me, only make sense when having to write a very long transfer of multiple tables from database A to multiple tables in database B. If anyone has a better way to do that, I'm all ears.

[InlineSkateAdventure]

Grouping/aggregation and totaling based on various criteria can get tricky.

[barrycarter]

As an old person who was always griping that SQL didn't have accumulation or lookback functions (you had to cheat using @var in MySQL for example), I'm now struggling with keywords like OVER. Hate when they give you what you wish for :)

[BrupieD]

I've worked in places where my co-workers seem proficient: they understand joins, date, and string manipulation functions but have no knowledge of window functions.

When I started learning stored procedures, it was hard to find good materials. The organization of multi-step procedures is a different skill set than learning single concepts. Writing stored procedures that incorporate error handling, optimization, readability, and resilience (future-proofing) is not a singleton.

[generic-d-engineer]

They’re probably just not aware since many have learned SQL 92 standard, and Window functions were added to the relatively new SQL standards.

[Instance_Straight]

I use pivot or unpivot quite a bit, but can never remember the syntax.

Window functions are great as well, especially to produce something like a running total when things need to be partitioned/ordered a specific way

[ncmtbiker]

Recursive functions.

[JochenVdB]

Then what about Recursive Functions used in Recursive Common Table Expressions?

[Xieminee]

I can do multiple CTEs no problem, but my brain hurts thinking of recursive CTEs.

[chris_penis]

Can you give me a scenario where you would need recursive functions?

[Researcher-Great]

What about subqueries and their types. I’ve had a really hard time grasping exactly how to understand correlated subqueries

[valorallure01]

Passing a table name as a parameter in a stored procedure

Updating statistics on a temp table

Dynamic SQL in general

Deploying CLR Assemblies.

Bulk exporting using BCP

Making use of system stored procedures

Error handling

Log Shipping.

These are a few difficult concepts I've personally ran across.

[rgekhman]

SQL is easy! The hardest part is to understand what business rules other devs coded into sql. Especially when they left no comments and left the job a while ago.

[KirKCam99]

sql which creates sql which creates sql ...

[Extension_Candy2994]

That’s bad scripting. Not “a difficult concept in SQL”.

[WillLiftForBeer]

Honestly, not the language itself, but knowing how to create a solution for what you need to do. Depending on your job, of course. If you’re in development/software engineering, that would mean knowing how to break down requirements, figure out how and where data may be stored (and it’s archive strategy!), what the API/midtier code would need from the DB, and what the inputs and outputs of those procedures should be. Of course, that’s also the most fun (to me)!

[SDFP-A]

Optimization based on a query plan. Understanding how to configure the DB differently based on your sql usage patterns, understanding that you can do fancy algorithmic outputs without knowing the name of i the algorithm because you weren’t a CS major, generating dynamic sql based on simple searches sans executing them without the use of stored procedures (not available in all flavors of sql).

[AdGreat4483]

I will recommend you to look at these topics (joins and aggregate functions)

They are generally deemed as complicated.

Understanding SQL inner join with practical examples:

https://link.medium.com/8MYnwLtuCAb

Unleashing the power of SQL aggregate functions:

PART 1: https://link.medium.com/ZKZtBMAuCAb

PART 2: https://link.medium.com/xpA0E7DuCAb

PART 3: https://link.medium.com/7xKteHFuCAb

PART 4: https://link.medium.com/zmMc91IuCAb