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