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.