2 Using DDL Statements to Create and Manage Tables

Which database objects are schema objects in Oracle?

Tables, Views, Indexes, Sequences, and Private Synonyms, Constraints.

Which statement is correct? CREATE PUBLIC SYNONYM or CREATE PRIVATE SYNONYM

CREATE PUBLIC SYNONYM. For private the public word is omitted, just CREATE SYNONYM.

Can a user account own another user account or itself?

No, therefore it’s a nonschema object. The same stands for all nonschema objects. A user cannot own them.

Which database objects are nonschema objects in Oracle?

Users, Roles, and Public Synonyms.

What is a schema in Oracle?

A schema is a logical collection of database objects such as tables, indexes, views, and others owned by a user account. It has the same name as the user account.

What is the difference between a schema and a user account?

A user account is the owner identified by username with privileges; a schema is the set of objects owned by that account.

Can there be more accounts than users?

One user will often have one account and therefore one schema. But the opposite isn’t true, there can be more accounts than users.

What is the basic syntax to create a table in Oracle?

CREATE TABLE tablename ( columnname datatype [constraints], … );

What are the key parts of a CREATE TABLE statement?

Reserved words CREATE TABLE, the table name, column definitions (name, datatype, constraints), separated by commas.

What happens if you try to create a table that already exists in the schema?

The CREATE TABLE statement will fail with an error.

What are the naming rules for Oracle database objects?

Names must be 1–30 characters, start with a letter, may include letters, digits, $, _, #, not case-sensitive, and cannot use reserved SQL words.

Which are the reserved words?

• access, add, all, alter, and, any, as, asc, audit

• between, by

• char, check, cluster, column, column_value, comment, compress, create, connect, current

• date, decimal, default, delete, desc, distinct, drop

• else, exclusive, exists

• file, float, for, from

• grant, group

• having

• identified, immediate, in, increment, index, initial, insert, integer, intersect, into, is

• level, like, lock, long

• maxextents, minus, mlslabel, mode, modify, nested_table_id, noaudit, nocompress, not, nowait, null, number

• of, offline, on, online, option, or, order

• pctfree, prior, public

• raw, rename, resource, revoke, row, rowid, rownum, rows

• select, session, set, share, size, smallint, start, successful, synonym, SYSDATE

• table, then, to, trigger

• uid, union, unique, update, user

• validate, values, varchar, varchar2, view, whenever, where, with

What are quoted names in Oracle?

Names enclosed in double quotes, case-sensitive, can start with any character, include spaces or reserved words (for example “WHERE“ is a valid table name but “ROWID” isn’t for a column, but “ROWID 2” is). Must always be referenced with quotes.

What is a namespace in Oracle database?

A logical boundary grouping objects. Objects in the same namespace must have unique names; objects in different namespaces can share names. And for example table from schema 1 can have the same name as a table from different schema.

How are namespaces divided into blocks in Oracle?

• Users, roles - they are also. schema free

• Public synonyms

• Table, View, Sequence, Private synonym, user-defined types

• indexes

• constraints

Can there be such a thing as auto-generated object names in Oracle DBs?

Yes, for example when you create a table and within the CREATE TABLE statement you define a constraint, but without a name. The system will automatically generate a name - something with a prefix of SYS_C followed by a number generated automatically.

What command is used to review the structure of a table in Oracle?

DESC table_name (a SQL*Plus command, not standard SQL).

What’s the difference between SQL*Plus and SQL statements?

That each SQL*Plus statement ends at the end of its first line unless a continuation character is placed at the end of the line. So, we don’t need to use a semicolon, it’s optional. In SQL statement it’s the opposite, a semicolon marks the end.

What does the DESC/DESCRIBE command display?

It shows column names, NOT NULL constraints, and data types of the table.

What are the main categories of Oracle data types?

Character, Numeric, Date/Time, and Large Objects (LOBs). A column must be assigned a data type.

What is the difference between CHAR(n) and VARCHAR2(n)?

• CHAR(n) is fixed length and pads with blanks; CHAR(n) optional, default is 1, max 2000

• VARCHAR2 variable character, required, min is 1, max 4000 (bytes not characters) (or 32767 with EXTENDED set to initialisation parameter MAX_STRING_SIZE).

What are the precision and scale in NUMBER(n,m)?

Precision n = total number of digits (on either side of the decimal point). range from 1 to 38

Scale m = number of digits right of decimal. A negative value identifies how many significant digits to the left of the decimal point will be rounded off.

Defaults: both optional, max if both omitted. m can be –84 to 127. If n is specified and m is omitted, m defaults to zero.

If a value entered into a number column has a precision greater than the specified value, an error appears. If a value entered exceeds the scale, the entered value will be rounded off (.5 is rounded up) and accepted.

