SQL Programming Language and Oracle novations

SQL Programming Language and Oracle novations

A relational model is not enough, we need a specialized programming language to interact with the database. There have been many attempts to create such a language, and SQL is the winner.

History and Terminology

In 1970, E.F. Codd’s paper discussed the need for a data language: a relational algebra formed by combining relational operators. And preferably a simple one, meant to deal with simple relations.

Not all relational operations are relevant in typical database activity. Like with the relational model, knowing some of the names can at least help us understand what other people are talking about. Projection is choosing only certain columns. Join, of course, is combining relations together. Restriction or selection limits the tuples of a relation, by applying predicates. (That means results are filtered by functions that return true or false.) A Cartesian product (CROSS JOIN) results in a set that includes all possible combinations of elements from two sets. Set union (UNION ALL) combines all elements of a set, and set difference (MINUS) returns the difference between sets.

In the 1960s, IBM created the Structured English Query Language, SEQUEL. But don’t use history to prove a point about how to properly pronounce “SQL.” Just accept the fact that it’s commonly pronounced “S-Q-L” with databases like PostgreSQL and MySQL. And it’s commonly pronounced “SEQUEL” with databases like SQL Server and Oracle.

Oracle favors English-like syntaxes over cryptic syntaxes. SQL is somewhat similar to COBOL, in that they both have English-like syntaxes and were both designed for business users. The later addition of PL/SQL made similar design decisions. It uses what might be called a “Wirth” syntax, similar to languages designed by Niklaus Wirth. That is, it uses plain English words like BEGIN and END instead of curly brace characters, like in C++. Another common sense feature of Oracle is that lists start with 1, not 0. If we find ourselves creating a program that looks like a screen in the Matrix, we have strayed far from the intended path.

SQL frequently shows up near the top of programming language popularity lists. And those sites likely still underestimate the language’s real popularity, since many non-programmers use SQL but aren’t responding to developer surveys. PL/SQL isn’t a top-tier language, but it’s surprisingly close.

 

SQL Alternatives

The world is full of query languages. There’s XQuery for dealing with XML, Cypher for querying graph databases, REST APIs for querying data over the Internet, and many more. But when it comes to querying relational data, SQL is by far the best choice.

For querying relational data, the SQL alternatives are only of academic or historical interest. Languages like QUEL, Rel, and Datalog may be interesting, but not practical. Technology is not always a popularity contest, but SQL is so dominant that it would be ridiculous to not use it for relational queries. Even if another language was better, it wouldn’t have the huge number of programs built to use it, or the huge number of people familiar with it.

Is SQL a safe bet for the future? It’s foolish to predict that a better technology won’t be invented. But it is reasonable to predict that a significantly better technology will not replace it in the near future.

Some languages grow old and fade away, but some languages are timeless. C and Lisp are older than SQL, but they’re not going away anytime soon. Writing about technology over 30 years ago, Fred Brooks warned us to not expect any “silver bullets” for software development. That is, don’t expect a single new technology to provide order-of-magnitude improvements. SQL is a high-level language that’s always evolving; it’s possible the language is as good as we’ll ever get for querying relational data.

Some people hold out hope that a visual programming language will someday make database queries easier. Unfortunately that is not likely to happen, for several reasons.

Visual query builders are as old as SQL itself, starting with Query by Example (QBE) in the 1970s. The idea has been implemented many times but it’s rarely used for non-trivial queries. Programming in pictures looks cool but it’s easy to forget how great text is. Text can be easily shared, modified, and version-controlled. In programming, a picture is not worth a thousand words; it’s only worth 50.

The problem with visual programming is that you can’t have more than 50 visual primitives on the screen at the same time.

L.Peter Deutsch

The exact number of visual primitives is debatable, but the information density of visual programming languages is definitely lower than text.

A long time ago, I was an intern at National Instruments. It was a great experience to work at the company that created LabVIEW, arguably the best visual programming language. The language is fun to use and well suited to test and measurement tasks. But even the employees of that company would readily admit that visual programming is not a panacea.

As an example of visual programming, Figure 1 shows a simple query against the space schema.

A visual query in LibreOffice Base 

Figure 1 A visual query in LibreOffice Base

The preceding query finds all satellites that were launched on October 4, 1957. At a first glance the image looks fine. But visual queries only look nice for simple examples. Visual query builders do not scale, and the connection lines between tables will quickly look like spaghetti. The following is the same query using Oracle SQL:

select
   norad_id,
   satellite.launch_id,
   launch_date
from satellite
join launch
   on satellite.launch_id = launch.launch_id
where trunc(launch_date) = date '1957-10-04'
order by norad_id;

 Although text is superior to images, new article will explain why we still need to program with an integrated development environment (IDE).

Is SQL a Programming Language?

Yes.

Some programmers don’t consider SQL to be a “real” programming language. That mistake can lead to a self-fulfilling prophecy. If SQL isn’t treated like a real programming language, the code will look horrible, so developers don’t use it, which makes them think it’s not a real programming language.

There’s no theoretical or practical reason to think that SQL is not a “real” programming language. Defining exactly what a programming language is, and what it can do, is theoretically important. But too often those tests are only applied to a straw man version of Oracle SQL.

Original SQL is not Turing complete, which means the original version of SQL cannot theoretically solve every problem that is solvable by a Turing machine. A Turing machine is a simple abstract machine that can run forever. SQL doesn’t have an infinite loop and therefore cannot run forever, so technically it’s not Turing complete. But nobody has ever complained that their SQL didn’t take forever to run, so it’s a pointless theoretical argument.

And Oracle SQL has many extensions that can make SQL act like a procedural language. With recursive common table expressions and the model clause, Oracle SQL can run imperative code. Oracle SQL and PL/SQL have been slowly merging for many years, and PL/SQL is a traditional imperative programming language.

There is no theoretical or practical limit to what we can accomplish with Oracle SQL.

Вас заинтересует / Intresting for you:

PL/SQL package: Collecting Rel...
PL/SQL package: Collecting Rel... 348 views sepia Sat, 01 Dec 2018, 10:54:57
Describe the Basic PL/SQL Prog...
Describe the Basic PL/SQL Prog... 573 views Андрей Волков Wed, 12 Sep 2018, 15:23:39
Why Oracle DBAs learn PL/SQL a...
Why Oracle DBAs learn PL/SQL a... 577 views Андрей Волков Wed, 12 Sep 2018, 14:43:12
A Brief History of SQL
A Brief History of SQL 675 views Александров Попков Wed, 17 Oct 2018, 15:04:29