Chapter 4 - Slides

Question 1

What are the naming conventions of a table and column name?

Answer 1

1. Must begin with a letter
2. Upto 30 characters long
3. Alphanumeric, and can use $/_/#
4. Spaces and Hyphens cannot be used
5. Must be unique
6. Cannot be an Oracle server-reserved word.
7. Case insensitive.
8. Should be short and meaningful

Question 2

True or False? Data types help to optimize storage space.

Answer 2

True

Question 3

The ____ type is a character data type to store variablelength alphanumeric data in a column.

Answer 3

VARCHAR2

Question 4

What is the maximum allowable size for a VARCHAR2 type?

Answer 4

4000 Characters

Question 5

_____ is the most appropriate type for a column whose

values do not have a fixed length

Answer 5

VARCHAR2

Question 6

True or False? If a VARCHAR2 value is longer than the specified size is entered, the longer values are truncated.

Answer 6

False, an error is generated

Question 7

True or False? The CHAR data type uses the storage more efficiently and processes data faster than the VARCHAR2 type.

Answer 7

True

Question 8

The maximum allowable size for a CHAR type character is:

Answer 8

2000 characters

Question 9

True or False? If a Char value longer than the specified size is entered, however, an
error is generated. The longer values are not truncated.

Answer 9

True

Question 10

When a number type is used for a column, its
precision and scale can be specified optionally, using
the general syntax:

Answer 10

fieldname NUMBER [([precision,][scale])]

Question 11

The precision value includes:.

Answer 11

only the digits, and does
not include formatting characters, such as the
currency symbol, commas, or the decimal point

Question 12

_____ is the total number of significant digits in the number, both to the left and to the right of the decimal
point. It n can range from 1 to 38.

Answer 12

Precision

Question 13

a _____ requires 1 to 22 bytes.

Answer 13

NUMBER

Question 14

What are the three Number data subtypes:

Answer 14

Integer
Fixed-Point
Floating Point

Question 15

The scale value for number can range from __ to __.

Answer 15

-84 to 127

Question 16

A FLOAT value is represented internally as NUMBER. The precision p can range from _ to _ binary digits.

Answer 16

1 to 126

Question 17

A FLOAT value requires

from _ to __ bytes

Answer 17

1 to 22

Question 18

The DATE data type is used for storing date and time

values. The range of allowable dates is between

Answer 18

January 1, 4712 B.C. and December 31, 9999 A.D.

Question 19

True or False? There is no need to specify size for the DATE type.

Answer 19

True

Question 20

The default date format is

Answer 20

DD-MON-YY HH:MM:SS A.M.

Question 21

The ___ type is used for variable-length character

data up to 2 gigabytes.

Answer 21

LONG

Question 22

The ___ type is similar to CHAR but uses 2-byte

binary encoding for each character

Answer 22

NCHAR

Question 23

True or False? The CHAR type is useful for character sets such as
the Japanese Kanji, which has thousands of different
characters

Answer 23

False, it is NCHAR

Question 24

____ is used to store binary data up to 4 gigabytes.

Answer 24

BLOB (Binary Large Object) date type

Question 25

____ is used to store singlebyte character data up to 4 gigabytes.

Answer 25

CLOB (Character Large Object) data type

Question 26

___ uses 2-byte character codes.

Answer 26

NCLOB (character large object) data type

Question 27

____ references to a binary file that is external to the database and is maintained by the operating system’s file system.

Answer 27

BFILE (Binary File type)

Question 28

___ or ____ are used for raw binary data of length size bytes.

Answer 28

RAW or LONG_RAW

Question 29

___, use when you are using an unique row address of a row.

Answer 29

ROWID

Question 30

____ enforce rules on tables

Answer 30

Constraints

Question 31

There are two types of constraints:

Answer 31

Integrity and Value Constraints

Question 32

What is an integrity constraint?

Answer 32

define both the primary | key and the foreign key with the table and primary key it references

Question 33

What is a value constraint?

Answer 33

define if NULL values are | disallowed, if UNIQUE values are required, and if only certain set of values are allowed in a column

Question 34

The general convention used for naming constraints is

Answer 34

__ | ie. dept_deptno_pk

Question 35

If you do not name a constraint, the Oracle server will ______

Answer 35

generate a name for it by using SYS_Cn format, where | n is any unique number

Question 36

_____ references a single column and is defined along with the definition of the column.

Answer 36

A Column-level Constraint

Question 37

_____ references one or more columns and is defined separately from the definitions of the columns.

Answer 37

A Table-level Contraint

Question 38

What is the general syntax of a Column level Constraint?

Answer 38

column datatype [CONSTRAINT constraint_name] constraint_type

Question 39

What is the general syntax of a Table level Constraint?

Answer 39

[CONSTRAINT constraint_name] constraint_type (Column, . . .)

Question 40

CONSTRAINT dept_deptid_pk PRIMARY KEY(DeptId) is an example of?

Answer 40

Primary Key Constraint

Question 41

The _____ constraint is also known as the referential integrity constraint.

Answer 41

FOREIGN KEY

Question 42

The ______ constraint ensures that the column has a value and the value is not a null (unknown or blank) value

Answer 42

NOT NULL

Question 43

The _____ constraint requires that every value in a | column or set of columns be unique.

Answer 43

UNIQUE

Question 44

The ____ constraint defines a condition that every | row must satisfy.

Answer 44

CHECK

Question 45

True or False? The A NOT NULL constraint can be declared as a CHECK constraint.

Answer 45

True

Question 46

A CREATE TABLE statement may have an optional | ______ clause

Answer 46

STORAGE

Question 47

The ____ clause is used to allocate initial diskspace for the table at the time of creation with INITIAL parameter, and also to allocate additional space with NEXT parameter in case the table runs out of allocated initial space.

Answer 47

STORAGE

Question 48

To display all columns, type the following statement:

Answer 48

SELECT * FROM USER_TABLES;

Question 49

You can get information about STORAGE clauses' | attributes by:

Answer 49

using data dictionary view

Question 50

slide 37

Answer 50

slide 37