ExamFinch.com My Study

Study guide

11.1 CREATE TABLE & Oracle Built-In Datatypes

Practice this exam

Oracle Database SQL Certified Associate 1Z0-071

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Relational Database Concepts & Logical Modeling

1.1 Relational Database Architecture & Terminology 1.2 Entity Relationship Modeling & SQL Mapping 1.3 Database Normalization & Design Fundamentals

2 Chapter 2: Retrieving Data with SQL SELECT

2.1 SQL SELECT Fundamentals & Projection 2.2 Literal Values, Concatenation, and Quote Operators 2.3 Handling NULLs & Distinct Rows in Projection

3 Chapter 3: Restricting and Sorting Query Data

3.1 WHERE Clause Filtering & Comparison Operators 3.2 Logical Conditions & Operator Precedence 3.3 Sorting Data with ORDER BY 3.4 Substitution Variables & Session Directives

4 Chapter 4: Customizing Output with Single-Row Functions

4.1 Character Manipulation & Case Conversion 4.2 Numeric & Mathematical Functions 4.3 Date Arithmetic & Date Manipulation Functions 4.4 Nesting Single-Row Functions

5 Chapter 5: Conversion Functions and Conditional Expressions

5.1 Implicit vs Explicit Datatype Conversion 5.2 TO_CHAR, TO_NUMBER, and TO_DATE Functions 5.3 General NULL-Handling Functions 5.4 Conditional Expressions: CASE and DECODE

6 Chapter 6: Reporting Aggregated Data with Group Functions

6.1 Aggregate Functions & NULL Mechanics 6.2 GROUP BY Clause & Aggregation Rules 6.3 Filtering Groups with the HAVING Clause

7 Chapter 7: Displaying Data from Multiple Tables (Joins)

7.1 ANSI/ISO SQL Equijoins & Natural Joins 7.2 Non-Equijoins, Self Joins, and Cartesian Products 7.3 Outer Joins: LEFT, RIGHT, and FULL

8 Chapter 8: Solving Queries with Subqueries

8.1 Single-Row Subqueries 8.2 Multiple-Row Subqueries & Inline Views 8.3 Correlated Subqueries & EXISTS Logic 8.4 Subquery Cardinality & NULL Traps

9 Chapter 9: Set Operators

9.1 UNION and UNION ALL Operators 9.2 INTERSECT and MINUS Operators 9.3 Set Operator Guidelines & ORDER BY Rules

10 Chapter 10: Managing Tables with DML & Transaction Control

10.1 INSERT, UPDATE, and DELETE Statements 10.2 MERGE and Multitable Inserts 10.3 Transaction Management: COMMIT, ROLLBACK, and SAVEPOINT

11 Chapter 11: Managing Tables and Relationships with DDL

11.1 CREATE TABLE & Oracle Built-In Datatypes 11.2 Integrity Constraints 11.3 Modifying Tables & Managing Constraints 11.4 Global Temporary Tables & External Tables

12 Chapter 12: Views

12.1 Simple vs Complex Views 12.2 DML Operations on Views & View Constraints

13 Chapter 13: Indexes, Synonyms, and Sequences

13.1 Sequences 13.2 Indexes 13.3 Synonyms

14 Chapter 14: Controlling User Access & Privileges

14.1 System Privileges 14.2 Object Privileges 14.3 Roles & Privilege Management

15 Chapter 15: Oracle Data Dictionary Views

15.1 Data Dictionary Structure & View Prefixes 15.2 Querying Schema & Object Metadata 15.3 Inspecting Constraints, Privileges, and Comments

16 Chapter 16: Managing Data in Different Time Zones

16.1 Datetime Data Types & Time Zone Offsets 16.2 Interval Data Types & Arithmetic 16.3 Time Zone Functions & Session Environment

11.1 CREATE TABLE & Oracle Built-In Datatypes

Key Takeaways Explain This Topic AI

Data Definition Language (DDL) statements implicitly issue a COMMIT before and after execution, rendering structural changes irreversible via ROLLBACK.

Oracle schema object identifiers must begin with an alphabetic character, contain only letters, numbers, _, $, and #, be at most 30 bytes (or 128 bytes in 12.2+), and remain case-insensitive unless enclosed in double quotes.

VARCHAR2 is variable-length without blank-padding (up to 4,000 bytes or 32,767 bytes extended), whereas CHAR is fixed-length and blank-padded with spaces up to its declared size.

NUMBER(precision, scale) enforces total significant digits (p: 1-38) and decimal positioning (s: -84 to 127); positive scale rounds decimal places, while negative scale rounds to powers of ten.

Identity columns (GENERATED ALWAYS | BY DEFAULT [ON NULL] AS IDENTITY) automate surrogate key generation, while CREATE TABLE ... AS SELECT (CTAS) copies table structure and data but inherits only NOT NULL constraints.

Last updated: August 2026

11.1 CREATE TABLE & Oracle Built-In Datatypes

In Oracle SQL, Data Definition Language (DDL) commands build, alter, and remove database structures. Unlike Data Manipulation Language (DML) statements ( INSERT , UPDATE , DELETE ) which operate within recoverable transactions, DDL statements—such as CREATE TABLE —execute an implicit COMMIT immediately before and after running. Consequently, DDL changes are permanent and cannot be undone using ROLLBACK .

To construct reliable schemas and pass the Oracle Database SQL (1Z0-071) exam, you must master the CREATE TABLE syntax, schema object naming conventions, built-in datatype storage mechanics, column defaults, identity columns, and table replication using CREATE TABLE ... AS SELECT (CTAS).

Oracle Schema Object Naming Rules

When declaring tables, columns, constraints, views, or indexes, names must comply with Oracle's standard identifier rules: +-------------------------------------------------------------------------+

  • | ORACLE IDENTIFIER NAMING RULES |
  • +-------------------------------------------------------------------------+
  • | 1. Must begin with an alphabetic character (A-Z, a-z). |
  • | 2. Can contain letters, digits (0-9), _, $, and #. |
  • | 3. Length: 1 to 30 bytes (up to 128 bytes in Oracle 12.2+). |
  • | 4. Case-insensitive by default (stored in UPPERCASE in data dictionary).|
  • | 5. Must not be an Oracle reserved word (e.g., SELECT, FROM, TABLE). |
  • | 6. Must be unique within the same schema and namespace. |
  • +-------------------------------------------------------------------------+
  • Standard (Unquoted) vs. Quoted Identifiers
  • Feature Standard (Unquoted) Identifier Quoted Identifier ( "name" )
  • Starting Character Must be alphabetic ( A-Z , a-z ) Any character, including spaces and numbers
  • Allowed Characters A-Z , a-z , 0-9 , _ , $ , # Any character (e.g., "Order Date#" , "First Name" )
  • Dictionary Storage Converted and stored in UPPERCASE Stored with exact case as entered
  • Reserved Words Strictly prohibited (e.g., SELECT ) Permitted (e.g., "SELECT" , "GROUP" )
  • Query Reference Case-insensitive ( emp , EMP , Emp ) Must always use double quotes and exact case

Exam Tip: Avoid using quoted identifiers in production. If you create a table using CREATE TABLE "emp_records" (...) , querying SELECT * FROM emp_records; will raise ORA-00942: table or view does not exist because Oracle looks for EMP_RECORDS in uppercase.

  • Basic CREATE TABLE Syntax
  • The fundamental syntax for creating a standard relational table is: CREATE TABLE [schema.]table_name (
  • column_1 datatype [DEFAULT expr] [inline_constraint],
  • column_2 datatype [DEFAULT expr] [inline_constraint],

...

[out_of_line_constraint],

...

  • );
  • Describing Tables: DESCRIBE / DESC

Before writing SQL against an unfamiliar table you need its column list, datatypes, and nullability. In SQL*Plus, SQL Developer, and SQLcl the DESCRIBE command (abbreviated DESC ) prints exactly that structure: DESCRIBE employees

-- Abbreviated form, optionally schema-qualified:

  • DESC hr.employees Name Null? Type
  • -------------------- -------- ----------------------
  • EMPLOYEE_ID NOT NULL NUMBER(6)
  • FIRST_NAME VARCHAR2(20)
  • LAST_NAME NOT NULL VARCHAR2(25)
  • EMAIL NOT NULL VARCHAR2(25)
  • HIRE_DATE NOT NULL DATE
  • JOB_ID NOT NULL VARCHAR2(10)
  • SALARY NUMBER(8,2)
  • COMMISSION_PCT NUMBER(2,2)
  • DEPARTMENT_ID NUMBER(4)
  • DESCRIBE Rules Tested on 1Z0-071

