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.

maalik
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

  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 TABLE…AS Command

  13. CREATE TABLE…AS 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 TABLE…ADD Command Example

  17. ALTER TABLE…MODIFY 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 TABLE…DROP COLUMN Command • Can only reference one column per execution • Deletion is permanent • Cannot delete last remaining column in a table

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

  21. ALTER TABLE…DROP 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

More Related