The Structured Query Language (SQL) is a language designed specifically for communicating with databases. Today SQL is the industry-wide standard language used by most database systems.
Databases allow collections of data to be stored in an organized manner – in the same way that data can be stored in an organized way inside files within a filing cabinet. Most modern DataBase Management Systems (DBMSs) store data in related tables, so are called Relational DBMS (RDBMS). The data stored inside databases can be examined and manipulated by SQL commands.
SQL commands are known as “queries” and utilize special keywords that can be used both to add data to a database, or extract details of data contained within a database. There are not many keywords so SQL is simple to understand but, despite its apparent simplicity, is a powerful language. Clever use of its language components enable complex sophisticated database operations to be performed.
A programming language (pronounced either “S-Q-L” or “sequel”) designed to manage database data.
The evolution of SQL
The model for the basis of SQL was defined back in 1970 by Dr. E. F. Codd, a researcher for IBM, in a paper entitled “A Relational Model of Data for Large Shared Data Banks”. This article generated a great deal of interest in the feasibility of producing a practical commercial application of such a system.
IBM really began to develop these ideas in 1974 when they started the System/R project which eventually created the Structured English Query Language (SEQUEL). This was rewritten in 1976 to include multi-table and multi-user features and was renamed SQL in 1978. During this time other software companies had begun working on database systems based upon the SQL model. Most notable of these were Oracle, Sybase and Ingres (from the University of California’s Berkeley Ingres project). The first to be released commercially was Oracle in 1979. IBM released improved database products named SQL/DS in 1982 and DB2 in 1983.
Modern versions of Oracle, Sybase, Ingres and DB2 DBMS are available today and are in widespread use around the world.
Standardization of SQL
In order to clarify the precise nature of SQL, so it could be implemented universally, each aspect of the language was defined in 1989 by the American National Standards Institute (ANSI) in a standard specification known as SQL-89. This was expanded three years later with publication of the SQL-92 specification by a joint committee of ANSI and the International Standards Organization (ISO). A third standard specification, SQL-99, was introduced in 1999 to address issues of advanced SQL syntax and has been subsequently updated with the SQL:2008 standard. Some DBMS vendors have added proprietary features to the ANSI-defined SQL standard. These extended versions even have their own names, such as PL-SQL and Transact-SQL.
“ISO” is not an acronym but is derived from the Greek word “isos” meaning equal – as in “isometric”.
Forms of SQL query
There are a number of ways that SQL queries may be sent to a database to deposit or extract data:
- Directly input through an integral SQL-client application that is part of the DBMS package – this is the most straightforward method.
- Input through a third-party SQL-client application – this method communicates with the database via an intermediate software “driver”. On Windows systems these are typically Open DataBase Connectivity (ODBC) data source drivers.
- From a script – often found on web servers to dynamically communicate with a database using a scripting language such as PERL, PHP or Python.
- From an Integrated Development Environment (IDE) – programmers using IDEs, such as Microsoft Visual Basic, can build programs that incorporate SQL queries to a database.
Learning standard ANSI-SQL enables you to interact with every major database that exists.
Making SQL queries
Access is the popular database program supplied as part of the Microsoft Office suite. It is popular with Office users on stand-alone PCs and small networks. The SQL View allows you to enter SQL queries to be executed when you click the !Run button.
Microsoft® SQL Server
The SQL Server DBMS products from Microsoft are popular on computers running the Windows operating system. Microsoft SQL Server Express is a free, lightweight and feature-rich database for data-enabled web and Windows applications. SQL queries can be executed from the SQL Server Management Studio.
Microsoft® Visual Studio
Visual Studio can be used to create computer programs that make queries against a database via an ODBC Data Source. Simply select the Connect to Database item on the Tools menu to launch the Add Connections dialog, then choose a data source from the “Use user or system data source name” dropdown list. SQL queries can then be made from the program code.
The Oracle DBMS is popular and widely used in commerce. Oracle Database Express Edition (Oracle Database XE) is a free entry-level, small-footprint database that is simple to administer. SQL queries can be executed from its SQL> command prompt.
The DB2 DBMS is a powerful multi-platform database system. DB2-Express-C is another free full function DB2 data server. SQL queries can be executed from its db2 => command prompt.
The world’s most popular open-source database server is the freely available MySQL DBMS product that is supplied with an integral SQL-client from which to execute SQL queries from its mysql> command prompt. MySQL is used throughout this book to demonstrate the SQL language.
£11.99 paperback / £6.99 ebook
By: Mike McGrath
Also available from other book retailers including Amazon, Barnes & Noble, WHSmith, Google Play Books, and Apple Books.