Structured Query Language/Handle Data

As shown in the previous page, we now have an empty table named person. What can we do with such a table? Just use it like a bag! Store things in it, look into it to check the existence of things, modify things in it or throw things out of it. These are the four essential operations, which concerns data in tables:

• INSERT: put some data into the table
• SELECT: retrieve data from the table
• UPDATE: modify data, which exists in the table
• DELETE: remove data from the table.

For each of these four operations, there is a SQL command. It starts with a keyword and runs up to a terminating semicolon. This rule applies to all SQL commands: They are introduced by a keyword and terminated by a semicolon. In the middle, there may be more keywords as well as object names and values.

Store new Data with INSERT Command

When storing new data in rows of a table, we must name all affected objects and values: the table name (there may be a lot of tables within the database), the column names and the values. All this is embedded within some keywords so that the SQL compiler can recognize the tokens and their meaning. In general, the syntax for a simple INSERT is

INSERT INTO <tablename> (<list_of_columnnames>)
VALUES                  (<list_of_values>);

Here is an example

-- put one row
INSERT INTO person (id, firstname, lastname,    date_of_birth,     place_of_birth, ssn,           weight)
VALUES             (1,  'Larry',   'Goldstein', date'1970-11-20', 'Dallas',        '078-05-1120', 95);
-- confirm the INSERT command
COMMIT;

When the DBMS recognizes the keywords INSERT INTO and VALUES, it knows what to do: it creates a new row in the table and puts the given values into the named columns. In the above example, the command is followed by a second one: COMMIT confirms the INSERT operation as well as the other writing operations UPDATE and DELETE. (We will learn much more about COMMIT and its counterpart ROLLBACK in a later chapter.)

A short comment about the format of the value for date_of_birth: There is no unique format for dates honored all over the world. Peoples use different formats depending on their cultural habits. For our purpose, we decide to represent dates in the hierarchical format defined in ISO 8601. It may be possible that your local database installation use a different format so that you are forced to either modify our examples or to modify the default date format of your database installation.

Now we will put some more rows into our table. To do so, we use a variation of the above syntax. It is possible to omit the list of column names if the list of values correlates precisely with the number, order, and data type of the columns used in the original CREATE TABLE statement.

Hint: The practice of omitting the list of column names is not recommended for real applications! Table structures change over time, e.g. someone may add new columns to the table. In this case, unexpected side effects may occur in applications.

-- put four rows
INSERT INTO person VALUES (2,  'Tom',    'Burton',    date'1980-01-22', 'Birmingham',    '078-05-1121', 75);
INSERT INTO person VALUES (3,  'Lisa',   'Hamilton',  date'1975-12-30', 'Mumbai',        '078-05-1122', 56);
INSERT INTO person VALUES (4,  'Debora', 'Patterson', date'2011-06-01', 'Shanghai',      '078-05-1123', 11);
INSERT INTO person VALUES (5,  'James',  'de Winter', date'1975-12-23', 'San Francisco', '078-05-1124', 75);
COMMIT;

Retrieve Data with SELECT Command

Now our table should contain five rows. Can we be sure about that? How can we check whether everything worked well and the rows and values exist really? To do so, we need a command which shows us the actual content of the table. It is the SELECT command with the following general syntax

SELECT   <list_of_columnnames>
FROM     <tablename>
WHERE    <search_condition>
ORDER BY <order_by_clause>;

As with the INSERT command, you may omit some parts. The simplest example is

SELECT   *
FROM     person;

The asterisk character '*' indicates 'all columns'. In the result, the DBMS should deliver all five rows, each with the seven values we used previously with the INSERT command.

In the following examples, we add the currently missing clauses of the general syntax - one after the other.

Add a list of some or all columnnames

SELECT   firstname, lastname
FROM     person;

The DBMS should deliver the two columns firstname and lastname of all five rows.

Add a search condition

SELECT   id, firstname, lastname
FROM     person
WHERE    id > 2;

The DBMS should deliver the three columns id, firstname and lastname of three rows.

Add a sort instruction

SELECT   id, firstname, lastname, date_of_birth
FROM     person
WHERE    id > 2
ORDER BY date_of_birth;

The DBMS should deliver the four columns id, firstname, lastname and date_of_birth of three rows in the ascending order of date_of_birth.

Modify Data with UPDATE Command

If we want to change the values of some columns in some rows we can do so by using the UPDATE command. The general syntax for a simple UPDATE is:

UPDATE <tablename>
SET    <columnname> = <value>, 
       <columnname> = <value>,
                   ...
WHERE  <search_condition>;

Values are assigned to the named columns. Unmentioned columns keep unchanged. The search_condition acts in the same way as in the SELECT command. It restricts the coverage of the command to rows, which satisfy the criteria. If the WHERE keyword and the search_condition are omitted, all rows of the table are affected. It is possible to specify search_conditions, which hit no rows. In this case, no rows are updated - and no error or exception occurs.

Change one column of one row

UPDATE person
SET    firstname = 'James Walker' 
WHERE  id = 5;
COMMIT;

The first name of Mr. de Winter changes to James Walker, whereas all his other values keep unchanged. Also, all other rows keep unchanged. Please verify this with a SELECT command.

Change one column of multiple rows

UPDATE person
SET    firstname = 'Unknown' 
WHERE  date_of_birth < date'2000-01-01';
COMMIT;

The <search_condition> isn't restricted to the Primary Key column. We can specify any other column. And the comparison operator isn't restricted to the equal sign. We can use different operators - they solely have to match the data type of the column.

In this example, we change the firstname of four rows with a single command. If there is a table with millions of rows we can change all of them using one single command.

Change two columns of one row

-- Please note the additional comma
UPDATE person
SET    firstname = 'Jimmy Walker', 
       lastname  = 'de la Crux' 
WHERE  id = 5;
COMMIT;

The two values are changed with one single command.

Remove data with DELETE Command

The DELETE command removes complete rows from the table. As the rows are removed as a whole, there is no need to specify any columnname. The semantics of the <search_condition> is the same as with SELECT and UPDATE.

DELETE
FROM   <tablename>
WHERE  <search_condition>;

Delete one row

DELETE
FROM   person
WHERE  id = 5;
COMMIT;

The row of James de Winter is removed from the table.

Delete many rows

DELETE
FROM   person;
COMMIT;

All remained rows are deleted as we have omitted the <search_condition>. The table is empty, but it still exists.

No rows affected

DELETE
FROM   person
WHERE  id = 99;
COMMIT;

This command will remove no row as there is no row with id equals to 99. But the syntax and the execution within the DBMS are still perfect. No exception is thrown. The command terminates without any error message or error code.

Summary

The INSERT and DELETE commands affect rows in their entirety. INSERT puts a complete new row into a table (unmentioned columns remain empty), and DELETE removes entire rows. In contrast, SELECT and UPDATE affect only those columns that are mentioned in the command; unmentioned columns are unaffected.

The INSERT command (in the simple version of this page) has no <search_condition> and therefore handles exactly one row. The three other commands may affect zero, one, or more rows depending on the evaluation of their <search_condition>.