Friday Philosophy – The Answer To Everything January 27, 2012Posted by mwidlake in Friday Philosophy.
Tags: Humour, SQL
mdw1123> select all knowledge from everything 2 / KNOWLEDGE ---------- 42
This above is a real SQL statement (version 18.104.22.168, 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?
Well, I tried. It did not work:
mdw1123> create table EVERYTHING (ALL number not null) 2 / create table EVERYTHING (ALL number not null) * ERROR at line 1: ORA-00904: : invalid identifier
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):
mdw1123> create table everything ("all" number not null) 2 / mdw1123> desc everything Name Null? Type ----------------------------------------------------------------- -------- -------- all NOT NULL NUMBER mdw1123> insert into everything values (42) 2 / mdw1123> select "all" from everything 2 / all ---------- 42 -- but be careful of case mdw1123> select "ALL" from everything 2 / select "ALL" from everything * ERROR at line 1: ORA-00904: "ALL": invalid identifier
I was not happy with this though, I was having to put the quotes in my line and be careful about the syntax.
So, what is the word ALL used for? A quick check of the SQL reference manual:
It is the opposite of DISTINCT and the default, so we never have to put it in the statement.
With the above in mind I was able to quickly come up with something close, but not quite, what I originally asked for. See below for how.
mdw1123> create table everything (KNOWLEDGE NUMBER NOT NULL) 2 / mdw1123> insert into everything values (42) 2 / mdw1123> select all knowledge from everything 2 / KNOWLEDGE ---------- 42 mdw1123>
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🙂 :
mdw1123> drop table everything purge;