Basic SQL

Just as your savings account would be useless without a valid ID or bank card to get to that money, information contained within a database is useless data unless you have the means of extracting it. SQL is the language that does just that; it allows for quick and complex access to the data contained in your database through the use of queries. Queries pose the questions and return the results to your application, usually in the form of a recordset.

CAUTION

Don't think of SQL as simply a way of extracting information. The SQL language can be complex, allowing not only queries from a database, but can add, modify, and delete information from a database as well.

Consider trying to extract information from the EmployeeStore table of the Dorknozzle database. Recall that the EmployeeStore table resembles the table that follows (although this table does not show the ItemDescription and Headshot columns):

Field Name	Date Type
ItemID	AutoNumber
ItemName	Text
Quantity	Currency
Cost	Number

You can then list products in rows that would look like the following:

ItemID	ItemName	Cost	Quantity
1	Dorknozzle Shirt	$12.99	100
2	Dorknozzle Hooded	$29.99	100
3	Dorknozzle Longsleeve	$19.99	100
4	Dorknozzle Polo	$24.99	100
5	Dorknozzle Sticker	$ 1.99	100
6	Dorknozzle Mousepad	$ 4.99	100
7	Dorknozzle Coffee Mug	$ 6.99	100
8	Dorknozzle Water Bottle	$ 9.99	100

Consider some important aspects about the previous table, columns, and data contained in the eight rows. The EmployeeStore table contains four columns: an ItemID with an AutoNumber that increments a value whenever an item is added, an ItemName that contains a Text data type allowing for a simple title of the product to be added, a column for Cost with a Currency data type that allows us to store price information for each specific item, and a Quantity column with a Number data type that allows us to store a numeric value indicating how many of a specific item we have left in our inventory. The last thing to consider is the data contained in the table. We are storing a list of Dorknozzle employee store items that are to be sold from the Web Store application.

Now what? You have the table created, columns and data types have been outlined, and you have rows of data in the table. Our next step is to get to our data somehow. The next few sections outline how to use SQL to extract data from your tables.

The SELECT Statement

The foundation to all SQL queries is the SELECT statement. Made up of two keywords, the SELECT statement provides a means for retrieving the data from the database. In its simplest form, the SELECT statement is written using the following elements:

• SELECT The SELECT keyword is used to identify the statement or action you are attempting to perform on the database. Other keywords include INSERT, DELETE, and UPDATE. More on these later.

• * or field names The asterisk or names of the fields tell the statement which columns you want to extract data from. In this case, the asterisk means "all fields."

• FROM The FROM keyword identifies which table to extract the data from. The FROM keyword is required with all SELECT statements.

• Table name(s) The table name from which you want to extract the data.

The following example extracts all records from your EmployeeStore table:

 SELECT * FROM EmployeeStore 

The preceding statement uses two keywordsthe SELECT keyword and the FROM keywordto extract all records from the EmployeeStore table. The previous statement would produce the following results (some fields have been excluded in order to fit on the page):

ItemID	ItemName	Cost	Quantity
1	Dorknozzle Shirt	$12.99	100
2	Dorknozzle Hooded	$29.99	100
3	Dorknozzle Longsleeve	$19.99	100
4	Dorknozzle Polo	$24.99	100
5	Dorknozzle Sticker	$ 1.99	100
6	Dorknozzle Mousepad	$ 1.99	100
7	Dorknozzle Coffee Mug	$ 1.99	100
8	Dorknozzle Water Bottle	$ 9.99	100

Selecting Certain Fields

If you did not want to select all the fields in the database table, you could modify the field names to include only the fields that you wanted.

 SELECT ItemID, ItemName, Cost FROM EmployeeStore 

Notice that the preceding statement retrieves the data only from the ItemID, ItemName, and Cost fields. The preceding query produces the following results:

ItemID	ItemName	Cost
1	Dorknozzle Shirt	$12.99
2	Dorknozzle Hooded	$29.99
3	Dorknozzle Longsleeve	$19.99
4	Dorknozzle Polo	$24.99
5	Dorknozzle Sticker	$ 1.99
6	Dorknozzle Mousepad	$ 1.99
7	Dorknozzle Coffee Mug	$ 1.99
8	Dorknozzle Water Bottle	$ 9.99

