Oracle SQL Certification 1Z0-071 Course | Section 4: Use DDL to Manage Tables and Their Relationships

In this section we'll learn basics (but in depth) around creating tables and their properties

Data Definition Language, or DDL, is used to define and modify database structures. In the Oracle SQL 1Z0-071 exam, DDL topics are tested frequently and often combined with constraint behavior, data types, and transaction rules.

This section focuses on creating tables, defining columns and constraints, modifying table structures, and understanding special table types supported by Oracle.

Video 1: Creating a Table and Using DESCRIBE

Creating tables is a foundational skill for Oracle SQL and a core exam topic. A CREATE TABLE statement defines the table name, column names, data types, and constraints.

The general structure of a CREATE TABLE statement includes:

  • The CREATE keyword
  • The object type TABLE
  • The table name
  • Column definitions
  • Optional constraints

Multiple columns are defined within parentheses and separated by commas. Each column requires a name and a data type.

Common data types introduced include NUMBER for numeric values, VARCHAR2 for variable length text, and DATE for date and time values.

Constraints are introduced directly inside the CREATE TABLE statement. Primary key constraints enforce uniqueness and automatically apply a NOT NULL restriction. Default values can be defined to automatically populate columns during insert operations, such as using SYSDATE.

Oracle provides the DESCRIBE command to inspect a table’s structure. DESCRIBE, or its short form DESC, displays column names, data types, and nullability. It is an Oracle specific command, does not require a semicolon, and works in both SQL Developer and SQLPlus.

Understanding how to read table metadata using DESCRIBE and graphical tools in SQL Developer is important for both practice and exam questions.

-- CREATE objectType objectName attributes
-- create table
CREATE TABLE cars (
car_id NUMBER,
car_manufacturer VARCHAR2(20),
car_model VARCHAR2(20),
owner_id NUMBER NOT NULL,
manufacture_date DATE DEFAULT SYSDATE,
car_color VARCHAR2(15),
CONSTRAINT car_pk PRIMARY KEY ( car_id )
);
-- description of table
DESC cars;
-- remove table as we don't need it atm
DROP TABLE cars;

Video 2: Data Types for Columns

Choosing correct data types is one of the most heavily tested topics in the 1Z0-071 exam. Data types affect how data is stored, validated, and processed.

Oracle data types are grouped into several categories:

  • Character
  • Numeric
  • Date and Time
  • Large Objects

Character data types include CHAR and VARCHAR2. CHAR stores fixed length data and pads unused space with blanks, while VARCHAR2 stores only the actual length of the value. For most use cases and exam questions, VARCHAR2 is preferred unless fixed length storage is explicitly required.

The NUMBER data type is defined using precision and scale. Precision specifies the total number of digits, while scale defines how many digits appear to the right of the decimal point. Oracle applies rounding rules when inserting values and raises errors when precision limits are exceeded. Negative scale values cause rounding to the left of the decimal point.

Date and time data types include DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE. The DATE type stores both date and time components, which is a common exam trap.

Large object data types such as BLOB, CLOB, and NCLOB are used to store large amounts of data. These types have important restrictions. They cannot be used as primary keys and cannot participate in DISTINCT, GROUP BY, ORDER BY, or join operations.

Oracle relies on NLS_DATE_FORMAT to implicitly convert strings into dates, which affects how date literals are interpreted.

--character data type example
CREATE TABLE test_table_character (
character1 CHAR(5),
character2 varchar2(100) -- the size number has to be defined and larger than 0
);
INSERT INTO test_table_character VALUES (
'A', -- character1
'A' -- character2
);
SELECT
*
FROM
test_table_character;
--DELETE test_table_character;
-- drop table as we don't need it anymore
DROP TABLE test_table_character;
-- number data type example
CREATE TABLE test_table (
number1 NUMBER,
number2 NUMBER(2),
number3 NUMBER(5, 2),
number4 NUMBER(5, 2),
number5 NUMBER(4, 1),
number6 NUMBER(3, 2),
number7 NUMBER(5, -2)
);
INSERT INTO test_table VALUES (
1.23, -- number1
11.23, -- number2
1.23, -- number3
1.26, -- number4
1.26, -- number5
11.45, -- number6
1265.34 -- number7
);
SELECT * FROM test_table;
-- DELETE test_table;
-- drop table as we don't need it anymoreDROP TABLE test_table;

