Creating your first table in Oracle Database is a foundational skill for any developer or database administrator. This guide provides a clear, step-by-step example of how to create a simple table, focusing on practical syntax and essential data types.

Understanding the Basics of Oracle Table Creation

Before diving into the code, it is important to understand that in Oracle, a table is a collection of related data organized in rows and columns. Each column is defined by a name and a specific data type, such as VARCHAR2 for text or NUMBER for numeric values. The structure you define acts as a blueprint for the data you will store.
Simple Table Example: The Department Table

Let us move straight to a concrete example. Suppose we need to store basic information about departments within a company. A "simple table" in this context means a table with a few essential columns that illustrate the core syntax without unnecessary complexity.
SQL Code for the Example

To create the table, you will use the CREATE TABLE statement. Below is the exact syntax for our Department example. This command tells Oracle to allocate storage and define the structure for a new table named DEPARTMENTS.
CREATE TABLE departments ( |
| department_id NUMBER(4) NOT NULL, |
| department_name VARCHAR2(30) NOT NULL, |
| location_id NUMBER(4) |
); |
Breaking Down the Syntax

Let us analyze what is happening in this code. The CREATE TABLE command is followed by the table name, DEPARTMENTS, adhering to standard naming conventions. Inside the parentheses, we define three columns.
- department_id: This uses the NUMBER data type with a precision of 4, meaning it can hold up to four digits. The NOT NULL constraint ensures that this field must have a value for every row.
- department_name: This uses VARCHAR2(30), a variable-length character string capable of holding up to 30 characters. This is ideal for textual data like names.
- location_id: Another NUMBER column, but this one does not have a NOT NULL constraint, allowing it to remain empty if the specific location is unknown.
Executing the Command

To run this example, you need access to an Oracle Database environment. You can execute the SQL statement using tools like SQL*Plus, Oracle SQL Developer, or any other database client connected to your schema. Upon successful execution, Oracle returns a confirmation message indicating that the table has been created.
Best Practices for Simple Tables



















When building simple table examples, it is wise to prioritize clarity and integrity. Always define a primary key, even in our basic example; in a production scenario, you would likely add a PRIMARY KEY constraint to the department_id. Using consistent naming conventions, such as using singular nouns for table names, makes your database schema easier to manage as it grows.
Verifying Your Work
After creating the table, it is good practice to verify its structure. You can describe the table using a command like DESC departments;. This command lists the columns, their data types, and whether they accept null values, providing immediate feedback that your simple table example has been built correctly.