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?

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

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 think it's best highlighted with an example. If you feel you could write the following SQL statement quickly with little/no reference material, then I'd guess that you probably meet their Advanced SQL requirement:

DECLARE @date DATETIME
SELECT @date = '10/31/09'

SELECT
      t1.EmpName,
      t1.Region,
      t1.TourStartDate,
      t1.TourEndDate,
      t1.FOrdDate,
      FOrdType  = MAX(CASE WHEN o.OrderDate = t1.FOrdDate THEN o.OrderType  ELSE NULL END),
      FOrdTotal = MAX(CASE WHEN o.OrderDate = t1.FOrdDate THEN o.OrderTotal ELSE NULL END),
      t1.LOrdDate,
      LOrdType  = MAX(CASE WHEN o.OrderDate = t1.LOrdDate THEN o.OrderType  ELSE NULL END),
      LOrdTotal = MAX(CASE WHEN o.OrderDate = t1.LOrdDate THEN o.OrderTotal ELSE NULL END)
  FROM 
      (--Derived table t1 returns the tourdates, and the order dates
      SELECT
            e.EmpId,
            e.EmpName,
            et.Region,
            et.TourStartDate,
            et.TourEndDate,
            FOrdDate = MIN(o.OrderDate),
            LOrdDate = MAX(o.OrderDate)
        FROM #Employees e INNER JOIN #EmpTours et
          ON e.EmpId = et.EmpId INNER JOIN #Orders o
          ON e.EmpId = o.EmpId
       WHERE et.TourStartDate <= @date
         AND (et.TourEndDate > = @date OR et.TourEndDate IS NULL)
         AND o.OrderDate BETWEEN et.TourStartDate AND @date
       GROUP BY e.EmpId,e.EmpName,et.Region,et.TourStartDate,et.TourEndDate
      ) t1 INNER JOIN #Orders o
    ON t1.EmpId = o.EmpId
   AND (t1.FOrdDate = o.OrderDate OR t1.LOrdDate = o.OrderDate)
 GROUP BY t1.EmpName,t1.Region,t1.TourStartDate,t1.TourEndDate,t1.FOrdDate,t1.LOrdDate

(source of query)

And to be honest, that's a relatively simple query - just some inner joins and a subquery, along with a few common keywords (max, min, case).

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)...

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

Some "Advanced" features

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

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.

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

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

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.

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.