Working with SQL

We spent the last section discussing some of the key concepts concerned with relational databases and communicating with them. SQL is the standard language for communicating with relational databases. SQL provides us with a programming language that we use to select, insert, update, and delete information from a relational database.

The SQL language was developed by IBM researchers as part of a project to develop a prototype relational database based on the relational database model described by E.F. Codd. The new language SEQEL (Structured English Query Language) was first described in a 1976 article of the IBM Journal of R&D. The name was later changed to SQL. In 1978, IBM released System R, based on Codd's relational database model. IBM came to market with a product named System/R in 1978. In 1979, Oracle Corporation introduced the first commercially available implementation of SQL.

Pure SQL is an American National Standards Institute (ANSI) standard and is therefore independent of any database product. However, most database products include proprietary extensions to the standard SQL language. Database companies include these proprietary extensions for two reasons: to provide features that support developer's needs and to distinguish their database product from other DBMS products.

Understanding SQL

These nonstandard extensions, although proprietary, often provide solutions to common problems. As you move forward with your development, you should keep in mind that if you use these proprietary extensions, you lose portability. For example, ANSI-SQL does not provide a way to automatically assign a number ID to new records. In Access, this is handled with a data type of AutoNumber. Oracle handles this with special database objects called sequences. The obvious drawback is that code written for an Access database might not be totally compatible with an Oracle database.

The primary advantages of SQL are as follows:

• English-like structure. Makes the basic syntax easy to learn.

• Vendor independence and portability. For the most part, SQL enables the user to change the underlying brand of database and DBMS without having to rewrite the SQL code. (Variations in the standard and proprietary extensions can complicate the issue.)

• Standardized. The standard is established by ANSI, with most vendors now supporting the SQL 92 or SQL 99 version.

• Dynamic data definition. SQL offers advanced data processing commands that enable embedded and multilevel database queries.

Queries

We are now to the point were we can roll up our sleeves and look at some code! There are a number of reserved words or keywords that are used by SQL. In our discussion. we look at the commands and keywords that are associated with data manipulation.

There are four major command words in SQL that you use to perform most of your database transactions: SELECT, INSERT, UPDATE, and DELETE. In the following sections, we cover all four using an example database to illustrate their use.

Figure 23.1 shows the relationships that exist between the various tables we are using for the SQL examples.

Figure 23.1. Table relationships for SQL examples.

SELECT

The SELECT statement, used to retrieve data from one or more tables, is the most commonly used statement. It requires a minimum of two clauses. The first clause contains the KEYWORD SELECT and identifies the data to be retrieved. The second clause defines the location of the data and is prefaced with the keyword FROM. The following example uses the wildcard character * to retrieve all columns from the table CUSTOMER:

 SELECT *  FROM CUSTOMER 

Although there are cases where you need to retrieve all columns, you should refrain from using the wildcard character unless all rows are required. You improve performance by retrieving only the columns that are needed.

Our second example demonstrates retrieval of specific columns. Note that a comma separates the column names:

 SELECT CustomerID,CustomerFirstName,CustomerLastName  FROM CUSTOMER 

As you examine more SQL, you'll notice that there a number of methods for formatting the SQL code. The following example shows another way of formatting the same SQL statement in a manner that some feel is easier to edit at a later time:

 SELECT CustomerID,         CustomerFirstName,         CustomerLastName  FROM CUSTOMER 

There are two additional clauses that can be part of the SELECT query. One is the WHERE clause, which acts as a filter and is discussed in greater detail shortly.

 SELECT CustomerID, CustomerEmail,  FROM CUSTOMER  WHERE CustomerID = 1234 

The third clause is the ORDER BY clause , which is used to sort the returned recordset. If more than one column is used to order the sort, the column names are separated by a comma. Sorts can be placed in ascending (ASC) or descending (DESC) order. If neither is specified, the default ASC is assumed. The following example shows the sort done on CustomerLastNames and then CustomerFirstNames in descending order:

 SELECT CustomerID,CustomerFirstName,CustomerLastName  FROM CUSTOMER  ORDER BY CustomerLastName, CustomerFirstName DESC 

A technique that is commonly used with SELECT statements is renaming of columns with aliases using the AS keyword. We commonly do this when one of the following conditions applies:

• You are using aggregate functions, such as Count() or Sum().

• You are retrieving multiple columns from multiple tables. This is for clarity and helps reduce the length of the name.

• A condition exists that makes it desirable to rename the column. The condition might be a confusing column name or an illegal character in the column name.

 SELECT count(CustomerID) AS CustomerCount  FROM CUSTOMER  

INSERT

The INSERT statement is used to insert or add new records into a table. The first clause of the INSERT statement uses the keywords INSERT INTO followed by the table name and list of column names, again separated by commas. The second clause contains the keyword VALUES followed by a comma-delimited list of values that will be inserted.

 INSERT INTO CUSTOMER(CustomerID,CustomerFirstName,       CustomerLastname,CustomerEmail)  VALUES (1234,'Bradford','Haynes',       '[e-mail]') 

Notice that the value 'Bradford' is surrounded by a single quote although the value 1234 is not. The presence of a single quote indicates that the value is a string.

UPDATE

The UPDATE statement is comprised of at least two clauses and is used to modify existing records. The first clause uses the keyword UPDATE followed by the table to be updated. The second clause uses the keyword SET and lists the column(s) to be updated and the new value.

 UPDATE CUSTOMER  SET CustomerLastName = 'Smith' 

