“a SQL” or “an SQL”?

 

This is an age old question and of course the answer depends on how you say “SQL”.

  • … a Structured Query Language statement…
  • … a Sequel statement…
  • … an Es Queue El statement… (I say it this way)

Different people say it different ways. Most of the time I don’t notice, but I just read something by another writer and kept seeing “a SQL …” and it was freaking me out. Then I realised I always write “an SQL …”, which reads “an sequel …” to some people, which sounds really stupid. :)

According to the Oracle docs it is sequel, so “a SQL …” is the correct way.

“SQL (pronounced sequel) is the set-based, high-level declarative computer language…”

The MySQL docs go the other route.

“The official way to pronounce MySQL is My Ess Que Ell (not my sequel), but we do not mind if you pronounce it as my sequel or in some other localized way.”

According to Wikipedia, it doesn’t matter either way.

“The original standard declared that the official pronunciation for “SQL” was an initialism: /ˈɛs kjuː ˈɛl/ (“es queue el”).[11] Regardless, many English-speaking database professionals (including Donald Chamberlin himself[37]) use the acronym-like pronunciation of /ˈskwəl/ (“sequel”),[38] mirroring the language’s pre-release development name of “SEQUEL”

If one of the designers can’t make his mind up, what hope to we have? :)

Anyway, when you are reading my stuff and you see “an SQL …” everywhere, it’s correct for the way I say SQL. :)

Cheers

Tim…

PS. All the other typos are just plain typos because I’m practically illiterate. :)

PPS. PL/SQL is a lot simpler as it is “a PL/SQL …” regardless of how you say it. :)

Update: The general consensus from comments, twitter and emails seems to be:

  • When you write it, it should be “a SQL …”, regardless of how you say it. Taking me back to school, Paul Steffensen said, “if it starts with a vowel it’s an, otherwise it’s a”. In my reply, I mentioned that “an Es Queue El …” does start with a vowel. :) That being said, “a SQL …” goes with the basic written language, the sequel version and the full wording of the acronym, so it fits more of the cases.
  • If, like me, you say “Es Queue Ell”, you should probably say “an Es Queue Ell” when speaking, but still write it “a SQL …”
  • Andrew Taylor said, “just to be on the safe side I say *some* SQL.” Pure genius!
  • In many cases, you can probably reword the statement so the “a” is not necessary. This feels like a cop out, but it might be what I end up doing. :)

Pattern Matching (MATCH_RECOGNIZE) in Oracle Database 12c

 

I’ve spent the last couple of evenings playing with the new SQL pattern matching feature in Oracle 12c.

I’m doing some sessions on analytic functions in some upcoming conferences and I thought I should look at this stuff. I’m not really going to include much, if anything, about it as my sessions are focussed on beginners and I don’t really want to scare people off. The idea is to ease people in gently, then let them scare themselves once they are hooked on analytics. :) I’m thinking about Hooked on Monkey Fonics now…

At first glance the pattern matching seems pretty scary. There are a lot of options and as soon as you throw regular expressions into the mix it does make your head swim a little. After a couple of half-baked attempts, where I found convenient excuses to give in when the going got tough, I finally sat down and plugged through the docs. If you actually RTFM it is a lot easier than hoping to wing it. Who’da thunk it? :)

I’ve tried to keep the article really light. The docs are pretty good for this stuff (if you read them) and they have a lot of examples. I started adding more and more detail to the article, then chopped most of it out. There is no point regurgitating all the options when it is in the docs. Most of the examples I’ve seen before just talk about basic patterns, like V and W shapes, but it’s quite simple to do complicated stuff once you start playing. In fact it takes more time to set up the example data than it does to figure out the queries to bring it back.

In the near future I will be copy/pasting examples and adjusting them or just sitting with my article and the docs when trying to use this stuff. I think it’s going to take a long time before I can type this stuff from memory. Partly that’s because I can’t see myself having lots of cause to use it. I can’t think of any scenarios I’ve experienced where this would have been a natural fit. Having said that, I’ve never worked in things like stock markets, betting and stuff like that where I can imagine this sort of pattern matching is needed all the time. I seem to remember one person at a conference, who shall remain nameless, saying this feature was one of their drivers for upgrading to 12c. I wonder if that was for real or an exaggeration?

Anyway, if you need this sort of analysis, I think it’s worth checking out, but try to remember it’s not as scary as it first looks. :)

