Category Archives: SQL

MERGE in SQL

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);

 

Related posts:

Aliases

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;

Related posts:

DCL Grant, Revoke

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

Related posts:

TCL Rollback, Commit

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

Related posts: