What is "Advanced" SQL?

Question

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