iSelfSchooling.com - Since 1999  References  |  Job Openings
    Home  | Search more  | Oracle Syntax  | Computer Institute   | (Login or Register to access to VIDEOS)
 

Copyright & User Agreement

   Suggestions Email2aFriendHomepage us! |  Bookmark

Services

  Vision/Mission

  Services

  Biography

  Contact Us

 FREE Training

  Start...

  SQL

  PL/SQL

  Forms 

  Reports

  DBA Fundamentals

  Performance

  Prepare for OCP

  ShareUrNotes

...

  Acknowledgement

  Who is who

  University Directory

  Links...

 

 

 

More Resources by Google:

Topics:  Hands-On 03 - Creating Oracle Database

Overview of Data Modeling and Database Design

    Basic types of Data Relationships

    Reading an Entity-Relationship Diagram

Creating the Tables of an Oracle Database

    Creating a Table with Integrity Constraints

    Using Table Naming Conventions

    Datatypes and Column Definitions

    Indexes Created by Constraints

    Creating One table with Data From Another

Oracle Data Dictionary

    Available Dictionary Views

    Querying the Data Dictionary

Manipulating Oracle Data

    Inserting New Rows into a Table

    Making Changes to Existing Row Data

    Deleting Data from Oracle Database

 

Manuscript

Create an employee table that contains five columns:

employee id, last name, first name, phone number and department number.

The last and first name should be not null.

Make a check constraint to check the department number is between 9 and 100.

Make a primary constraint on the employee ID column.

Make a foreign key constraint on the department number column.  Use the "delete cascade" option to delete all records if parent gets deleted.

Use the "phone number" as a unique key.

>>

        create table employee

        (empid                number(10),

        lastname            varchar2(20) not null,

        firstname          varchar2(20) not null,

phone_no           varchar2(15),

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!

 
 
 
Google
 
Web web site