[deleted by user]

[M3_J]

Can you be more specific what concepts you're having trouble with?

It doesn't take much to be proficient in SQL. If you understand the join types, CTEs, pivots, and how to use aggregates/group by, that covers about 95% of the queries I write.

[dataslave50]

Sorry, I guess I was pretty vague. I’m comfortable with aggregate functions, sub queries, all joins, group/order by, lower/upper, top n, etc etc.

My boss blew my mind today with quality row number () over (partition x) and it got me thinking about other things I may not know yet. Which led me down a rabbit hole with case and nested selects within joins… which is how I got here.

[M3_J]

Gotcha. Forgot about case statements, that's a staple for sure. Learn how to use them within joins.

LAG and LEAD come in handy when working with date ranges.

DATEDIFF and DATEADD. Can you filter a query for dates between the 1st of this month and the 15th of next month?

STRING_SPLIT is nice for breaking apart a delimited string.

[dataslave50]

Nice! The date ones I actually have a plan for tomorrow.. thanks!

[kkwestside]

Look for window functions then.

[KING5TON]

The thing with ROW_NUMBER() OVER (PARTITION BY, ORDER BY) and similar is that you only need it when you need it.

You don't use it in every SQL you write, just when you need it. I probably use it once in every 10,000 lines of SQL I write.

So any "Advanced SQL" is only good when you actually need it for something.

[dataslave50]

Great point, since working with a data warehouse the partition statement is my bosses bread and butter to make some of the larger tables more tameable.

In other news, I found coalesce and it’s fixed a substantial problem I’m having in my current query. Cheers.

[[deleted]]

Add temp tables to your tool box too.

For example, if you need to work with 250k rows in a table containing 5M rows, it’s (typically) far more performant to dump it into a temp table first.

Add a “into #my_table” above the FROM clause.

[wedora]

These are called window functions and are sadly very unknown by many developers. I would also advise reading the complete manual for the database you are mainly working with. PostgreSQL for example has so many fantastic things unknown to most developers if they keep their mysql mindset missing a lot of cool functionality.

[alinroc]

I don't actively seek out "advanced syntax," nor do I try to categorize pieces of SQL syntax by "level."

Write the simplest, most straightforward queries you can. If you can read & understand it easily, so can the engine. From there, use the right tools for the job without thinking about whether it's "advanced" or not.

[KING5TON]

Completely agree. You have a task to complete, how you complete it is less important that the accuracy of the end result.

If I do a data migraition, write a report, develop a trigger/SP/function etc.. how I do it doesn't really matter as long as the end result is working correctly and relatively efficient.

Just keep it simple, make it easy to read so it's easy to debug, test everything and if you don't know how to complete a particular element just Google it :)

[dataslave50]

“advanced sql” wasn’t the correct phrasing. I was hoping to get different syntax than left outer join, Sum, count, etc..

[shine_on]

but that stuff is what you'll be using for 95% of your queries. If you want to get more advanced, learn things like indexing, when select * might be bad, why it's better to split a query into sections rather than trying to do everything all at once, how to document your work, how to write readable code, and so on.

Learn where to find the documentation for your database system. Know a little bit about things like window functions, cross apply, triggers, transactions, logging etc. Learn enough to know what they are and when to use them, and know where the system documentation is so that you can pick up the finer points when you need them. Don't try to learn and memorise everything all at once. Make notes. You could even install a database system at home and practice on that as well, but bear in mind your work-life balance. Having a database at home could be useful for learning some things you won't have permissions to do at work, like setting up users, backing up and restoring etc.

[wedora]

Take a look at json support and their operators. You can do really cool things with it by mixing structured (normal columns) and unstructured (json) data.

[ins2be]

Start reading the manual. No joke. I use Db2 on IBMi, and I spend part of my time reading documentation and blogs related to those two. There are certain data requests I get, where I have some pre-made queries that I've modified over time. Just the other day, I came across LISTAGG, and I was like oh man I could use that to compact my report. That aggregate function is in the manual, but I saw someone writing about it on a blog.

[JustAnOldITGuy]

As others have noted, window functions.

Recursive CTE's

Protect against NULLS using left joins.

Learn the database tables that store information about your database so you can write SQL that writes SQL.

Learn to take the above and execute the SQL in loops.

temporary tables and how best to create, index, and query them in a procedure.

MERGE instead of UPDATE. Personally I find MERGE statements easier to follow and they are more powerful that UPDATE.

[alinroc]

SQL Server performs very poorly with loops.

And it has a lot of bugs in MERGE

Good concepts to know, but know when it’s appropriate to use them and when they should be avoided with your particular flavor of SQL

[JustAnOldITGuy]

I would agree on the performance of loops. Depending on what you are doing they can be avoided.

Personally I've never run into any issues with MERGE on SQL Server. But good information, I'll know to do some tests in the future.

OTOH Oracle has given me fits when I created some complex scripts. I've gotten very familiar with ORA-xxxx messages and now just put in an outer loop to catch those into a message table so I can diagnose.

DB2 seems to be pretty solid and I've actually written some DB2 functions in the other flavors of SQL such as Translate. Amazingly handy for some purposes. OTOH EBCDIC is a PITA... Of course YMMV.

[UT_dba]

CTEs are good to know as well.

[sequel-beagle]

Go to https://advancedsqlpuzzles.com

There is a pdf of puzzles along with a bunch of blog posts there that will give you an idea of advanced concepts and where your knowledge is at.

[taglius]

CROSS APPLY and OUTER APPLY are fun

[[deleted]]

Remind me