INTRODUCTION
SQL
is divided into the following
Ø
Data
Definition Language (DDL)
Ø
Data
Manipulation Language (DML)
Ø
Data
Retrieval Language (DRL)
Ø
Transaction
Control Language (TCL)
Ø
Data
Control Language (DCL)
DDL -- create, alter, drop, truncate, rename
DML -- insert, update, delete
DRL -- select
TCL -- commit, rollback, savepoint
DCL -- grant, revoke
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
This
will be used to insert the records into table.
We
have two methods to insert.
Ø
By
value method
Ø
By
address method
a) USING VALUE METHOD
Syntax:
insert into <table_name) values (value1,
value2, value3 …. Valuen);
Ex:
SQL> insert
into student values (1, ’sudha’, 100);
SQL> insert into student values (2, ’saketh’, 200);
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.
b) 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)
c) 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’);
d) 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.
Ex:
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')
SELECTING
DATA
Syntax:
Select * from <table_name>; --
here * indicates all columns
or
Select col1,
col2, … coln from <table_name>;
Ex:
SQL> select
* from student;
NO NAME MARKS
--- ------ --------
1 Sudha 100
2 Saketh 200
1 Jagan 300
2 Naren
400
3 Ramesh
4 Madhu
5 Visu
6 Rattu
SQL> select
no, name, marks from student;
NO NAME MARKS
--- ------ --------
1 Sudha 100
2 Saketh 200
1 Jagan 300
2 Naren 400
3 Ramesh
4 Madhu
5 Visu
6 Rattu
SQL> select
no, name from student;
NO NAME
--- -------
1 Sudha
2 Saketh
1 Jagan
2 Naren
3 Ramesh
4 Madhu
5 Visu
6 Rattu
CONDITIONAL SELECTIONS AND OPERATORS
We
have two clauses used in this
Ø
Where
Ø
Order
by
USING
WHERE
Syntax:
select * from <table_name>
where <condition>;
the
following are the different types of operators used in where clause.
v
Arithmetic
operators
v
Comparison
operators
v
Logical
operators
v
Arithmetic
operators -- highest precedence
+, -, *, /
v
Comparison
operators
Ø
=,
!=, >, <, >=, <=, <>
Ø
between,
not between
Ø
in,
not in
Ø
null,
not null
Ø
like
v Logical operators
Ø
And
Ø
Or -- lowest
precedence
Ø
not
a) USING =, >, <, >=, <=, !=, <>
Ex:
SQL> select
* from student where no = 2;
NO NAME MARKS
--- ------- ---------
2 Saketh 200
2 Naren 400
SQL> select
* from student where no < 2;
NO NAME MARKS
--- ------- ----------
1 Sudha 100
1 Jagan 300
SQL> select
* from student where no > 2;
NO NAME MARKS
--- ------- ----------
3 Ramesh
4 Madhu
5 Visu
6 Rattu
SQL> select
* from student where no <= 2;
NO NAME MARKS
--- ------- ----------
1 Sudha 100
2 Saketh
200
1 Jagan 300
2 Naren 400
SQL> select
* from student where no >= 2;
NO NAME MARKS
--- ------- ---------
2 Saketh 200
2
Naren 400
3 Ramesh
4 Madhu
5 Visu
6 Rattu
SQL> select
* from student where no != 2;
NO NAME MARKS
--- ------- ----------
1 Sudha
100
1 Jagan 300
3 Ramesh
4 Madhu
5 Visu
6 Rattu
SQL> select
* from student where no <> 2;
NO NAME MARKS
--- ------- ----------
1
Sudha 100
1 Jagan 300
3 Ramesh
4 Madhu
5 Visu
6 Rattu
b) USING AND
This will gives the output when all the
conditions become true.
Syntax:
select * from <table_name> where <condition1>
and <condition2> and ..
<conditionn>;
Ex:
SQL> select
* from student where no = 2 and marks >= 200;
NO NAME MARKS
--- ------- --------
2 Saketh 200
2 Naren 400
c) USING OR
This will gives the output when either of
the conditions become true.
Syntax:
select * from <table_name> where <condition1>
and <condition2> or ..
<conditionn>;
Ex:
SQL> select
* from student where no = 2 or marks >= 200;
NO NAME MARKS
--- ------- ---------
2 Saketh 200
1 Jagan 300
2 Naren 400
d) USING BETWEEN
This will gives the output based on the
column and its lower bound, upperbound.
Syntax:
select * from <table_name> where <col>
between <lower bound> and <upper
bound>;
Ex:
SQL> select
* from student where marks between 200 and 400;
NO NAME MARKS
--- ------- ---------
2 Saketh 200
1 Jagan 300
2 Naren 400
e) USING NOT BETWEEN
This will gives the output based on the
column which values are not in its lower bound,
upperbound.
Syntax:
select * from <table_name> where <col>
not between <lower bound> and
<upper
bound>;
Ex:
SQL> select
* from student where marks not between 200 and 400;
NO NAME MARKS
--- ------- ---------
1 Sudha 100
f) USING IN
This will gives the output based on the
column and its list of values specified.
Syntax:
select * from <table_name> where <col>
in ( value1, value2, value3 … valuen);
Ex:
SQL> select
* from student where no in (1, 2, 3);
NO NAME MARKS
--- ------- ---------
1 Sudha 100
2 Saketh 200
1 Jagan 300
2 Naren 400
3 Ramesh
g) USING NOT IN
This will gives the output based on the column which values are not in
the list of
values
specified.
Syntax:
select * from <table_name> where <col>
not in ( value1, value2, value3 … valuen);
Ex:
SQL> select
* from student where no not in (1, 2, 3);
NO NAME MARKS
--- ------- ---------
4 Madhu
5 Visu
6 Rattu
h) USING NULL
This will gives the output based on the
null values in the specified column.
Syntax:
select * from <table_name> where <col>
is null;
Ex:
SQL> select
* from student where marks is null;
NO NAME MARKS
--- ------- ---------
3 Ramesh
4 Madhu
5 Visu
6 Rattu
i) USING NOT NULL
This will gives the output based on the not
null values in the specified column.
Syntax:
select * from <table_name> where <col>
is not null;
Ex:
SQL> select
* from student where marks is not null;
NO NAME MARKS
--- ------- ---------
1 Sudha 100
2 Saketh 200
1 Jagan 300
2 Naren 400
j) USING LIKE
This will be used to search through the
rows of database column based on the pattern
you specify.
Syntax:
select * from <table_name> where <col>
like <pattern>;
Ex:
i) This will give the rows whose marks
are 100.
SQL> select
* from student where marks like 100;
NO NAME MARKS
--- ------- ---------
1 Sudha 100
ii) This will give the rows whose name
start with ‘S’.
SQL> select
* from student where name like 'S%';
NO NAME MARKS
--- ------- ---------
1 Sudha 100
2 Saketh 200
iii) This will give the rows whose name
ends with ‘h’.
SQL> select
* from student where name like '%h';
NO NAME MARKS
--- ------- ---------
2 Saketh 200
3 Ramesh
iV) This will give the rows whose
name’s second letter start with ‘a’.
SQL> select
* from student where name like '_a%';
NO NAME MARKS
--- ------- --------
2 Saketh 200
1 Jagan 300
2 Naren 400
3 Ramesh
4 Madhu
6 Rattu
V) This will give the rows whose name’s
third letter start with ‘d’.
SQL> select
* from student where name like '__d%';
NO NAME MARKS
--- ------- ---------
1 Sudha 100
4 Madhu
Vi) This will give the rows whose
name’s second letter start with ‘t’ from ending.
SQL> select
* from student where name like '%_t%';
NO NAME MARKS
--- ------- ---------
2 Saketh 200
6 Rattu
Vii) This will give the rows whose
name’s third letter start with ‘e’ from ending.
SQL> select * from student where name like
'%e__%';
NO NAME MARKS
--- ------- ---------
2 Saketh 200
3 Ramesh
Viii) This will give the rows whose
name cotains 2 a’s.
SQL> select * from student where name like '%a% a %';
NO NAME MARKS
--- ------- ----------
1 Jagan 300
* You
have to specify the patterns in like
using underscore ( _ ).
USING
ORDER BY
This
will be used to ordering the columns data (ascending or descending).
Syntax:
Select * from <table_name> order by <col>
desc;
By
default oracle will use ascending order.
If
you want output in descending order you have to use desc keyword after the column.
Ex:
SQL> select
* from student order by no;
NO NAME MARKS
--- ------- ---------
1 Sudha 100
1 Jagan 300
2 Saketh 200
2 Naren 400
3 Ramesh
4 Madhu
5 Visu
6 Rattu
SQL> select
* from student order by no desc;
NO NAME MARKS
--- ------- ---------
6 Rattu
5 Visu
4 Madhu
3 Ramesh
2 Saketh 200
2 Naren 400
1 Sudha 100
1 Jagan 300
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);
MULTIPLE 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.
No comments:
Post a Comment