For example for NUMBER(5,-3) 1659.34 = 2000; 1059.34 = 1000 - and it can accept In this case numbers up to 9999999.9 (7 digits because it will add 1 after the round up) or 11111111 where the number of digits on the right side doesn’t matter

Describe the Date data type.

data type consists of fields, and each field is a component of a data or time, such as hours, minutes, etc.

Name all datetime fields and their ranges.

• YEAR - -4712 to 9999 (excluding 0)

• MONTH - 01 to 12

• DAY - 01 to 31 (limited for each month)

• HOUR - 00 to 23

• MINUTE - 00 to 59

• SECOND - 00 to 59.9(n) where 9(n) is the precision of time in fractional seconds
  doesn’t apply to DATE

• TIMEZONE_HOUR - -12 to 14
  doesn’t apply to DATE or TIMESTAMP

• TIMEZONE_MINUTE - 00 to 59
  doesn’t apply to DATE or TIMESTAMP

• TIMEZONE_REGION - example America/Chicago, from view V$TIMEZONE_NAMES in column TZNAME
  doesn’t apply to DATE or TIMESTAMP

• TIMEZONE_ABBR - in column TZABBREV of the view V$TIMEZONE_NAMES
  doesn’t apply to DATE or TIMESTAMP

What does DATE datatype store in Oracle?

Year, Month, Day, Hour, Minute, Second.

What is the default Oracle DATE format?

DD-MON-RR, controlled by NLS_DATE_FORMAT or NLS_TERRITORY. Use SHOW PARAMETERS NLS_TERRITORY to display it. Can be altered y ALTER SESSION or ALTER SYSTEM.

What is the TIMESTAMP(n) datatype?

It extends DATE with fractional seconds (n=0–9, default 6, is optional). So, just like DATE it stores year, month, day, hours, minutes, seconds and adds fractional seconds

What is the TIMESTAMP(n) WITH TIME ZONE datatype?

A TIMESTAMP that stores time zone region or offset for time zone. The range for n is 0-9, default is 6 if omitted.

What is TIMESTAMP(n) WITH LOCAL TIME ZONE?

Variation of TIMESTAMP. Time zone offset is not stored with the column’s value but it is calculated. It sends the date in user’s local session timezone. If n is omitted then 6 is the default.

What are INTERVAL data types?

• INTERVAL YEAR(n) TO MONTH - defined in only year and month values where n is the number of digits used to define the YEAR value. n = 0-9, default is 2. Useful for storing the difference between two dates

• INTERVAL DAY(n1) TO SECOND(n2) - defined in days, hours, minutes and seconds, where n1 = precision for days, n2 - seconds. n1 = 0-9, default is 2. n1 - how many digits are accepted in declaring the size of a number for DAY. n2 - fractional seconds precision for SECOND, range is 0-9, default is 6.

  Both used for representing spans of time.

What are Oracle LOB types?

Large objects - BLOB for binary large objects (image or video), CLOB for character large objects, NCLOB for Unicode text. Cannot be used in primary keys, DISTINCT, GROUP BY, ORDER BY, or joins. There can be multiple BLOB columns in one table.

Can you create a constraint with a CREATE statement?

No, you create a constraint as part of another statement, like CREATE TABLE or ALTER TABLE. The creation of a primary key, unique and a foreign key will automatically trigger the creation of a corresponding index object with the same name.

How many ways you create a constraint inside a create table constraint?

There are two ways: inline and out of line.

Describe inline constraints inside a create table statement

CREATE TABLE tab1 (col1 NUMBER PRIMARY KEY, col2 CHAR(1) NOT NULL)

or

CREATE TABLE tab1 (col1 NUMBER CONSTRAINT col1_pk PRIMARY KEY, col2 CHAR(1) CONSTRAINT col2_nn NOT NULL)

Describe out of line constraints inside a create table statement

CREATE TABLE tab1 (col1 NUMBER, PRIMARY KEY (col1))

or

CREATE TABLE tab1 (col1 NUMBER, CONSTRAINT col1_pk PRIMARY KEY (col1))

Describe adding a constraint using the ALTER statement inline

ALTER TABLE tab1 MODIFY col1 PRIMARY KEY

Describe adding a constraint using the ALTER statement out-of-line

ALTER TABLE tab1 ADD CONSTRAINT col_tab_uq UNIQUE (col1);

or

ALTER TABLE tab1 ADD UNIQUE (col1)

In what table can you see the system-generated constraint name?

In a data dictionary: USER_CONSTRAINTS. Example: Select constraint_name from user_constraints where table_name =’tab1’

What are the five types of constraints in Oracle?

NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK.

What is a NOT NULL constraint?

