| 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 within 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 become complex, allowing not only queries from a database, but adding, modifying, and deleting information from a database as well.
Consider trying to extract information from the product table of the Web store example. If you set up the table correctly, it should resemble the table that follows: | Field Name | Date Type | | ProductID | AutoNumber | | ProductName | Text | | ProductDescription | Memo | You could also list products within rows that could look like the following: | ProductID | ProductName | ProductDescription | | 1 | Black Hawk Down | DVD | | 2 | Black Hawk Down | VHS | | 3 | Black Hawk Down | CD Soundtrack | | 4 | The Natural | DVD | | 5 | Pulp Fiction | DVD | | 6 | Armageddon | DVD | | 7 | Armageddon | VHS | | 8 | Heat | DVD | Consider some important aspects about the following table, columns, and data contained within the eight rows. The products table contains three columns, a ProductID with an AutoNumber that increments a value whenever an item is added, a ProductName that contains a Text data type allowing for a simple title of the product, and a column for ProductDescription with a Memo data type. The last data type could be either Text or Memo. The reason for the Memo data type is simply as a precautionary measure in case the person who is doing the data entry for the product table wants to add a full paragraph description of the product being added. The last thing to consider is the data contained within the table. We are simply storing a list of DVDs, VHS tapes, and CD soundtracks that are to be sold within the Web store application. 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: 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. * or Field Names The asterisk or names of the fields tell the statement which columns you want to extract data from. 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 or names from which you want to extract the data. The following example would extract all records from your products table: Select * From Products The preceding statement uses two keywords, the Select keyword and the From keyword to extract all records from the products table. Note the use of the "*" after the Select keyword. Rather than typing out every column name within our table in the statement, the "*" could be included instead. The following line would produce similar results: Select ProductID, ProductName, ProductDescription From Products The following table would produce the results of your statement: | ProductID | ProductName | ProductDescription | | 1 | Black Hawk Down | DVD | | 2 | Black Hawk Down | VHS | | 3 | Black Hawk Down | CD Soundtrack | | 4 | The Natural | DVD | | 5 | Pulp Fiction | DVD | | 6 | Armageddon | DVD | | 7 | Armageddon | VHS | | 8 | Heat | DVD | Selecting Certain Fields If you did not want to select all the fields within the database table, you could modify the field names to include only the fields that you wanted. Select ProductID, ProductName From Products Notice that the preceding statement would retrieve the data only from the ProductID and the ProductName fields. The results would produce the following: | ProductID | ProductName | | 1 | Black Hawk Down | | 2 | Black Hawk Down | | 3 | Black Hawk Down | | 4 | The Natural | | 5 | Pulp Fiction | | 6 | Armageddon | | 7 | Armageddon | | 8 | Heat | You could also modify the statement in an effort to retrieve the same information in a different order. For example, switching the field names by placing ProductName in front of ProductID would give the following result: | ProductName | ProductID | | Black Hawk Down | 1 | | Black Hawk Down | 2 | | Black Hawk Down | 3 | | The Natural | 4 | | Pulp Fiction | 5 | | Armageddon | 6 | | Armageddon | 7 | | Heat | 8 | Selecting Unique Data The information within the productstable contains duplicate values. Although we know that Black Hawk Down has three formats, it does not change the fact that it is listed within ProductName three times. If someone wanted to know just the titles of the movies that were in the database, it would retrieve two values twice and in the case of the title Black Hawk Down, it would produce three results. The Distinct keyword could be used before the field name to extract only unique instances of data contained within the table column. Select Distinct ProductName From Products The preceding statement would produce the following result: | ProductName | | Black Hawk Down | | The Natural | | Pulp Fiction | | Armageddon | | Heat | The Insert Statement Collecting information from your users is not uncommon and in most cases, it is a necessity. With the Web store, although you would never want a user to add information to the products table, you would want them to register on your site so that they could begin shopping. When registering, you would want your users to create new accounts which would, in turn, create new records in the customers table. To illustrate this point, take the customers table and observe some of the fields that make it up: | Field Name | Date Type | | CustomerID | AutoNumber | | FirstName | Text | | LastName | Text | | Username | Text | | Password | Text | | Email | Text | | PhoneNumber | Number | | BillingAddress | Text | | BillingCity | Text | | BillingState | Text | | BillingZip | Text | You could easily generate a new record using the following statement: Insert Into Customers (FirstName, LastName, Username, Password, Email, PhoneNumber, BillingAddress, BillingCity, BillingState, BillingZip) Values ('Zak', 'Ruvalcaba', 'zruvalcaba', 'password', 'zak@modulemedia.com', '5555555555', '555 Sample St.', 'San Diego', 'Ca', '92069') The preceding statement would insert all the values that you specified into the proper columns within the customers table. The Insert keyword generally uses: Insert The Insert keyword is used to identify the statement or action you are attempting to perform on the database. Other keywords included Select, Delete, and Update. Into The Into keyword specifies that you are inserting something into a specific table. Table Name The table name that you want to insert the values into. Values The actual values that are to be inserted. You could also use the Select statement within the Insert statement to literally copy information from one table to the other, which is very similar to what you will be doing with the transactions table. Insert Into Transactions (FirstName, LastName, Email) Select FirstName, LastName, Email From Customers The Update Statement The Update statement is used to define changes within your database tables. Database information is not static; rather, it is constantly changing depending on user feedback or input. The Update statement requires certain keywords, operators, and usually a Where clause to modify the specific record, for instance: Update Customers Set LastName = "Smith" Where CustomerID = '2' This statement would effectively change the last name of the customer whose ID matches 2. 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.
The Delete Statement The Delete statement can be used to remove unneeded records from the database. The Delete statement can be used to delete all records, specific records, or records that meet certain criteria. Delete From Customers The preceding statement would effectively remove all the customers from the customers table. Delete From Customer Where CustomerID = '2' This statement would remove only the record with the CustomerID of 2. Delete From Customer Where LastName = 'Smith' This statement would remove all records that contain the last name of Smith. | 
