Structured Query Language

Traditionally abbreviated SQL.

SQL is a DeclarativeLanguage, one in which you specify what you want and let the language figure out what steps to take to do it. The classes of DeclarativeLanguage are RelationalLanguage and FunctionalProgrammingLanguage (and ConstraintLanguage?); I'm not sure to which one SQL belongs.

It has also at times been called a FourthGenerationLanguage.

Some SQL commands are used to set up a relational structure for storing information. For example, the CREATE TABLE command takes parameters identifying the names and data types of fields in a table. Another group of commands is used to modify the information contained in the relational structure. For example:

SELECT * FROM EMPLOYEES WHERE LASTNAME = 'SMITH'
UPDATE EMPLOYEES SET SALARY = 5000 WHERE SSN='123-45-6789'
DELETE FROM EMPLOYEES WHERE SALARY = 0

The part that gets confusing is where information is stored in more than one table, requiring various types of joins connecting the tables.

SELECT ZIP FROM EMPLOYEES, ADDRESSES WHERE EMPLOYEES.CITY = ADDRESSES.CITY AND EMPLOYEES.TITLE = 'DIRECTOR'

This gives the zip codes of all directors. There are "inner" and "outer" joins, "left" and "right" joins, etc. which provide combinations of return values depending which tables have values and which don't.

The basics of SQL are almost identical across several database vendors. However, each vendor has added their proprietary extensions. For example, both Oracle and SQL Server allow the automation of deleting cross-referenced items when the last referencing item is deleted; both provide for automatically numbering newly added rows; and both can ensure that only entries present in a related look-up table may be inserted as field values in a master table. Both syntax and semantics are utterly unique between these products (and others such as DB2) for these features.

In general, SQL statements instruct the SQL database engine about the desired end-state condition, but do not have to give step by step instructions to the engine. For example, in handling a SELECT statement, the database engine program may flush buffers, dirty write caches, read sectors from disk, follow linked lists, etc., none of which the programmer has to know. But with most SQL engines, the programmer can add proprietary hints to adjust the way the query is processed. By the way, deletes, updates and inserts are also generically referred to as queries.

Additional proprietary extensions, such as Oracle's PL/SQL and Microsoft's T-SQL, add procedural constructs such as explicit looping, flow control, local variables and so forth the SQL. The idea is that some database maintenance, data cleaning, summary generation, and even business logic functionality can execute inside the database for speed and efficiency. The widespread use of PL/SQL in the ArsDigitaCommunitySystem is one reason for the difficulty of that system's rewrite in Java.

- ChrisBaugh

SQL queries can be straightforwardly translated to PrologLanguage, but the reverse is not that easy since there is no possibility of recursive queries in SQL (for example you cannot traverse a graph in SQL). As a matter of fact, pure SQL is based on the relational algebra which can be viewed as a subset of predicate calculus. So I'd say SQL falls in the RelationalLanguage category. -- Bob

SQL is a non-intuitive, rather obtuse language created for data base queries etc. - except for simple queries, it is rather non-intuitive and someone should replace it or put a wrapper over it so that it can die and go away. --RaySchneider

Replacing it would be great. A wrapper ain't going to cut it, because SQL has some serious problems in the model of a database it uses. The best explanation of these, IMHO, is in ChrisDate and HughDarwen's Foundation for Future Database Systems: TheThirdManifesto (Third Edition). The eighth edition of Date's classic An Introduction to Database Systems also covers some of this ground.

30 years of advances in language design theory vanish before your eyes when you view TransactSql... -- PhlIp

The classes of DeclarativeLanguage are RelationalLanguage and FunctionalProgrammingLanguage; I'm not sure to which one SQL belongs.

Neither. It was a failed attempt at a relational data sublanguage.

Some SQL commands are used to set up a relational structure for storing information.

Not really. SQL tables are bags, not relations, because they can contain duplicates.

As a matter of fact 'pure' SQL is based on the relational algebra which can be viewed as a subset of predicate calculus.

