In Oracle SQL, DDL (Data Definition Language) statements are used to define and
manage database structures like tables, views, indexes, and more. These commands affect the
schema and are automatically committed, meaning changes are permanent.
Common Oracle DDL Statements
Command Purpose Example Syntax
CREATE Creates database objects CREATE TABLE employees (...);
ALTER TABLE employees ADD address
ALTER Modifies existing objects VARCHAR2(100);
DROP Deletes objects permanently DROP TABLE employees;
TRUNCATE
Removes all rows from a table TRUNCATE TABLE employees;
(no rollback)
RENAME Renames a database object RENAME employees TO staff;
COMMENT ON TABLE employees IS 'Stores
COMMENT Adds comments to objects employee data';
📌 Examples
✅ Create a Table
CREATE TABLE employees (
id NUMBER PRIMARY KEY,
name VARCHAR2(50),
salary NUMBER
);
✏️Alter a Table
The ALTER TABLE statement in SQL is used to modify the structure of an existing table. Here's
a breakdown of its syntax and examples for common operations:
🧱 Basic Syntax
ALTER TABLE table_name
[ADD | DROP | MODIFY | RENAME COLUMN | RENAME TO] column_name datatype;
🧪 Examples of ALTER TABLE Usage
➕ Add a Column
ALTER TABLE employees ADD email VARCHAR2(100);
Adds a new column email to the employees table.
🧹 Drop a Column
ALTER TABLE employees DROP COLUMN email;
Removes the email column from the table.
✏️Modify a Column's Data Type
ALTER TABLE employees MODIFY salary NUMBER(10,2);
Changes the salary column to allow decimal values.
🔄 Rename a Column
ALTER TABLE employees RENAME COLUMN name TO full_name;
Renames the name column to full_name.
🧾 Rename the Table
ALTER TABLE employees RENAME TO staff;
Changes the table name from employees to staff.
Add a Constraint
ALTER TABLE employees ADD CONSTRAINT emp_email_unique UNIQUE (email);
Adds a unique constraint to the email column.
🧨 Drop a Constraint
ALTER TABLE employees DROP CONSTRAINT emp_email_unique;
Removes the previously added constraint.
ALTER TABLE employees ADD department_id NUMBER;
Drop a Table
DROP TABLE employees;
🚫 Truncate a Table
TRUNCATE TABLE employees;
🔄 Rename a Table
RENAME employees TO staff;
Absolutely! The CREATE statement in SQL is used to define new database objects, most
commonly tables. Here's a detailed breakdown with examples to help you understand how it
works:
🧱 What Is the SQL CREATE TABLE Statement?
It creates a new table in the database.
You define the table name, columns, and data types.
You can also add constraints like PRIMARY KEY, NOT NULL, UNIQUE, etc.
🧪 Basic Syntax
CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint,
...
);
📌 Example: Creating a Simple Table
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
salary DECIMAL(10, 2),
hire_date DATE
);
Explanation:
id: Integer type, primary key (must be unique and not null).
name: Variable character string up to 50 characters, cannot be null.
email: Unique constraint ensures no duplicate emails.
salary: Decimal with 10 digits total, 2 after the decimal.
hire_date: Stores date values.
🔐 Adding Constraints
Constraint Purpose Example
PRIMARY KEY Uniquely identifies each row id INT PRIMARY KEY
NOT NULL Prevents null values name VARCHAR(50) NOT NULL
UNIQUE Ensures all values are different email VARCHAR(100) UNIQUE
DEFAULT Sets a default value status VARCHAR(10) DEFAULT 'active'
Validates data based on a
CHECK salary DECIMAL CHECK (salary > 0)
condition
Links to another table’s primary FOREIGN KEY (dept_id) REFERENCES
FOREIGN KEY
key departments(id)
🧬 Advanced Example with Constraints
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE DEFAULT SYSDATE,
amount DECIMAL(8,2) CHECK (amount > 0),
FOREIGN KEY (customer_id) REFERENCES customers(id)
);
SQL constraints are rules applied to table columns to ensure the accuracy, integrity, and
validity of the data. They help prevent invalid data entry and enforce business logic directly at
the database level.
🧱 Types of SQL Constraints with Examples
Constraint Purpose Example
Ensures a column cannot have NULL
NOT NULL name VARCHAR(50) NOT NULL
values
Ensures all values in a column are
UNIQUE email VARCHAR(100) UNIQUE
unique
PRIMARY Combines NOT NULL and UNIQUE to
id INT PRIMARY KEY
KEY identify rows
FOREIGN Links a column to another table’s FOREIGN KEY (dept_id) REFERENCES
KEY primary key departments(id)
CHECK Validates data based on a condition salary DECIMAL CHECK (salary > 0)
DEFAULT Sets a default value if none is provided status VARCHAR(10) DEFAULT 'active'
🧪 Full Table Example
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
salary DECIMAL(10,2) CHECK (salary > 0),
department_id INT,
status VARCHAR(10) DEFAULT 'active',
FOREIGN KEY (department_id) REFERENCES departments(id)
);
This table:
Requires name to be present.
Ensures email is unique.
Uses id as the primary key.
Validates that salary is positive.
Links department_id to another table.
Defaults status to 'active' if not provided.
The terms row constraints and table constraints are often confused, but in SQL, the more
accurate distinction is between column-level constraints and table-level constraints. Here's
how they differ:
🧩 Column-Level Constraints (Sometimes called "Row Constraints")
Applied directly within a column definition.
Affect only that specific column.
Examples: NOT NULL, UNIQUE, CHECK, DEFAULT.
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
salary DECIMAL(10,2) CHECK (salary > 0)
);
In this example:
NOT NULL and CHECK are column-level constraints.
They apply to individual columns (name, salary).
🧱 Table-Level Constraints
Defined after all column definitions.
Can apply to multiple columns or define relationships between them.
Used for constraints like PRIMARY KEY, FOREIGN KEY, CHECK across columns.
CREATE TABLE orders (
order_id INT,
product_id INT,
quantity INT,
price DECIMAL(10,2),
CONSTRAINT pk_order PRIMARY KEY (order_id, product_id),
CONSTRAINT chk_quantity_price CHECK (quantity > 0 AND price > 0)
);
In this example:
PRIMARY KEY spans two columns.
CHECK validates a condition involving multiple columns.
🔍 Key Differences
Feature Column-Level Constraint Table-Level Constraint
Scope One column One or more columns
Placement Inside column definition After all column definitions
Use Case Simple rules per column Complex rules across columns
Examples NOT NULL, DEFAULT, CHECK PRIMARY KEY, FOREIGN KEY, CHECK
🧪 Creating a Table from Another Table
CREATE TABLE all_orders AS
SELECT * FROM orders;
This creates a new table all_orders with all data from orders table.
CREATE TABLE recent_orders AS
SELECT * FROM orders WHERE order_date > SYSDATE - 30;
This creates a new table recent_orders with data from the last 30 days.
