The biggest killers (vs. postgres): no simple helpers (generate_series, for example, is achievable with simple, efficient recursion, but you have to write the whole statement); a truly antiquated client program (sqlplus); built-in json, but you do have associative arrays/hash maps and nested tables, so you can achieve exactly the same thing, and it's indexable*; and range types (meh imo).
Basically WITH-AS lets you declared temporary named subqueries. That is, a pretty stock standard programming technique -- give a name to a fiddly expression to simplify subsequent code. Very useful.
Oracle has lots of annoyances though. No SERIAL or IDENTITY type. Having to write the same boilerplate sequence/insert trigger/update trigger over and over and over is very tedious and a recipe for subtle errors when you forget one of them or mess it up.
No boolean type in SQL. I don't even know what to say about that.
Tom ties himself into unbelievable knots trying to defend this. It's the purest form of technical Stockholm Syndrome I've ever seen from a putatively intelligent person.
http://www.oracle.com/technetwork/developer-tools/sql-develo...
http://www.firebirdsql.org/en/sql-conformance/
Whenever I think of Firebird, i get a little bit of a bad conscience because I've never really played with it :-)Time intervals are also in the SQL standard as I recall (although not in the format listed here - postgres supports standard syntax too though). Not sure how fully supported it is cross-database, though.