INTRODUCTION
SQL is divided into the following
1 Data Definition Language (DDL)
2 Data Manipulation Language (DML)
3 Data Retrieval Language (DRL)
4 Transaction Control Language (TCL)
5 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.
1 By value method
2 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
1 Where
2 Order by
USING
WHERE
Syntax:
select * from <table_name> where <condition>;
The
following are the different types of operators used in where clause.
1 Arithmetic operators
2 Comparison operators
3 Logical operators
1 Arithmetic operators -- highest precedence
+, -, *, /
2 Comparison operators
Ø =, !=, >, <, >=, <=, <>
3 between, not between
4 in, not in
5 null, not null
6 like
7
Logical operators
8 And
9 Or --
lowest precedence
10 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