It is a client tool command, not a SQL statement. SQL*Plus, SQL Developer, and SQLcl interpret DESCRIBE locally instead of sending it to the SQL engine, so it cannot be nested inside a SELECT , used in PL/SQL, or issued by an application through JDBC as SQL.

Columns appear in COLUMN_ID order — the same physical order that an INSERT without a column list requires.

The Null? column reports only NOT NULL or blank. It shows nullability alone: no constraint names, no default values, no primary/foreign key information, no CHECK conditions.

It also works on views and synonyms , displaying the projected column structure of the underlying object.

The SQL equivalent — the one you can actually embed in queries, scripts, and application code — is USER_TAB_COLUMNS / ALL_TAB_COLUMNS (Chapter 15): SELECT column_name, data_type, data_length, data_precision, data_scale, nullable

  • FROM user_tab_columns
  • WHERE table_name = 'EMPLOYEES'
  • ORDER BY column_id;
  • Oracle Built-In Datatypes

Choosing the correct datatype guarantees domain integrity, optimizes physical disk storage, and ensures predictable query sorting and filtering behavior. ORACLE DATATYPES

  • |
  • +---------------+---------------+---------------+---------------+
  • | | | | |
  • CHARACTER NUMERIC DATETIME LOB BINARY
  • VARCHAR2 - NUMBER - DATE - CLOB - RAW
  • CHAR - TIMESTAMP - BLOB - LONG RAW
  • LONG (legacy) - INTERVAL - BFILE
  • Character Datatypes: VARCHAR2 vs. CHAR

Datatype Description & Maximum Size Blank-Padding Semantics Recommended Usage

VARCHAR2(size [BYTE | CHAR]) Variable-length character string. Max: 4,000 bytes (or 32,767 bytes if MAX_STRING_SIZE = EXTENDED ). No padding. Stores only the actual characters entered. General text attributes (names, emails, descriptions).

CHAR(size [BYTE | CHAR]) Fixed-length character string. Max: 2,000 bytes . Defaults to CHAR(1) if size is omitted. Blank-padded. Right-padded with spaces up to the declared size. Fixed-length codes (e.g., CHAR(2) for US states, CHAR(1) for status flags).

LONG Legacy variable-length text up to 2GB. Max 1 LONG column per table. No padding. Deprecated in favor of CLOB . Legacy compatibility only. Prohibited in WHERE , GROUP BY , ORDER BY , DISTINCT , or subqueries.

  • Blank-Padding Comparison Mechanics
  • When comparing CHAR and VARCHAR2 values, Oracle uses distinct comparison semantics: CREATE TABLE code_test (
  • char_code CHAR(5),
  • vchar_code VARCHAR2(5)
  • );
  • INSERT INTO code_test VALUES ('ABC', 'ABC');
  • - char_code stores 'ABC ' (length 5)
  • - vchar_code stores 'ABC' (length 3)
  • - Query 1: Literal comparison with CHAR
  • SELECT * FROM code_test WHERE char_code = 'ABC';
  • - RETURNS ROW (Oracle blank-pads the literal 'ABC' to 'ABC ')
  • - Query 2: Cross-column comparison
  • SELECT * FROM code_test WHERE char_code = vchar_code;
  • - RETURNS NO ROWS! Non-padded comparison: 'ABC ' != 'ABC'
  • The NUMBER Datatype: Precision and Scale Mechanics

The NUMBER datatype stores fixed and floating-point numbers with up to 38 decimal digits of precision . Syntax: NUMBER ( p , s ) \text{Syntax: } \text{NUMBER}(p, s) Syntax: NUMBER ( p , s )

Precision ( p p p ): Total number of significant decimal digits (range: 1 to 38 ).

Scale ( s s s ): Number of digits to the right (positive) or left (negative) of the decimal point (range: -84 to 127 ).

Allowed Integer Digits: p − s p - s p − s digits are permitted to the left of the decimal point. Precision (p = 7): Total Significant Digits

  • <----------------------------------------->
  • 1 2 3 4 5 . 6 7
  • <-----------------------> <------->
  • Integer Digits (p - s=5) Scale (s=2)
  • Precision and Scale Rules Matrix