Ensures a column must always have a value. By default, columns allow NULL. Cannot be created out of line - (NOT NULL (col1)) is invalid. Or (constraint col1_nn not null (col1)) is invalid. And also ALTER TABLE tab1 ADD not null (col1) - won’t work. Also ALTER TABLE tab1 ADD constraint col1_nn not null (col1) won’t work

What is a UNIQUE constraint?

Ensures all values in a column (or group of columns) are unique. Allows NULLs.

What is a PRIMARY KEY constraint?

Combination of NOT NULL and UNIQUE. A table may have only one PRIMARY KEY. It may be single-column or composite.

What is a FOREIGN KEY constraint?

Enforces referential integrity by requiring values in child table columns to exist in parent table’s PRIMARY KEY or UNIQUE columns. Can be nullable.

How to create a out-of-line foreign key

constraint name_fk foreign key (child_col1) references parent_tab(parent_col1);

What is ON DELETE CASCADE in FOREIGN KEY constraints?

When parent rows are deleted, child rows referencing them are also deleted.

What is ON DELETE SET NULL in FOREIGN KEY constraints?

When parent rows are deleted, child table’s foreign key column values are set to NULL.

What is a CHECK constraint?

Allows only rows that satisfy a Boolean condition expression (TRUE or unknown, due to a NULL). Cannot use subqueries, scalar subquery expressions, user-defined functions, NEXTVAL, CURRVAL, LEVEL, ROWNUM, certain functions that whose value are unknown at the time of the call: SYSDATE, SYSTIMESTAMP, CURRENT_DATE, and others, columns from other tables, etc.

Can use a column from its own table

Can NOT NULL be defined out-of-line in Oracle?

No. NOT NULL can only be defined inline or via ALTER TABLE column modification.

Which data types cannot have a unique, primary key and foreign key constraints?

timestamp with time zone, blob, clob

What happens when you drop a column in Oracle?

The column and its data, constraints, and indexes are permanently removed.

What is the syntax for dropping a column?

ALTER TABLE tab1 drop column co1;

ALTER TABLE tab1 drop (col1); - can drop multiple columns at once

What keywords are used to drop a parent column that is referenced by a foreign key constraint?

CASCADE CONSTRAINTS. or CASCADE CONSTRAINT (both work the same)

Like: ALTER TABLE tab1 DROP COLUMN col1 CASCADE CONSTRAINTS;

Do you need to use CASCADE CONSTRAINT even on the child column when dropping it?

No, the foreign key constraint will drop automatically. But it will work with the key word CASCADE CONSTRAINT regardless.

Unless it’s a compound foreign key consisting of two or more columns then the CASCADE CONSTRAINT is mandatory OR drop all columns at once using drop (col1, col2, …)

Can you drop all columns from a table in Oracle?

No. A table must always have at least one column.

What is the effect of setting a column UNUSED?

It becomes permanently unavailable but not physically dropped. Still counts toward 1000 column limit until dropped later. Rollback won’t work on it. You can create a new column with the same name as the unused one.

Why use UNUSED instead of a drop statement?

For performance

What is the syntax of SET UNUSED?

ALTER TABLE tab1 SET UNUSED COLUMN col1;

You can set unused multiple columns at once like so:
ALTER TABLE tab1 SET UNUSED (col1, col2);

All constraints of a table unused column must be satisfied → can’t set unused a column which is referenced from a child table.

How to drop unused columns from a table?

ALTER TABLE tab1 DROP UNUSED COLUMNS;

What is an external table in Oracle?

A read-only table defined in the database but whose data resides outside the database. Metadata of the table is stored in this database but the data is outside of the database.

What restrictions apply to external tables?

You can query them with a SELECT. Cannot have LOB columns, cannot have constraints or index, cannot perform any DML statements, cannot set columns UNUSED.

What is the purpose of external tables?

Provide a way to integrate external data sources with Oracle SQL tools and queries. Stage large data files before transforming and inserting into internal database tables.

What is the purpose of a DIRECTORY object in Oracle?

Creates a name in the database to a path in the server’s file system for external tables.

What is the syntax of creating a directory?

CREATE DIRECTORY dir_name AS dir_reference;

where directory_reference is a string literal, surrounded by single quotation marks, that identifies a location within server’s file system, into which you wish for external tables to be read from.

example: CREATE DIRECTORY BANK_FILES AS ‘F:\bnk_files\trnsfr‘

What privileges does a user need to work with the directory?

Once a directory has been created, the owner must grant READ and/or WRITE access to any user who may use it:

GRANT READ ON DIRECTORY directory_name TO username;

Which Oracle utilities are used with external tables?

SQL*Loader and Oracle Data Pump.

what is the syntax for creating an external table?

CREATE TABLE tab1(col1 data_type, …) ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY dic_name ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE SKIP 2 FIELDS (col1 data_type,…)) LOCATION (‘file.txt’))