Looking For Hard-Core SQL Books or Websites

[choppedwood]

Hi,

I'm looking for a place to find SQL (preferably Oracle but it doesn't have to be) that deals with exotic things.  Basically, things that you, mostly, never do, would never think of doing, but could do.  Books or websites would be fine.  Thanks for the help.

For example the kinds of things I'm after are:

update (select c1, c2, c3 from table1 t1)
set c1 = (select avg(t2) from table2 t2 where t1.c2 = t2.c2)
where t1.c3 > any (select t3.c3 from table3 t3 where t3.c2 = t1.c2);

I didn't test that but it should work.

Or,

Select c1, c2, c3
from sometable
order by (select c1 from dual);

Which appears to work.

compared to

select c1, c2, c3
from sometable
order by (select 1 from dual);

Which doesn't work but doesn't throw an error.

compared to

select c1, c2, c3
from sometable
order by 1;

Which works exactly as I'd expect it to.

[pxsant]

Joe Celko has some pretty good books on SQL including "SQL for Smarties".    He has several other books on the subject also.  I know they are available on Amazon but I believe they are also available as PDF's.  Best bet is to do a search for either the title or author's name.

I purchased one from eBay  a couple of years ago at a fraction of the list price.

[choppedwood]

I'm familiar with him but the reviews at Amazon kind of steered me away.  PDF's and used, however, would be fine.

Thanks!

[Walter Mitty]

Hi,

I'm looking for a place to find SQL (preferably Oracle but it doesn't have to be) that deals with exotic things.  Basically, things that you, mostly, never do, would never think of doing, but could do.  Books or websites would be fine.  Thanks for the help.

For example the kinds of things I'm after are:

update (select c1, c2, c3 from table1 t1)
set c1 = (select avg(t2) from table2 t2 where t1.c2 = t2.c2)
where t1.c3 > any (select t3.c3 from table3 t3 where t3.c2 = t1.c2);

I didn't test that but it should work.

Or,

Select c1, c2, c3
from sometable
order by (select c1 from dual);

Which appears to work.

compared to

select c1, c2, c3
from sometable
order by (select 1 from dual);

Which doesn't work but doesn't throw an error.

compared to

select c1, c2, c3
from sometable
order by 1;

Which works exactly as I'd expect it to.

I have to admit that your examples look somewhat opaque to me.  And I made a living off of SQL in the late 80s and most of the 90s.  Are you trying to bend the language into doing something it wasn't meant to do?  Or are you trying to overcome the consequences of a lousy database design?  Or are you just looking for clever hacks? 

I never read SQL for smarties, but I did interact with Joe Celko in some online forums.  He's professorial and opinionated, and that can be hard to take.  But I expect the SQL for Smarties is still a worthwhile read.

PS:  I think one of your examples is born to lose.  Do you want to discuss it?

[The Gorn]

Given your statement of your interests, SQL for Smarties is what you want.

It is a book of "brain teasers" and extreme, very insightful examples of SQL use.

I have a lightly used copy that I will probably never open again, I'd love to find a good home for. I will sell it for $35 shipping included. PM me for information.

[choppedwood]

Walter,

No need.  I wasn't trying to produce a specific result, instead I was trying to get as exotic as I possibly could and produce a result.  You can, in fact, write an update like that, now what that update does with all of the correlated subqueries I didn't go so far as to confirm that.

What happened is that I decided to sit for the Oracle SQL Expert Exam, figuring I'd been writing SQL for years. knew most of the terminology on the test (not the Oracle specific parts which I had to learn), and discovered that Oracle and I have a minor difference of opinion as to what constitutes an expert and according to them most of my issues were with raw SQL which blows me away because I figured if I had any problems it would be with the Oracle specific aspects.  I'd have bet good money, before, during and right up until I hit submit that I had passed that test and passed with a safe margin.  I must have literally missed every single question where I had any doubt as well as some I was certain I knew the answer.  Very frustrating, very humbling and whomever made that test is just mean.

Gorn,

thanks on the book but amazon owes me some money from when I emptied my change jar in the machine at the supermarket so I'll use that.  I would swear you could buy his books for $10.00 in the 90's but I guess not now.

[Walter Mitty]

Choppedwood,

I'm glad to hear that your attraction to exotic code was based on experiences with Oracle and not your own preferences.  I have a strong preference for code that is easy to read, easy to understand, easy to maintain, and easy to assimilate.  That kind of code doesn't look impressive, but it has more value than exotic code.  I would probably prefer the code you wrote professionally to the crap the Oracle exam was testing for.

I don't know why anybody would want to update an in line view.  I don't know when "order by 1"  would ever be a useful thing to code. 

Having said that, it's possible to use SQL to accomplish some radical transformations and updates that are not obvious to people who have only done simple SQL.  But I would call that stuff "advanced" rather than "exotic".