Notice that in this case, the ItemName, ItemDescription, and Quantity columns are left off. You could also modify the statement in an effort to retrieve the same information in a different order. For example, we could switch the field names by placing ItemName in front of ItemID like this:

 SELECT ItemName, ItemID, Cost FROM EmployeeStore 

This code would give the following result:

ItemName	ItemID	Cost
Dorknozzle Shirt	1	$12.99
Dorknozzle Hooded	2	$29.99
Dorknozzle Longsleeve	3	$19.99
Dorknozzle Polo	4	$24.99
Dorknozzle Sticker	5	$ 1.99
Dorknozzle Mousepad	6	$ 1.99
Dorknozzle Coffee Mug	7	$ 1.99
Dorknozzle Water Bottle	8	$ 9.99

Selecting Unique Data

The information in the EmployeeStore table contains duplicate values. As you can see, we have three items in our table that are priced at $1.99. If someone wanted to know about the unique variety of prices in our database, we would have to modify the statement to produce distinct results. The DISTINCT keyword can be used before the Cost field in this case to extract from the table only unique instances of data contained in that field.

 SELECT DISTINCT Cost FROM EmployeeStore 

The preceding statement would produce the following result:

Cost

$12.99

$29.99

$19.99

$24.99

$1.99

$9.99

As you can see, in this case, all cost information is displayed but the results are limited to unique price instances. $1.99 is only listed once as opposed to three times.

Clauses

Clauses are portions of SQL that allow for further refinement of the query or additional work that must be accomplished by the SQL statement. The following clauses are covered in this section:

• The WHERE clause

• The ORDER BY clause

• The GROUP BY clause

The WHERE Clause

The WHERE clause is used in conjunction with the SELECT statement to deliver a more refined search based on individual field criteria. This example could be used to extract a specific employee based on a name:

 SELECT * FROM Employees WHERE Name = 'Ada' 

Notice that the selection is made only when a certain criteria is true. If a record with the name of Ada did not exist, it wouldn't return anything. But what if we had more than one Ada in the database? You could refine your search even further by using the AND operator:

 SELECT * FROM Employees WHERE Name = 'Ada' AND Phone = '5555551111' 

In this case, even if two Adas were listed in our database, we can assume that they don't have the same phone number. In this situation, the query returns one result (assuming, of course, that the two Adas aren't roommates).

The ORDER BY Clause

The ORDER BY clause provides you with a quick way of sorting the results of your query in either ascending or descending order. Consider the following table of information:

EmployeeID	Name	Email
1	Cammy	[e-mail]
2	Ferris	[e-mail]
3	Ada	[e-mail]
4	Dave	[e-mail]
5	Agnes	[e-mail]

If you selected all the records by using a SELECT All statement (SELECT *), it would return all the results, ordering them based on the value in the EmployeeID field: 1 through 5. Using the SELECT statement with an ORDER BY clause allows you to sort based on a different field name:

 SELECT * FROM Employees ORDER BY Name 

The preceding statement would return results in the following order:

EmployeeID	Name	Email
3	Ada	[e-mail]
5	Agnes	[e-mail]
1	Cammy	[e-mail]
4	Dave	[e-mail]
2	Ferris	[e-mail]

You can also order by multiple columns by adding a comma after the field name and entering a second field name:

 SELECT * FROM Employees ORDER BY Name, Phone 

In this case, all records with identical Name fields are sorted by phone.

TIP

You might decide to sort the results of your query in either ascending or descending order. When this is the case, you can use the ASC and DESC keywords preceding the field names as follows:

 SELECT * FROM Employees ORDER BY Name, Phone DESC 

The GROUP BY Clause

When a query statement includes a GROUP BY clause, the SELECT statement for that query can list functions while operating on groups of data values in other columns. For example, data within the Orders table could look similar to the following table:

OrderID	EmployeeID	ItemID	Quantity
1	1	2	2
2	1	4	4
3	3	8	4
4	4	7	2
5	5	2	2
6	5	7	1

If you wanted to retrieve the total number of orders that were received, you could run the following query:

 SELECT Count(Quantity) AS NumberOfOrders FROM Orders 

The result would return the following:

NumberOfOrders

6

In this case, we're exploring two unique concepts. First, we're selecting a count, using the built in Count function to return a total number of orders for the Quantity column. Second, we're using the AS keyword to create a virtual field called "NumberOfOrders." What this does is gives us a total count of orders and stores that number (6) temporarily within a virtual field called NumberOfOrders.