Declared Datatype Input Value Stored Value Explanation

NUMBER(6, 2) 1234.56 1234.56 Fits exactly within 4 integer digits ( 6 − 2 6 - 2 6 − 2 ) and 2 decimal digits.

NUMBER(6, 2) 1234.567 1234.57 Rounded to 2 decimal places. Scale rounding never causes an error.

NUMBER(6, 2) 12345.6 ORA-01438 Fails: 5 integer digits exceeds maximum allowable integer digits ( 6 − 2 = 4 6 - 2 = 4 6 − 2 = 4 ).

NUMBER(4) or NUMBER(4,0) 9876.4 9876 Integer only; rounded to 0 decimal places.

NUMBER(4) 12345 ORA-01438 Fails: 5 digits exceeds precision 4.

NUMBER(5, -2) 12345 12300 Negative scale: rounded to the nearest hundred ( 10 2 10^2 1 0 2 ). Max value: 9999900 9999900 9999900 .

NUMBER(5, -2) 12368 12400 Rounded up to nearest hundred.

NUMBER(3, 5) 0.00123 0.00123 Scale > Precision ( s > p s > p s > p ): Requires at least s − p = 2 s - p = 2 s − p = 2 leading zeros after decimal point.

NUMBER(3, 5) 0.01234 ORA-01438 Fails: Only 1 leading zero after decimal point; exceeds precision.

NUMBER (no params) 123456.789 123456.789 Defaults to floating-point number with maximum precision (38 digits).

Exam Trap: Exceeding scale causes Oracle to round the fraction silently. Exceeding the allowable integer digits ( p − s p - s p − s ) causes Oracle to reject the statement with ORA-01438: value larger than specified precision allowed for this column .

  • Datetime and LOB Datatypes
  • Datetime Datatypes

Datatype Storage Size Components Stored Fractional Seconds / Timezone

DATE Fixed 7 bytes Century, Year, Month, Day, Hour, Minute, Second No fractional seconds. No timezone support.

TIMESTAMP[(fsp)] 7 to 11 bytes Date + Time + Fractional seconds ( fsp 0–9, default 6) Fractional seconds supported. No timezone support.

TIMESTAMP WITH TIME ZONE 13 bytes Date + Time + Fractional seconds + Time zone offset/region Preserves original timezone offset or region name.

TIMESTAMP WITH LOCAL TIME ZONE 7 to 11 bytes Date + Time + Fractional seconds Normalized to database timezone on disk; displayed in session timezone.

Large Object (LOB) and Binary Datatypes

CLOB (Character Large Object): Stores single-byte or multi-byte character text data up to ( 4 GB − 1 ) × DB_BLOCK_SIZE (4\text{GB} - 1) \times \text{DB\_BLOCK\_SIZE} ( 4 GB − 1 ) × DB_BLOCK_SIZE (typically 8TB to 128TB).

BLOB (Binary Large Object): Stores unstructured binary data (images, audio, PDFs, executables) up to the same multi-terabyte limits.

RAW(size) : Variable-length raw binary data up to 2,000 bytes (or 32,767 extended bytes).

BFILE : Stores a read-only locator pointer to an operating system file outside the database.

DEFAULT Column Values

The DEFAULT clause specifies a value to be automatically assigned when an INSERT statement omits the column or specifies the DEFAULT keyword: CREATE TABLE orders (

  • order_id NUMBER(10) PRIMARY KEY,
  • order_date DATE DEFAULT SYSDATE NOT NULL,
  • status VARCHAR2(20) DEFAULT 'PENDING',
  • priority NUMBER(1) DEFAULT 1
  • );
  • Rules and Prohibitions for DEFAULT Clauses

Valid expressions: Literals ( 'ACTIVE' , 100 ), deterministic expressions ( 10 * 5 ), and built-in SQL functions ( SYSDATE , CURRENT_TIMESTAMP , USER ).

Prohibited in DEFAULT:

References to other table columns (e.g., DEFAULT col_a + 10 is invalid).

Pseudocolumns such as ROWNUM , LEVEL , PRIOR , or XMLDATA .

Subqueries (e.g., DEFAULT (SELECT default_val FROM config) is invalid).

