SQL (Structured Query Language) in a book about general purpose programming languages? Our main goal is to find the most expressive programs, and SQL is definitely able to describe the solution of some complex tasks in a concise and expressive way. These tasks do not only include queries and updates, but also the related transaction handling which is not as easily handled by any of other language in this book.
The relational database model was developed by Ted Codd at IBM in the late 1960's and first published in 1970. SQL dates back to IBM's implementation of a relational database system during the 1970's. This system had a query language called SEQUEL (Structured English Query Language) whose name was later shortened to SQL. The language was also adopted by another (at that time) small company that created a relational database management system called Oracle.
The first ANSI standard for SQL, SQL86, was adopted in 1986. Since then there have been three updates, SQL89, SQL92, and finally SQL99 (now standardizing everything from complex data types (arrays, etc.) to a call level interface similar to ODBC). However, there are significant differences between the between the SQL implementations of the different relational database systems, especially when it comes to the procedural extensions of SQL.
SQL is always implemented as part of a relational database system (RDBMS) which means that we have to pick a suitable RDBMS for our examples. Sticking to open source, PostgreSQL offers very good SQL support and also has a extension similar to Oracle's psql which shows how procedural elements can be added to SQL to make it a fully-fledged general purpose programming language.
Since we are dealing with a database system based on the client-server model, getting started takes a little bit more effort than just starting an interactive shell. We need to install the software, start the database server, create a user and database, and finally start the interactive client. Fortunately, PostgreSQL is fairly easy to install, and most of this burden is handled automatically. When installing the PostgreSQL package on a Linux system (Debian in my case), the system creates the UNIX user postgresunder which the server runs and adds the server to the boot sequence (/etc/init.d/postgresql) so that it starts automatically. The only thing left to do is to create a database user for our personal UNIX user (in my case ahohmann) by calling the create_user command under the postgres user.
Once this is accomplished, we can start PostgreSQL's interactive client psql and start our experiments.
ahohmann@kermit:~$ psql sample
Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quitThis interactive shell allows us to enter any SQL statement. Shell commands start with a backslash (e.g., \q to leave the shell). We can use the help commands \? and \? to get an overview of the available commands and SQL statements.
Written by one of the PostgreSQL developers, [MOMJIAN01]> is a gentle introduction to SQL using the PostgreSQL database system.