SQL-material to data base testing for experineced candidates
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, &col3 …. &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
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
