Chapter 3 - Table Creation And Management

What database object can you query to see information about all the tables in your schema?

Query the USER_TABLES data dictionary.

What database object can you query to identify hidden and invisible columns?

Query the USER_TAB_COLS data dictionary.

What database object can you query to see DEFAULT settings and virtual column definitions?

Query the USER_TAB_COLUMNS data dictionary.

How can you determine if a table has unused columns?

Query the USER_UNUSED_COL_TABS data dictionary object.

Give me the syntax to rename a table

RENAME olbTabelName TO newTableName; • Renaming a table breaks any views that use it. • Renaming a table does not break indexes.

What happens to a table's index when the table is purged from the recycle bin?

The index is dropped as well.

What are the BLOB and CLOB datatypes

• BLOB is Binary Large Object • CLOB is Character Large Object They are used to store large amounts of Binary or Character data - up to 4gb.

Describe a virtual column

• It is a column who's values are determined at the time it is queried. No data is stored in the column in the BD. • You use the AS keyword to create the column. • The DBMS assigns the data type for virtual columns. • CREATE TABLE tableName ( price NUMBER(5,2), quantity NUMBER(3,0), total AS (quantity * price);

Describe the CHAR(n) datatype

• It is used to store fixed length character data. • Maximum size is 2000 bytes • The size is automatically padded so the data takes up the entire size. Spaces are added to the end to make the data fit. • For exmaple, insert "A" into the column that has data type CHAR(2) and the value will be "A "

Describe the VARCHAR2(n) datatype

• It is used to store variable length character data. • Maximum size is 4000 bytes • There is NO default size. • A size must be provided.

Describe the DATE datatype

• Stores date and time • Stores dates/time between 01/01/4712BC - 12/31/9999 • 7 bytes are allocated to the column by default. You cant change this. • Dates are displayed DD-MON-YY (08-AUG-92) • You can change the way the date is displayed by using TO_CHAR()

What are the rules for naming a table or column in Oracle 12c?

• Up to 30 characters • Can NOT include shite space characters. • Numbers, Underscore and Hashtag are allowed. • Table and Column names are unique. • Reserved words cant be used, such as SELECT, CHAR, NUMBER etc.

Describe the NUMBER(p,s) datatype

• Used to store numeric data only. • p stands for precision. It is how many digits total. • Maximum precision is 38 digits. • s stands for scale. It is how many digits come after the decimal. • NUMBER(3,2) can store 1.23 • NUMBER(5,1) can store 1234.5 • NUMBER(7,2) can store 12345.67

Describe the EXTENDED datatype

• When enabled it allows VARCHAR2(n) to store up to 32,767 bytes. • Indexes on the column wont work properly on extended datatype columns.

Give me the basic syntax for a table creation with a subquery.

CREATE TABLE tablename (columnName1, columnName2, ... ) AS (subquery); • (columnName1, ... ) is optional. Use this only when you want to rename the columns that are coming in from the subquery. • You could also use column alias in the subquery to rename to columns. • The columns in the column list must match the columns in the subquery or you will receive an error. • You don't have to rename the columns. • The subquery MUST be enclosed in ()

Commands used to create or modify database tables are called?

DDL - Data Definition Language

How do you delete a table and send it to the recycling bin? How do you permanently delete a table in one statement?

DROP TABLE tableName; DROP TABLE tableName PURGE;

Give me the basic table creation syntax

CREATE TABLE shcema.user tableName (columnName datatype DEFAULT value, columnName datatype DEFAULT value); •Enclosing the column list in () is required. •Using , to separate columns is required.

How many columns can a table have?

A maximum of 1000.

Give me the syntax to add a column to a table

ALTER TABLE tableName ADD ( columnName datatype [DEFAULT], columnName datatype [DEFAULT], ... );

Give me the syntax to drop a column

ALTER TABLE tableName DROP columnName; • you can only drop one column at a time. • primary key can NOT be dropped. • Can not drop the last remaining column from a table. • You can NOT undo a column drop. Not even with ROLLBACK.

Give me the syntax to modify an existing column. What does the modify command allow you to change?

ALTER TABLE tableName MODIFY (column datatype [DEFAULT], ... ); It allows you to change... • column size (increase / decrease) • change datatype (such as VARCHAR2 to CHAR) • Change/Add default value • Set column invisible/visible • Columns must be as wide to accommodate current data • NUMBER can only be decreased if all columns are empty. It can always be increased. • Changing the DEFAULT only changes default for future data.

Give me the basic ALTER TABLE syntax.

ALTER TABLE tablename ADD | MODIFY | DROP COLUMN | columnName [definition]; ADD, MODIFY, DROP depends on the type of change you want to do.

How do you recover a table from the recycling bin? How do you purge a table? How do you empty the recycling bin?

FLASHBACK TABLE tableName TO BEFORE DROP; PURGE TABLE tableName; • Table name needs to be the name stored in the OBJECT_NAME column of the RECYCLEBIN table. PURGE RECYCLE BIN; •This will empty all the objects in the recycle bin. • When you restore a table from the recycling bin, you are restoring table structure and data.

T/F: Dates are stored as string objects.

Fasle - see page 365. Dates are stored as numeric data.

How do you mark a column to be deleted at a later time

Use SET UNUSED or SET UNUSED COLUMN (they are synonyms) with the ALTER TABLE command. ALTER TABLE tableName SET UNUSED (columnName); or ALTER TABLE tableName SET UNUSED COLUMN columnName; Once a column is set to unused, it will not appear in ANY query. Nor is it available for updates, etc. The ONLY thins that you can do is use the DROP UNUSED COLUMNS command with the ALTER TABLE command. ALTER TABLE tableName DROP UNUSED COLUMNS;

What permission must you have to create a table in a schema other than your own?

You must be granted the CREATE TABLE command for that schema. See chapter 7 for privileges.

How do you remove all data from a table and recover the space that it occupied?

You use the TRUNCATE TABLE tableName commands. TRUNCATE TABLE tableName;