INTRODUCTIO
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
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
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
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
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.
good
ReplyDelete