iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

Basics - SQL 

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 |

 

Lesson 04

"I can write better than anybody who can write faster, and I can write faster than anybody who can write better." - A. J. Liebling (1904-1963)

 

Read first then play the video:

   SQL-VIDEO -Creating Oracle Database Objects

 

Create the Database objects

Create table, Primary key, Foreign Key, Unique, and Delete Cascade

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.

 

SQL> 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 UNQUE (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.

 

Creating a composite index

If you have an index that contains two or more columns, we call them a composite index. When an index is a composite index, you should make sure that the first column position in the index always be in your WHERE clause. The more columns from the composite index that you have in the WHERE clause the faster you will be able to retrieve your data. Avoid creating too many indexes.

Create an index table using the “Create Index” statement. Create a composite index that contains two columns (last name and first name).

 

SQL> CREATE INDEX employee_lname_fname_ind_01

              ON employee (lastname, firstname);

 

USER_TABLES table

You use USER_TABLES to query or view all table objects (schema) that are belong to the user who login to a database.

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

 

SQL> SELECT table_name

              FROM user_tables

              ORDER BY table_name;

 

USER_INDEXES table

You use USER_INDEXES to query or view all index objects (schema) that are belong to the user who login to a database. Indexes are attached to their tables. Dropping a table will drop all its indexes.

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

 

SQL> SELECT index_name, uniqueness

              FROM user_indexes

              WHERE table_name = 'EMPLOYEE';

 

Notice that there are three index tables of which two are unique. Make sure that table name is in uppercase. All tables’ name are stored in uppercase in the Oracle database.

 

USER_CONSTRAINTS table

You use USER_CONSTRAINTS to query or view all constraint objects (schema) that are belong to the user who login to a database. The same as indexes, the constraints are attached to their tables. Dropping a table will drop all its constraints. You use USER_CONSTRAINTS to view table dependencies with its different types of constraints.

 

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.

 

SQL> SELECT constraint_name, constraint_type, status

              FROM user_constraints

              WHERE table_name = 'EMPLOYEE';

 

COLUMN command

You use the COLUMN command to change size or format of your column to be displayed.

Syntax:

COL[UMN] [{column|expr} [option ...]]

options:

ALIAS alias Assign an alias to a column

CLEAR Reset the display attributes

 

Format a column (e.g. COL emp_name FORMAT A15)

HEA[DING] 'text' Set a column heading

JUSTIFY {L[EFT]|C[ENTER]|C[ENTRE]|R[IGHT]}

LIKE {expr|alias}Format like another column (already defined)

NEWLINE Same as FOLD_BEFORE

NOPRINT|PRINT Display the column

NUL[L] char Display NULL values as Char

ON|OFF Enable or disable column format attributes

WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]

How to treat long CHAR strings

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

 

SQL> COLUMN column_name FORMAT a30

 

USER_IND_COLUMNS table

USER_IND_COLUMNS contains all information about those columns that are index. It keeps information such as index_name, column_name, table_name, column position, etc.

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.

 

SQL> SELECT index_name, column_name, column_position

              FROM user_ind_columns

              WHERE table_name = 'EMPLOYEE';

 

INSERT statement using column names

The INSET statement writes a record in to a table. The following are some of INSERT syntaxes that are frequently used:

 

Syntax: INSERT [hint] INTO [schema.] table [@dblink] [t_alias] (column, column,...) VALUES (expression) INSERT [hint] INTO [schema.] table [@dblink] [t_alias] VALUES (expression) INSERT [hint] INTO [schema.] table [[SUB]PARTITION (ptn_name)] (column, column,...) VALUES (expression) INSERT [hint] INTO subquery WITH [READ ONLY | CHECK OPTION [CONSTRAINT constraint] ] [t_alias] (column, column,...) VALUES (expression)

 

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.

 