Video 3: Constraints

Constraints enforce data integrity rules and are one of the most critical exam topics. Oracle SQL supports five constraint types tested in the exam:

  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • NOT NULL
  • CHECK

Constraints can be defined inline with column definitions or out of line after all columns are declared. Oracle can generate constraint names automatically, or you can specify them explicitly.

A table can have only one primary key, which may consist of one or multiple columns. UNIQUE constraints allow NULL values, while primary keys do not. NOT NULL constraints can only be defined inline.

Foreign keys establish relationships between tables and enforce referential integrity. A referenced table cannot be dropped while dependent foreign keys exist.

PRIMARY KEY, UNIQUE, and FOREIGN KEY constraints automatically create indexes. Certain data types, such as LOBs and TIMESTAMP WITH TIME ZONE, cannot be used with some constraints.

Understanding when constraints are validated and how they behave during insert and update operations is essential for exam success.

-- Primary key constraint
-- inline constraint - system named
CREATE TABLE constraint_table_1 (
constraint_id NUMBER PRIMARY KEY
);
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = UPPER('constraint_table_1');
-- inline constraint - Named
CREATE TABLE constraint_table_2 (
constraint_id NUMBER CONSTRAINT constraint_2_pk PRIMARY KEY
);
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = UPPER('constraint_table_2');
-- Out of line constraint - Named
CREATE TABLE constraint_table_3 (
constraint_id NUMBER,
CONSTRAINT constraint_3_pk PRIMARY KEY ( constraint_id )
);
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = UPPER('constraint_table_3');
-- Out of line constraint - System named
CREATE TABLE constraint_table_4 (
constraint_id NUMBER,PRIMARY KEY ( constraint_id )
);
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = UPPER('constraint_table_4');

-- add pk with alter table
CREATE TABLE constraint_table_5 (
constraint_id NUMBER
);
ALTER TABLE constraint_table_5
MODIFY constraint_id PRIMARY KEY;
ALTER TABLE constraint_table_5
ADD PRIMARY KEY (constraint_id);
ALTER TABLE constraint_table_5
ADD CONSTRAINT alter_pk_named PRIMARY KEY (constraint_id);
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = UPPER('constraint_table_5');
DROP TABLE constraint_table_1;
DROP TABLE constraint_table_2;
DROP TABLE constraint_table_3;
DROP TABLE constraint_table_4;
DROP TABLE constraint_table_5;

-- Unique constraint
-- inline constraint - system named
CREATE TABLE constraint_table_1 (
constraint_id NUMBER UNIQUE
);
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = UPPER('constraint_table_1');
-- inline constraint - Named
CREATE TABLE constraint_table_2 (
constraint_id NUMBER CONSTRAINT constraint_2_uniq UNIQUE
);
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = UPPER('constraint_table_2');
-- Out of line constraint - Named + composite
CREATE TABLE constraint_table_3 (
constraint_id NUMBER,
constraint_name VARCHAR(20),
CONSTRAINT constraint_3_uniq UNIQUE ( constraint_id,constraint_name )
);
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = UPPER('constraint_table_3');
-- Out of line constraint - System named
CREATE TABLE constraint_table_4 (
constraint_id NUMBER,
UNIQUE ( constraint_id )
);

SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = UPPER('constraint_table_4');
DROP TABLE constraint_table_1;
DROP TABLE constraint_table_2;
DROP TABLE constraint_table_3;
DROP TABLE constraint_table_4;

Video 4: Dropping Columns and Setting Columns UNUSED

Oracle provides two ways to remove columns from a table. Dropping a column permanently removes both the column definition and its data. This operation can be time consuming on large tables.

Setting a column as UNUSED is a faster, metadata only operation. The column becomes inaccessible, cannot be rolled back, and still counts toward the table’s column limit. UNUSED columns must be explicitly dropped later to free storage.

Key rules include:

  • A table must always contain at least one column
  • Columns involved in constraints may require CASCADE CONSTRAINTS
  • UNUSED columns cannot be recovered
  • UNUSED columns can be identified using data dictionary views

Understanding when to drop a column versus setting it UNUSED is important for both real world scenarios and exam questions.

