More about maintaining a table:
The Data Manipulation Language (DML) in SQL*Plus for Oracle contains commands that allow you to manipulate the data
base. Some of the commands are:
| SQL |
Processing |
| INSERT |
Add a row of data to an existing table |
| UPDATE |
Changes data in an existing table |
| DELETE |
Deletes rows of data from an existing table |
CREATE:
The create is used to create the structure of the table. To review, I am creating a table called MAINTAIN which contains
the five columns/fields shown below.
SQL CODE:
SQL> CREATE TABLE MAINTAIN
2 (idno VARCHAR2(3),
3 itemname VARCHAR2(10),
4 price NUMBER(6,2),
5 purchased DATE,
6 dept CHAR(2));
Table created.
DESCRIBE:
Then I am describing the table using DESC (could use DESCRIBE) so that I can see the structure or layout that I have
created.
SQL CODE:
SQL> DESC maintain;
Name Null? Type
------------------------------- -------- ----
IDNO VARCHAR2(3)
ITEMNAME VARCHAR2(10)
PRICE NUMBER(6,2)
PURCHASED DATE
DEPT CHAR(2)
INSERT:
The INSERT command that we looked at put values in all columns of a row. In this example, I am adding a record for a
Teddy Bear.
SQL CODE:
SQL> INSERT INTO maintain
2 VALUES('123', 'Teddy Bear', 20, '12-OCT-99','TY');
1 row created.
SQL> SELECT *
2 FROM maintain;
3
IDN ITEMNAME PRICE PURCHASED DE
--- ---------- --------- --------- --
123 Teddy Bear 20 12-OCT-99 TY
SQL>
The INSERT command can also be used to create a row with only specific columns filled in. In this example, I have
decided to create a row/record that will only have idno, itemname, and dept. Price and purchased will not contain data.
Later I can update this row and put data in the fields.
SQL CODE:
SQL> INSERT INTO maintain (idno, itemname,dept)
2 values('234','Dump Truck','TY');
1 row created.
SQL> SELECT * FROM maintain;
IDN ITEMNAME PRICE PURCHASED DE
--- ---------- --------- --------- --
123 Teddy Bear 20 12-OCT-99 TY
234 Dump Truck TY
SQL>
UPDATE:
The UPDATE command is used to change data on an existing file. You use update and then the name of the table that you
are changing. For each column that you are changing, you will use the set command followed by the column name. If you
want to effect a single record or multiple records that meet a particular criteria, you can use the WHERE command to
select the records to be updated. In the first example below, I am updating the price on the record that contains no
price with 15. On the second example, I am changing price to 15.95 and putting a date in the purchased field.
Remember, the original purchased field contained no date.
SQL CODE:
SQL> UPDATE maintain
2 SET price = 15
3 WHERE idno = '234';
1 row updated.
SQL> SELECT * FROM maintain;
IDN ITEMNAME PRICE PURCHASED DE
--- ---------- --------- --------- --
123 Teddy Bear 20 12-OCT-99 TY
234 Dump Truck 15 TY
SQL> UPDATE maintain
2 SET price = 15.95, purchased = '15-NOV-98'
3 WHERE idno = '234';
1 row updated.
SQL> SELECT * FROM maintain;
IDN ITEMNAME PRICE PURCHASED DE
--- ---------- --------- --------- --
123 Teddy Bear 20 12-OCT-99 TY
234 Dump Truck 15.95 15-NOV-98 TY
DELETE:
The DELETE command allows you to remove data from the file. The syntax is DELETE FROM followed by the table name. The
where clause is used to specify the criteria for the record or records to be deleted. If you do not use the where
clause, all the records in the table will be deleted. In the example below, I created a third record in the maintain
file and then deleted the record.
SQL CODE:
SQL> INSERT INTO maintain
2 values('235','whatever',1,'01-JAN-99','XX');
1 row created.
SQL> SELECT * FROM maintain;
IDN ITEMNAME PRICE PURCHASED DE
--- ---------- --------- --------- --
123 Teddy Bear 20 12-OCT-99 TY
234 Dump Truck 15.95 15-NOV-98 TY
235 whatever 1 01-JAN-99 XX
SQL> DELETE FROM maintain
2 WHERE idno = '235';
1 row deleted.
SQL> SELECT * FROM maintain;
IDN ITEMNAME PRICE PURCHASED DE
--- ---------- --------- --------- --
123 Teddy Bear 20 12-OCT-99 TY
234 Dump Truck 15.95 15-NOV-98 TY
ALTER:
The ALTER command can be used to change the structure of the file. In the example below, I added a field called COST
to the table named maintain. I then used the update command to put data in the fields. Notice that the update command
put .9 * price in as the cost. I then decided to change the length of the itemname field from 10 to 12. I did this by
using the modify within the alter command. Note: You can add or modify or column but you cannot drop a column from the
table.
When you add columns to a table, the new column becomes the last column in the structure. When you modify a column you
can change its datatype, size and/or default value. If you change the default value, this will only effect subsequent
inserts. More about default values later when we go into more sophistication with the create.
SQL CODE:
SQL> ALTER TABLE maintain
2 ADD (cost number(6,2));
Table altered.
SQL> DESC maintain;
Name Null? Type
------------------------------- -------- ----
IDNO VARCHAR2(3)
ITEMNAME VARCHAR2(10)
PRICE NUMBER(6,2)
PURCHASED DATE
DEPT CHAR(2)
COST NUMBER(6,2)
SQL> SELECT * FROM maintain;
IDN ITEMNAME PRICE PURCHASED DE COST
--- ---------- --------- --------- -- ---------
123 Teddy Bear 20 12-OCT-99 TY
234 Dump Truck 15.95 15-NOV-98 TY
SQL> SELECT * FROM maintain;
IDN ITEMNAME PRICE PURCHASED DE COST
--- ---------- --------- --------- -- ---------
123 Teddy Bear 20 12-OCT-99 TY
234 Dump Truck 15.95 15-NOV-98 TY
SQL> UPDATE maintain
2 SET COST = .9 * PRICE;
2 rows updated.
SQL> SELECT * FROM maintain;
IDN ITEMNAME PRICE PURCHASED DE COST
--- ---------- --------- --------- -- ---------
123 Teddy Bear 20 12-OCT-99 TY 18
234 Dump Truck 15.95 15-NOV-98 TY 14.36
SQL> ALTER TABLE maintain
2 MODIFY (itemname VARCHAR2(12));
Table altered.
SQL> DESC maintain;
Name Null? Type
------------------------------- -------- ----
IDNO VARCHAR2(3)
ITEMNAME VARCHAR2(12)
PRICE NUMBER(6,2)
PURCHASED DATE
DEPT CHAR(2)
COST NUMBER(6,2)