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
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.