Absolutely not. SQL is actually a mix of relational algebra & calculus, is more complex than both together and less powerful than any. And the relational algebra & calculus are equivalent but different: neither is derived of the other, not a subset of the other, and both have the same expressiveness.

-- LeandroDutra

A piece of trivia about SQL which is not often appreciated. SQL is deliberately *not* TuringComplete. This is because it is designed to be completely analyzed and then very well optimized on the fly. Most obvious things that people would like to change about it would make it more convenient to a human, and would immediately introduce the HaltingProblem.

Moore's law is great and all, but for the forseeable future there is a role for a specialized LittleLanguage which can be optimized very well. This is because current hardware with real data sets does not perform adequately with the bad quadratic and cubic algorithms that we naturally write. It comes up enough to no longer be PrematureOptimization to write it in a way that the machine can recognize what you want and find a log-linear algorithm to do it. For now SQL is that language.

-- BenTilly

But now SQL is TuringComplete. ISO/IEC 9075:2008 specifies the syntax and semantics of recursive common table expressions (temporary tables created in the context of a single query, which can have rows appended to them even as they are being scanned):

  WITH RECURSIVE t(n) AS (
      VALUES (1)
    UNION ALL
      SELECT n+1 FROM t WHERE n < 100
  )
  SELECT sum(n) FROM t;

which adds the integers from 1 to 100 inclusive. Or, somewhat more usefully in the real world,

  WITH RECURSIVE included(component, part, quant) AS (
      SELECT component, part, quant FROM parts WHERE part = partID
    UNION ALL
      SELECT p.component, p.part, p.quant
      FROM included pr, parts p
      WHERE p.part = pr.component
    )
  SELECT component, SUM(quant) as total_quantity
  FROM included
  GROUP BY component

Querying the "parts" table to find out how many of which components go into making it up (including components of components).

SQLJ

JavaLanguage recently (early 2004) got a boost thru IbmCorporation increased support of SQLJ in its latest DbTwo offering. See http://www.service-architecture.com/database/articles/sqlj.html for additional information on SQLJ, which was first supported in OracleDatabases.

StructuredQueryLanguage QuickQuestions

Q Does SQL have a standard way to associate comments with queries and table definitions?

A ?

(Doesn't the second question refer to a data-definition language rather than SQL?)

Q What's so funny about naming a child Robert);--DROP TABLES Students

This seems like a good idea: a web site that gives a tutorial on SQL, and supports free-form SQL in HTML forms for exercises: http://www.sqlcourse.com/

I gave it a quick whirl and it seemed to be what it claimed; it retrieved correct results for the queries against their toy test table.

This makes so much sense that now I wonder if there are lots of such things out there, and I just never noticed.

Another good idea is a SQL Tutorial that provides concrete examples on all SQL commands: http://www.1keydata.com/sql/sql.html

One of the best sql tutorials with thousands of various queries covering all sql commands: http://www.w3resource.com/sql/tutorials.php

Results of searching c2 for "sql"; needs trimming:

DocQueryInSql DomainLogicAndSqlArticle EmbeddedSql HypersonicSql IwannaLearnSqlserver MicrosoftSqlServer MySql OraclePlsqlUnit PerniciousIngrownSql PlSql PlSqlUnit PortableSql PortableSqlDataTypes PortableSqlMetaData PostgreSql PushDocQueryInSql RefactorSql ScatterSqlEverywhere SqLite SqlAntiPatterns SqlFlaws SqlForSmarties SqlLineCount SqlMyopia SqlPattern SqlRdmsProgrammerMentality SqlReportingServices SqlServer SqlServerDesktopEngine SqlServerWithJava SqlStringsAndSecurity SqlWindows TheSqlParadox ThinkSqlAsConstraintSatisfaction TimeSeriesInSql TreeInSql TsqlUnit UnitTestingMyLibraryPrepareAndExecuteSql UsingSql WritingUnmaintainableSql

See also: RelationalDatabases, TheSqlParadox, SqlFlaws, RelationalLanguage, http://c2.com/cgi/wiki?search=sql (all pages here with SQL in the title)

CategoryProgrammingLanguage CategoryDatabase CategoryQueryLanguage