CREATE and INSERT CommandS in database

CREATE(DDL Command)

Create is a DDL(data definition language) command.

The CREATE TABLE command defines each column of th table uniquely.Each column has a minimum of three attributes-name, datatype, size.Each column definition is separated from the other by a comma.

Rules for Creating Table:-

•  A name can have maximum upto 30 characters.
• Reserved keywords are not allowed
• Name should begin with an alphabet.

Basic Syntax to create tables:-

CREATE
( columnname1 datatype(size)

 columnname2 datatype(size)

.

.

.

 columnnamen datatype(size)

);

Note:A table can have at most 1000 columns not more than that.
eg:-
CREATE Table studentInfo(

sname varchar2(10),
srollno number(10),

scity  varchar(10));

Different ways of creating table:-

•  First way is to creating a new table using above syntax
•  Second way is to creating a table with already existing structure of another table.

Syntax:-
CREATE  table tablename as 

 (SELECT * FROM  existingtable)
WHERE 1=2 ;
unsatisfied conditions like 1=2, name=city

If you specify the condition which is true then content will be copied to new table.

eg:
 CREATE Table sudentDetails as (SELECT * FROM studentInfo WHERE 1=2);

INSERT (DML COMMAND)

 Once Table is created the most thing to do is load this table with data.
This command allows us to insert the values in to the specified column or all the columns in to the tables.

Syntax:-
INSERT into tablename(column list)

values(values);

While entering the valus into char,varchar, data datatypes of columns then corresponding value must be taken in single quotation marks.
If you want to insert all values into table column names are not required.

eg:

SQL> INSERT into studentInfo(sname,srollno,scity)
values ('student1',12345,'newyork');
SQL>INSERT into studentInfo(sname)

values('student2');

SQL>INSERT into studentInfo

values('student3',12346,'srilanka');

Inserting of a record into table can be done in two ways/

1. static 
2. dyanamic

Above are the examples of static approach where we can insert only one record at a time and to insert n record we need to write n insert statement.To overcome this problem dynamic approach can be used.

see below commands:-
SQL>INSERT INTO studentInfo

VALUES('&SNAME',&ROLLNO,'&SCITY');

Enter values for SNAME:################
Enter values for ROLLNO:##########

Enter values for SCITY:##################

Record inserted.
SQL>/
"/" is used to execute the command which is recently executed and saved in the buffer memory.

Hope u like it......!

10:55