More about maintaining a table - speaker notes

Slide 1:

This presentation will deal with INSERT, UPDATE and DELETE as well as CREATE and DESCRIBE.

Slide 2:

Review of creating a table.

Slide 3:

If you do not remember the structure used when you created the table, issue the DESCRIBE command followed by the table name to see the structure.

Note that DESCRIBE can also be entered as DESC.

Slide 4:

Remember that character data - VARCHAR2 and CHAR as well as DATE data are enclosed in single quotes. Numeric data is not enclosed in single quotes.

Slide 5:

If the column/field names are not listed after the table name, it is assumed that data will go into all of the fields.

Slide 6:

UPDATE can be used to change existing data or add data to null columns/fields.dat

Slide 7:

This UPDATE commands updates two columns/fields for the row/record where the stuidno = 3333.

Slide 8:

In this slide, I inserted a record into the table and then showed the file with the inserted record.

Then, using the DELETE FROM, I deleted the record and showed the results.

Slide 9:

ALTER TABLE allows you to change the structure of a table.

In this example, I used ADD.

As can be seen in this example, I added a column/field called STU_OPT.

When I do a DESCRIBE of the table you can see the addition and when I SELECT all of the data you can see a blank column on each row/record.

Slide 10:

The UPDATE is now used to update the new column that was created with the ALTER.

Slide 11:

Let's say I decided that stu_opt was too small. I can modify the structure using ALTER TABLE followed by MODIFY.

Slide 12:

Now that I have changed stu_opt from 5 characters to 10 characters, I can change the data in the column as shown with this update.

Slide 13:

This slide shows an error result from trying to decrease a column/field size that contains data. Data would have been lost if the ALTER had been allowed.

Slide 14:

All records will end up with a GPA of 4.