Create Table In Oracle

CREATING TABLE IN ORACLE DATABASE: -  Table is the combination of rows and columns, it is object of user or schema. In database the data is stored in the form of Tables.

To create table in Oracle: -

Syntax: -

CREATE TABLE <tablename>

( colname datatype (size),

colname datatype (size),

---------------------);

Rules that are followed while creating a table: -

○ Table name should start only with Alphabets, and the name of the table should not contain spaces and special symbols or characters except _, $, # these three can be  used in table names.

Examples for table name:-

Emp 123 (Invalid)

Emp*123 (Invalid)

Emp_123 (Valid)

Emp$123 (Valid)

Emp#123 (Valid)

○ Table name can be maximum of 30 characters, a table can have maximum of 1000 columns and number of rows are unlimited.

Example: -

Creating a table with following structure: -

Table name EMP

EMPNO

ENAME

JOB

SAL

HIREDATE

SQL>

CREATE TABLE EMPL

(EMPNO NUMBER (4),

ENAME VARCHAR2 (10),

JOB VARCHAR2 (9),

SAL NUMBER (7, 2),

HIREDATE DATE);

Above command creates table structure/ definition of table/ metadata that includes column names, data type and size.

DESCRIBE COMMAND: - It is a command used to see the table definition or structure or metadata. It is SQL PLUS command.

 DIFFERENCE BETWEEN SQL COMMANDS AND SQL PLUS COMMANDS

SQL COMMANDS	SQL PLUS COMMANDS
These are used to perform operations over database.	These are used to set the environment
It must be terminated with ‘;’.	‘;’ is not required.
These commands cannot be Abbreviated.	These can be Abbreviated.

Syntax for Describe command: -

DESC  <tablename>

Table and it's Defintion

INSERTING DATA INTO TABLE: -

“INSERT” command is used to insert data into table.

Syntax: -

INSERT INTO <tablename>

VALUES(V1, V2, V3, V4, ……….);

Strings and date’s must be enclosed in single quotes ‘’

Example: -

INSERT INTO EMPL

VALUES (100, ‘SACHIN’, ‘MANAGER’, 10000, ’26-AUG-16’);

Inserted Rows

INSERT INTO EMPL

VALUES (101, ‘SHRI’, ‘ACCOUNTANT’, 8000, SYSDATE);

In the above row we wrote sysdate instead of date, with that function we will get the system date and that sysdate should not be mentioned in single quotes ‘’. 

Row with Sysdate

If we place sysdate in single quotes then oracle consider it as string and it will display’s the text as sysdate.