USING DML
USING UPDATE
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 will update entire table.
SQL> update student set marks
= 500 where no = 2;
SQL> update student set marks
= 500, name = 'Venu' where no = 1;
USING DELETE
This can be used to delete the table data temporarily.
Syntax:
Delete <table_name> where <condition>;
Ex:
SQL> delete student;
If you are not
specifying any condition this will delete entire table.
SQL> delete student where no =
2;
USING DDL
USING ALTER
This can be used to add or remove columns and to modify the
precision of the datatype.
a) ADDING COLUMN
Syntax:
alter table <table_name> add <col datatype>;
Ex:
SQL> alter table student add
sdob date;
b) REMOVING COLUMN
Syntax:
alter table <table_name> drop <col datatype>;
Ex:
SQL> alter table student drop
column sdob;
c) INCREASING OR DECREASING PRECISION OF A COLUMN
Syntax:
alter table <table_name> modify <col
datatype>;
Ex:
SQL> alter table student
modify marks number(5);
* To decrease precision
the column should be empty.
d) MAKING COLUMN UNUSED
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.
d) DROPPING UNUSED COLUMNS
Syntax:
alter table <table_name> drop unused columns;
Ex:
SQL> alter table student drop
unused columns;
* You can not drop
individual unused columns of a table.
e) RENAMING COLUMN
Syntax:
alter table <table_name> rename column <old_col_name> to <new_col_name>;
Ex:
SQL> alter table student rename
column marks to smarks;
USING TRUNCATE
This can be used to delete the entire table data permanently.
Syntax:
truncate table <table_name>;
Ex:
SQL> truncate table student;
USING DROP
This will be used to drop the database object;
Syntax:
Drop table <table_name>;
Ex:
SQL> drop table student;
USING RENAME
This will be used to rename the database object;
Syntax:
rename <old_table_name> to <new_table_name>;
Ex:
SQL> rename student to stud;
USING TCL
USING COMMIT
This will be used to save the work.
Commit is of two types.
Ø Implicit
Ø Explicit
a) IMPLICIT
This will be issued
by oracle internally in two situations.
Ø When any DDL operation
is performed.
Ø When you are exiting
from SQL * PLUS.
b) EXPLICIT
This will be issued
by the user.
Syntax:
Commit or commit work;
* When ever
you committed then the transaction was completed.
USING ROLLBACK
This will undo the operation.
This will be applied in two methods.
Ø Upto previous commit
Ø Upto previous rollback
Syntax:
Roll or roll work;
Or
Rollback or rollback
work;
* While process is going on, if suddenly power goes then oracle
will rollback the transaction.
USING SAVEPOINT
You can use savepoints to rollback portions of your current set
of transactions.
Syntax:
Savepoint <savepoint_name>;
Ex:
SQL> savepoint s1;
SQL> insert into student
values(1, ‘a’, 100);
SQL> savepoint s2;
SQL> insert into student
values(2, ‘b’, 200);
SQL> savepoint s3;
SQL> insert into student
values(3, ‘c’, 300);
SQL> savepoint s4;
SQL> insert into student
values(4, ‘d’, 400);
Before rollback
SQL> select * from student;
NO NAME MARKS
---
------- ----------
1 a 100
2 b 200
3 c 300
4 d 400
SQL> rollback to savepoint s3;
Or
SQL> rollback to s3;
This will rollback
last two records.
SQL> select * from student;
NO NAME MARKS
---
------- ----------
1 a 100
2 b 200
USING DCL
DCL
commands are used to granting and revoking the permissions.
USING
GRANT
This is used to grant the privileges to other users.
Syntax:
Grant <privileges> on <object_name> to <user_name> [with grant option];
Ex:
SQL> grant select on student
to sudha; -- you can give
individual privilege
SQL> grant select, insert on
student to sudha; -- you can give set
of privileges
SQL> grant all on student to
sudha; -- you can give all privileges
The sudha user has to
use dot method to access the object.
SQL> select * from
saketh.student;
The sudha user can not
grant permission on student table to other users. To get this type of
option use the
following.
SQL> grant all on student to sudha with grant option;
Now sudha user also
grant permissions on student table.
USING REVOKE
This is used to revoke the privileges from the users to which
you granted the privileges.
Syntax:
Revoke <privileges> on <object_name> from <user_name>;
Ex:
SQL> revoke select on student
form sudha; -- you can revoke
individual privilege
SQL> revoke select, insert on
student from sudha; -- you can revoke set of
privileges
SQL> revoke all on student
from sudha; -- you can revoke all
privileges
USING ALIASES
CREATE
WITH SELECT
We can create a table using existing table [along with data].
Syntax:
Create table <new_table_name> [col1,
col2, col3 ... coln] as select * from
<old_table_name>;
Ex:
SQL> create table student1 as
select * from student;
Creating table with
your own column names.
SQL> create table
student2(sno, sname, smarks) as select * from student;
Creating table with
specified columns.
SQL> create table student3 as
select no,name from student;
Creating table with out
table data.
SQL> create table
student2(sno, sname, smarks) as select * from student where 1 = 2;
In the above where
clause give any condition which does not satisfy.
INSERT WITH SELECT
Using this we can insert existing table data to a another table
in a single trip. But the table structure should be same.
Syntax:
Insert into <table1> select * from <table2>;
Ex:
SQL> insert into student1
select * from student;
Inserting data into
specified columns
SQL> insert into student1(no,
name) select no, name from student;
COLUMN ALIASES
Syntax:
Select <orginal_col>
<alias_name> from <table_name>;
Ex:
SQL> select no sno from
student;
or
SQL> select no “sno” from
student;
TABLE ALIASES
If you are using table aliases you can use dot method to the
columns.
Syntax:
Select <alias_name>.<col1>, <alias_name>.<col2> … <alias_name>.<coln>
from
<table_name> <alias_name>;
Ex:
SQL> select s.no, s.name from
student s;
USING MERGE
MERGE
You
can use merge command to perform insert and update in a single command.
Ex:
SQL> Merge into student1 s1
Using (select *From student2) s2
On(s1.no=s2.no)
When matched then
Update set marks = s2.marks
When not matched then
Insert (s1.no,s1.name,s1.marks)
Values(s2.no,s2.name,s2.marks);
In the above the two
tables are with the same structure but we can merge different structured
tables also but the
datatype of the columns should match.
Assume that student1 has
columns like no,name,marks and student2 has columns like no,
name, hno, city.
SQL> Merge into student1 s1
Using (select *From student2) s2
On(s1.no=s2.no)
When matched then
Update set marks = s2.hno
When not matched then
Insert (s1.no,s1.name,s1.marks)
Values(s2.no,s2.name,s2.hno);
MULTIBLE INSERTS
We have table called
DEPT with the following columns and data
DEPTNO DNAME LOC
-------- -------- ----
10 accounting new york
20 research dallas
30 sales Chicago
40 operations boston
a) CREATE STUDENT TABLE
SQL> Create table student(no number(2),name varchar(2),marks
number(3));
b) MULTI INSERT WITH ALL FIELDS
SQL> Insert all
Into student values(1,’a’,100)
Into student values(2,’b’,200)
Into student values(3,’c’,300)
Select *from dept where deptno=10;
-- This inserts 3 rows
c) MULTI INSERT WITH SPECIFIED
FIELDS
SQL> insert all
Into student (no,name)
values(4,’d’)
Into student(name,marks)
values(’e’,400)
Into student values(3,’c’,300)
Select *from dept where deptno=10;
-- This inserts 3 rows
d) MULTI INSERT WITH DUPLICATE
ROWS
SQL> insert all
Into student values(1,’a’,100)
Into student values(2,’b’,200)
Into student values(3,’c’,300)
Select *from dept where deptno
> 10;
-- This inserts 9 rows because in the
select statement retrieves 3 records (3 inserts for each
row retrieved)
e) MULTI INSERT WITH CONDITIONS
BASED
SQL> Insert all
When deptno > 10 then
Into student1 values(1,’a’,100)
When dname = ‘SALES’ then
Into student2 values(2,’b’,200)
When loc = ‘NEW YORK’ then
Into student3 values(3,’c’,300)
Select *from dept where
deptno>10;
-- This
inserts 4 rows because the first condition satisfied 3 times, second
condition
satisfied once and the last none.
f) MULTI INSERT WITH CONDITIONS
BASED AND ELSE
SQL> Insert all
When deptno > 100 then
Into student1 values(1,’a’,100)
When dname = ‘S’ then
Into student2 values(2,’b’,200)
When loc = ‘NEW YORK’ then
Into student3 values(3,’c’,300)
Else
Into student values(4,’d’,400)
Select *from dept where
deptno>10;
-- This inserts 3 records because the else
satisfied 3 times
g) MULTI INSERT WITH CONDITIONS
BASED AND FIRST
SQL> Insert first
When deptno = 20 then
Into student1 values(1,’a’,100)
When dname = ‘RESEARCH’ then
Into student2 values(2,’b’,200)
When loc = ‘NEW YORK’ then
Into student3 values(3,’c’,300)
Select *from dept where deptno=20;
-- This inserts 1 record because the first
clause avoid to check the remaining conditions
once the condition is satisfied.
h) MULTI INSERT WITH CONDITIONS
BASED, FIRST AND ELSE
SQL> Insert first
When deptno = 30 then
Into student1 values(1,’a’,100)
When dname = ‘R’ then
Into student2 values(2,’b’,200)
When loc = ‘NEW YORK’ then
Into student3 values(3,’c’,300)
Else
Into student values(4,’d’,400)
Select *from dept where
deptno=20;
-- This inserts 1 record because the else
clause satisfied once
i) MULTI INSERT WITH MULTIBLE
TABLES
SQL> Insert all
Into student1 values(1,’a’,100)
Into student2 values(2,’b’,200)
Into student3 values(3,’c’,300)
Select *from dept where deptno=10;
-- This inserts 3 rows
** You can use multi tables with specified
fields, with duplicate rows, with conditions, with
first and else clauses.
Check out more about sql commands here
ReplyDelete