Behavior with NULL: If an INSERT explicitly passes NULL into a column with a standard DEFAULT clause, the column is populated with NULL , not the default value.

Identity Columns (Oracle Database 12c+)

An Identity Column automates surrogate key generation using an implicit database sequence bound directly to the table column: Syntax: col_name NUMBER GENERATED [ALWAYS | BY DEFAULT [ON NULL]] AS IDENTITY [(options)] \text{Syntax: } \text{col\_name NUMBER GENERATED [ALWAYS | BY DEFAULT [ON NULL]] AS IDENTITY [(options)]} Syntax: col_name NUMBER GENERATED [ALWAYS | BY DEFAULT [ON NULL]] AS IDENTITY [(options)] CREATE TABLE departments_auto (

  • dept_id NUMBER GENERATED ALWAYS AS IDENTITY (START WITH 10 INCREMENT BY 10) PRIMARY KEY,
  • dept_name VARCHAR2(30) NOT NULL
  • );
  • The Three Identity Options

Identity Option Insert Without Column Insert Explicit Value Insert Explicit NULL

GENERATED ALWAYS AS IDENTITY Uses Sequence ORA-32795 (Cannot insert into identity column generated ALWAYS) ORA-32795

GENERATED BY DEFAULT AS IDENTITY Uses Sequence Accepts user value ORA-01400 (Cannot insert NULL into NOT NULL column)

GENERATED BY DEFAULT ON NULL AS IDENTITY Uses Sequence Accepts user value Uses Sequence (Replaces NULL with sequence value)

Identity Column Restrictions

Only one identity column is permitted per table.

Must be a numeric datatype ( NUMBER , INTEGER ); user-defined datatypes are not permitted.

Cannot specify a separate DEFAULT clause on an identity column.

NOT NULL and NOT DEFERRABLE are applied implicitly; an inline constraint that conflicts with either raises an error.

CREATE TABLE ... AS SELECT (CTAS)

CREATE TABLE ... AS SELECT allows you to create a new table and populate it with rows from an existing query in a single DDL operation: -- Copy table structure and data for department 50

  • CREATE TABLE emp_dept50 AS
  • SELECT employee_id, first_name, last_name, salary * 12 AS annual_salary
  • FROM hr.employees
  • WHERE department_id = 50;

CTAS Rules & Constraint Inheritance Matrix +-------------------------------------------------------------------------+

  • | CTAS CONSTRAINT INHERITANCE RULES |
  • +-------------------------------------------------------------------------+
  • | Constraint / Object Type | Inherited in New Table? | Action Required |
  • | :------------------------ | :---------------------- | :---------------- |
  • | NOT NULL Constraints | YES (explicit only) | None |
  • | DEFAULT Values | NO (Ignored) | Redefine via ALTER|
  • | PRIMARY KEY Constraints | NO (Ignored) | Add via ALTER |
  • | FOREIGN KEY Constraints | NO (Ignored) | Add via ALTER |
  • | UNIQUE Constraints | NO (Ignored) | Add via ALTER |
  • | CHECK Constraints | NO (Ignored) | Add via ALTER |
  • | Table Indexes | NO (Ignored) | Recreate manually |
  • +-------------------------------------------------------------------------+
  • Critical CTAS Guidelines

Column Aliases Required for Expressions: Any calculated expression, literal, or function in the SELECT list must have an explicit column alias, or the statement will fail with ORA-00998: must name this expression with a column alias .

Structure-Only Copy: To copy the table schema without copying any data rows, use an impossible condition in the WHERE clause: CREATE TABLE emp_template AS

SELECT * FROM hr.employees WHERE 1 = 2;

Only Explicit NOT NULL Survives: Oracle carries over a NOT NULL constraint only when it was explicitly created on the source column and the subquery selects that column directly (not wrapped in an expression). A NOT NULL that Oracle generated implicitly — for example, the one behind a PRIMARY KEY — is not carried over.

Column DEFAULT Values Are Lost: CREATE TABLE ... AS SELECT does not copy DEFAULT clauses. If ORDER_DATE was DEFAULT SYSDATE in the source table, the CTAS copy has no default at all and must be re-declared with ALTER TABLE ... MODIFY (order_date DEFAULT SYSDATE) .