I have a favorite story that I read online about a guy who ended up rewriting the weekend update from java into pure SQL.  He was just prototying to get the logic right.  But his code ended up taking 43 minutes to do the update where the java code had been taking 43 hours.  not that java is a bad language.  But it's possible for programmers to optimize the wrong stuff.

[Walter Mitty]

Choppedwood,

Adding to my previous reply... I lean towards the opinion that the Oracle test that humbled you was designed to get the wrong people to fail. 

The people who produce code that's more exotic than it needs to be are part of what's wrong with IT these days. There were always programmers who produced mysterious code.  I'm embarassed about how much of that stuff I produced in my younger years.  But they weren't as universally upvoted as they are now.

[Walter Mitty]

A little bit of thread drift.  I cut over from a different dialect of SQL to Oracle's dialect in the mid 1990s. 

I had been doing "SQL plus" for about six months when I got it into my head to write an SQL script that would do the following:  given the name of a table or view, produce a CSV format file with a dump of the contents.  I limited myself to straight SQL, and not PL-SQL, because PL-SQL was an option at the time, and my previous client didn't have it.

And of course, I wouldn't have had to write such a thing with later versions of Oracle, because it was provided as a built-in feature. 

It turned out to be a "tour de farce"  (sic).  I did things in that code that I never would have dreamed of doing if I was "being professional".  I eventually offered it up in cyberspace as a freebie.  Years, later I got one glowing report from somebody who was desperately seeking such a solution.  He was amazed at my code.

But it was a "clever hack",  not a well written piece of software.  My category for this stuff is "skunk works". 

[choppedwood]

Walter,

I would tend to agree with your comments.  I spent the last several years pulling data out of a system that was often very painful.  Some of the things they did were arguably brilliant but they often made it very hard to look at the data in a way that they didn't design it for, especially since the vendor refused to add indexes.  Some of it, on the other hand, just looked dumb but there's almost always a reason someone did it that way.  Probably, they were using it for something I didn't know about that needed it done that way because something else was causing problems.  The life of an ERP.  Anyways, I've always believed that the simpler I kept things the better.

The test, I think, is trying to test that you know everything, about everything, on the test, and often in the same question.  The questions often seemed to test multiple concepts and did things that still don't make sense to me.  One that stands out is something that I'll probably never find an answer to, and I'm convinced from looking at the tables they showed, would have thrown an error on 3 of the 4 answers but the answer required that you choose 2.  So, there was some minutiae there that I'm unaware of or I misread the question (there's time pressure so I may have).

Anyways, I came really close (a question or two) so I don't feel horrible about it.  Unfortunately, I've goofed off for too long and it's getting time to find something to do.  And, I'm pretty sure it won't be in Oracle.  So, hopefully, I can use what I learned on some other platform and maybe I'll come back and do this in a few months as I feel really stubborn about it and want to pass the damn thing.

[choppedwood]

I have to be honest in that I just found the answer to the question I referenced above.  It wouldn't have thrown an error like I thought, and ironically, I probably got it right.

[Walter Mitty]

While I was out for a walk, it suddenly came to me that "order by 1" doesn't mean what I thought it meant.  It really means "order by column 1 of the result".  I knew that, once upon a time, but I forgot.

Oops.  I'm sure I would have failed their test.  I failed the only test they ever gave me, which asked me for definitions of several of the fundamentals.  I never memorized formal definitions.  That had little to do with understanding and applying the fundmental concepts to the problem at hand. 

Oh, well.  My race is run.  I just hope you are able to find people to hire you for what you can do, not for how well you know the esoterica. 

[choppedwood]

While I was out for a walk, it suddenly came to me that "order by 1" doesn't mean what I thought it meant.  It really means "order by column 1 of the result".  I knew that, once upon a time, but I forgot.

Oops.  I'm sure I would have failed their test.  I failed the only test they ever gave me, which asked me for definitions of several of the fundamentals.  I never memorized formal definitions.  That had little to do with understanding and applying the fundmental concepts to the problem at hand.

I tend to agree with that but the test is what it is.  I give them credit for one thing.  It got me to look at SQL at a level I'd never have done otherwise and I have database certifications for other products.  And, I'm so damn stubborn that I really want to pass it even if it probably doesn't help me much.  It's a challenge.  They have another test that apparently is much easier (although it's broader) and also points you towards their DBA certification but I just happened to pick this one.  The cute little flower turned out to be a mean old patch of poison oak instead.

[Walter Mitty]

Well, I wish you well.


May you obtain the title of "exotic coder" if that is your wish.  And may you never have to take the test for "exotic dancer". 

[Richardk]

I guess as stated, the test is what it is but I'd be unhappy with SQL that's loaded with DB specific features or nuances. Unless you're committed to that DB, it's not very portable. On the other hand I've seen DB's that want to be 'generic' and shed almost no light on the structure. Again a big one is no indices or primary keys that are merely an integer, with no meaning whatsoever. Sure it's portable across multiple DB's but ugly as heck too.

My preferred author for Oracle was Thomas Kyte. Not sure if he's still relevant today.