Database Programming - Study Guide Sections 8-9

Name ______Date ______

  1. An Oracle database can contain multiple data structures, list them:

table index synonym

view sequence

  1. When can tables be created?

Tables can be created at any time, even while users are using the database.

  1. How many columns can a table have?

A table can have up to 1,000 columns.

  1. What are the naming rules for column names and table names?

must begin with a letter

must be 1 to 30 characters long

must contain only A-Z, a-z, 0-9, _, $, and #

must not duplicate the name of another object owned by the same user

must not be an Oracle Server reserved word

  1. To create a table, what does a user need?

CREATE TABLE privilege and a storage area

  1. How is a table creation confirmed?

DESCRIBE table;

  1. How does a user view a table that belongs to another user?

If a table does not belong to the user, the owner’s name must be prefixed to the table.

  1. List and describe the four categories of data dictionary views.

USER_ - these views contain information about objects owned by the user

ALL_ - these views contain information about all the tables accessible to the user

DBA_ - these views are restricted views, which can be accessed only by people who have been assigned the DBA role

V$ - these views are dynamic performance views, database server performance, memory, and locking

  1. Why is it important to know the datatypes of the data?

The data must be of the same data type to calculate, storage capabilities

  1. Is the time zone displacement stored as part of the column data?

No, the time zone displacement is not stored as part of the column data, the server returns the data in the users’ local session time zone.

  1. What are the guidelines for creating a table using a subquery syntax?

the table is created with the specified column names and the rows retrieved by the SELECT statement are inserted into the table

the column definition can contain only the column name and default value

the number of columns must equal the number of columns in the subquery SELECT list

the column names of the table are the same as the column names in the subquery

the integrity rules are not passed onto the new table, only the column data type definitions

  1. What is the ALTER TABLE statement used for?

add a new column

modify an existing column

define a default value for the new column

drop a column

  1. When adding a column, where does it appear on the table?

The new column becomes the last column on the table.

  1. Must the column data be deleted to drop a column from a table?

No, a column may or may not contain data.

  1. Why mark a column unused?

So the columns can be dropped when the demand on system resources is lower.

  1. Can a DROP TABLE statement be ROLLBACKed?

The DROP TABLE statement is irreversible.

  1. What happens when a table is dropped?

The database removes the definition of the table, it loses all the data in the table and all the indexes associated with it.

  1. What happens when a table is truncated?

The database removes all the rows of data from the table, releases the storage space used by the table.

  1. Why is the TRUNCATE TABLE statement faster than the DELETE statement?

The TRUNCATE TABLE statement releases the storage space used by the table.

  1. How big can a comment be?

Comments of up to 2,000 bytes can be added about a column, table, view or snapshot by using the COMMENT statement.

21.  Why do you add comments to a table?

To clarify the table to users.

Oracle Academy 1 1-May-2005