Create Table ( CTAS ) and Data Types, LOB Data Types in Oracle SQL | Oracle SQL Tutorials -27

Hi,

I will explain Create Table ( CTAS ) and Data Types in Oracle SQL in this post of Oracle SQL Tutorial series.

Read the previous post of this tutorial series before this.

SQL Subquery, Nested Queries and In Any All Operator in Subquery in Oracle SQL | Oracle SQL Tutorials -26

Create Table

Table is a database object that is used to store the data. Table is composed of the rows and columns.

You can use the “A–Z, a–z, 0–9, _, $, #” characters in table naming.

You can not use the same table name under the same Schema.

Don’t use the Oracle reserved keyword in table naming like create,alter,drop,select and etc…

You can create the table as follows.

Create table syntax

Create table syntax is as follows.

CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
columnN data_type(size)
);

Let’s make an example about table creation in Oracle SQL as follows.

Normally, there is no MEHMET table as follows and I will create and check it.

SQL> desc MEHMET
ERROR:
ORA-04043: object mehmet does not exist


SQL> CREATE TABLE MEHMET
  2  (
  3  UserID int,
  4  FirstName varchar(100), 
  5  LastName varchar(100),
  6  City varchar(100)
  7  ); 

Table created.

SQL> 
SQL> desc MEHMET
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USERID                                             NUMBER(38)
 FIRSTNAME                                          VARCHAR2(100)
 LASTNAME                                           VARCHAR2(100)
 CITY                                               VARCHAR2(100)

SQL>

If you don’t specify the Schema before table name, then this table is created under SYSTEM Schema automatically.

If you want to create the table under the specific Schema, then create it as follows.

SQL> CREATE TABLE MSDEVECI.MYEMPLOYEE
   (
     EMPLOYEE_ID NUMBER(6),
     FIRST_NAME VARCHAR2(20 BYTE),
     LAST_NAME VARCHAR2(25 BYTE),
     HIRE_DATE DATE,
     JOB_ID VARCHAR2(10 BYTE),
     SALARY NUMBER(8,2)
   );

Table created.

SQL> DESC MSDEVECI.MYEMPLOYEE
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID                                        NUMBER(6)
 FIRST_NAME                                         VARCHAR2(20)
 LAST_NAME                                          VARCHAR2(25)
 HIRE_DATE                                          DATE
 JOB_ID                                             VARCHAR2(10)
 SALARY                                             NUMBER(8,2)

SQL> desc MYEMPLOYEE
ERROR:
ORA-04043: object MYEMPLOYEE does not exist


SQL>

CTAS ( Create table as Select * from )

You can create any table from existing table using CTAS ( Create Table  As Select * from) method.

Let’s make an example about CTAS method. Create the msdeveci.jobs table from hr.jobs as follows.

SQL> 
SQL> desc msdeveci.jobs;
ERROR:
ORA-04043: object msdeveci.jobs does not exist


SQL> create table msdeveci.jobs as select * from hr.jobs;

Table created.

SQL> desc msdeveci.jobs;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 JOB_ID                                             VARCHAR2(10)
 JOB_TITLE                                 NOT NULL VARCHAR2(35)
 MIN_SALARY                                         NUMBER(6)
 MAX_SALARY                                         NUMBER(6)

SQL> 

SQL> select count(*) from msdeveci.jobs;

  COUNT(*)
----------
        20

SQL> 


Create the msdeveci.employees from hr.employees as follows.

SQL> desc msdeveci.employees
ERROR:
ORA-04043: object msdeveci.employees does not exist


SQL> 
SQL> create table msdeveci.employees as select * from hr.employees;

Table created.

SQL> select count(*) from msdeveci.employees;

  COUNT(*)
----------
       106

SQL>

Data Types

This part can be a bit boring data types will be examined briefly.

Numeric: Store the numerical values used in arithmetic operations.

Character: The data type that store a character

Boolean: store either True or False value

Datetime: store the Data type with date and time

Number: numeric data is stored. NUMBER (full decimal).

PLS_INTEGER – BINARY_INTEGER: Unlike Number type, it performs faster arithmetic operations and takes less space.

BINARY_FLOAT – BINARY_DOUBLE: The data type ends with f (5.67f) and ends with d in BINARY_DOUBLE.

VARCHAR2 – NVARCHAR2: Variable is the field in which the alphanumeric or byte data is stored.

TIMESTAMP: stores data of year, day, month, hour, minute and second.

LOB (Large Object) Data Types

Data types are data types that store data that are not of a particular data type (image, image, etc.).

BFILE: Large binary objects that are stored outside the database on the operating system. It cannot exceed 4GB.

BLOB: These are large binary objects that are stored in the database and are 8 to 12TB in size.

CLOB: Character data is in large blogs is 8 to 12TB size.

NCLOB: NLS stores Unicode data of character type. It is 8 to 12TB in size.

Dou want to learn Oracle SQL Tutorial for Beginners, then read the following articles.

Oracle SQL Tutorials For Beginners – Learn Oracle SQL from scratch with Oracle SQL Online Course