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.

What are quoted names in Oracle?

Names enclosed in double quotes, case-sensitive, can start with any character, include spaces or reserved words. 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.

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

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

What does the DESC 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).

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

CHAR(n) is fixed length and pads with blanks; VARCHAR2(n) is variable length and does not pad. CHAR max 2000, VARCHAR2 max 4000 (or 32767 with EXTENDED).

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

Precision n = total number of digits. Scale m = number of digits right of decimal. Defaults: both max if omitted. m can be –84 to 127.

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 NLSDATEFORMAT or NLS_TERRITORY.

What is the TIMESTAMP(n) datatype?

It extends DATE with fractional seconds (n=0–9, default 6).

What is the TIMESTAMP WITH TIME ZONE datatype?

A TIMESTAMP that stores time zone region or offset.

What is TIMESTAMP WITH LOCAL TIME ZONE?

Stores time without offset; displayed in user’s local session timezone.

What are INTERVAL data types?

INTERVAL YEAR(n) TO MONTH and INTERVAL DAY(n1) TO SECOND(n2). Used for representing spans of time.

What are Oracle LOB types?

BLOB for binary large objects, CLOB for character large objects, NCLOB for Unicode text. Cannot be used in primary keys, DISTINCT, GROUP BY, ORDER BY, or joins.

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.

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.

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. Cannot use subqueries, NEXTVAL, CURRVAL, SYSDATE, etc.

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

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

What happens when you drop a column in Oracle?

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

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

CASCADE CONSTRAINTS.

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.

What is an external table in Oracle?

A read-only table defined in the database but whose data resides outside the database.

What restrictions apply to external tables?

Cannot have LOB columns, cannot have constraints, cannot update/insert/delete rows, cannot set columns UNUSED.

What is the purpose of a DIRECTORY object in Oracle?

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

Which Oracle utilities are used with external tables?

SQL*Loader and Oracle Data Pump.

What does TRUNCATE TABLE do in Oracle?

Removes all rows from a table quickly without firing DML triggers. It is DDL, auto-commits, and cannot be rolled back.

What happens if TRUNCATE TABLE is applied to a parent table with ON DELETE CASCADE child rows?

It raises an error unless TRUNCATE TABLE … CASCADE is used.