Chapter 4 - Slides Flashcards
What are the naming conventions of a table and column name?
- Must begin with a letter
- Upto 30 characters long
- Alphanumeric, and can use $/_/#
- Spaces and Hyphens cannot be used
- Must be unique
- Cannot be an Oracle server-reserved word.
- Case insensitive.
- Should be short and meaningful
True or False? Data types help to optimize storage space.
True
The ____ type is a character data type to store variablelength alphanumeric data in a column.
VARCHAR2
What is the maximum allowable size for a VARCHAR2 type?
4000 Characters
_____ is the most appropriate type for a column whose
values do not have a fixed length
VARCHAR2
True or False? If a VARCHAR2 value is longer than the specified size is entered, the longer values are truncated.
False, an error is generated
True or False? The CHAR data type uses the storage more efficiently and processes data faster than the VARCHAR2 type.
True
The maximum allowable size for a CHAR type character is:
2000 characters
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.
True
When a number type is used for a column, its
precision and scale can be specified optionally, using
the general syntax:
fieldname NUMBER [([precision,][scale])]
The precision value includes:.
only the digits, and does
not include formatting characters, such as the
currency symbol, commas, or the decimal point
_____ 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.
Precision
a _____ requires 1 to 22 bytes.
NUMBER
What are the three Number data subtypes:
Integer
Fixed-Point
Floating Point
The scale value for number can range from __ to __.
-84 to 127
A FLOAT value is represented internally as NUMBER. The precision p can range from _ to _ binary digits.
1 to 126
A FLOAT value requires
from _ to __ bytes
1 to 22
The DATE data type is used for storing date and time
values. The range of allowable dates is between
January 1, 4712 B.C. and December 31, 9999 A.D.
True or False? There is no need to specify size for the DATE type.
True
The default date format is
DD-MON-YY HH:MM:SS A.M.
The ___ type is used for variable-length character
data up to 2 gigabytes.
LONG
The ___ type is similar to CHAR but uses 2-byte
binary encoding for each character
NCHAR
True or False? The CHAR type is useful for character sets such as
the Japanese Kanji, which has thousands of different
characters
False, it is NCHAR
____ is used to store binary data up to 4 gigabytes.
BLOB (Binary Large Object) date type
____ is used to store singlebyte character data up to 4 gigabytes.
CLOB (Character Large Object) data type
___ uses 2-byte character codes.
NCLOB (character large object) data type
____ references to a binary file that is external to the database and is maintained by the operating system’s file system.
BFILE (Binary File type)
___ or ____ are used for raw binary data of length size bytes.
RAW or LONG_RAW
___, use when you are using an unique row address of a row.
ROWID
____ enforce rules on tables
Constraints
There are two types of constraints:
Integrity and Value Constraints
What is an integrity constraint?
define both the primary
key and the foreign key with the table and primary key it references
What is a value constraint?
define if NULL values are
disallowed, if UNIQUE values are required, and if only certain set of values are allowed in a column
The general convention used for naming constraints is
__
ie. dept_deptno_pk
If you do not name a constraint, the Oracle server will ______
generate a name for it by using SYS_Cn format, where
n is any unique number
_____ references a single
column and is defined along with the definition of the
column.
A Column-level Constraint
_____ references one or more columns and is defined separately from the definitions of the columns.
A Table-level Contraint
What is the general syntax of a Column level Constraint?
column datatype [CONSTRAINT constraint_name] constraint_type
What is the general syntax of a Table level Constraint?
[CONSTRAINT constraint_name] constraint_type (Column, . . .)
CONSTRAINT dept_deptid_pk PRIMARY KEY(DeptId) is an example of?
Primary Key Constraint
The _____ constraint is also known as the referential integrity constraint.
FOREIGN KEY
The ______ constraint ensures that the column has a value and the value is not a null (unknown or blank) value
NOT NULL
The _____ constraint requires that every value in a
column or set of columns be unique.
UNIQUE
The ____ constraint defines a condition that every
row must satisfy.
CHECK
True or False? The A NOT NULL constraint can be declared as a CHECK constraint.
True
A CREATE TABLE statement may have an optional
______ clause
STORAGE
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.
STORAGE
To display all columns, type the following statement:
SELECT * FROM USER_TABLES;
You can get information about STORAGE clauses’
attributes by:
using data dictionary view
slide 37
slide 37
