What is advanced SQL?

Question

Review job descriptions where “advanced SQL” is required. I can write basic queries, like anyone else, and work with MySQL databases in a professional environment, but what would I get with these tasks if I were hired? What are examples of extended SQL, and where am I on the SQL noob scale for SQL master?

Answer 1

I think this is best illustrated by an example. If you feel that you can quickly write the following SQL statement with little / no reference material, I would suggest that you probably meet their Advanced SQL requirements:

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 

(request source)

And, frankly, this is a relatively simple query - only some internal joins and a subquery, as well as a few common keywords (max, min, case).

Answer 2

The basics

---

• SELECT columns from a table
• Aggregates Part 1: COUNT , SUM , MAX / MIN
• Aggregates Part 2: DISTINCT , GROUP BY , HAVING

Intermediate

---

4. JOIN s, ANSI-89 and ANSI-92 syntax
5. UNION vs UNION ALL
6. NULL Processing: COALESCE and Native Processing Native
7. Subqueries: IN , EXISTS and Inline Views
8. Subqueries: correlated
9. WITH Syntax: Subquery Factoring / CTE
10. representation

Additional topics

---

• Functions, Stored Procedures, Packages
• Pivot: CASE and PIVOT syntax
• Hierarchical queries
• Cursors: Implicit and Explicit
• Triggers
• Dynamic SQL
• Materialized views
• Query Optimization: Indexes
• Query Optimization: Explaining Plans
• Query Optimization: Profiling
• Data Modeling: Normal Forms, 1 to 3
• Data Modeling: Primary and Foreign Keys
• Data Modeling: Table Limitations
• Data Modeling: Link / Corrollary Tables
• Full text search
• XML
• Isolation levels
• Entity Relationship Diagrams (ERDs), logical and physical
• Transactions: COMMIT , ROLLBACK , Error Handling

Answer 3

The rest of the job opening list may provide context to better understand what "Advanced SQL" might include.

I disagree with comments and answers indicating that understanding JOINs and aggregated queries are “advanced” skills; I'm afraid many employers find this pretty simple. Here's a rough guess what "Advanced" may mean.

Over the past few years, the “terrible” lot of new things has appeared in the RDBMS domain!

The “Advanced SQL” requirement probably hints at the knowledge and possibly knowledge of several new concepts , such as:

• CTE (common table expressions)
• UDF (custom functions)
• Full Text Search / Integration Extensions
• performance tuning using new partitioning schemes, filtered indexes, sparse columns ...)
• new data types (e.g. GIS / spatial or hierarchical)
• XML Support / Integration
• LINQ
• and a few more ... (By the way, the above list is somewhat oriented to MSSQL, but a similar evolution is observed on most other DBMS platforms).

Although supporting (and cons) new functions is an important task for any "advanced SQL" practitioner, the old "advanced basics" are probably also considered part of the "advanced" :

• triggers and stored procedures on large
• Cursors (when to use, how to avoid ...)
• design experience: definition of tables, indexing, type of indexes
• overall performance tuning experience
• query optimization (reading query plans, knowing what is essentially slow, etc.).
• Procedural SQL
• ...

Note: The aforementioned focuses on programming / lead skills. Advanced SQL can also refer to experience with administrative roles (replication, backup, hardware layout, user management ...). Think about it, a serious programmer should be familiar with such practices.

Edit : LuckyLindy posted a comment, which I found rather insightful. This suggests that Advanced can effectively have a different purpose than implying a fair expert level in most of the categories listed above ...
I repeat this comment here to make it more visible.

I think many companies publish Advanced SQL because they are tired of having someone say, “I am an SQL expert,” and it’s hard for me to assemble 3 external external connections. I publish such materials in the transaction entries, and my expectation is simply that the candidate does not need to constantly contact me for help in writing SQL. (LuckyLindy comment)

Answer 4

I would expect:

• creating and using stored procedures
• combines (internal and external) and how to use GROUP BY
• performance evaluation / tuning
• knowledge of effective (and ineffective) ways to perform actions in queries (understanding how certain things can affect performance, for example, using functions in WHERE clauses)
• dynamic SQL and knowledge of cursors (and IMO they should be used several times)
• understanding of schema design, indexing and referential integrity

Answer 5

Give up SQL for Smarties . I thought that I also knew SQL well until I read this book ... Goes into tons of depth, talks about things that I have not seen elsewhere (the difference in IE between the 3rd and 4th normal form , Boyce Codd Normal Form, etc.) ...

Answer 6

Some "advanced" features

• recursive queries
• window / ranking functions
• pivot and univot
• performance tuning

Answer 7

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

Answer 8

I assume that subqueries and PIVOT will qualify, as well as several associations, unions, etc.

Answer 9

Performance tuning, creating indexes, stored procedures, etc.

“Advanced” means something different for everyone. I would suggest that this type of thing means something different for each poster.

Answer 10

When you see that they are indicated in the requirements, they usually include:

• representation
• Saved Procedures
• Custom functions
• Triggers
• sometimes cursors

Internal and external joins are required, but I rarely ever see this in requirements. And it is amazing how many so-called db professionals cannot get their heads around a simple external connection.

Answer 11

At my previous job, we had a technical test for which all candidates were asked to sit. 10 questions, took about an hour. However, despite all the honesty, 90% of the failures could be dismissed because they could not write the INNER JOIN instruction. Even external.

I would consider the prerequisite for any job description using SQL to be necessary and leave myself alone until it is mastered. From there, talk to them - any additional information about what they are actually looking for, in the worst case, will be a useful list of things to learn as part of your professional development.

Answer 12

"Advanced SQL" is a contradiction with the terms.