DDL - Data Definition Language Flashcards
What is the syntax for CREATE TABLE?
CREATE TABLE table_name (
column_name1 data_type ,
column_name2 data_type ,
. . . . . . . . . .
column_nameN data_type
<optional CONSTRAINT constraint_name constraint_type);
What is the syntax for CREATE objectType statement?
CREATE objectType objectName attributes;
ObjectType is any database object except CONSTRAINT
Attributes can be zero to a series of clauses that are unique to each individual objectType
What are the rules for naming database objects and object components?
Length of name 1-30 characters
First character of a name must be a letter
After the first character you can have numbers, $, _, #, no other special characters allowed
Names cannot be reserved words
Names are case insensitive and treated as if they are all uppercase unless it is enclosed in double quotes in which case it becomes case sensitive and must always be referenced with case sensitivity and double quotes. Using double quotes you can use special characters and spaces in names
What is a namespace?
A namespace is a logical boundary within the database that encompasses a particular set of database objects. You must provide unique names for objects in the same namespace
The database has its own namespace shared by users, roles and public synonyms - all the non schema objects so they must have unique names compared to each other
A schema does not have its own namespace but has schema objects which do have their own namespaces
Tables, views, sequences, private synonyms and user defined types share a single namespace within a particular schema and must have unique names compared to each other
Indexes have their own namespace within a schema
Constraints have their own namespace within a schema
Indexes and constraints can have the same names since they are in different namespaces and they can also have the same names as tables, views, sequences, private synonyms and user defined types and users, roles and public synonyms have their own namespace
Objects in different namespaces can have identical names
When you create a table and give it a primary key constraint you can also name the constraint. If you do not the system will generate a name for the constraint.
if you name the constraint the system will automatically create an index for the constraint and will name the index with the same name as the constraint. You can override this and specify your own name for the index with the USING INDEX clause of the CREATE TABLE statement
What are the prefixes oracle uses for system defined objects?
SYS_ ALL_ DBA_ GV$_ NLS_ ROLE_ USER_ V$
Example of CREATE TABLE
CREATE TABLE cruises
( cruise_id NUMBER,
cruise_type_id NUMBER,
cruise_name VARCHAR2(20),
captain_id NUMBER NOT NULL,
start_date DATE,
end_date DATE,
status VARCHAR2(5) DEFAULT ‘Dock’,
CONSTRAINT cruise_pk PRIMARY KEY (cruise_id));
How do you get the table structure?
Describe statement DESC table_name outputs
Column names, null or not null, data type for each column
What are the general categories of data types?
Numerical,
Character,
Date
LOB large objects
What are the kinds of character data types?
CHAR(n) - fixed length alphanumeric value
Remaining unused space padded with blanks
Value > n results in error
n default 1
n max value 2000
VARCHAR2(n) - variable length alphanumeric value
N can be 1 - 4000 not optional
No padding length varies according to data
What are the kinds of Numeric data types?
NUMBER (n,m) - Numeric data including 0, +- numbers
n,m are optional
n specifies precision max number of significant total digits
m specifies scale - total digits to right side of decimal
n = 1 - 38 default 38, m = -84 to 127
If value > max precision error! value > scale rounded off
-ve value for m specifies how many digits to the left of decimal will be rounded off
Data type. Value entered. Value stored
NUMBER. 4.56 4.56
NUMBER(2) 4.56 5 because scale is zero
NUMBER(5,2) 4.5678 4.57
NUMBER(3,2) 10.56 Error val precision 4 declared 3
NUMBER(5,-2) 1056.34 1100 2 digits to left of decimal
rounded off
