This above is a real SQL statement (version 11.2.0.3, just in case you wanted to know :-) ).

This was prompted by a silly discussion at lunch time about the answer to everything and databases and I wondered aloud how you could go about getting Oracle to respond with 42 when you “selected all from everything”. My colleagues looked at me like I was an idiot and said “create a table called everything with a column called all and select it”. Yeah, of course, and I laughed. So much for being an expert at Oracle huh?

Damn. It’s a reserved word. But for what? Off the top of my head I could not remember what ALL is used for in Oracle select syntax. Never mind, I could get around the issue to some degree by the use of quotes around the column name (and just for fun, I made the column name lowercase too – this is how you can create lowercase columns but you have to be careful with this, as you will see below):

Having said it was not quite what I had originally set out to do, I actually prefer this version.

Of course, I cleaned up after myself. It feels odd typing in commands that have an English meaning that would not be what you want to do – I could not get over the nagging feeling that the below was going to cause a lot of data to disappear :-) :

The original silliness of the lunchtime banter was about what is going into the data warehouse we are building… as usual the client wants to “SELECT ALL FROM EVERYTHING” which sounds a nice simple piece of SQL.

But you are only getting 42 which suggests a high degree of aggregation