
Lecture material
I'm sincerelly overwhelmed by the quality of this kind of Reg articles
I think I'll borrow(TM) it (wink, wink) for my RDBMS classes! (with the mandatory reference to the original publishing)
In the fifty years since SQL was first proposed, it has become the dominant query language for working with relational databases. As such, it underpins the transactions vital to the workings of government, business and the world's economy. But like many standards, its path to global domination is not been a straight one, and in …
Best thing you can do if you like our articles is tell your friends and share our links.
We appreciate it so much; thank you. We don't need subscriptions or pay walls or anti-ad-blocker begging.
We just need people from all walks of IT life reading and sharing us. Thanks again :)
C.
If you had a badge and then it vanished, you just need to post some more again. The badges do time out, but if you qualified for one in the past and post again, it will come back.
And this is automated: it's not like we have time or inclination to manually take badges off people.
C.
Informix, originally Marathon, was there in the early days alongside Oracle. In fact I heard of it and used it well before I heard of Oracle. You may guess at some of the ways in which Oracle came to dominance although it has to be said that Informix also made some pretty silly errors in buying up other businesses which didn't fit and presumably took money and management attention away from the main product.
Marathon was a relational wrapper around C-ISAM access (pairs of data files and B-tree index files) with the choice of access via a C library and command-line tools very much oriented to teletype including the report generator ACE plus, IIRC, a rudimentary TUI program called, IIRC, Informer. With the change of name from Marathon to Informix (could have been worse!) there was a TUI replacement for the main query tool and the original Informer replaced with a much better product, Perform. Perform providing some high-level programmability and a good deal more with the ability to enhance the run-time interpreter with C functions callable from the higher level Perform code.
Informix 2 introduced SQL, the ESQLC pre-processor to embed SQL into C and 4GL. 4GL was vaguely like structured BASIC with embedded SQL and event-driven menu handling which compiled down into ESQLC. 4GL programs could replace ACE or Perform. The consequence was that for a considerable period the combination of SCO and Informix was the backbone of many small businesses and the likes of HP-UX, AIX or Solaris and Informix ran larger businesses. They also supported the development of packaged applications.
In the end they lost out to Oracle and were bought up by IBM, probably because IBM wanted some aspects of their technology. If they hadn't fumbled as few times would they have remained competitive? Would we have seen all these Birmingham type situations? Who knows. It would have been a different and perhaps better alternative reality. As it was it supported me for most of the second half of my working life.
I used Informix on a project in the late 90s as a whippersnapper. It seemed fine until someone from Informix had to come out and fix the database for us armed with a magic floppy disk. It had run out of storage and it was apparently impossible for us to fix ourselves because any operation, including anything that might have freed up space, required it to log that (maybe it was the rollback logs, this was all new to me at the time) and it couldn't - there was no space to do so.
No doubt there was incompetence on our part involved, and probably someone here can explain why that's all bollocks and that we could have fixed it, but that's how it played out. We stuck to Oracle and Db2 after that.
A dial-in modem would have obviated the need for a visit. Your problem probably wasn't an absolute running out of space as a long transaction that ate the available log allowance.
By logging the dial-in port I discovered that what they did was upload the source for a program that did that, compiled it on the local box and ran it. I suppose your magical floppy might have had the source or possibly a executable. I believe that was eventually fixed by enabling the system to add its own emergency temporary log, probably because nobody liked allowing remote access any more.
I worked on Oracle and Informix on Unix and always on "cooked" filesystems max DB size was about 25GB at that point around 1997. At one point I changed jobs and had my first exposure to working with big Informix DBs stored entirely on raw disks on AIX, it was a real shock having to learn how deal with raw devices and DBs that were around 10TB in the late 1990s.
I worked on Informix in the mid-90s. The stored procedure language was pleasant, if rather cumbersome, in the terminal-level application I was asked to work with. SQL is SQL. I don't care that everyone does it a little differently - the mental model remains the same.
There have been many DBMSes which enjoyed their day in the sun, and a lot of them are still around. We have quite a few customers migrating from IMS on z to our IMS emulation (which implements both IMS DB and IMS TM, aka IMS DC), which implies there are many more still using IMS on z; so that's quite a few votes for a hierarchical DBMS with DL/I as its query language.
IDMS, Cullinet's network DBMS, was a big deal back in the 1980s, and I believe Broadcom are still selling it today. (CA acquired Cullinet, then Broadcom acquired CA.) While I don't often hear about it from our customers — probably because we don't emulate it, so a customer that uses it needs to work with one of our migration partners — I'm sure there's still a bunch of it.
Here at Rocket, the multidimensional MultiValue DBMS is still a big product line. That originated with the Pick OS.
We've had customers asking about migrating from System 2000, a hierarchical-hash DBMS first released in 1970. Apparently there's still plenty of it running on IBM mainframes around the world.
ADABAS, most often used with programs written in Natural, is also still around. It's an inverted-index DBMS.
And then there have been the various graph DBMSes and OO DBMSes and so forth.
So, yeah, while RDBMSes speaking SQL are the biggest members of the old guard, and NoSQL key-value (DynamoDB, Redis, etc) and document (e.g. MongoDB) DBMSes are the current generation of upstarts, there are plenty of others still chugging along.
One of the reasons (apart from their appalling licencing) that I moved away from developing mostly with Oracle (After using them for about 10 years, along with Informix, Sybase, Rdb etc.,) was that I probably needed to iron only one shirt a fortnight. The only viable customer-base was large government infrastructures - We were a small company, and would only see crumbs. We found a niche by (professionally) prototyping things with MS Access and then upgrading it to a MS SQL Server backend.
This approach happened by accident. We were asked to put something together in a few days for a new customer, our existing tools were not going to work, and showing pretend screen-shots was not going to work either. I had some experience with the DOS relational database R:Base, some of whose developers wrote MS Access (R:Base was licensed to be sold in Europe by Microsoft). Windows/NT 3.11/3.1 had just come out, so I looked at Access. A few simple tables, forms and reports later I had something to show the customer. After a couple of days of on-site discussion and adding/removing things I told the customer that we were ready to port it to another system - "Why, that one does what we want, why should we pay to have something else"? I told them that it needed upgrading to be "more reliable on a network" and better inherent security. How long? they said. A couple of days (to split it into a front-end/back-end system and put in referential integrity). They went with it and it quickly grew such that I upsold them a SQL Server 4.2 version.
The customer eventually went with us for most of their IT. They told me that they had done the same scoping exercise with one of our larger competitors who was going to use Oracle/VB or Powerbuilder/Watcom/Sybase - They quoted 3 months to finish prototyping and another 6 for production, with much higher hardware and licencing costs. I retired and sold the company a few years back. The new owners tell me that most of the Access/SQL Server systems are still running, but upgraded to use the latest versions of Access and SQL Server - So that is over 30 years now.
Same here. I once wrote the entire ERP of the company which just hired me, using the couple SQL Server / Access, in a little more than three months, starting from scratch. I then turned to other activities, but it went on (although maintained and partially extended) for the next ten years.
By the way, I still miss the ease offered by Access to rapidly draw user interfaces, put some VB logic into it just to check the basic correctness of user input, and send / read data to / from the SQL database. I switched to Linux ten years ago and now use the excellent Postgre RDBMS, but never found quite an equivalent interface-designing tool. Had a go with wxWidgets and C++, and some hand-made web interfaces as well (with Javascript taking on VB duty), but it's much heavier and less satisfying.
Even worse than that : Have I really been using it for 60% of its lifespan? (Answer : yes, although less and less as time has gone on).
By the way, despite what the article says, PL/SQL is something very different from pure SQL, being a complete functional programming language.
Isn't PL/SQL an imperative/procedural language running inside the Oracle query executor?
It would be surely interesting to have LISP/SQL in the same setting, though. Lisp is functional.
See this page for language classification:
http://sappeur.di-fg.de/classification.html
Apparently it is a factor of 100 slower than LISP for general purpose computing tasks like Prime Number calculation. SQL was never intended as a general purpose language, so that's OK for me.
In the imagined "SQL/LISP" environment, SQL would be used for "data reading and writing from permanent storage" while LISP would be used for "complex computation".
There would be at least three software engineers who could make good use of this approach ;-)
Good point. The confusion has, however, gone even further in some quarters. I recently discussed a proposed advanced data base design with (postgraduate no less) in information systems. His approach to design was immediately to "write some SQL" rather than to design the structure conceptually first. In fact, the problem really called for a graph database, and a fairly sophisticated one at that, so a lot of actual thought would have been needed before anything else.
I had to do stuff like ML and Prolog at university near that time, also "initial quotient term algebrae" in formal proof methods.
Can't say that I liked it all that much, I just wanted results and loved e.g. Turbo Pascal, because it was so fast to compile and detect syntax errors, making for quick turn-around times even on tiny 8-bit CP/M machines like my Apple ][ clone with a Z-80 card.
And while all that formal, functional and logical stuff was somewhat fascinating, everybody just thought it was too impractical and complicated for anyone to use in daily life.
Only years later it dawned on me that spreadsheet formulae were functional. And by then I really wanted them inside my databases, too, to be evaluated as part of the query from within the DB engine, while the queries were functional too. You could do HPC that way, much better than in Fortran!
I guess some of the fuzzy things I had in my head were actually realized in MUMPS or Caché. I guess the lack of an open source variant meant I never found out.
So in fact these "complicated" languages may have seen far more use by far more people than those classic imperative programming languages we IT guys always thought were what made us better than those mere "users".
And today they also seem more likely to survive because loops, which we were taught to think in, beause they pretty much offered inductive proof of correctness, today only mean you haven't done your work to parallelise it to those tens of thousands of cores everybody with a smartphone or better has at his fingertips.
Because in functional that's natural and imperative sequential is really an aberration...
GT.M has a free to use MUMPS implantation for windows or x86 Linux.
The whole thing is totally procedural! But if you want a high performance, flexible database it’s the way to go.
Only problem is you have to learn one of the most orrible programming language ever devised.
I think methodologies tend to shift largely in response to what hardware offers. For example, we've seen a generation of developers grow up without having to think much about memory use or network latency. It's really always about learning which techniques are possible and choosing the rights ones for the job.
BTW. not convinced that spreadsheets are good example for anything: dependency resolution can be just as tricky as getting parallelism right.
"Only years later it dawned on me that spreadsheet formulae were functional. And by then I really wanted them inside my databases, too, to be evaluated as part of the query from within the DB engine, while the queries were functional too. You could do HPC that way, much better than in Fortran!"
As a DBA I just screamed NOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO!
Keep your damned functions and calculations out of my database server! Put them in your application layer where they belong!
We wanted the language to look as closely as possible to natural language so they could read it and understand it, like it was an English sentence
This may have advantages to appeal to non-technical people, but personally I consider it a fundamental issue. Cobol was also designed this way, but there is a reason we now use algebraic-like notation for programming and not Cobol.
It's relatively easy to write and understand a declaration like x1 = (-b + sqrt(b^2-4*a*c)) / 2*a, but if you try to express that as an English sentence you are going to be in pain, and anybody trying to read the result is going to be in a lot of pain.
I think it's very weird that a lot of SQL queries are built by concatenating strings that are afterwards parsed by the database engine. I feel like asking if the transmission should done by printing and scanning punch cards as well. We have so many ways to structure data in a logical way, it would make much more sense for queries to be defined, transmitted and read as a logically structured object rather than a string.
And yes, non-technical may be able to use SQL and would not understand structured data. They are also able to understand spreadsheets and not C++, but we still code in C++.
We also write in C or C++ instead of assembler, let alone machine code.
The entire art and science of programming is to present the requirement in as high a level way as possible. Indeed, that's been the way since assembler replaced writing binary by hand and binary replaced plug-boards and the like. The computer is very good at doing all that parsing and translating. Composing your logically structured object directly would be much harder and more error prone than letting the engine parse your SQL for you.
《It's relatively easy to write and understand a declaration like x1 = (-b + sqrt(b^2-4*a*c)) / 2*a, but if you try to express that as an English sentence you are going to be in pain, and anybody trying to read the result is going to be in a lot of pain.》
It would easier to explain as completing the square.
The two closed form solutions of the quadratic are declarative whereas the completing the square is largely procedural but the polloi are basic process thinkers if the gray cells engage at all.
I suspect that even the more mathematical actually think of a proof or derivation as intrinsically a process which, at lesst from my point of view, it is not.
My first SQL exposure was on Ingres on AIX back around 1992. I live in the arse-end of East Anglia and I went to the local library to borrow some audio books and they actually had "Teach Yourself SQL", I think the library only had about a dozen computer books in total but a SQL book was one of them! I took that to be a sign of good things to come.
I borrowed it 3 times in a row and photocopied the relevant pages, it really gave me the leg up I needed. Prior to that we had to pay this consultancy firm to come in at £250/day to write our SQL reports but I learned SQL and saved the company a small fortune once I worked out how to use the Ingres tools and SQL. I didn't think I had a hope in hell of getting serious with IT until I learned my worth using SQL, I left and took a job working on Oracle and Informix for about 4x the money! Ha ha!
I couldn't find much on it except the Wikipedia article. Most of the other Google results talked about QUEL without actually showing any examples of it.
It does not look better than SQL to me. They say it's closer to Codd’s relational calculus but I don't see how that makes a damn bit of difference outside an academic paper, and that's the main advantage that's cited.
> Is Ingres QUEL really better?
Not now. But back then - very slightly!
I started on Ingres in 1988 and version 5 had just come out which included SQL as a 'first class' query language - before then SQL was converted into QUEL behind the scenes. So we pretty much used SQL from then on and I did very little QUEL. I seem to recall it was better for some specific processing that SQL's group by / having clauses couldn't cope with so we sometimes still used it in the report writer. Also it was easier for nested selects (which weren't allowed in SQL back then).
But modern SQL has been extended so many times it's lost any vestige of the earlier simplicity. Occasionally I still write (well prototype) queries in the form "select a.b, c.d from a, c where a.join_col = c.join_col" and the youngsters look at me weirdly because they're expecting to see "from a inner join c" in there. :-)
I feel your pain....
I leant a bit of Ingres QUEL/Windows4GL back in the day and when the Microsoft inner/outer join stuff came along I was a bit confused as to why you need to to state the bleeding obvious to the parser... I had stoppped programming SQL type stuff by then so inner/outer join still sit uncomfortably in my locally cached SQL/QUEL syntax tables....
I reckon the use of a natural-language-like tool for questioning a database could appear very appalling to customers, for sure. But quite a heavy use of SQL taught me that :
a) as soon as you are to write queries in a real-life system, they may well soon become very difficult to construct (as far as you have to use some non-trivial JOIN expressions, want to exclude some cases, compute some values on aggregated rows, etc.) and even more to re-read and understand. The introduction of CTE did help a little, but still. Hence the use of tools like stored procedures or PG/PL functions which you would address either directly or in you SQL expressions - a fallback to programming languages of their own which have very little in common with plain English sentences;
b) it's very easy to write a SQL query which is syntactically correct, appears to be meaningful - only to return horribly wrong results.
I remember Paradox using a very clever and friendly tool called "Query by example", although I don't know if those queries where translated in plain SQL under the hood.
> as soon as you are to write queries in a real-life system, they may well soon become very difficult to construct (as far as you have to use some non-trivial JOIN expressions, want to exclude some cases, compute some values on aggregated rows, etc.)
This. SQL is horrible for this. Especially update/insert statements where you often find you can't debug a query by first writing a select statement that returns what you want and then, when it works, simply adding an 'update' to the beginning. Self-joins to the table being updated are particularly bad for this.
The boat has sailed but it would have been nice if SQL had been more a more orthogonal language in the sense of a standard way of 'identifying' data (by joining, filtering etc), giving a name to that 'identified' data and then using that named data in subsequent select, or insert, or update statements as a self-contained element.
The query optimiser gets the job of determining how to 'compile' the query into a single thing that can be passed to the database to be executed.
This is where the misunderstanding begins.
in a) Understanding that SQL is a data set language is key. There are many things it is not suited to do, even though it "can" do them. I.e. aggregation, sorting. These things will just add more compute drain on a possibly taxed SQL server. Resulting in poor query plan reusage and poor plans. This is why we strongly encourage developers to do these things in the Application server layer.
in b) This is completely caused by a misunderstanding of how SQL works. Don't blame the language for the results you got, because it returned "exactly" what you asked it to return!
What are non-trivial joins? I almost never have problems with joins, which should be a natural expression of the underlying relations. But I do struggle with subqueries which can indeed make understanding the projection far from trouble; other people are obviously far more familiar with the mechanism and the syntax and craft them with ease. CASE constructs, however, are an example of how painful any kind of procedural task is. I now find that a combination of views (materialised or not) and CTEs allows me to construct fairly elaborate queries without me losing sight of what I'm trying to do.
"There are 20 or 30 popular programming languages in the software development world. In the database world, there's only one."
There’s more than that. Mongo Query Language is fairly popular, as are Power Query and Excel.
Not saying they are better than SQL, but they are widely used. Excel in particular is used far more than it should be.
SQL is what is correct and Sequel is what is common.
Whether the latter makes it more correct is another matter.
But nobody who talks about Sequel today is referring to the declarative language from the 1970s.
SQL is what I grew up with and if others want to say Sequel, then grand, let them.
Good 20min video about the same subject on YouTube by Asianometry, called "The Birth of SQL & the Relational Database".
Re. standard drifts. It would bear mentioning that we'd better know how standards-compliant a vendor is if the ANSI SQL standards were freely available or more reasonably priced. As very few people have seen the actual documents in question, one is mostly left to take a vendor's word at face value that they are "highly, highly, standards compliant".
With some care and discipline it is also not impossible, at least in some fields, to write reasonably ANSI-compliant code. However, a lot of the bigger traps, once you avoid vendor-specific functions, have to do with really simple things the string concatenation operator and date handing. That requires some kinda macro preprocessor to work around.
Last, one measure of SQL's appeal is how non-relational database systems often tend to provide SQL-like query languages.
Thanks for this. It is a nice trip down memory lane.
I used Ingress at University and loved the ability to do test finding paths around Berkely California on Goequels test data set. This was on the geospatial version of Quel, GeoQuel.
We also worked with relational calculus. But then we had APL keyboards so the symtax was easy to type. I do recall trying an early version of System R at the IBM research centre and was shocked that the single use terminal we had was commanding the full resources of a 360 mainframe!
I met Michael Stonbraker and Eugene Wong in London many decades ago. Lovely man to talk to.
Thanks for memories
For the last 10 years at least, they've predicted the death of SQL.
It still exists, simply because for most tasks, it is the best language for the job.
New tools come, but they end up building an extension for new tooling for a SQL interpreter. That alone tells you what we've known for years.
A few years ago I was involved in a project to present a large and complex database as a GraphQL model. The idea is that downstream users of the data don't have to know the details of the relational model, they just present a JSON template of the data they need, and the software generates SQL. Essentially a duplication of the effort that had once been put into SQL interpreters.
Starting off in the biz in the mid-80s, I was first introduced to IMS via DL/1 via the company I worked for's trainee program and which, of course, made no sense to someone with zero experience of computers.
To add further confusion, one of the first programs I worked on used SQL/DS on VM on which I had received no training whatsoever (it was over 15 years later that I had anything to do which involved IMS which rather ironically has an SQL interface these days).
Since then, I've written a very large chunk of a commercial product that interacts heavily with Db2 and I've still never had any training in either Db2 or SQL; fortunately IBM's documentation is pretty good so that's never been a problem.
Not really a typical career then, but as an aside, I do know why Db2 (and other) page tables are sized in 4K multiples (cough... *BLOCKIO should anyone have ever heard of that, courtesy of the big book of VM: CP Programming Services and DIAG X'250'). Can't complain, it's been a good living for many years.
We're about the same age I gather but I started in Unix super-micros & minis rather than the big iron, so I learned whatever the original query language was for Informix, (which like many other RDBMSs also a QBE interface like the one that shipped with Paradox, mentioned elsewhere) then Quel, and only then SQL. I didn't think SQL was particularly better or worse than the others but was awfully glad one of them developed into a standard, so I didn't have to learn a dozen other query languages with more or less equivalent capabilities.
I don't know that I had much formal training on SQL either but ironically, have taught it a number of times. Thanks for the memories...
It used to be illegal when writing about relational databases not to say "Codd and Date".