• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint

Introduction to SQL

The following pages will give you an overview of some of the most important SQL statements that will get you started with SQL. For the given examples, we use the Z Gadfly Database. However, the Z Gadfly Database only supports some of the actual SQL statements. For more detailed information on SQL, please refer to an SQL book. There are also some good tutorials on the Internet at






After introducing the statements, you will learn how to use Z SQL methods to interact with your database via a Web page.

Example: Creating an Addressbook Database

For this example, we want to create a database using the Z Gadfly Database that is to function as an address book. Consequently, we need certain data to be stored in this database:

Last Name

First Name



ZIP Code

Phone Number

First, we need to create a new folder on the file system in the /var/gadfly/ directory so that we can store the addresses in a separate database. We will call this folder MyAddressbook. The /var/gadfly/ directory now contains two folders—demo and MyAddressbook. For this example, we will use the database connection that we created earlier in the chapter.

Go to the Properties View of the MyGadflyDatabaseConnection and change the data source from demo to MyAddressbook. The connection is now established and we can start creating tables in the database and fill those tables with data.

The CREATE TABLE Statement

Next, we create a table via the Test View. Enter the following SQL statement into the text area in the view:

1: CREATE TABLE Addressbook (
2:     Name varchar,
3:     FirstName varchar,
4:     Address varchar,
5:     City varchar,
6:     ZIP integer,
7:     Phone varchar)

After you have clicked the Submit Query button, you can see the new table in the Browse View. You need to click the plus sign next to ADDRESSBOOK to see all names of the table columns, as shown in Figure 3.13.

Figure 3.13. Browse View of the created table.

The INSERT INTO Statement

The first step in creating an address book is done. Now we need to fill the table with data. This is done with the INSERT INTO statement. The statement's syntax is as follows:

1:     INSERT INTO table_name (first_row_name,...,last_row_name)
2:     VALUES (first_value, second_value, ... , last_value)

In this example, if we want to enter the address

John Smith

249 9th Ave

New York, NY 10001


into the Addressbook table, we need to send the following SQL statement to the MyAddressbook database:

1: INSERT INTO Addressbook
2: (Name, FirstName, Address, City, ZIP, Phone)
4: ('Smith', 'John', '249 9th Ave', 'New York, NY', 10001, '(212)987-1234')

This SQL statement will return the result shown in Figure 3.14.

Figure 3.14. A data record was successfully inserted.

Although this page looks like an error, it is actually the standard result page if an SQL statement does not return data.

The SELECT...FROM... Statement

To see the new data entered in the table, we need the SELECT...FROM... statement. This statement can be used to get a certain kind of data from a table or the entire table. To see the entire table, you use a wildcard *, as shown in the following:

SELECT * FROM Addressbook

The result of this statement is

249 9th Ave Smith John (212)987-1234 New York, NY 10001

To select certain rows from the table, you use the names of the table rows instead of the wildcard, as shown in the following:

SELECT Name, FirstName FROM Addressbook

This statement will give you the following result:

Smith John

If you want to get certain records from a table, perhaps the name of everyone who lives in New York, NY or everyone whose last name is Smith, you need to specify exactly what you want. This is done by adding a WHERE statement to the SELECT...FROM... statement:

SELECT * FROM Addressbook WHERE (Name='Smith')

If you have a list of entries in your Addressbook table and combine the row and column specifications, as shown in the following:

SELECT Name, FirstName FROM Addressbook
WHERE (Name='Smith')

you might get something like

Smith Jane
Smith John
Smith Ellen

The UPDATE Statement

It is often necessary to change records in an address book because some of the information has changed, perhaps the phone number and/or the address of a person. This is done with the UPDATE statement. The UPDATE statement has the following form:

UPDATE table_name SET col_name = new_value

Using this statement on a table would change the values of the entire col_name column. Therefore, you need to specify which row you want to change. Again, this is done with the WHERE specification:

UPDATE table_name SET col_name = new_value
WHERE col_name = certain_value

The following is an example of how to change the last name of Jane Smith to Williams:

1:     UPDATE Addressbook SET Name='Williams'
2:     WHERE Name='Smith' AND FirstName='John'

The DELETE FROM Statement

To delete records from a table, you use the DELETE FROM statement. Again you need the WHERE statement to specify what you want to delete. For example, if you want to delete the John Smith record from the Addressbook table, the necessary command will be

DELETE FROM Addressbook
WHERE Name='Smith' AND FirstName='John'

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint