1 / 24

Chapter 8 Table Creation and Management

Chapter 8 Table Creation and Management. 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 Objectives.

babu
Download Presentation

Chapter 8 Table Creation and Management

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 8Table Creation and Management Oracle9i: SQL

  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 Oracle9i: SQL

  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 Oracle9i: SQL

  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 Oracle9i: SQL

  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 Oracle9i: SQL

  6. Common Datatypes Oracle9i: SQL

  7. CREATE TABLE Command Oracle9i: SQL

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

  9. CREATE TABLE Command Example Oracle9i: SQL

  10. DESCRIBE Command Displays structure of specified table Oracle9i: SQL

  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 Oracle9i: SQL

  12. CREATE TABLE…AS Command Oracle9i: SQL

  13. CREATE TABLE…AS Command Example Oracle9i: SQL

  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 Oracle9i: SQL

  15. ALTER TABLE Command Syntax Oracle9i: SQL

  16. ALTER TABLE…ADD Command Example Oracle9i: SQL

  17. ALTER TABLE…MODIFY Command Example Oracle9i: SQL

  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 Oracle9i: SQL

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

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

  21. ALTER TABLE…DROP UNUSED Command Frees up storage space from columns previously marked as unused Oracle9i: SQL

  22. RENAME Command Used to rename a table – old name no longer valid Oracle9i: SQL

  23. Truncating a Table – TRUNCATE Command Rows are deleted - structure of table remains Oracle9i: SQL

  24. DROP TABLE Command Table structure and contents are deleted Oracle9i: SQL

More Related