ORACLE SQL

SQL TABLE

CREATE TABLE
SYNTAX	Create table <table_name> (col1 datatype1, col2 datatype2 …coln datatypen);
EX	SQL> create table student (no number (2), name varchar (10), marks number (3));

INSERT INTO TABLE:

This will be used to insert the records into table. We have two methods to insert.

By value method
By address method

USING VALUE METHOD
Syntax:	insert into <table_name> values (value1, value2, value3 …. Valuen);
Ex:	SQL> insert into student values (1, ’sudha’, 100);
INSERTING DATA INTO SPECIFIED COLUMNS USING VALUE METHOD
Syntax:	insert into <table_name)(col1, col2, col3 … Coln) values (value1, value2, value3 ….Valuen);
Ex:	SQL> insert into student (no, name) values (3, ’Ramesh’); SQL> insert into student (no, name) values (4, ’Madhu’);

USING ADDRESS METHOD (To insert a new record again you have to type entire insert command, if there are lot of records this will be difficult.This will be avoided by using address method)
Syntax:	insert into <table_name) values (&col1, &col2, &col3 …. &coln);
	This will prompt you for the values but for every insert you have to use forward slash
Ex:	SQL> insert into student values (&no, ‘&name’, &marks); Enter value for no: 1 Enter value for name: Jagan Enter value for marks: 300 old 1: insert into student values(&no, ‘&name’, &marks) new 1: insert into student values(1, ‘Jagan’, 300) SQL> / Enter value for no: 2 Enter value for name: Naren Enter value for marks: 400 old 1: insert into student values(&no, ‘&name’, &marks) new 1: insert into student values(2, ‘Naren’, 400)
*INSERTING DATA INTO SPECIFIED COLUMNS USING ADDRESS METHOD*
Syntax:	insert into <table_name)(col1, col2, col3 … coln) values (&col1, &col2 ….&coln);
	This will prompt you for the values but for every insert you have to use forward slash.
	SQL> insert into student (no, name) values (&no, ‘&name’); Enter value for no: 5 Enter value for name: Visu old 1: insert into student (no, name) values(&no, ‘&name’) new 1: insert into student (no, name) values(5, ‘Visu’) SQL> / Enter value for no: 6 Enter value for name: Rattu old 1: insert into student (no, name) values(&no, ‘&name’) new 1: insert into student (no, name) values(6, ‘Rattu’)

USING UPDATE
USING DELETE
USING ALTER
ADDING COLUMN

(This can be used to modify the table data)
Syntax	Update <table_name> set <col1> = value1, <col2> = value2 where <condition>;
Ex	SQL> update student set marks = 500;
	If you are not specifying any condition this update entire table will. SQL> update student set marks = 500 where no = 2; SQL> update student set marks = 500, name = ‘Venu’ where no = 1;

(This can be used to delete the table data temporarily)
Syntax	Delete <table_name> where <condition>;
	SQL> delete student;
	If you are not specifying any condition this will delete entire table. SQL> delete student where no = 2;

This can be used to add or remove columns and to modify the precision of the datatype

Syntax:	alter table <table_name> add <col datatype>;
EX:	SQL> alter table student add sdob date;

REMOVING COLUMN
INCREASING OR DECREASING PRECISION OF A COLUMN
MAKING COLUMN UNUSED
DROPPING UNUSED COLUMNS

Syntax:	alter table <table_name> drop <col datatype>;
EX:	SQL> alter table student drop column sdob;

Syntax:	alter table <table_name> modify <col datatype>;
EX:	SQL> alter table student modify marks number(5);

Syntax:

alter table <table_name> set unused column <col>;

EX:

SQL> alter table student set unused column marks;

(Even though the column is unused still it will occupy memory)

Syntax:

alter table <table_name> drop unused columns;

EX:

SQL> alter table student drop unused columns;

(You cannot drop individual unused columns of a table)

RENAMING COLUMN
USING TRUNCATE
USING DROP
USING RENAME

Syntax:	alter table <table_name> rename column <old_col_name> to <new_col_name>;
EX:	SQL> alter table student rename column marks to smarks;

Syntax:	truncate table <table_name>;
Ex:	SQL> truncate table student;

Syntax:	Drop table <table_name>;
Ex:	SQL> drop table student;

Syntax:	rename <old_table_name> to <new_table_name>;
Ex:	SQL> rename student to stud;

ORACLE SQL

SQL TABLE

CREATE TABLE

SYNTAX

EX

INSERT INTO TABLE:

USING UPDATE

USING DELETE

USING ALTER

ADDING COLUMN

REMOVING COLUMN

INCREASING OR DECREASING PRECISION OF A COLUMN

MAKING COLUMN UNUSED

DROPPING UNUSED COLUMNS

RENAMING COLUMN

USING TRUNCATE

USING DROP

USING RENAME

Questions

CONTACT US

We are social:

Visitor Counter