Demystifying SQL

SQL (Structured Query Language) is an ANSI/NCITS language standard for querying and manipulating databases. It provides for a small measure of sanity when a DBA or programmer switches from one database to another by standardizing the commands needed to do their jobs. Most modern commercial databases support SQL (I cannot think of one that does not), and most of them also extend the language in useful but annoying ways. Suffice to say, you can usually accomplish what you need to with the standard SQL commands, but it may be easier to do the task with a database-specific command.

The SQL commands themselves are very basic, but they can be put together in very complex ways. They fall into one of two categories: DDL (Data Definition Language - create/modify/delete tables) and DML (Data Manipulation Language - create/modify/delete data in the tables). DML commands usually consist of an action/verb, a table identifier, and some supporting data. These commands can be modified to include things like conditionals, so that a subset of data is acted on rather than the whole set, ordering directives, to get the data back in a pre-determined order, and aggregation directives, to perform data aggregations. SQL is a case-insensitive language, however you will often see SQL syntax in uppercase to help distinguish it from the data.

To extract a list of employees from a database we would use the SELECT statement:

	SELECT firstname, lastname FROM employees;
This uses the SELECT statement to get values for the FirstName and LastName fields from the Employees table. It will iterate through all records in the table (in no guaranteed sequence) and return the data in the two fields for each record. If we wanted to select the same fields but only when the employee is also a Manager, and order it alphabetically, we would have:
	SELECT firstname, lastname FROM employees
		WHERE is_manager = 1
		ORDER BY lastname, firstname;
This uses the WHERE clause to limit the result set and the ORDER BY clause to give us the data in a predefined sequence. Similar commands exist for updating, adding and deleting data:
	UPDATE employees
		SET lastname = 'Smith-Jones'
		WHERE firstname = 'Mary' AND lastname = 'Smith';
		
	INSERT INTO employees (lastname, firstname)
		VALUES ('Brown', 'Mike');
	DELETE FROM employees WHERE is_fired = 1;
More complex actions, like joins and sub-queries, require more complex SQL statements, although they are built using the same simple commands. This next example updates data from one table based on data in another table:
	UPDATE employees
		SET hourly_wage = hourly_wage + 2
		WHERE employee_number IN
			(SELECT employee_number FROM gets_a_raise);

For more of an introduction to SQL, see Jim Hoffman's excellent SQL Tutorial at http://w3.one.net/~jhoffman/sqltut.htm.


Copyright © Joseph L. Casadonte Jr. 1998. All rights reserved.
Demystifying SQL / 17 January 1998 / joc@netaxs.com