Cheers

Tim…

Should SQL have a compulsory ORDER BY clause?

 

How many times have you heard someone say, “The rows always came back in the correct order, but since the *event* it’s now wrong. The *event* may be an upgrade or some maintenance task on the table etc.

Oracle clearly say,

“Use the ORDER BY clause to order rows returned by the statement. Without an ORDER BY clause, no guarantee exists that the same query executed more than once will retrieve rows in the same order.”

This also applies for the GROUP BY clause, that haunted people during their 10g upgrades.

“The GROUP BY clause groups rows but does not guarantee the order of the result set. To order the groupings, use the ORDER BY clause.”

Both these statements can be found in the docs for the SELECT statements.

So why not make the ORDER BY clause compulsory? The statement could include an explicit NOORDER, like this.

SELECT * FROM emp e ORDER BY e.empname

SELECT * FROM emp e ORDER BY NOORDER

Of course, if that happened I would probably be in the group of people complaining about how ridiculous it is to force us to specify a clause we don’t need, but it would make people think… :)

Cheers

Tim…

Update: There seems to be a little confusion about what I am saying. I am not suggesting ordering should be mandatory. I am suggesting the ORDER BY clause could be mandatory, making you have to explicitly say if the output from the query is ordered or not. So these two statements would be functionally identical.

SELECT * FROM emp

SELECT * FROM emp ORDER BY NOORDER

 

Oracle Database SQL Expert (1Z0-047) Notes…

 

About 7 months ago I wrote about sitting the Oracle Database SQL Expert (1Z0-047) exam. Since then I’ve been promising myself I’d write some articles about some of the sections tested by the exam. Lots of the content is pretty straight forward if you’ve been writing SQL for a few years. Also, there are lots of things that are covered in existing articles on the site.

I finally got round to writing a few posts about some of the exam content that isn’t covered, or is a bit “dispersed”, on my site.

OK, so the last one is not tested in the exam, but it should be. :)

With a bit of luck I’ll remember to link to these articles when people ask me questions in the future.

Cheers

Tim…

Oracle Database SQL Expert (1Z0-047)…

 

I can see this post degenerating into a rant, so I would like to preemptively appologize to anyone involved in the production of this exam. I’m guessing it’s a real pain to develop these exams, especially when some ass like me starts moaning about them. Added to that, I’m guessing the word “Expert” means slightly different things to different people…

I’ve been barking on recently that in my opinion, the most important skill required by any PL/SQL developer is SQL, with knowledge of PL/SQL itself coming in second place. Having recently taken the “Oracle Database 11g: Advanced PL/SQL (1Z0-146)” exam (mentioned here), I thought it was a little hypocritical not to sit the “Oracle Database SQL Expert (1Z0-047)” exam as well, so this morning I did just that.

Here are some of my thoughts on the exam, in no particular order of importance:

  • Regular Expressions: I think it is important that people understand what regular expressions can do and when it is appropriate to use them, but I don’t think it is necessary to test people on the meta-characters themselves. That’s what the docs are for.
  • Analytic Functions: No sign of them in my questions from the pool. Surely analytic functions are more important than regular expression meta-characters.
  • The majority of the exhibits were pointless. It seems like they were placed there to waste the time of people with bad exam technique, rather than to assist in answering the question. This was especially true of the schema diagrams, which I only referred to once when the datatype of one of the columns was important.
  • Several of the questions could be answered without reading the question at all, as the incorrect answers jumped out at you because they contained blatantly incorrect statements.
  • Several of the questions included the “ANY” and “ALL” comparison conditions, which are barely mentioned in the documentation (here)*. I guess these are only included in Oracle because the are part of ANSI SQL. I can’t remember ever using them in Oracle or seeing them being used by others. I have come across them in MySQL so I knew what they were for, which was fortunate.
  • There were lots of questions that included DML against inline views rather than directly against tables. It got to the point where I felt like, “If it’s got braces in it I’m going to tick it”.

I very quickly turned into a grumpy old man and started to rush through the exam, spending most of my time thinking about writing this blog post, rather than the exam itself. :)

In the end I got 96%, which I guess means I got 3 questions wrong out of the 70. Serves me right for rushing it so I could come home and bitch about it. :)

So I am now an “Oracle Database: SQL Certified Expert” as well as a grumpy old shite…

Cheers

Tim…

* Updated thanks to Pierre’s comment.