The code in the previous example sets the value for CustomerLastName to Smith for all records in the table. Although you might occasionally want to do global updates, you more frequently need to update specific records or sets of records. The UPDATE statement can also use a third clause of WHERE, which acts as a filter to specify the records to be updated.

 UPDATE CUSTOMER  SET CustomerFirstName = 'Mandi'  WHERE CustomerID = 1234 

If you be updating more the one column, the column name value pairs are separated by commas.

 UPDATE CUSTOMER  SET CustomerFirstName = 'Mandi',       CustomerEmail = '[e-mail]'  WHERE CustomerID = 1234  

DELETE

The last of our data manipulation statements is the DELETE statement, which is used to remove records from a table. The DELETE statement can be used with a single clause, using the DELETE keyword followed by the table name:

 DELETE FROM CUSTOMER 

This is great if you want to delete all records in a table, but it is bad if you want delete only one record. To delete a specific record or group of records, you add the WHERE clause to the DELETE statement.

 DELETE FROM CUSTOMER  WHERE CustomerID = 1234 

Obviously, a great deal of care has to be exercised when using the DELETE statement. Something that we do prior to executing DELETE statements for the first time is to write the equivalent SELECT statement. This enables you to ensure that the DELETE statement is deleting the record or recordset that you thought it would.

The WHERE Clause

The WHERE clause acts as a filter for your queries. As we saw from some of the previous examples, an UPDATE query without a WHERE clause performs a global update and modifies all rows. The same holds true for a DELETE statement; all records will be deleted. If we are looking for the email address of a specific customer, we do not want a query returning several hundred records. The WHERE clause acts as a filter. In our previous examples, we saw the WHERE clause used only with the = condition. Now let's examine some of the other conditions that can be used in the WHERE clause. For instance, not equal (<>) returns all values not equal to the specified value.

 WHERE CustomerID <> 1234 

Less than (<) returns all values less than the specified value.

 WHERE CustomerID < 1234 

Less than or equal to (<=) returns all values less than or equal to the specified value/

 WHERE CustomerID <= 1234 

Greater than (>) returns all values greater than the specified value.

 WHERE CustomerID > 1234 

Greater than or equal to (>=) returns all values greater than or equal to the specified value.

 WHERE CustomerFirstName >= 'H' 

Now's the time for a couple quick comments on evaluating strings. Remember that strings are evaluated one character at a time and are evaluated as numeric, lowercase, and then uppercase. This means that the previous WHERE clause would return "'Hank"' and "'ROBERT"' but not "'sarah'". BETWEEN returns all values in a given range; this is inclusive of the upper and lower values.

 WHERE CustomerID BETWEEN 1234 AND 6000 

The BETWEEN statement is actually a statement that combines a greater than or equal to and a less than or equal to condition.

 WHERE CustomerID >= 1234 AND WHERE CustomerID <= 6000 

The LIKE keyword returns any record that contains the specified pattern. Use of the wildcard % indicates that any value can appear.

 WHERE CustomerLastName LIKE '%nes' 

The preceding statement returns all CustomerLastNames that end with "nes."

The following statement returns all CustomerLastNames that contain "y."

 WHERE CustomerLastName LIKE '%y%' 

This statement returns all CustomerLastNames that begin with "Ha."

 WHERE CustomerLastName LIKE 'Ha%' 

There is a second type of wildcard that can be used with the LIKE condition. This is used to specify a single character or specific characters.

 WHERE CustomerFirstName LIKE 'Mand[iy]' 

This statement returns Mandi or Mandy but not Mandaline.

The IN returns all records that match any of the values in a given list.

 WHERE CustomerFirstName IN('Bradford','Mandi','EK') 

The NOT condition negates the rest of the condition. In this example, it would return all CustomerID values that were less than 1234 or greater than 6000.

 WHERE CustomerID NOT BETWEEN 1234 AND 6000 

IS NULL returns all records where the column value contains a null value (in other words, the column values are empty).

 WHERE CustomerFirstName IS NULL 

IS NOT NULL returns all records that contain a value for the column specified.

 WHERE CustomerFirstName IS NOT NULL 

We can also combine conditions. In addition, we can specify whether all the given conditions must be met or just one. If we need to have multiple conditions met, we join the conditions with the keyword AND. In the following example, both conditions must be met.

 WHERE CustomerFirstName = 'Mandi' AND        CustomerLastName = 'Haynes' 

In our next example, we are using the OR keyword. This means that if either condition is met, the record is a match.

 WHERE CustomerFirstName = 'Mandi' OR        CustomerLastName = 'Smith' 

When writing conditions that contain both AND and OR, you need to understand the precedence that is used to evaluate the statement. All AND conditions are evaluated first, and then the OR conditions are evaluated. When we wrote the condition

 WHERE CustomerFirstName = 'Mandi' AND        CustomerLastName = 'Haynes' OR        CustomerLastName = 'Smith' 

we wanted all records with a CustomerFirstName of Mandi and CustomerLastName of either Haynes or Smith. What we received were records with a CustomerFirstName of Mandi, a CustomerLastName of Haynes, and all records with a CustomerLastName of Smith. Use parentheses to control the evaluation. This returns the recordset that you really wanted.

 WHERE CustomerFirstName = 'Mandi' AND        (CustomerLastName = 'Haynes' OR        CustomerLastName = 'Smith')  

Summary

SQL is used to access data in a relational database. SQL provides you with a means of both retrieving and manipulating the data. The four command words of SELECT, INSERT, UPDATE, and DELETE are used for data manipulation. The WHERE clause provides the means for filtering or setting conditions for the records that are returned. The ORDER BY clause enables you to sort the recordset to meet your needs. Although SQL is an ANSI standard, many DBMSs include proprietary extensions. Use of these proprietary extensions prevents code from being platform-independent.