Showing How CREATE TABLE Statement Works

Example 1: Let’s create a simple table called “employees” with columns for employee ID, name and salary.

-- Create table statement
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR2(50),
    emp_salary NUMBER(10, 2)
);

Output:

Explanation: This “CREATE TABLE” statement creates the ’employees’ table with three columns: ’emp_id’ of type INT as the primary key, ’emp_name’ of type VARCHAR2(50), and ’emp_salary’ of type NUMBER(10, 2). Also the table created is empty as it does not have any values inserted in its coulmns. we will learn about adding the values in columns when we see the next example.

Example 2: Creating a Table and Adding Values to It

Let’s create another table called “products” with columns for product ID, name, and price, along with a foreign key constraint referencing the ‘category_id‘ column in a ‘categories table’.

-- Create table statement
CREATE TABLE products (
    prod_id INT PRIMARY KEY,
    prod_name VARCHAR2(100),
    prod_price NUMBER(10, 2),
    category_id INT,
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
);
INSERT INTO products (prod_id, prod_name, prod_price, category_id)
VALUES (1, 'Pen', 20.00, 101);

INSERT INTO products (prod_id, prod_name, prod_price, category_id)
VALUES (2, 'paper', 15.00, 102);

INSERT INTO products (prod_id, prod_name, prod_price, category_id)
VALUES (3, 'Notebook', 30.00, 104);

Output:

Explanation: In this example, the “products” table is created with four columns: ‘prod_id’, ‘prod_name’, ‘prod_price’, and ‘category_id’. The ‘prod_id’ column serves as the primary key, and the ‘category_id’ column is specified as a foreign key referencing the ‘category_id’ column in a hypothetical categories table.

Example 3: Creating a Table Named ‘Students’

Let’s create another simple table named “students” with three columns: “student_id”, “student_name”, and “age”. We will then insert some sample data into the table and see how it works.

-- Create table statement
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR2(50),
    age INT
);

-- Inserting sample data
INSERT INTO students (student_id, student_name, age) VALUES (1, 'Alex', 22);
INSERT INTO students (student_id, student_name, age) VALUES (2, 'Becky', 20);
INSERT INTO students (student_id, student_name, age) VALUES (3, 'Charles', 21);

Output:

Explanation: Here, after executing the above SQL statements, the “students” table is created with three columns named ‘student_id’ , student_name’ , and ‘age’.

PL/SQL CREATE TABLE statement is a fundamental aspect of database design and allows users to define the structure of new tables, including columns, data types and constraints. This statement is crucial in organizing data effectively within a database and providing a blueprint for how data should be structured.

In this article, we will explore the syntax and examples of using the CREATE TABLE statement to create and manage tables in a PL/SQL environment.