PL/SQL CREATE TABLE Statement
Last Updated : 18 Oct, 2024
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.
CREATE TABLE Statement in PL/SQL
The PL/SQL CREATE TABLE statement is essential for database design. It allows us to define the layout of new tables, including columns, data types, and constraints. This statement is fundamental for organizing data effectively in a database and helping us to specify how our data should be structured.
Syntax
The basic syntax for creating a table is as follows:
CREATE TABLE table_name ( column1 datatype [constraint], column2 datatype [constraint], ... columnN datatype [constraint] );
- table_name: The name of the table.
- datatype: Data type for each column (e.g., VARCHAR2, INT, NUMBER).
- constraint: Optional, defines rules for the data (e.g., PRIMARY KEY, NOT NULL)
This statement will create a new table containing rows and columns with different datatypes and constraints if required.
Examples: How to Use CREATE TABLE Statement
Example 1: Creating a Basic Table
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:
prod_id | prod_name | prod_price | category_id |
|---|
1 | Pen | 20.00 | 101 |
2 | paper | 15.00 | 102 |
3 | Notebook | 30.00 | 104 |
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:
student_id | student_name | age |
|---|
1 | Alex | 22 |
2 | Becky | 20 |
3 | Charles | 21 |
Explanation: Here, after executing the above SQL statements, the "students" table is created with three columns named 'student_id' , student_name' , and 'age'.
Important Points About PL/SQL CREATE TABLE Statement
- Ensure the data types match the intended data, especially when using constraints like FOREIGN KEY referencing another table's column.
- Table and column names in Oracle are not case-sensitive unless quoted.
- Avoid using SQL reserved words as column or table names to prevent errors.
- Remember to specify GLOBAL TEMPORARY if the table is meant for temporary data.
Similar Reads
SQL CREATE VIEW Statement
The SQL CREATE VIEW statement is a very powerful feature in RDBMSs that allows users to create virtual tables based on the result set of a SQL query. Unlike regular tables, these views do not store data themselves rather they provide a way of dynamically retrieving and presenting data from one or ma
4 min read
PL/SQL CASE Statement
PL/SQL stands for Procedural Language Extension to the Structured Query Language and it is designed specifically for Oracle databases it extends Structured Query Language (SQL) capabilities by allowing the creation of stored procedures, functions, and triggers. The PL/SQL CASE statement is a powerfu
4 min read
MySQL DROP TABLE Statement
MySQL is a free and open-source relational database management system written in C and C++ that is extremely popular among developers. Like other relational database management systems, MySQL provides various rich features to create databases and tables, insert data in them, and further manipulate t
6 min read
MySQL CREATE VIEW Statement
MySQL, an open-source relational database management system, offers a variety of features to manage and manipulate data efficiently. One of these features is the CREATE VIEW statement, which allows you to create a virtual table known as a view. A view provides a way to simplify complex queries, enha
5 min read
PL/SQL DELETE Statement
In PL/SQL(Procedural Language/Structured Query Language), the DELETE statement is the powerful command used to remove one or more records from the database table. It is an essential part of database management and enables the users to efficiently manage and maintain the data integrity by selectively
7 min read
SQL CREATE INDEX Statement
In SQL, indexes are important for optimizing query performance by speeding up data retrieval operations. The CREATE INDEX statement is used to create indexes in tables, enabling quicker searches and improving database efficiency. In this article, we will explain how to use the SQL CREATE INDEX State
5 min read
PL/SQL INSERT Statement
The PL/SQL INSERT statement is vital for adding new records to a database table. By specifying the table's name and providing values for its columns, users can populate their database with essential information. This functionality enables efficient data entry and ensures the completeness of datasets
3 min read
Python SQLite - Create Table
In this article, we will discuss how can we create tables in the SQLite database from the Python program using the sqlite3 module. In SQLite database we use the following syntax to create a table: CREATE TABLE database_name.table_name(                     column1 datatype PRIMARY
2 min read
PL/SQL UPDATE Statement
The UPDATE statement in the PL/SQL(Procedural Language/ Structural Query Language) is the powerful SQL (Structured Query Language) command used to modify the existing data in the database table. In this article, we will explain the PL/SQL UPDATE Statement, its syntax, and examples in detail. PL/SQL
7 min read
SQL CASE Statement
The CASE statement in SQL is a versatile conditional expression that enables us to incorporate conditional logic directly within our queries. It allows you to return specific results based on certain conditions, enabling dynamic query outputs. Whether you need to create new columns, modify existing
4 min read