SQL - CH 3: Table Creation & Management

CHAR(n)

fixed-length character column, and the n represents the column's length - the default size is 1, and the maximum size is 2000. - Example: __(9) can contain up to nine letters, numbers or symbols. However, if fewer than nine are entered, spaces are added to the right to force the data to reach a length of nine.

What does the following command do? ALTER TABLE ... SET UNUSED or SET UNUSED COLUMN

marks a column for deletion at a later time

NUMBER(p, s)

numeric column - p indicates precision, the total number of digits to the left and right of the decimal position, to a maximum of 38 digits; the s, or scale, indicates the number of positions to the right of the decimal - Example: NUMBER(7, 2) can store a numeric value up to 99999.99. If precision or scale isn't specified, he column defaults to a precision of 38 digits.

What does the following command do? PURGE TABLE

permanently deletes a table in the recycle bin

What does the following command do? FLASHBACK TABLE ... TO BEFORE DROP

recovers a dropped table if PURGE option not used when table dropped

What does the following command do? DROP TABLE

removes an entire table from the Oracle 11g database

VARCHAR2(n)

variable-length character data, and the n represents the maximum length - The maximum size is 4000 characters. There's no default size for this datatype; a minimum value must be specified. - Example: __(9) can contain up to nine letter, numbers, or symbols

Before creating columns, you must do the following:

- choose a name for each column - determine the type of data each column stores - determine (in some cases) the column's maximum width you need to identify the type of data to be stored in each column so that you can assign an appropriate datatype for each column

Rules for Naming Both Tables & Columns

- the names of tables and columns can be up to 30 characters and must begin with a letter. These limitations apply only to a table or column name, not to data in a column. - the names of tables and columns can't contain any blank spaces. - numbers, the underscore symbol (_), and the number sign (#) are allowed in tale and column names. - each table owned by a user should have a unique table name, and the column names in each table should be unique

Database Object

a defined, self-contained structure in Oracle 11g

What does the following command do? ALTER TABLE ... ADD

adds a column to a table

What does the following command do? ALTER TABLE ... MODIFY

changes a column size, datatype, or default value

What does the following command do? RENAME ... TO

changes a table name

Data Definition Language (DDL)

commands used to create or modify database tables

What does the following command do? DROP UNUSED COLUMNS

completes the deletion of a column previously marked with SET UNUSED

What does the following command do? CREATE TABLE

creates a new table in the database - the user names the column and identifies the type of data to be stored. To view a table, use the SQL*Plus command DESCRIBE

What does the following command do? CREATE ... AS

creates a table from existing database tables, using the AS clause and subqueries

What does the following command do? TRUNCATE TABLE

deletes all table rows, but the table name and column structure remain

What does the following command do? ALTER ... DROP COLUMN

deletes one column from a table