➤ EMP DEPT Tables Queries
➤ Set Operators in Oracle
➤ CREATE command
➤ RENAME command
➤ DROP command
➤ Default Value in Column
➤ ABS() in Oracle SQL
➤ CEIL() in Oracle SQL
➤ SYSDATE in Oracle
➤ Trunc Oracle date
➤ TO_CHAR() in Oracle
➤ GROUP BY Clause
➤ Having Clause
➤ ORDER BY Clause
➤ Constraints in Oracle
➤ Rename Constraint
➤ Disable Constraint
➤ Drop Constraint
➤ NOT NULL Constraint
➤ UNIQUE Constraint
➤ PRIMARY KEY Constraint
➤ FOREIGN KEY Constraint
➤ CHECK Constraint
➤ Unlock User in SQL Plus
➤ Find SID in Oracle database
➤ Check Database Version
➤ Check Database Size
➤ Error ORA 01031
Oracle SQL Create Table Example | In Oracle SQL, the CREATE command is used to create database objects like table, view, synonym, index, and e.t.c. To create a new table in the Oracle database we must use the CREATE TABLE statement. Tables contain columns and constraints, rules to which data must conform.
Create Table Query in Oracle
Create table syntax in Oracle:-
CREATE TABLE tablename ( columnname1 datatype(size), columnname2 datatype(size), … );
The create table syntax start with CREATE TABLE followed by table name then specify the column name, its datatype with size (if applicable). For more than one column they should be separated with comma(,). Each valid Oracle SQL statement should end with a semicolon(;).
Oracle Create Table Example
Let us create a table STUDENT with column names SNO and NAME with NUMBER and VARCHAR2 datatypes respectively.
CREATE TABLE student ( sno NUMBER(10), name VARCHAR2(10) );
To view the structure of the table we are using the DESC command. It is a SQL *Plus tool-specific command i.e. only works in the SQL *Plus tool. The DESC command gives information about the table like column names, its data type, and whether the column has the NOT NULL constraint or not.
Name Null? Type ----------- -------- ------------ SNO NUMBER(10) NAME VARCHAR2(10)
To display the table data we must use the SELECT query, it will display all column names and their data.
SELECT * FROM student;
no rows selected
Since we only created the table that’s why it is empty. To insert data into detail we must use the INSERT statement which is discussed in detail in the INSERT statement. One simple example is given here.
INSERT INTO student VALUES(1010, 'OLIVIA'); INSERT INTO student VALUES(1011, 'EMMA'); SELECT * FROM student;
SNO NAME ---------- ---------- 1010 OLIVIA 1011 EMMA
Create a Table in Another Schema
In Order to create a table in the Oracle database, you must have CREATE TABLE system privilege in your schema, and CREATE ANY TABLE system privilege to create a new table in another user’s schema.
Syntax to create table in another user’s schema:-
CREATE TABLE schema_name.tablename ( columnname1 datatype(size), columnname2 datatype(size), ... );
Table with Constraints
We can also apply constraints on the columns of the table, there are five constraints in Oracle that are discussed in detail in the Oracle constraints topic. Tables contain columns and constraints, rules to which data must conform.
Create table syntax with a constraint:-
CREATE TABLE tablename ( columnname1 datatype(size) column_constraint, columnname2 datatype(size) column_constraint, ... table_constraint );
Point to remember while creating a table in the Oracle database:-
- A table can have a minimum of one column.
- The maximum number of columns allowed in an Oracle table:- prior to Oracle 7 it can have a max of 254 columns per table but Oracle 8i, 9i, 10g, 11g, 12c, 18c, 19c can have a maximum of 1000 columns per table.
If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!