databasedev.co.uk - database solutions and downloads for microsoft access

SQL - Structured Query Language

What is SQL?

Structured Query Language (SQL) is both the International Organization for Standardization (ISO) and the American National Standards Institute (ANSI) standard language for creating, updating and querying relational database management systems (RDBMS). Both ISO and ANSI are influential in establishing standards for technology and computing. The standardization of SQL, at both the international and national levels, ensures compatibility and consistency in database development around the world.

SQL dates back to the 1970s when IBM, with the assistance of others, developed SEQUEL, Structured English Query Language. SEQUEL involved a relational model, for large shared data banks, with multiple tables and multiple users. A revised version was named SEQUEL/2, and later, for legal reasons, it was renamed SQL. Other software vendors, including Oracle, Ingres and Sybase, were receptive of the relational model and also developed SQL-based products.

In 1986, the ANSI standardized SQL and in 1987 the ISO standardized it. The early standards were not complete, leaving some features to be defined by the user or "implementer defined". Later revisions have advanced the language to include object-relational concepts, integrity management and call level interfaces.

SQL provides standards for

  • The language used to construct and manipulate database tables
  • Schema for defining, administering and manipulating data
  • Defining data types, triggers, recursive queries
  • Schema for defining, administering and manipulation of objects
  • Defining methods, hierarchies, inheritance, polymorphism, encapsulation of objects

SQL was designed to provide ease of use and readability. SQL statements can be considered simple, instructiona,l English sentences. The statements instruct the server as to what data to find as opposed to providing instructions on "how to find", as is the case for most computational languages. Some basic components of SQL statements include the following:

  • Keywords – predefined words that are recognized by SQL as having a specific meaning. Keywords are used in SQL commands, clauses, function names and special terms. Some common keywords include SELECT, INSERT, UPDATE, DROP, CREATE and DELETE.
  • Identifiers – variable names for tables, columns and other database objects.
  • Special characters – reserved characters that logically affect the meaning and arrangement of keywords, identifiers and literals. Some special characters include parenthesis, brackets, asterisks, dollar signs, commas, periods and semicolons.
  • Operators – special characters that perform logical or mathematical operations between data values. Some common mathematical operators include addition (+), subtraction (-), division (/), multiplication (*), factorial (!) and absolute value (@). Some common comparison operators include equivalence (=), less than (<) and greater than (>). Common logical operators include AND, OR, and NOT.
  • Constants - data values that can be of type string, bit string, integer, floating point and Boolean

The primary purpose of relational database systems is to establish “relations” between tables of data. A database table consists of structured records divided into fields of data. SQL consists of structured statements, used to create tables and also add, delete and modify records within the table. The database also contains columns that define the name and type of data that can be found in a particular record field. Every table must have a least one column because without columns, rows of data would be ambiguous. A table, on the other hand, may exist without rows.

SQL also provides standards for specifying read and write permissions, committing and rolling back transactions, as well as creating and specifying indexes. Each SQL statement begins with a command that describes the action to be taken, and typically, one or more clauses.

SQL is embedded in general purpose-programming languages and various other ANSI SQL compliant RDBMS, including Microsoft Access, SQL Server, Oracle, Informix and MySQL. As the established standard, SQL is embedded in most all relational databases though some database systems also include proprietary statements unique to their own system.

Other Resources: