Oracle 9i Tables and Constraints Page - 2

Creating Table Using Constraints:

Note: In the above screen shot lines after “----“ are comments. Different ways of defining constraints are given above.

ALTER TABLE command is used for modifying structure of the table.
1) 1) Adding and deleting or renaming columns.
2) 2) Increasing and decreasing column data size.
3) 3) Changing column data type.
4) 4) Enable or Disable constraints.

Syntax:
ALTER TABLE
ADD | MODIFY | DROP | ENABLE | DISABLE
|

The following screen shots illustrate how to add a column and how to remove a column.

ALTER TABLE can be used to add and remove constraints as shown below

Entering records into a table

1. To enter records into a table we use INSERT command
2. Using & we can read the values from key board in SQL .

SQL > INSERT INTO STUD (SNO, SNAME)
VALUES (3 , 'MANOJ');
1 row created.

SQL > SELECT * FROM STUD;

SNO SNAME COURSE FEE
---- --------------- --------------- ----------
1 KISHAN ORACLE 9i 123.45
2 SHARADA ORA APPS 1234.5
3 MANOJ

Modifying existing values using UPDATE

UPDATE command is used to update the values of the fields.

Syntax :
UPDATE
SET = value WHERE = values


SQL > UPDATE STUD
2 SET COURSE = 'J2EE' WHERE SNO=3;

1 row updated.


SQL > SELECT * FROM STUD;

SNO SNAME COURSE FEE
----- ---------- --------------- --------------- -------- --
1 KISHAN ORACLE 9i 123.45
2 SHARADA ORA APPS 1234.5
3 MANOJ J2EE

SQL > UPDATE STUD
2 SET COURSE='J2EE' ;

3 rows updated.

SQL > SELECT * FROM STUD;

SNO SNAME COURSE FEE
---------- --------------- --------------- ----------
1 KISHAN J2EE 123.45
2 SHARADA J2EE 1234.5
3 MANOJ J2EE

Note: Without proper condition UPDATE will take effect on more than one record.

Using DELETE command

This command is used to delete rows from a table.

SQL > SELECT * FROM STUD;


SNO SNAME COURSE FEE
---------- --------------- --------------- ----------
1 KISHAN J2EE 123.45
2 SHARADA J2EE 1234.5
3 MANOJ J2EE

SQL > DELETE FROM STUD
2 WHERE SNO=3;

1 row deleted.

SQL > SELECT * FROM STUD;

SNO SNAME COURSE FEE
---------- --------------- --------------- ----------
1 KISHAN J2EE 123.45
2 SHARADA J2EE 1234.5

SQL > DELETE FROM STUD;

2 rows deleted.

ROLLBACK, COMMIT AND SAVEPOINT

ROLLBACK, COMMIT and SAVEPONT are called as Transaction Control Commands. A transaction is a sequence of SQL statements that Oracle treats as a single unit. Various changes we make to the table with INSERT, UPDATE, DELETE commands are temporary. To make them permanent we use COMMIT command. To undo the changes we use ROLLBACK.