SQL> INSERT INTO employee

              (empid, lastname, deptno, firstname, phone_no)

              VALUES (100, 'smith', 10,'joe', ‘7038212211');

 

COMMIT statement

A record will not be added to a table unless you execute the COMMIT statement. All not committed records are stored in UNDO segment. It will give you a chance to undo your transaction. COMMIT means save all none committed transaction. It guarantees that the DBA will be able to recover your data to the point of failure.

Save the transaction.

SQL> COMMIT;

 

INSERT statement using the column position

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.

SQL> INSERT INTO employee

              VALUES (200, 'KING', 'Allen', 5464327532, 10);

 

Save the transaction.

SQL> COMMIT;

 

Query the employee table.

SQL> SELECT * FROM employee;

 

UPDATE statement

To change a value of a column in a table, you use the UPDATE statement. You must use the WHERE clause for specific record or records. Notice that if you don’t use a WHERE clause then the entire table will be changed. That could be an action that you did not want it.

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

 

SQL> UPDATE employee

              SET lastname = 'Judd'

              WHERE empid = 100;

 

Save the transaction;

SQL> COMMIT;

 

Query the employee table to see the changes;

SQL> SELECT * FROM employee;

 

DELETE statement

A record or records can be deleted from a table by using the DELETE statement. Again the same as UPDATE, you must make sure to have a WHERE clause in your query. Avoiding a WHERE clause will delete your all records in the table.

Delete the employee record where its employee id is 200.

 

SQL> DELETE

              FROM employee

              WHERE empid = 200;

 

Save the transaction;

SQL> COMMIT;

 

Query the table.

SQL> SELECT * FROM employee;

 

DELETE all records

As we mentioned before, to delete all records, you only avoid a WHERE clause. You can also truncate a table. If you want to delete all records from a table, you should use the TRUNCATE statement. It will change the table watermark. The table watermark is an address that indicates a last location of a record in a table. On the DELETE statement the watermark will not change. But using the TRUNCATE statement will change the watermark to the beginning of the table.

Delete all records from the employee table using the DELETE statement and do not commit.

SQL> DELETE FROM employee;

 

Query the table.

SQL> SELECT * FROM employee;

 

ROLLBACK statement

If you change, delete, insert a record into a table but not execute the commit statement. All your before transaction block images are in an UNDO segment. You can execute the ROLLBACK statement in order to undo your transaction. It is a perfect statement for correcting a user mistake such as deleting a table’s records.

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

SQL> ROLLBACK;

 

Query the employee table again.

SQL> SELECT * FROM employee;

All records are back. Notice that you only are able to undo a transaction to the last point that you executed a COMMIT statement.

 

TRUNCATE statement

Now, truncate the employee table.

SQL> TRUNCATE TABLE employee;

Do not commit. Notice that the TRUNCATE command is a DDL statement and all DDL statements have commit inclusive. That is why the ROLLBACK action after truncation does not work.

 

Undo the truncation.

SQL> ROLLBACK;

 

Query the employee table again.

SQL> SELECT * FROM employee;

 

Note that you lost all the data. Always remember that the truncate statement is a DDL statement and in all the DDL statements the commit is implicit.

 

 

"People demand dom of speech to make up for the dom of thought which they avoid." - Soren Aabye Kierkegaard (1813-1855)

 

Questions:

Q: Create an employee table that contains five columns:

Such as Employee Id, last name, First name, Phone number and Department number with the following constraints.

1. The last name and first name should be not null.

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

3. Make a primary constraint on the employee ID column.

4. Make a foreign key on the department number column.

5. Use the "delete cascade" to delete all records if parent gets deleted.

6. Use the "phone number" as a unique key.

Q: Create a composite index on the employee table that contains two index columns (last name and first name).

Q: Query the tables that you as a user own.

Q: Query the index tables that belong to the employee table and owns by the iself user.

Q: Change the size of the "column_name" to 30 characters logically (for display only.

Q: Query the indexes columns of the employee table.

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

Q: Insert a record using the column position format.

Q: How do you save the inserted transaction?

Q: Change the "last_name" column value from “Smith” to “Judd” where the "employee id" is 100.

Q: Delete all the employee records from the "employee" table using the delete command and the truncate command.

Q: How do you undo a transaction?

Q: What is the difference between the delete statement and the truncate statement?