Ch. 3 Flashcards
________ _____ _______ are commands used to create or modify database tables.
data definition language (DDL)
A ________ is a defined, self-contained structure in Oracle 11g.
database object
Database tables are also considered to be database _______.
objects
What is the command that creates a new table in the database. The user names the columns and identifies the type of data to be stored?
CREATE TABLE
What command creates a table from existing database tables, using the AS clause and subqueries?
CREATE TABLE . . . AS
What command adds a column to a table?
ALTER TABLE . . . ADD
What command changes a column size, datatype, or default value?
ALTER TABLE . . . MODIFY
What command deletes one column from a table?
ALTER TABLE . . . DROP COLUMN
What command marks a column for deletion at a later time?
ALTER TABLE . . . SET UNUSED
OR
SET UNUSED COLUMN
What command completes the deletion of a column previously marked with SET UNUSED?
DROP UNSED COLUMNS
What command changes a table name?
RENAME . . . TO
What command deletes all table rows, but the table name and column structure remain?
TRUNCATE TABLE
What command removes an entire table from the Oracle 11g database?
DROP TABLE
What command permanently deletes a table in the recycle bin?
PURGE TABLE
What command recovers a dropped table if PURGE option not used when table dropped?
FLASHBACK TABLE . . . TO BEFORE DROP
The names of ________ and _______ can be up to 30 characters and must begin with a letter. These limitations apply only to a ______ or _______ name, not to data in a column.
table and column
The names of tables and columns can’t contain any ______ spaces.
blank
Numbers, the underscore symbol (_), and the number sign (#) are allowed in _____ and ______ names.
table and column
Each table owned by a user should have a ______ table name, and the column names in each table should be _____.
unique
What are Oracle 11g “reserved words”, and can’t be used for table or column names?
SELECT, DISTINCT, CHAR, and NUMBER
What is the description of VARCHAR2(n)?
Variable-length Character data, and the n represents the column’s max length.
- Max is 4000 characters
- no default size for this datatype
- minimum value must be specified
- can contain letters, numbers or symbols
What is the description of CHAR(n)?
Fixed-length character column, the n represents the column’s length.
- default size is 1
- max size is 2000
- can contain number, letters or symbols
- if fewer than (n) are entered, spaces are added to the right to force the data to reach a length of (n)
What is the description of NUMBER(p, s)?
Numeric column. The 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.
What is the description of DATE?
stores dates
A ______ identifies the type of data Oracle 11g is expected to store in a column.
datatype
A ________ _______ generates a value automatically based on other column values in the table.
Virtual Column
A ________ can be included to indicate who “owns” the table.
Schema
A _______ is a SELECT statement used in another SQL command.
Subquery
