[sql] What is "Advanced" SQL?

Looking at a job descriptions where "advanced SQL" is a requirement. I can write basic queries as well as anyone, and have worked with MySQL databases in a professional setting, but what would I be getting into with these jobs, if I were to be hired? What are examples of advanced SQL and where am I along the scale of SQL noob to SQL master?

When you see them spelled out in requirements they tend to include:

• Views
• Stored Procedures
• User Defined Functions
• Triggers
• sometimes Cursors

Inner and outer joins are a must but i rarely ever see it mentioned in requirements. And it's surprising how many so-called db professionals cannot get their head around a simple outer join.

The rest of the job opening listing could provide context to provide a better guess at what "Advanced SQL" may encompass.

I disagree with comments and responses indicating that understanding JOIN and aggregate queries are "advanced" skills; many employers would consider this rather basic, I'm afraid. Here's a rough guess as what "Advanced" can mean.

There's been an "awful" lot of new stuff in the RDBMS domain, in the last few years!

The "Advanced SQL" requirement probably hints at knowledge and possibly proficiency in several of the new concepts such as:

• CTEs (Common Table Expressions)
• UDFs (User Defined Functions)
• Fulltext search extensions/integration
• performance tuning with new partitionning schemes, filtered indexes, sparse columns...)
• new data types (ex: GIS/spatial or hierarchical)
• XML support / integration
• LINQ
• and a few more... (BTW the above list is somewhat MSSQL-centric, but similar evolution is observed in most other DBMS platforms).

While keeping abreast of the pro (and cons) of the new features is an important task for any "advanced SQL" practitioner, the old "advanced fundamentals" are probably also considered part of the "advanced":

• triggers and stored procedures at large
• Cursors (when to use, how to avoid ...)
• design expertise: defining tables, what to index, type of indexes
• performance tuning expertise in general
• query optimization (reading query plans, knowing what's intrinsically slow etc.)
• Procedural SQL
• ...

Note: the above focuses on skills associated with programming/lead role. "Advanced SQL" could also refer to experience with administrative roles (Replication, backups, hardware layout, user management...). Come to think about it, a serious programmer should be somewhat familiar with such practices as well.

Edit: LuckyLindy posted a comment which I found quite insightful. It suggests that "Advanced" may effectively have a different purpose than implying a fair-to-expert level in most of the categories listed above...
I repeat this comment here to give it more visibility.

I think a lot of companies post Advanced SQL because they are tired of getting someone who says "I'm a SQL expert" and has trouble putting together a 3 table outer join. I post similar stuff in job postings and my expectation is simply that a candidate will not need to constantly come to me for help writing SQL. (comment by LuckyLindy)

I suppose subqueries and PIVOT would qualify, as well as multiple joins, unions and the like.

Check out SQL For Smarties. I thought I was pretty good with SQL too, until I read that book... Goes into tons of depth, talks about things I've not seen elsewhere (I.E. difference between 3'rd and 4'th normal form, Boyce Codd Normal Form, etc)...

At my previous job, we had a technical test which all candidates were asked to sit. 10ish questions, took about an hour. In all honesty though, 90% of failures could be screened out because they couldn't write an INNER JOIN statement. Not even an outer.

I'd consider that a prerequisite for any job description involving SQL and would leave well alone until that was mastered. From there though, talk to them - any further info on what they're actually looking for will, worst case scenario, be a useful list of things to learn as part of your professional development.

Performance tuning, creating indices, stored procedures, etc.

"Advanced" means something different to everyone. I'd imagine this type of thing means something different to every job-poster.

Basics

1. SELECTing columns from a table
2. Aggregates Part 1: COUNT, SUM, MAX/MIN
3. Aggregates Part 2: DISTINCT, GROUP BY, HAVING

Intermediate

4. JOINs, ANSI-89 and ANSI-92 syntax
5. UNION vs UNION ALL
6. NULL handling: COALESCE & Native NULL handling
7. Subqueries: IN, EXISTS, and inline views
8. Subqueries: Correlated
9. WITH syntax: Subquery Factoring/CTE
10. Views

Advanced Topics

• Functions, Stored Procedures, Packages
• Pivoting data: CASE & PIVOT syntax
• Hierarchical Queries
• Cursors: Implicit and Explicit
• Triggers
• Dynamic SQL
• Materialized Views
• Query Optimization: Indexes
• Query Optimization: Explain Plans
• Query Optimization: Profiling
• Data Modelling: Normal Forms, 1 through 3
• Data Modelling: Primary & Foreign Keys
• Data Modelling: Table Constraints
• Data Modelling: Link/Corrollary Tables
• Full Text Searching
• XML
• Isolation Levels
• Entity Relationship Diagrams (ERDs), Logical and Physical
• Transactions: COMMIT, ROLLBACK, Error Handling

I would expect:

• stored procedure creation and usage
• joins (inner and outer) and how to correctly use GROUP BY
• performance evaluation/tuning
• knowledge of efficient (and inefficient) ways of doing things in queries (understanding how certain things can affect performance, e.g. using functions in WHERE clauses)
• dynamic SQL and knowledge of cursors (and IMO the few times they should be used)
• understanding of schema design, indexing, and referential integrity

SELECT ... HAVING ... is a good start. Not many developers seem to understand how to use it.

Some "Advanced" features

• recursive queries
• windowing/ranking functions
• pivot and unpivot
• performance tuning