SQL Material for Experienced Candidates

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