Docstoc

Chapter 1 Overview of Database Concepts - Montgomery College_4_

Document Sample
Chapter 1 Overview of Database Concepts - Montgomery College_4_ Powered By Docstoc
					            Chapter 8
 Table Creation and Management




Chapter 8    Oracle9i: SQL       1
            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
Chapter 8          Oracle9i: SQL               2
            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

Chapter 8          Oracle9i: SQL                 3
             Database Table
•   A database object
•   Stores data for the database
•   Consists of columns and rows
•   Created and modified through Data
    Definition Language (DDL) commands



Chapter 8          Oracle9i: SQL         4
            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

Chapter 8           Oracle9i: SQL            5
            Common Datatypes




Chapter 8        Oracle9i: SQL   6
     CREATE TABLE Command




Chapter 8    Oracle9i: SQL   7
     CREATE TABLE Command
• Column definition list must be enclosed in
  parentheses
• Datatype must be specified for each column
• Maximum of 1,000 columns




Chapter 8          Oracle9i: SQL           8
     CREATE TABLE Command
            Example




Chapter 8    Oracle9i: SQL   9
            DESCRIBE Command
Displays structure of specified table




Chapter 8           Oracle9i: SQL       10
            Table Creation Through
                  Subqueries
• Can use subquery to retrieve data from
  existing table
• Requires use of AS keyword
• New column names can be assigned




Chapter 8           Oracle9i: SQL          11
            CREATE TABLE…AS
                Command




Chapter 8        Oracle9i: SQL   12
            CREATE TABLE…AS
             Command Example




Chapter 8         Oracle9i: SQL   13
            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



Chapter 8             Oracle9i: SQL      14
       ALTER TABLE Command
              Syntax




Chapter 8     Oracle9i: SQL   15
            ALTER TABLE…ADD
             Command Example




Chapter 8         Oracle9i: SQL   16
      ALTER TABLE…MODIFY
         Command Example




Chapter 8    Oracle9i: SQL   17
            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


Chapter 8            Oracle9i: SQL           18
            ALTER TABLE…DROP
             COLUMN Command
• Can only reference one column per
  execution
• Deletion is permanent
• Cannot delete last remaining column in a
  table



Chapter 8          Oracle9i: SQL             19
            ALTER TABLE…SET
             UNUSED Command
• Once marked for deletion, column cannot
  be restored
• Storage space freed at later time




Chapter 8         Oracle9i: SQL             20
            ALTER TABLE…DROP
             UNUSED Command
   Frees up storage space from columns
   previously marked as unused




Chapter 8          Oracle9i: SQL         21
            RENAME Command
Used to rename a table – old name no longer valid




Chapter 8             Oracle9i: SQL                 22
             Truncating a Table –
            TRUNCATE Command
Rows are deleted - structure of table remains




Chapter 8           Oracle9i: SQL               23
            DROP TABLE Command
Table structure and contents are deleted




Chapter 8           Oracle9i: SQL          24

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:0
posted:9/16/2013
language:English
pages:24