"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?
|