-- dropping columns
CREATE TABLE multi_column_table (
id_column NUMBER,
text_column_1 VARCHAR2(100),
text_column_2 VARCHAR2(100),
text_column_3 VARCHAR2(100)
);
-- drop column
ALTER TABLE multi_column_table DROP COLUMN text_column_1;
SELECT * FROM multi_column_table;
-- other way, this way we can drop multiple columns in one line
ALTER TABLE multi_column_table DROP (text_column_2, text_column_3);
SELECT * FROM multi_column_table;
-- drop table as we do not need it
DROP TABLE multi_column_table;
-- dropping columns with constraints
CREATE TABLE primary_table (
prim_id NUMBER PRIMARY KEY,
text_column_1 VARCHAR2(100)
);
CREATE TABLE reference_table (
id_ref_column NUMBER PRIMARY KEY,
prim_id NUMBER,
CONSTRAINT fk_prim FOREIGN KEY ( prim_id )
REFERENCES primary_table ( prim_id )
);
ALTER TABLE primary_table DROP COLUMN prim_id; -- WON'T WORK!
ALTER TABLE primary_table DROP COLUMN prim_id CASCADE CONSTRAINTS; --
constraints are also dropped
SELECT * FROM primary_table;
-- tip: CONSTRAINT and COSTRAINTS keywords (singular and plural) are both valid!
--drop tables as we do not need it atm
DROP TABLE primary_table;
DROP TABLE reference_table;

-- setting columns unused
CREATE TABLE multi_column_table (
id_column NUMBER,
text_column_1 VARCHAR2(100),
text_column_2 VARCHAR2(100),
text_column_3 VARCHAR2(100)
);
SELECT * FROM multi_column_table;
ALTER TABLE multi_column_table SET UNUSED COLUMN text_column_1;
ALTER TABLE multi_column_table SET UNUSED (text_column_2, text_column_3);
--we can check unused columns:
SELECT * FROM USER_UNUSED_COL_TABS;
--drop columns which are unused
ALTER TABLE multi_column_table DROP UNUSED COLUMNS;
--drop table as we do not need it atm
DROP TABLE multi_column_table;

Video 5: External Tables

External tables allow Oracle to read data directly from files stored outside the database. They are defined as database objects, but the data itself remains external.

External tables are created using ORGANIZATION EXTERNAL and ORACLE_LOADER. They rely on DIRECTORY objects, which must have appropriate read and write privileges granted.

External tables support only SELECT operations. INSERT, UPDATE, and DELETE are not allowed. Constraints, indexes, and LOB data types are not supported. Columns cannot be set UNUSED, but they can be dropped.

Dropping an external table removes only its metadata. The underlying file remains unchanged.

External tables are commonly used for data integration, reporting, and querying system generated files without loading them into database tables.

-- create directory -- from sys user on our pluggable database
CREATE OR REPLACE DIRECTORY receipts_dir AS 'C:\app\';
--grant read/write to hr user -- from sys user on our pluggable database
GRANT READ ON DIRECTORY receipts_dir TO HR;
GRANT WRITE ON DIRECTORY receipts_dir TO HR;
-- create text file in directory as specified
RECEIPT_ID / DATE / BATCH
======================
555123 01-NOV-2020 ABCD1
555124 02-NOV-2020 ABCD1
555125 03-NOV-2020 ABCD2
555126 03-NOV-2020 ABCD2
-- create external table
CREATE TABLE external_receipts (
receipt_id CHAR(6),
receipt_date CHAR(10),
receipt_batch_id CHAR(5)
)
ORGANIZATION EXTERNAL ( TYPE oracle_loader
DEFAULT DIRECTORY receipts_dir ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
SKIP 4
FIELDS (
receipt_id CHAR (6),
receipt_date CHAR (12),
receipt_batch_id CHAR (6)
)
) LOCATION ( 'RECEIPTS.TXT' )
);
SELECT * FROM external_receipts;

Section Outcome

After completing this section, you should:

  • Be able to create and modify tables using DDL
  • Understand how data types affect storage and behavior
  • Apply and interpret constraints correctly
  • Modify table structures safely and efficiently
  • Understand the purpose and limitations of external tables

These skills form the foundation for writing reliable SQL and are heavily tested in the Oracle SQL 1Z0-071 certification exam.

Now already involved in this SQL journey, you can subscribe to the YouTube channel and maybe subscribe to newsletter below?

Join ForgeFuse Newsletter