You could use the GROUP BY clause in this instance to group the orders by EmployeeID as follows:

 SELECT EmployeeID, Count(Quantity) AS NumberOfOrders FROM Orders GROUP BY EmployeeID 

The result would be as follows:

EmployeeID	NumberOfOrders
1	2
3	1
4	1
5	2

The result is based on the fact that employees 1 and 5 made two orders each while employees 3 and 4 made one order each.

The INSERT Statement

Collecting information from your users is not uncommon and, in most cases, it is a necessity. When you collect information such as registration information, you're not querying data, but rather you're inserting data into the database. In our Dorknozzle example for instance, we'll create an Admin page that allows administrators to insert new employees into the database. To illustrate this point, consider the Employees table and some of the fields that make it up:

Field Name	Date Type
EmployeeID	AutoNumber
DepartmentID	Number
Name	Text
Username	Text
Password	Text
Email	Text
Phone	Text
Headshot	Text
BillingShippingAddress	Text
BillingShippingCity	Text
BillingShippingState	Text
BillingShippingZip	Text

You could easily insert a new record into the Employees table using the following INSERT statement:

 INSERT INTO Employees       (DepartmentID, Name, Username, Password, Email,        Phone, Headshot, BillingShippingAddress, BillingShippingCity,        BillingShippingState, BillingShippingZip) VALUES       (1, 'Zak', 'zak', 'zak', '[e-mail]', '5555555555',        'Images\head_zak.gif', '555 Sample St.', 'San Diego', 'Ca', '92115') 

The preceding statement inserts all the values you specified into the proper columns within the Employees table. The INSERT keyword generally uses the following elements:

• INSERT The INSERT keyword is used to identify the statement or action you are attempting to perform on the database.

• INTO The INTO keyword specifies that you are inserting something into a specific table.

• Table name The name of the table into which you want to insert the values.

• VALUES The actual values to be inserted.

You could also use the SELECT statement within the INSERT statement to literally copy information from one table to the other:

 INSERT INTO Transactions (EmployeeID, Name, Email)  SELECT EmployeeID, Name, Email FROM Employees WHERE EmployeeID = 1 

The preceding statement, of course, assumes that we have a Transactions table. At any rate, this statement effectively copies from the Employees table the EmployeeID, Name, and Email whose EmployeeID is equal to 1 and copies this data into the Transactions table.

The UPDATE Statement

The UPDATE statement is used to define changes within your database tables. As you're probably aware, database information is not static, rather, it is constantly changing depending on user feedback or input. As an example, assume that an administrator wanted to change specific data (maybe a username and password) for a particular employee within the Employees table. To make these changes to an existing record in the table, an UPDATE statement would have to be used.

The UPDATE statement requires certain keywords, operators, and usually a WHERE clause to modify the specific record, for instance:

 UPDATE Employees SET Name = "Cammi" WHERE EmployeeID = 3 

This statement effectively changes Cammy's name to "Cammi' since she matches the EmployeeID of 3.

NOTE

Operators enable you to connect certain portions of your statement, whereas clauses allow for more refined queries and searches. Both are discussed later in the chapter.

Of course, you don't have to use the EmployeeID field with the WHERE clause. Instead, you could use Cammy's name as follows:

 UPDATE Employees SET Name = "Cammi" WHERE Name = "Cammy" 

In this case, all instances of "Cammy" are replaced with "Cammi" in the database.

The DELETE Statement

The DELETE statement can be used to remove unneeded records from the database. For instance, if you wanted to remove all employees from the Employees table, we might write a DELETE statement as follows:

 DELETE  FROM Employees 

The preceding statement effectively removes all the employees from the Employees table. Of course, this doesn't make much sense! You wouldn't want to just go and remove all employees from your database. Instead, you might want to delete a specific employeefor instance, if they were fired. If this were the case, you could append a WHERE clause to your statement to simply remove one record:

 DELETE  FROM Employees WHERE EmployeeID = 2 

This statement removes only the record where the EmployeeID is equal to 2. As was the case with the UPDATE example, you could also delete a user by name:

 DELETE FROM Employees WHERE Name = 'Agnes' 

This statement removes all records from the Employees table whose Name field matches "Agnes."