deptno               number(2) check (deptno between 9 and 100),

constraint pk_employee_01 primary key (empid),

constraint fk_dept_01 foreign key (deptno)

references dept (deptno) on delete cascade,

constraint uk_employee_01 unique (phone_no));

 

Always, start the constraint name with PK prefix for a primary key, FK prefix for a Foreign key, UK prefix for a Unique key, or CK prefix for a check constraint.

 

 

Create an index table using the “Create Index” statement.  Avoid creating too many indexes.

Create a composite index that contains two columns (last name and first name).

>>           create index employee_lname_fname_ind_01

    on employee (lastname, firstname);

 

Query the tables that iself owns.  The “Employee” table should be listed.

>>        select table_name

    from user_tables

    order by table_name;

 

Query the index tables that belongs to the employee table and owns by the iself user.

>>           select index_name, uniqueness

        from user_indexes

        where table_name = 'EMPLOYEE';

Notice that there are three index tables of which two are unique.

 

Query the constraints name of the employee table including their types and status.

On the constraint type column, "C" is for a "null and check" constraint;  "U" is for a unique key;  "R" is for a foreign key; and "P" is for a primary key.  The status column can be enabled or disabled.

>>           select constraint_name, constraint_type, status

        from user_constraints

        where table_name = 'EMPLOYEE';

 

Use the column command to change the size of the "column_name" to 30 characters.

>>         col column_name format a30

 

Then, query the index columns of the employee table.  Remember that on the composite index the sequence of the column would be the same as the column position.  Notice that the last name has the first position and the first name has the second position in the composite index.

>>           select index_name, column_name, column_position

        from user_ind_columns

        where table_name = 'EMPLOYEE';

 

Insert a record into the "employee" table using column names.

In this type of insert, the input data values are inserted by a position of column.

For example, 100 goes to employee id;  "smith" goes to the "lastname"; 10 goes to the "department number;  "joe" goes to the "firstname"; and 703 821 2211 goes to the "phone_no" column.

>>           insert into employee

        (empid, lastname, deptno, firstname, phone_no)

        values (100, 'smith', 10,'joe', 7038212211');

 

Then save the transaction.

>>         commit;

 

Insert a record using the column position format.   In this case, the input data are inserted by the sequences of position of columns in the table.  For example, 200 goes into the first column of the table; "KING" goes into the second column of the table; and so on.

>>           insert into employee values (200, 'KING', 'Allen', 5464327532, 10);

 

Save the transaction.

>>           commit;

 

Query the employee table.

>>           select * from employee;

 

Change "Smith" to “Judd” where "employee id" is 100.

>>           update employee set lastname = 'Judd' where empid = 100;

 

Save the transaction;

>>           commit;

 

Query the employee table to see the changes;

>>           select * from employee;

 

Delete the employee record where its employee id is 200.  

>>           delete from employee

        where empid = 200;

 

Save the transaction;

>>          commit;

 

Query the table.

>>           select * from employee;

 

Delete all records from the employee table;

Do not commit.

>>           delete from employee;

 

Query the table.

>>           select * from employee;

 

Undo the delete transaction, as long as you did not commit the transaction.

>>           rollback;

 

Query the employee table again.

>>           select * from employee;

All records are back.

 

Now, truncate the employee table.

>>           TRUNCATE table employee;

 

Do not commit;

 

 

Undo the truncation.

>>           rollback;

 

Query the employee table again.

>>           select * from employee;

 

You lost all the data.

Always remember:  Truncate is a DDL statement and in all the DDL statements the commit is implicit.

 

Now, you should practice this over and over, until you become a master at it.

Good Luck!