Chapter 8 Table Creation and Management

1
Chapter 8Table Creation and Management
2
Chapter Objectives

• Create a new table using the CREATE TABLE command
• Name a new column or table
• Use a subquery to create a new table
• Add a column to an existing table
• Modify the size of a column in an existing table

3
Chapter Objectives

• Drop a column from an existing table
• Mark a column as unused, then delete it at a
  later time
• Rename a table
• Truncate a table
• Drop a table

4
Database Table

• A database object
• Stores data for the database
• Consists of columns and rows
• Created and modified through Data Definition
  Language (DDL) commands

5
Table and Column Names

• Maximum 30 characters - no blank spaces
• Must begin with a letter
• Can contain numbers, underscore (_), and number
  sign ()
• Must be unique
• No reserved words allowed

6
Common Datatypes
7
CREATE TABLE Command
8
CREATE TABLE Command

• Column definition list must be enclosed in
  parentheses
• Datatype must be specified for each column
• Maximum of 1,000 columns

9
CREATE TABLE Command Example
10
DESCRIBE Command

• Displays structure of specified table

11
Table Creation Through Subqueries

• Can use subquery to retrieve data from existing
  table
• Requires use of AS keyword
• New column names can be assigned

12
CREATE TABLEAS Command
13
CREATE TABLEAS Command Example
14
Modifying Existing Tables

• Accomplished through ALTER TABLE command
• Use ADD clause to add a column
• Use MODIFY clause to change a column
• Use DROP COLUMN to drop a column

15
ALTER TABLE Command Syntax
16
ALTER TABLEADD Command Example
17
ALTER TABLEMODIFY Command Example
18
Modification Guidelines

• Column must be as wide as the data it already
  contains
• If a NUMBER column already contains data, size
  cannot be decreased
• Adding or changing default data does not affect
  existing data

19
ALTER TABLEDROP COLUMN Command

• Can only reference one column per execution
• Deletion is permanent
• Cannot delete last remaining column in a table

20
ALTER TABLESET UNUSED Command

• Once marked for deletion, column cannot be
  restored
• Storage space freed at later time

21
ALTER TABLEDROP UNUSED Command

• Frees up storage space from columns previously
  marked as unused

22
RENAME Command

• Used to rename a table old name no longer valid

23
Truncating a Table TRUNCATE Command

• Rows are deleted - structure of table remains

24
DROP TABLE Command

• Table structure and contents are deleted