Oracle_Create Table

admin Oracle

Hello guys,
In this blog, we will discuss different ways of creating a table in Oracle database.
The first question is, what is a table?
A table is an object in the database which actually holds the data in the form of rows and columns.
So whenever we talk about data, we actually talk about tables exist in the database software.

To create a table in your own schema (we will talk about schema in detail in up-coming blogs, here you can understand it as a user account in the database) you must have CREATE TABLE system privilege. Also, if you want to create a table in another schema, you must have CREATE ANY TABLE system privilege. Also, the owner of the schema must have space quota to contain the table data on the “default tablespace” which was assigned while creating user account (schema) in the Oracle database.
Let me know if you want me to write a separate detailed blog about Tablespace and Datafile.

Syntax:
CREATE TABLE schema.table_name (
column_name datatype (data width),
column_name2 datatype (data width)
);

Note: you can omit schema if you are creating table in your own schema. If you want to create a table another user’s schema, mentioned that schema.

Examples:
A. Creating a table in your own schema-

CREATE TABLE emp (
FIRST_NAME varchar2 (20),
LAST_NAME varchar2 (20),
EMAIL varchar2 (50));

B. If you want to create a table in dbgeek’s schema than you need to mentioned dbgeek in the query before the table name.

CREATE TABLE dbgeek.emp (
FIRST_NAME varchar2 (20),
LAST_NAME varchar2 (20),
EMAIL varchar2 (50));

Let’s do some more examples and see how easy it is.

Creating a new table with columns from existing table (Oracle database )
For the demonstration purpose, I have already created a table EMP with some data in it. I will use this table as existing table in the following examples.

1) With all columns from existing table with data


CREATE TABLE emp2 --new table
AS
SELECT
* FROM
emp;--existing table

Output…
Table EMP2 created.

Using the above query, you can create new table emp2 with same columns and data in table emp.
As we have not mentioned column name, data type and data width in the Create Table statement so the columns and their data type will be same what emp table has. So, we can say that emp2 table will have the same columns and data returned by SELECT statement after AS keyword.

2) With all columns from existing table without data


CREATE TABLE emp3 --new table
AS
SELECT
* FROM
emp where 1=2;--existing table


Output…
Table EMP3 created.

In the above query, I added a condition in the where clause that is considered False all the time. 1 can never be equal to 2. It means Select query will return 0 rows.
Though emp3 table has been created with the columns but there are no rows as SELECT statement returns 0 rows.


SELECT
* FROM
emp where 1=2;

Output…
no rows selected

3) With selected columns from existing table with data.


CREATE TABLE emp4 --new table
AS
SELECT
First_name,
Last_name
FROM
emp;--existing table


Output…
Table EMP4 created.

I assume that the output of above query is clear to you.
Table Emp3 is created with First_Name and Last_Name columns with data records in these columns. You can run the select statement after the AS keyword first and then a select statement to emp3 table. The output will be same.

4) With selected columns from existing table without data.



CREATE TABLE emp5 --new table
AS
SELECT
First_name,
Last_name
FROM
emp where 1=2;--existing table


Output…
Table EMP5 created.

Do I still need to explain above query?

If you have any questions please comment below or drop me an email.

If you like this blog, do share with your friends and colleagues on social media.
For more updates join my facebook group and do like my facebook page.

Thank you,
Kapil

Sharing is caring!

You May Also Like..

Oracle Set Operators

Hello guys, Hope you are doing well. Today, we will talk about Oracle Operators. An operator manipulates individual data items […]

Oracle_Insert into

Hello guys, In one of our previous blogs, we have learned how to create a table in Oracle database. Do […]

Oracle SQL_CASE Function

Hello guys, Hope you are doing well. I am back once again with a detailed blog on Oracle CASE function. […]

Leave a Reply

Your email address will not be published. Required fields are marked *