Manipulating Data
Creating New Tables
All tables within a database must be created at some point in time...let's
see how we would create the Orders table:
CREATE TABLE ORDERS
(OWNERID INTEGER NOT NULL,
ITEMDESIRED CHAR(40) NOT NULL);
This statement gives the table name and tells the DBMS about each column in
the table. Please note that this statement uses generic data
types, and that the data types might be different, depending on what DBMS you
are using. As usual, check local listings. Some common generic data types are:
- Char(x) - A column of characters, where x is a number designating the
maximum number of characters allowed (maximum length) in the column.
- Integer - A column of whole numbers, positive or negative.
- Decimal(x, y) - A column of decimal numbers, where x is the maximum length
in digits of the decimal numbers in this column, and y is the maximum number
of digits allowed after the decimal point. The maximum (4,2) number would be
99.99.
- Date - A date column in a DBMS-specific format.
- Logical - A column that can hold only two values: TRUE or FALSE.
One other note, the NOT NULL means that the column must have a value
in each row. If NULL was used, that column may be left empty in a given row.
Altering Tables
Let's add a column to the Antiques table to allow the entry of the price of a
given Item:
ALTER TABLE ANTIQUES ADD (PRICE DECIMAL(8,2) NULL);
The data for this new column can be updated or inserted as shown later.
Adding Data
To insert rows into a table, do the following:
INSERT INTO ANTIQUES VALUES (21, 01, 'Ottoman', 200.00);
This inserts the data into the table, as a new row, column-by-column, in the
pre-defined order. Instead, let's change the order and leave Price blank:
INSERT INTO ANTIQUES (BUYERID, SELLERID, ITEM)
VALUES (01,
21, 'Ottoman');
Deleting Data
Let's delete this new row back out of the database:
DELETE FROM ANTIQUES
WHERE ITEM = 'Ottoman';
But if there is another row that contains 'Ottoman', that row will be deleted
also. Let's delete all rows (one, in this case) that contain the specific data
we added before:
DELETE FROM ANTIQUES
WHERE ITEM = 'Ottoman' AND BUYERID = 01
AND SELLERID = 21;
Updating Data
Let's update a Price into a row that doesn't have a price listed yet:
UPDATE ANTIQUES SET PRICE = 500.00 WHERE ITEM = 'Chair';
This sets all Chair's Prices to 500.00. As shown above, more WHERE
conditionals, using AND, must be used to limit the updating to more specific
rows. Also, additional columns may be set by separating equal statements with
commas.