SQL CH 3

What guidelines should you follow when naming tables and columns in Oracle 11g?

. 1.The first letter should be a character 2. There should be a maximum of 30 characters 3. No blanks space are allowed or special symbols other than dollar sign and underscore. 4. Reserved words can't be used as a table or column.

Which command is used to create a table based on data already contained in an existing table?

. Create table ....AS command with a subquery

If you add a new column to an existing table, where does the column appear in relation to existing columns?

. It will appear in the last column.

What happens to the existing rows of a table if the DEFAULT value of a column is changed?

. They will not be affected.

Explain the difference between truncating a table and deleting a table.

. Truncating a table deletes all its data but keeps the table structure and deleting a table removes everything. (The data and the table)

What is the difference between dropping a column and setting a column as unused?

. When dropped is used the space is emptied immediately and when marked as unused, the storage . space isn't released until later.

List four datatypes supported by Oracle 11g, and provide an example of data that could be stored by each datatype

1. VARCHAR2(n) variable length data. Ex. HELLO 2. CHAR (N) Fixed-length character column ex .MA 3. NUMBER(P, S) NUMERIC COLUMNS 4. DATE stores date and time between Ex. DATE: 13-MAR-01.

DDL

CREATE ,ALTER AND DROP

What happens if you try to decrease the scale or precision of a NUMBER column to a value less than the data already stored in the field?

Nothing, you can't change the scale or precision of a number containing data.

Are a table and the data contained in the table erased from the system permanently if a DROP TABLE command is issued on the table?

Only if the purge option is used in the dropped command

How many columns can be dropped in one ALTER TABLE command?

There can be only one table dropped at a time.

purge

can remove a table from yhe recycle bin

The USER_TABLES

data dictionary object maintains information regarding all your tables

describe

displays the structure of a table(everything in a table)

drop and purge

drop table customer purge will not sent table to rycycle bin

data dictionary

is a typical component of a DBMS that maintains information about database objects

alter

is used to change the definition of an existing database object(such as the table)

create

is used to create a new table indexes, and spo on

DROP

is used to delete an existing database object

flashback

recovers a table from the recycle bin

drop

table structure and data is deleted