Tutorial DDL SQL
DDL Part 1
Objectives
After completing this lesson, you should be
able to do the following:
•Create tables
•Alter table definitions
•Drop, rename, and truncate tables
*Lesson
Aim
In this lesson, you
learn about tables, the main database objects, and their relationships to each
other. You also learn how to create, alter, and drop
tables.*
Outline
•Naming Rules
•DDL Syntax
–Creating Table
–Altering Table
–Dropping Table
Naming Rules
Table names and column names:
•Must begin with a letter
•Must be 1–30 characters long
•Must contain only A–Z, a–z, 0–9, _, $, and #
•Must not duplicate the name of another object
owned by the same user
The CREATE TABLE
Statement
You must have:
–CREATE TABLE privilege
–A storage area
Ex.
CREATE TABLE [schema.]table
(column datatype [DEFAULT expr][, ...]);
The CREATE TABLE
Statement
–schema is the same as the owner’s name
–table is
the name of the table
–DEFAULT expr specifies a default value if a value is omitted in the INSERT statement
–column is the name of the column
–Datatype is the column’s data type and length
Referencing Another User’s
Tables
•Tables belonging to other users are not in
the user’s schema.
•You should use the owner’s name as a prefix
to those tables.
The DEFAULT
Option
•Specify a default value for a column during
an insert.
... hire_date DATE DEFAULT SYSDATE,
...
•The default data type must match the column
data type.
*The
DEFAULT Option
A column can be given
a default value by using the DEFAULT option. This option
prevents
null values from entering the columns if a row is inserted without a
value for the column.*
Creating Tables
•Create the table.
CREATE TABLE
dept
(deptno NUMBER(2),
dname VARCHAR2(14),
loc VARCHAR2(13));
Table
created.
•Confirm table creation.
DESCRIBE dept
*Creating
Tables
The example on the
slide creates the DEPT table, with three
columns: DEPTNO, DNAME, and
LOC. It further confirms
the creation of the table by issuing the DESCRIBE
command.
Since creating a
table is a DDL statement, an automatic commit takes place when this
statement is executed.*
Creating a Table
by Using a Subquery
Syntax
•Create a table and insert rows by combining
the CREATE TABLE statement and the AS
subquery option.
CREATE TABLE table
[(column, column...)]
AS subquery;
•Match the number of specified columns to the
number of subquery
columns.
•Define columns with column names
anddefault values.
Creating a Table by Using
a Subquery
CREATE TABLE dept80
AS
SELECT employee_id, last_name,
SELECT employee_id, last_name,
salary*12 ANNSAL,
hire_date
FROM employees
WHERE department_id = 80;
FROM employees
WHERE department_id = 80;
Table
created.
DESCRIBE dept80
The ALTER TABLE
Statement
Use the ALTER TABLE statement to:
•Add a new column
•Modify an existing column
•Define a default value for the new column
•Drop a column
*The
ALTER
TABLE Statement
After you create a
table, you may need to change the table structure because: you omitted a
column, your column definition needs to be changed, or you need to remove
columns. You can
do this by using the ALTER TABLE statement.*
Modifying a Column
•You can change a column’s data type, size,
and default value.
ALTER TABLE dept80
MODIFY (last_name VARCHAR2(30));
Table
altered.
•A change to the default value affects only
subsequent insertions to the table.
Dropping a Column
Use the DROP
COLUMN clause to drop columns you no longer need
from the table.
ALTER TABLE dept80
DROP COLUMN job_id;
Table
altered.
The SET UNUSED
Option
•You use the SET UNUSED
option to mark one or more columns as unused.
•You use the DROP UNUSED COLUMNS option to remove the columns that are marked
as
unused.
ex.
ALTER TABLE table
SET UNUSED (column);
ALTER TABLE table
SET UNUSED COLUMN column;
ALTER TABLE table
DROP UNUSED COLUMNS;
*The
ALTER
TABLE Statement
(continued)
You can add, modify,
and drop columns to a table by using the ALTER TABLE statement.
In the syntax:
table is the name of
the table
ADD|MODIFY|DROP is the type of
modification
column is the name of
the new column
datatype is the data type
and length of the new column
DEFAULT expr specifies the default
value for a new column
Note: The slide gives the
abridged syntax for ALTER
TABLE. More about ALTER TABLE is covered in a
subsequent lesson.
Instructor Note
In Oracle8i and later, there are new options for
the ALTER TABLE command, including the
ability to drop
a column from a table, which are covered later in this lesson.*
Adding a Column
DEPT80 New column
DEPT80
Adding a Column
•You use the ADD
clause to add columns.
ALTER TABLE dept80
ADD (job_id VARCHAR2(9));
Table
altered.
•The new column becomes the last column.
Dropping a Table
•All data and structure in the table is
deleted.
•Any pending transactions are committed.
•All indexes are dropped.
•You cannot roll back the DROP TABLE
statement.
ex.
DROP TABLE dept80;
Table
dropped.
Changing the Name of an
Object
•To change the name of a table, view,
sequence, or synonym, you execute the RENAME
statement.
ex.
RENAME dept TO detail_dept;
Table
renamed.
•You must be the owner of the object.
Truncating a Table
•The TRUNCATE
TABLE statement:
–Removes all rows from a table
–Releases the storage space used
by that table
–
TRUNCATE TABLE detail_dept;
Table
truncated.
•You cannot roll back row removal when using TRUNCATE.
•Alternatively, you can remove rows by using
the DELETE statement.
*Truncating
a Table
Another DDL statement
is the TRUNCATE TABLE statement, which is used to
remove all rows
from a table and to release the storage space used by that
table. When using the TRUNCATE
TABLE statement, you
cannot roll back row removal.
Ex.
Syntax
TRUNCATE
TABLE table;
In the syntax:
table is the name of the
table
You must be the owner
of the table or have DELETE TABLE system privileges to truncate a
table.
The DELETE statement can also
remove all rows from a table, but it does not release storage
space. The TRUNCATE command is faster.
Removing rows with the TRUNCATE statement is
faster
than removing them with the DELETE
statement for the following reasons:
The TRUNCATE statement is a data
definition language (DDL) statement and generates no
rollback information.
Truncating a table
does not fire the delete triggers of the table.
If the table is the
parent of a referential integrity constraint, you cannot truncate the table.
Disable the constraint before issuing the TRUNCATE
statement.*
Adding Comments to a Table
•You can add comments to a table or column by
using the COMMENT statement.
COMMENT ON TABLE
employees
IS 'Employee
Information';
Comment
created.
•Comments can be viewed through the data
dictionary views:
–ALL_COL_COMMENTS
–USER_COL_COMMENTS
–ALL_TAB_COMMENTS
–USER_TAB_COMMENTS
*Adding
a Comment to a Table
You can add a comment
of up to 2,000 bytes about a column, table, view, or snapshot by using
the COMMENT statement.
The comment is stored in the data dictionary and can be viewed in
one of the
following data dictionary views in the COMMENTS
column:
ALL_COL_COMMENTS
USER_COL_COMMENTS
ALL_TAB_COMMENTS
USER_TAB_COMMENTS
Syntax
COMMENT ON
TABLE table |
COLUMN table.column
IS 'text';
In the syntax:
table is the name of the
table
column is the name of the
column in a table
text is the text of
the comment
You can drop a
comment from the database by setting it to empty string (''):
COMMENT ON TABLE employees IS ' ';*
Summary
In this lesson, you
should have learned how to use DDL commands to create, alter, drop, and
rename
tables. You also learned how to truncate a table and add comments to a table.
CREATE TABLE
Create a table.
Create a table based
on another table by using a subquery.
ALTER TABLE
Modify table
structures.
Change column widths,
change column data types, and add columns.
DROP TABLE
Remove rows and a
table structure.
Once executed, this
statement cannot be rolled back.
RENAME
Rename a table, view,
sequence, or synonym.
TRUNCATE
Remove all rows from
a table and release the storage space used by the table.
The DELETE statement removes
only rows.
COMMENT
Add a comment to a
table or a column.
Query the data
dictionary to view the comment.
Comments
Post a Comment