|
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 oracle 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 oracle 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.
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 oracle 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? |
