Test 3 Study Guide

Question 1

How many characters can a table name contain?

Answer 1

A table name can contain up to 30 characters, with no blank spaces.

Question 2

What are the special characters that are allowed in a table name?

Answer 2

A table name can contain numbers, underscores, and number signs.

Question 3

What are the four main datatypes? What makes each unique.

Answer 3

VARCHAR2- Characters, up to 4000 in length, with no default size, and can contain up to it’s set limit in characters.
CHAR - Up to 2000 characters, and when its size is set, if less characters are added to it, spaces are added to the right.
NUMBER - No limits (apparently), takes two values, the 2nd determining precision.
DATE - Duh.

Question 4

What is the maximum number of columns that can be held in one table?

Answer 4

The maximum number of columns in a table is 1,000.

Question 5

What command can be used to provide the structure of a table?

Answer 5

The DESCRIBE command is used to show the structure of a table.

Question 6

What command allows for modifications of an existing table? What commands can be used with it?

Answer 6

ALTER TABLE allows for the modification of existing tables, and the commands ADD, MODIFY, and DROP COLUMN go along with it, after specifying the table name.

Question 7

What command allows for the recovery of a dropped table?

Answer 7

The FLASHBACK command pulls a dropped table back from the recycle bin.

Question 8

What command deletes a table instead of just sending it to the recycle bin?

Answer 8

The PURGE option for DROP TABLE will make sure it’s gone for good.

Question 9

What command deletes all the rows from a table?

Answer 9

The TRUNCATE command deletes all the rows from a table.

Question 10

What constraint is used to ensure that a value being added meets certain criteria?

Answer 10

The CHECK command ensures that data being added to a table meets specific criteria.

Question 11

What is the constraint naming convention?

Answer 11

The constraint naming convention is “tablename_columnname_constrainttype”.

Question 12

When are constraints created?

Answer 12

Constraints can be created at table creation, or after.

Question 13

Why are some constraints made at the column level, and others are made at the table level?

Answer 13

Table level constraints are necessary when you need to base a restraint on multiple columns.

Question 14

When are table-level restraints not able to be used?

Answer 14

Table-level restraints can be used on any restriction save NOT NULL.

Question 15

What happens if a data value violates a constraint?

Answer 15

If a data value violates a constraint, the entire row is rejected.

Question 16

When is ADD used, compared to MODIFY?

Answer 16

NOT NULL can only be added as a constraint by MODIFY, otherwise, ADD is appropriate. As with all constraints being added to an existing table, ALTER TABLE is the command used.

Question 17

Where are the two places constraints can be included in the CREATE TABLE command?

Answer 17

Constraints can be added in either with the column definition with each column being followed by CONSTRAINT, the constraint name, and the specific constraint, or as a group after the column declarations.