Adding Data

Granted, most of the work you will be doing with web databases will probably be viewing. For that, the SELECT-FROM-WHERE statements will be perfect for you. However, in many cases you will also want browsers to be able to modify your database. For example, perhaps you want them to be able to add themselves to a mailing list.

The most basic database manipulation is the INSERT operation that adds a new row to a specified table. The INSERT operation follows the basic syntax of:

    INSERT INTO table_name (column_names)
    VALUES (values);
    

For example, we might want to add a new EMPLOYEE using:

INSERT INTO EMPLOYEES 
  (EMP_NUM, EMP_NAME, EMP_COMMISSION, EMP_SALARY)
VALUES 
  (004, 'Lee Eng Lock', '15%', 65000);
    

Of course, since we are assigning values for each column, we need not specify the column names. Thus, the following statement would work as well:

    INSERT INTO EMPLOYEES 
    VALUES 
	(004, 'Lee Eng Lock', '15%', 65000);
    

We now have:

Note that when we perform an INSERT, the incoming value must correspond to the data type defined for that column. Thus, CHARACTER strings must be enclosed in single quotes. Also note that if a row is inserted and some of the columns are not defined, the column will be filled with NULL. Finally, note that the order in which you specify columns and values does not matter, but if you change the order, you must specify column names.

It is also useful to remember that there is no concept of outside order to an SQL table. That is, you cannot know which row will be stored first or last in a table. Thus, you should be careful when assigning things such as unique ordered row id numbers.

Finally, know that you can easily include a SELECT-FROM-WHERE statement as the VALUE such as:

    CREATE TABLE NEW_TABLE
        (EMP_NAME CHAR (20),
         CLIENT_NUM SMALLINT,
         PRODUCT_NUM SMALLINT);

    INSERT INTO NEW_TABLE
	(EMP_NAME, CLIENT_NUM, PRODUCT_NUM)
        SELECT EMPLOYEES.EMP_NAME,
               SALES.C_NUM,
	       SALES.P_NUM
        FROM EMPLOYEES, SALES
        WHERE SALES.E_NUM = EMPLOYEES.EMP_NUM;

The resulting table would look like:

Subqueries
Introduction to Databases for the Web | Table of Contents
Modifying Data