Ms.S.A.

Kshirsagar DMS Notes SYCM-3-K

UNIT- III INTERACTIVE SQL AND PERFORMANCE TUNING

(MARKS: 18M)
COURSE OUTCOMES:
CO3 - Manage database using SQL.
THEORY LEARNING OUTCOMES:
TLO 3.1 Write SQL queries using DDL, DML,DCL and TCL.
TLO 3.2 Write SQL queries to join relations.
TLO 3.3 Write SQL queries for ordering and grouping data.
TLO 3.4 Use various class of operators in SQL. .
TLO 3.5 Create schema objects for performance tunning.
CONTENTS:
3.1 SQL: -Data-types, Data Definition Language (DDL),Data Manipulation
language (DML), Data Control Language (DCL), Transaction Control
Language (TCL).
3.2 Clauses & Join:- Different types of clauses - Where, Group by ,Order by,
Having. Joins: Types of Joins, Nested queries.
3.3 Operators: - Relational, Arithmetic, Logical, Set operators.
3.4 Functions :- Numeric , Date and time, String functions, Aggregate
Functions.
3.5 Views, Sequences, Indexes: -Views : Concept ,Create, Update, Drop Views.
Sequences :- Concept ,Create, Alter ,Drop, Use of Sequence in table, Index:
Concept ,Types of Index , Create ,Drop Indexes

3.1 SQL: -Data types, Data Definition Language (DDL),Data Manipulation

language (DML), Data Control Language (DCL), Transaction Control
Language (TCL).

DATA TYPES
1. NUMERIC DATA TYPES
INT or INTEGER
SMALLINT
NUMBER

2. APPROXIMATE NUMERIC DATA TYPES

FLOAT(p)
Real

Sou.Venutai Chavan Polytechnic, Pune Page 1

 Ms.S.A.Kshirsagar DMS Notes SYCM-3-K

3. DATE AND TIME DATA TYPES

datetime
date
time
timestamp
4. CHARACTER STRINGS DATA TYPES
char
varchar
varchar(max)
text
5. FORMATTED NUMBERS
DEC(p, s) or DECIMAL(p,s)
NUM(p,s) or NUMERIC(p,s)
p - precision value i.e. number of digits in a number
s - scale value i.e. number of digits to right side after point.

DDL (DATA DEFINITION LANGUAGE)

This includes changes to the structure of the table like creation of the table, altering
table, deleting structure of the table(i.e. drop)
All DDL commands are auto committed that means it saves all the changes
permanently.
e.g. List of DDL commands
1. Create
2. Alter
3. Describe
4. Drop
5. Truncate
6.Rename

1) CREATE
This command is used to create the table or define the structure of the table.

Syntax: without primary key

create table table_name( column_name1 datatype(size), column_name2

datatype(size),…., column_name_n datatype(size) );
e.g:
create table student ( rollno number(3), name varchar(20), city varchar(20),
dob date);
Sou.Venutai Chavan Polytechnic, Pune Page 2
 Ms.S.A.Kshirsagar DMS Notes SYCM-3-K

Syntax: with primary key

create table table_name( column_name1 datatype(size), column_name2

datatype(size),…., column_name_n datatype(size) );
e.g:
create table Employee ( Empno number(3) primary key ,E_name varchar(20),
salary number(9),doj date) ;

2) DESCRIBE or DESC
Used to describe the structure of the table
Syntax:
desc tablename;
OR
Describe tablename;
e.g:
desc student;
desc Emplyee;

4)TRUNCATE
This command is used to delete all data/records or rows .
After using this command only all records/data deleted ,the structure of the table
remains same. i.e the table structure remains as it is.
After truncate the space will frees, we can reenter the data.

Syntax

truncate table table_name;

e.g. truncate table student ;

in above example only data is deleted and space will free. Table structure remains
as it is.

5) RENAME
This command is used to rename the name of table. That is we can give new name
to table.
Syntax:
Rename old_table_name to new_table_name;

e.g. rename Employee to Emp;

Sou.Venutai Chavan Polytechnic, Pune Page 3

 Ms.S.A.Kshirsagar DMS Notes SYCM-3-K

now the name of table is Emp .

3)ALTER

This command is used to make certain changes in structure of table which is

already created.
It helps in modifying the table structure if we want to change datatype or size of
column ,add new column, delete i.e drop any column from existing table, add
primary key or remove primary key, rename name of column etc.

Syntaxes:

a)To add new column in an existing table:

syntax:

alter table table_name add column_name datatype(size);

e.g. alter table student add percentage number(3);

in above example ercentage column added in existing student table.

b)To delete any column from existing table:

syntax:
alter table table_name drop column column_name;

e.g.alter table student drop column city;

in above example city column is deleted from existing student table.

c)To add primary key

Syntax:
Alter table table_name add primary key(column_name);

e.g. alter table student add primary key(rollno);

Sou.Venutai Chavan Polytechnic, Pune Page 4

 Ms.S.A.Kshirsagar DMS Notes SYCM-3-K

in above example we have added or set the primary key for column rollno.

d)To remove primary key

Syntax:
alter table table_name drop primary key;

e.g. alter table student drop primary key;

in above example we had unset primary key for column rollno.

e)To change the size or datatype

Sytax:
alter table table_name modify column_name datatype(size);

Here datatype is changed datatype and size is changed size.

e.g. alter table student modify mpercentage float(4);

in above example we have changed the datatype of percentage column number to

float and size from 3 to 4.
e.g. alter table student modify percentage number(3);
in above example we have changed the datatype of percentage column float to
number.

f)To rename column name

Syntax:
Alter table table_name rename column old_column_name to
new_column_name;

e.g. alter table student rename column percentage to marks;

in above example we have changed name of column percentage to marks in an

existing table student.

Sou.Venutai Chavan Polytechnic, Pune Page 5

 Ms.S.A.Kshirsagar DMS Notes SYCM-3-K

6) DROP
This command is used to delete the structure of table. That is the whole table
structure is deleted with data permanently i.e. table does not exists.

Syntax:

drop table tablename;

e.g. drop table Emp;

in above example the structure of the emp table is deleted .it will does not exist
after using this command.

DML (DATA MANIPULATION LANGUAGE)

DML stands for data manipulation language,These commands are used to
insert,update,delete data in database table.
DML commands are insert,delete,update.
DML commands are used for manipulating the data stored in the table .
DML command are not auto committed.
That means changes are not permanent, they can be roll backed.
e.g. List of DML commands:
1. Insert
2. Update
3. Delete
4. select
DML stands for data manipulation language, These commands are used to
insert,update,delete, slect data in database table.
DML commands are insert, update delete,select.
i)insert:
we can insert data in table by 4 ways:
consider table student(rollno,name,dob,marks)

Sou.Venutai Chavan Polytechnic, Pune Page 6

 Ms.S.A.Kshirsagar DMS Notes SYCM-3-K

1. Syntax1: insert command without defining attributes

insert into table_name values (value1,value2,…..);
e.g: insert into student values(9,’sakshi’ ,’2-jan-2006’,85);

2. Syntax2: insert command with defining attributes

insert into table_name(col1,col2,.…) values (value1,value2,…..);
e.g: insert into student(rollno,name,dob,marks) values (8,’sayali’ ,’15-nov-2004’,89);

3. Syntax3: insert records with specific columns values.

insert into table_name(col1,col2) values (value1,value2);

e.g: 1) insert into student(rollno,name) values (3,’raj’);

2)insert into student(rollno,dob) values (2,’16-may-2001’);

4. Syntax4: To insert more than one record/row in a single insert query.

insert all into table_name values(value1,value2,……,valuen)
into table_name values(value1,value2,……,valuen)
.
.
.
.
into table_name values(value1,value2……valuen)
select * from dual;

Sou.Venutai Chavan Polytechnic, Pune Page 7

 Ms.S.A.Kshirsagar DMS Notes SYCM-3-K

example: insert 3 rows/records:

insert all into student values(5, ‘ram’, ’15-aug-2005’, 85)

into student values(6, ‘rushi’ , ’20-may-2009’,90)
into student values(7,’ajay’ ,’23-june-1999’,80)
select * from dual;

ii) Update:
this command is used to update the column values which are inserted already. This
command is used to update or modify single or multiple rows of the table.

Syntax:
update table_name set column_name=value where condition;
Example:
update student set name=’pravin’ where rollno=3;
in above ex name of rollno 3 is updated to pravin instead of raj.

2) Update student set marks=88 where name=’Ajay’;

In above ex, updated marks of ‘Ajay’ to 88 instead of 80.

3) update student set marks=95 where rllno<=3;

in above ex, marks are updated to 95 whose rollno is below or equals to 3

Sou.Venutai Chavan Polytechnic, Pune Page 8

 Ms.S.A.Kshirsagar DMS Notes SYCM-3-K

iii) DELETE
Detele command is used to delete single or multiple records from table.You can delete
specific rows using where condition/clause.When all rows deleted structure of the
table remains same
Syntaxes:

i)To delete specific records/rows:

Syntax:
delete from table_name where condition;

Example 1) delete from student where rollno=3;

Above example deletes the record from student table whose rollno is 3.
2) delete from student where rollno<=4;
In above example deletes the records from student table whose rollno is less than or
equls to 4
3)delete from student where rollno=5 or name=’ajay’;
above example deletes the records from student table whose rollno is 4 or having name
is ‘ajay’
4)Delete the records of employees whose salary is more than 25000.
Ans-> delete from employee where salary > 25000;

ii)To delete all records/rows:

Syntax:
delete from table_name;
example: delete from student;

Sou.Venutai Chavan Polytechnic, Pune Page 9

 Ms.S.A.Kshirsagar DMS Notes SYCM-3-K

above example deletes all rows/records/details from table student but table structure
remains same.

2) Delete from emp;

Above example Deletes all records from emp table.

4) select

Select is used to list/display/retrieve rows/records with columns from table.

SYNTAX:
1)To display all records/rows with all column values.

select * from table_name;

example:
select * from student;

2)To display specific record with where clause/condition

select * from table_name where condition;
example:
1) select * from student where rollno<15;
2) select * from student where marks<=90;

c)To display specific columns record values from table.

Select col1,col2,col3 from table_name where condition;
Example:
1) select rollno,name from student where rollno<5;
above example displays rollno and name values whose rollno is below 5
Sou.Venutai Chavan Polytechnic, Pune Page 10
 Ms.S.A.Kshirsagar DMS Notes SYCM-3-K

2) Select rollno,marks from student;

Above example displays only rollno and marks column values.

d)for applying more than one conditions we can use logical operators i.e
AND,OR,NOT with select command
example:
1) select name, maks from student where rollno< 3 and name= ‘pravin’;
2) select rollno,name, dob from student where rollno< 4 or name= ‘rupali’;
3) select rollno, name where rollno not like 5;

DCL (DATA CONTROL LANGUAGE)

DCL are the commands to grant and take back authority from any database users.
e. g. List of DCL commands:
1. Grant
2. Revoke

GRANT
Grant command is used to provide access or privileges on the database objects to the
users.

Syntax
GRANT privilege_name ON object_name TO user_name ;

Example:
1) GRANT insert, update ON student TO pravin ;
2) Grant select,delete, create table on student to ajay;
3) Grant insert,delete on emp to priya;

Syntax: to grant all privileges:

Grant all on object_name to username;
example:
1) grant all on student to jay;
2) grant all on emp to riya;

Note: To create user use following syntax:

Create user username identified by password;

Sou.Venutai Chavan Polytechnic, Pune Page 11

 Ms.S.A.Kshirsagar DMS Notes SYCM-3-K

e.g: create user pravin identified by root123;

privilege names are:

insert, update,delete,create table, create view etc

2. REVOKE
Revoke command removes/denies user access rights or privileges from the user on the
database objects.
Once you have granted privileges, you may to remove some or all of these privileges.
Revoke means taking authorization back from user.
The revoke command is used to denied grant given on the object.

Syntax

REVOKE privilege_name ON object_name FROM user_name;

Example
REVOKE insert, update ON emp FROM pravin ;

2)denied delete privilege provided to pradip on student object

Revoke delete on student from pradip;

3)denied insert, update, delete privilege provided to ajay on employee object.

Revoke insert,update,delete on employee from ajay;

2)To revoke all privileges:

Revoke all on object_name from user_name;

Example:
1) Revoke all on emp from riya;

2) Revoke all on student from ajay;

Sou.Venutai Chavan Polytechnic, Pune Page 12

 Ms.S.A.Kshirsagar DMS Notes SYCM-3-K

TCL (TRANSACTION CONTROL LANGUAGE)

TCL Commands control and manage the user transactions.
List of TCL commands:
1. Commit
2. Rollback
3. Savepoint

1)commit
Commit command is used to end the transaction permanently and also make the effect
permanent to the database. That is it save the transaction/ changes permanently.

Syntax:

Commit;
Example: commit;

Or

Syntax: Commit work;

Example: commit work;

2)savepoint
savepoint command is used to temporarily save transaction so that you can rollback to
that point whenever required

Syntax:
Savepoint savepoint_name;

Example: Savepoint s1;

3) Rollback:
This command restores the database to last committed state/ transaction.It is also used
with savepoint command to jump to a savepoint in an ongoing transaction.

Syntax:

Sou.Venutai Chavan Polytechnic, Pune Page 13

 Ms.S.A.Kshirsagar DMS Notes SYCM-3-K

ROLLBACK;

Example: rollback;
OR

ROLLBACK WORK;

Example: rollback work;

OR
ROLLBACK TO SAVEPOINT_NAME;

Example:
Rollback to savepoint s1;

3.2 Clauses & Join:- Different types of clauses - Where, Group by ,Order
by, Having. Joins: Types of Joins, Nested queries.

Different types of clauses

1)Where Clause
2)group by clause
3)having clause
4)Order by clause

Consider the employee table:

Eno Ename Deptno sal
1 A 11 5000
2 B 22 3000
3 C 22 9000
4 A 33 4000
5 B 33 6000
6 D 44 10000

1)Where Clause

Where clause is used with DML commands like update, delete, select except insert.
The condition in where is made up with operand(column) operator value (e.g :
salary>=20000)
Sou.Venutai Chavan Polytechnic, Pune Page 14
 Ms.S.A.Kshirsagar DMS Notes SYCM-3-K

Syntax: where for select

Select col1,col2,….,coln from tablename where condition;

e.g: select eno,ename from employee where deptno=2;

2)GROUP BY:

The group by clause used in a select statement to collect data across multiple
records and group by one or more columns.
It is used to divide the multiple rows/records in a table into groups by single or many
columns.
Aggregate functions are used to group multiple columns with one command.
Group by clause displays multiple records grouped together by one column.

Syntax:

Select col1,col2,….,coln aggregate_function (expression) from table_name

Where condition group by column_name1,column_name2 ;

e.g.: Select deptno ,avg(sal) from emp group by deptno;

deptno avg(sal)
o/p : 11 5000
22 6000
33 5000
44 10000

Display number of employee with name

Select ename, count(eno) from employee group by ename;

ename Count(eno)
A 2
B 2
C 1
D 1

3)display maximum, minimum salary ,sum and number of employees with name
/for each name/ for every name.

Sou.Venutai Chavan Polytechnic, Pune Page 15

 Ms.S.A.Kshirsagar DMS Notes SYCM-3-K

Select ename,max(sal),min(sal),sum(sal), , count(eno) from employee group by

ename;

O/p
ename Max(sal) Min(sal) Sum(sal) Count(eno)
A 5000 4000 9000 2
B 6000 3000 9000 2
C 9000 9000 9000 1
D 10000 10000 10000 1

3)Having clause
It can be used in a select statement to display the records.
It is like a where clause, except that it involves a summary value, rather than a column
value. i.e it uses aggregate function.
The having clause operates after the summation has been done.

{i.e in having condition it uses aggregate function(column) operator value

[sum(salary)>20000]}

Having clause is used in combination with the group by clause

A where clause specifies the rows in the table to be included in the summation.

Syntax :
Select col1,col2,….,col n aggregate_function (expression) From table_name Where
condition Group by column_name Having condition 1,condition2;

e.g: Select deptno, sum(sal) from employee group by deptno having sum(sal)>10000;
deptno sum(sal)
22 12000

Display employee names having average salary is greater than 5000

select ename,avg(sal) from employee group by ename having avg(sal)>5000 ;

ename avg(sal)
c 9000
d 10000

Sou.Venutai Chavan Polytechnic, Pune Page 16

 Ms.S.A.Kshirsagar DMS Notes SYCM-3-K

Display department nos having total salary is more than 15000.

select deptno,sum(sal) from employee group by deptno having sum(sal)>15000;

o/p: no rows selected

4)Order by clause:
 This clause is used for getting the result in particular order i.e. by descending or
ascending order.
 By default the order is ascending.
 To sort the records in a descending order, you can use the DESC keyword and
for ascending ASC
 The order by keyword is used to sort the result set by one or more columns.
.
Syntax:
SELECT column_name1,column_name2,…column_name_n
FROM table_name
ORDER BY column_name ASC|DESC;



Example:
SELECT * FROM employee ORDER BY deptno ASC ;

or

SELECT * FROM employee ORDER BY deptno ;

o/p:
Eno Ename Deptno Sal
1 A 11 5000
2 B 22 3000
3 C 22 9000
4 A 33 4000
5 B 33 6000
6 D 44 10000

Sou.Venutai Chavan Polytechnic, Pune Page 17

 Ms.S.A.Kshirsagar DMS Notes SYCM-3-K

It will display the details of employee table in ascending order of deptno.

Example 2) SELECT * FROM employee ORDER BY sal DESC;

It will display the details of employee table in descending order of salary.

O/P:
Eno Ename Deptno sal
6 D 44 10000
3 C 22 9000
5 B 33 6000
1 A 11 5000
4 A 33 4000
2 B 22 3000

SQL JOINS:
Consider the relations/ tables,

Student Result

Rollno Name Address

1 Priya Pune Rollno Percentage
2 Riya Mumbai 1 90
4 Raju Baner 3 88
6 Krushna Pune 4 85
9 75
A join clause is used to combine rows from two or more tables based on a related/same
column between them.

Different types of joins are as follows:

1. Inner join or equi join

2. Outer join
i. Left Outer Join
ii. Right Outer Join
iii. Full Outer Join
Sou.Venutai Chavan Polytechnic, Pune Page 18
 Ms.S.A.Kshirsagar DMS Notes SYCM-3-K

1)INNER Join(equi joins):

This is a simple JOIN which is the result is based on matched data/equalities same
values of same column is called equi/inner join.
It displays matched record from different table by combining it based on same column.
In equi join comparison operator “=” is used to perform a join.
It returns records that have matching values in both the tables.

Syntax :
SELECT column_name_list
from table_name1
INNER JOIN
table_name2
on table_name1.column_name = table_name2.column_name;

Example:
SELECT * from student inner join result on student.rollno = result.rollno;

Rollno Name address Rollno Percentage

1 Priya Pune 1 90
4 Raju Baner 4 85

2)The SQL OUTER JOIN

returns all rows from both the participating tables which satisfy the join condition
along with rows which do not satisfy the join condition.
Outer join are of three types:

1. Left outer join (also known as left join): this join returns all the rows/records from
left table combine with the matching rows of the right table. If no value matching in
the right table it returns NULL values.

syntax :
SELECT column_name_list from table_name1 LEFT OUTER JOIN table_name2 on
table_name1.columnname = table-name2.column-name;

Example:

SELECT * FROM student LEFT OUTER JOIN result ON

student.rollno=result.rollno;

Sou.Venutai Chavan Polytechnic, Pune Page 19

 Ms.S.A.Kshirsagar DMS Notes SYCM-3-K

Rollno Name address Rollno Percentage

1 Priya Pune 1 90
4 Raju Baner 4 85
2 Riya Mumbai NULL NULL
6 Krushna Pune NULL NULL

2. Right outer join (also known as right join): this join returns all the rows from
right table are combined with the matching rows of left table .If no value matching in
the left table, it returns null value.
Syntax:
SELECT column-name-list from tablename1 RIGHT OUTER JOIN table-name2 on
tablename1.column-name = table-name2.column-name;

Example:
SELECT * FROM student right OUTER JOIN result ON student.rollno=result.rollno;
o/p:
Rollno Name address Rollno Percentage
1 Priya Pune 1 90
4 Raju Baner 4 85
NULL NULL NULL 3 88
NULL NULL NULL 9 75

3. The full outer join keyword returns all records when there is a match in either left
(table1) or right (table2) table records. Those values are not matching it will returns
null value.
Syntax:
SELECT column_name list from table_name1 full outer join table_name2 on
table1.column_name =table2.column_name;
Example:

SELECT * FROM student right OUTER JOIN result ON student.rollno=result.rollno;

Rollno Name Address Rollno Percentage

1 Priya Pune 1 90
4 Raju Baner 4 85
2 Riya Mumbai NULL NULL
6 Krushna Pune NULL NULL
NULL NULL NULL 3 88
NULL NULL NULL 9 75

Sou.Venutai Chavan Polytechnic, Pune Page 20

 Ms.S.A.Kshirsagar DMS Notes SYCM-3-K

Nested queries./ Sub Queries:

A subquery or inner query or nested query is a query within another query and
embedded within the where clause.
The statement containing subquery is called as parent statement.
Subwueries can be used with select ,insert,update and delete statementsalong with the
operators lke =,>,<,>=,<=,IN,BETWEEN etc
Types: 1) single row subquery,(=,<,>,<=,>=)
2)multiple row subquery (IN,ANY,ALL).

Parent statement subquery

SYNTAX:
Select column_name_list From table_name where expression operator (select
column_name_list from table_name where condition);

Example:
Select * from employee where sal=(select max(sal) from employee);
o/p: consider employee table

Eno Ename Deptno sal

6 D 44 10000

It will display details of employees whose salary is equal to maximum of salary

2)display details of employees whose salary is greater than average salary of an

employee.

Select * from employee where sal=(select avg(sal) from employee);

Eno Ename Deptno sal

3 C 22 9000
6 D 44 10000

Rules:
1)subqueries enclosed within parenthesis

Sou.Venutai Chavan Polytechnic, Pune Page 21

 Ms.S.A.Kshirsagar DMS Notes SYCM-3-K

2)A subquery can have only one column in select clause.And multiple columns in the
main/parent query.
3)order by clause not used in subquery.it can be used in main query.
4)in subquery use multiple rows IN operator.
5)BETWEEN not used in subquery.

3.4 Functions :- Numeric /Arithmetic , Date and time, String

functions, Aggregate Functions.
Oracle function:
1) User defined function: A user defined function is a
function provided by the user of a program
2) Built in function/ready-made function: a function
that is built into an application and can be accessed
by end users.
a)Single row function:
i. Arithmetic function / Numeric Function
ii. Date and time function
iii. String function

b)Multi row/group function

i. Aggregate function

Numeric/ Arithmetic function:

1)ABS(a)-this function returns the absolute value of given number a.

E .g: select abs(-250) from dual;

o/p:250

2)CEIL(a):
Returns the smallest integer greater than or equal to passed number.
Eg:select ceil(48.876) from dual;
o/p:49

3)floor(a):

Sou.Venutai Chavan Polytechnic, Pune Page 22

 Ms.S.A.Kshirsagar DMS Notes SYCM-3-K

Returns the greater integer smaller than or equal to passed number.

Eg:select floor(48.876) from dual;
o/p:48

4)exp(a):
It returns exponential value .
Select exp(5) from dual;
o/p:148.4131

5)LN(a):
This function returns natural logarithm of value ‘a’.
e.g:selectln(1000) from dual;
o/p:6.9077

6)log(a,b):
Log function returns logarithm of b to the base a
e.g: select log(100,10) from dual;
o/p: 0.5

7)mod(a,b)
returns the reminder of the division a/b
e.g: select mod(5,2) from dual;

o/p:1

8)power(a,b) :
returns the value of power a to b
select power(2,3) from dual;
o/p :8

9)SIGN(a):
This function shows the negative or positive number.
It returns 1 if a>0
And it returns -1 if a<0
Returns 0 if a=0
e.g select sign(-22) from dual;
o/p: -1
e.g: select sign(12) from dual;

Sou.Venutai Chavan Polytechnic, Pune Page 23

 Ms.S.A.Kshirsagar DMS Notes SYCM-3-K

o/p: 1

10)SIN(a):
It returns the sine of angle of a
e. g: select sin(90) from dual;
o/p: 0.89399

11)SQRT(a) :
it returns the square root of value a.
e.g: select sqrt(15) from dual;
o/p: 4

12)Round(a,b):

This function returns the rounded value of a to the decimal places

e. g: select round(87.945,2) from dual;
o/p: 87.95

Sou.Venutai Chavan Polytechnic, Pune Page 24

 Ms.S.A.Kshirsagar DMS Notes SYCM-3-K

Date and time functions:

7) Sysdate
It returns system date.
e.g select sysdate from dual;

Sou.Venutai Chavan Polytechnic, Pune Page 25

 Ms.S.A.Kshirsagar DMS Notes SYCM-3-K

String functions:

Sou.Venutai Chavan Polytechnic, Pune Page 26

 Ms.S.A.Kshirsagar DMS Notes SYCM-3-K

Aggregate functions:
Aggregate functions are functions where the values of multiple rows are grouped
together to form a single value

These functions are generally mathematical functions

Consider the following employee table

Eno Ename Salary

1 A 5000
2 B 3000
3 C 9000
4 A 4000
5 B 6000
6 D 10000

1)avg(expression):
this function computes average of column.
e.g:select avg(salary) from employee;
o/p:6166.67

2)count(expression):
Counts the records/rows defined by the expression.
e.g: select count(eno) from employee;

o/p: 6

3)min(expression):
This function returns the minimum value of column.
e.g: select min(salary) from employee;
o/p:3000

4)max(expression):
This function returns the maximum value of column.
e.g: select max(salary) from employee;
o/p:10000

5)sum(expression)
Sou.Venutai Chavan Polytechnic, Pune Page 27
 Ms.S.A.Kshirsagar DMS Notes SYCM-3-K

This function returns the sum of the whole column of table

e.g: select sum(salary) from employee;
o/p:370000
3.3 Operators: - Relational, Arithmetic, Logical, Set
operators.

SQL OPERATORS:
SQL operators are used to operate on value or numbers.
The SQL operators are as follows:
1) Relational,
2) Arithmetic,
3) Logical,
4) Set operators.

Consider table emp:

Empno Empname deptname deptno salary address
1 A Production 11 35000 Pune
2 B Manufacturer 22 20000 Mumbai
3 C Design 33 25000 Kolhapur
4 D Testing 44 15000 Delhi
5 E developing 55 60000 Pune
1. Relational/ Comparision operators:
Used for comparing attribute values.
These are checking relations of attributes.
= -equal to
!= or < > -Not equal to
> -greater than
< - lessthan
> = -greater than or equal to
< = -less than or equal to
<> -not equal to
Example:
1) select empno,empname,salary from emp where salary> 20000;
2) select empno,salary from emp where empno<=4;
3) Select * from emp where salary =3000;
4) Select * from emp where empname < > ‘B’;

Note: comparision operators are used with where clause.

Sou.Venutai Chavan Polytechnic, Pune Page 28

 Ms.S.A.Kshirsagar DMS Notes SYCM-3-K

Arithmetic Operators:
List and explain any 4 arithmetic operators in SQL with example.

(For each - 1 mark)

Ans: Arithmetic operators are used to perform mathematical functions in SQL.

+ (addition)
- (subtraction)
* (multiplication
/ (division)
mod(a,b)

Addition
Addition is performed using the plus (+) symbol.
Example
SELECT SALARY + 20000 As “total_sal” FROM EMP;
O/p

total_sal
55000
40000
45000
355000
80000

Meaning:
The SALARY column is added with the 20000 for each row of data .

Subtraction
Subtraction is performed using the minus (-) symbol.
e.g
SELECT SALARY –5000 As “decremented_sal” FROM EMP;
O/P
decremented_sal
30000
15000
20000
Sou.Venutai Chavan Polytechnic, Pune Page 29
 Ms.S.A.Kshirsagar DMS Notes SYCM-3-K

10000
55000
Meaning:
The SALARY column is substracted by 5000 for each row of data

Multiplication
Multiplication is performed by using the asterisk (*) symbol.
Example:
SELECT SALARY * 10 FROM EMP;
Salary*10
350000
200000
250000
150000
600000

Meaning:
The SALARY column is multiplied by 10

Division
Division is performed through the use of the ‘/’ symbol.
e.g
SELECT SALARY / 10 FROM EMP;
o/p
Salary/10
3500
2000
2500
1500
6000
Meaning:
The SALARY column is divided by 10
Logical Operators:
It is used with specific or combination of conditions.
There are 3 logical operators
AND –used when all the conditions must be true.
OR -used when one of the condition is to be true.
NOT – it uses negation
Examples:
Select * from emp where salary > 20000 and salary < 40000;
Select * from emp where address=’pune’ or address= ‘kolhapur’;
Select empname, salary, address from employee where address not like ‘pune’;
Sou.Venutai Chavan Polytechnic, Pune Page 30
 Ms.S.A.Kshirsagar DMS Notes SYCM-3-K

F)SET OPERATORS:
When we want to combine the result of twoqueries then the special type of operators
are used known as set operators.
Used to join queries and retrieve theh number of rows.
Set operators are as follows:
 Union
 Union all
 Intersect
 Minus
Consider the tables,
Employee Department

Empid Empid Deptname

1 1 Production
2 2 Manufacturing
3 8 Design
4 10 Testing
8 11 Developing
10 12 Sales

Union

It select/combines values of same attribute from different table with avoiding duplicate
values.
Union operator selects different values of same attribute from different related tables.

e.g: select empid from employee union select empid from department;

output: empid
1
2
3
4
8
10
11
12

Sou.Venutai Chavan Polytechnic, Pune Page 31

 Ms.S.A.Kshirsagar DMS Notes SYCM-3-K

Union all

It selects/combines all values of same attribute from different related tables without
avoiding duplicate values.
i.e. Union all operator combines all records from both the queries without avoiding
duplicate values.
Select empid from employee union all select empid from department;
output: empid
1
2
3
4
8
10
1
2
8
10
11
12
Intersect
It selects similar values of same attribute from different related tables.
e.g. select empid from employee intersect select empid from department;
Output: empid
1
2
8
10
Minus
It performs the subtraction. i.e. values present in first table and not in second table are
displayed.
e.g. select empid from employee minus select empid from department;
Output: empid
3
4
e.g. select empid from department minus select empid from employee;
Output:
empid
11
12

Sou.Venutai Chavan Polytechnic, Pune Page 32

 Ms.S.A.Kshirsagar DMS Notes SYCM-3-K

G) Range operators: consider emp table (previously mentioned on page no.28)

(i) IN and NOT IN
(ii) BETWEEN and NOT BETWEEN.

(i) IN and NOT IN

IN:
Syntax: x in (y,z)
This operator tests that operand x matches at least one element of the list provided.

Example:
select * from emp where salary in(35000,60000,17000);

Meaning:it displays information of employees whose salary is in 35000,60000,17000.

.

NOT IN:
Syntax: x not in (y,z)

This operator tests that operand x does not match any elements of list provided
Example: select * from emp where salry not in (35000,60000,17000);

Meaning:it displays information of employees whose salary is not in

35000,60000,17000.

(ii) BETWEEN and NOT BETWEEN

BETWEEN:
Syntax: x between y and z
Tests that operand x is greater than or equal to operand y and less than or equal to
operand z.
It is used numeric and date datatype.
Example.
Select * from emp where salary between 20000 and 35000;

Meaning: it will display details of employees whose salary between 20000 and 35000.

NOT BETWEEN: x not between y and z

Testes that operand x is less than operand y or greater than operand z. It test the

Sou.Venutai Chavan Polytechnic, Pune Page 33

 Ms.S.A.Kshirsagar DMS Notes SYCM-3-K

Range other than b to c.

Example. Select * from emp where salary not between 15000 and 25000;

Meaning: it will display details of employees whose salary between 15000 and
25000.

H) Pattern Matching Operators: consider emp table (previously mentioned on

page no.28)

like:
like operator is used for pattern matching operator.
This is used with NOT logical operator i.e Not like

It uses special characters to match the paterne.g : ‘? ’ and ‘%’

_ (underscore) or ? - indicates single character .

% - indicates any number of characters.

Use: it indicates any number of characters and it is used in select with like to match
all characters .

Example:
Select empno, deptname from employee where deptname like ‘p%’;

o/p- empno deptname

1 production

Meaning: It displays empid , deptname whose deptname start from p i.e p on first
location.

Display empno,deptname whose employee does not contain ‘a’ at 2nd location.
Ans: select empno,deptname from employee where deptname not like ‘_a%’;

o/p- empno deptname

1 production
2 design
4 testing
5 developing

Sou.Venutai Chavan Polytechnic, Pune Page 34

 Ms.S.A.Kshirsagar DMS Notes SYCM-3-K

Display empno,deptname whose deptname contain ‘d’ at start and ‘n’ at end
location.
Ans: select empno,deptname from employee where deptname like ‘d%n’;
o/p- empno deptname
3 design
Display empno whose deptname ended with ‘s’.
Ans: select empno from department where deptname like ‘_%s’;
o/p no rows are selected

Display empno,name of the department contains ‘s’ at third location.

Select empno,deptname from employee where deptname like ‘_ _ s%’;
o/p- empno deptname
3 design
1 testing

Sou.Venutai Chavan Polytechnic, Pune Page 35

 Ms.S.A.Kshirsagar DMS Notes SYCM-3-K

3.5 Views, Sequences, Indexes: -Views : Concept ,Create, Update, Drop

Views. Sequences :- Concept ,Create, Alter ,Drop, Use of Sequence in table,
Index: Concept ,Types of Index , Create ,Drop Indexes

Views : Concept ,Create, Update, Drop Views.

Def: A view is a logical extract of a physical relation i.e. it is derived from any base
relation.
OR
View: Views are virtual relations mainly used for security purpose, and can be
provided on request by a particular user.

A view consists of row and columns like a table.

If data is changing in the table, the same change is reflected in the view ,and vise
versa.

create view.
Create view command used to create view .
Syntax

Create view view_name as select col1,col2,…col_n from table_name where

condition;

Example : (By condisdering Emp(empno,ename,salary,deptno))

Create view v1 as select ename , salary from emp;

Above view v1 is created with attributes ename and salary.

2) create view as empdetails with ename ,deptno,and salary of employee table whose
salary more than 6000.

Create view empdetails as select ename,deptno,salary from employee where

salary>6000;

Sou.Venutai Chavan Polytechnic, Pune Page 36

 Ms.S.A.Kshirsagar DMS Notes SYCM-3-K

Update view:
Update view command is used to update view.
i.e it is used to update data/ rows/record of the view. Same changes will reflect in
base table also.

Syntax:
Update view_name set column_name=value where condition;

Example:1)
Update v1 set salary=salary+1000 where empno=3;

It will update the salary of enmployee no 3 by adding 1000 to its salary.

1) Update v1 set salary=60000 where empno=5;
Above example will change the salary of employee no 5 to 60000.

Change the deptno to 55 in emp_details view whose ename is ‘raj’.

Update emp_details set deptno=55 where ename=’raj’;

DROP VIEW
Drop view command is used to drop/delete the view permanently.
It Drops the specified view . base table will not drop.it remains as it is.

Syntax:

DROP VIEW View_Name;

Example:

DROP VIEW v1;

Drop view emp_details;
In above examples, It will drops the v1 and emp_details view.

Consider following schema.

Employee (empname, empid, dob, salary, job)
Create a view on employee having attribute (empname, empid, dob, salary, job)
where salary is greater than 20,000.
Ans:Create view EMPVIEW as select empname, empid, dob, salary, job from
employee
where salary>20000;

Sou.Venutai Chavan Polytechnic, Pune Page 37

 Ms.S.A.Kshirsagar DMS Notes SYCM-3-K

OR
CREATE VIEW EMPVIEW AS SELECT * FROM EMPLOYEE WHERE SALARY
>
20000;

Consider following schema:

Depositor (Acc_no, Name, PAN, Balance)
Create a view on depositor having attributes (Acc_no, PAN) where balance is
greater than 100000.
(For command – 4 Marks)
Ans: Create view v1 as select Acc_no,PAN from depositor where balance>100000;

4) insert view:-
To insect records/data in view use the command insert.

Syntax:
insert into viewname Values (valuel, value 2, --valuen);
OR
insert into viewname. (coll, con,…coln) values (value1, value2, ….,valuen);

Example:

insert into v1 values (6, 50000);

example insert into v1 (empno, Salary) values (7,80000);

5) delete command in view:-

delete is used to delete rows/records From view.
If records deleted from view same records are also deleted from base table and
vise versa.
Syntax:
To delete all records of view:
delete from viewname;

OR
To delete specific record of view:
delete from Viewname where condition;
Sou.Venutai Chavan Polytechnic, Pune Page 38
 Ms.S.A.Kshirsagar DMS Notes SYCM-3-K

Examples:
1. delete from v1;
2. delete from emp_details where ename = 'raj';

select view:-
display list /rows/records of a view.
syntax:
select * from viewname;
OR
select * from viewname where condition;
OR
select coll, col2, from viewname where condition;

Example:
select * from v1;
select * from v1 where eno = 2
select ename, deptno from emp_details where deptno= 5;

Advantages of Views:
1. Views restrict access to the data because the view can display selective columns
from the table.
2. Views can be used to make simple queries to retrieve the results of complicated
queries. For example, views can be used to query information from multiple
tables without the user knowing how to write a join statement.
3. Views provide data independence for adhoc users and application programs.
One view can be used to retrieve data from several tables.
4. Views provide groups of users to access to data according to their particular
criteria. Thus implements security and authorization.

Sou.Venutai Chavan Polytechnic, Pune Page 39

 Ms.S.A.Kshirsagar DMS Notes SYCM-3-K

Sequences:

Definition: A sequence is a database object that is generating unique and sequential

integer values.

 Oracle provides an object called as a Sequence that can generate numeric values.
 The value generated can have maximum of 38 digits.
 These numbers can be ascending or descending order.
 Provide intervals between numbers
 Sequence creates a series of values which are computer generated and which can
be
 inserted into a table.

Use:
1. It saves a time by reducing application code.
2. It is used to generate unique sequential integers.
3. It is used to create an auto number fields.
4. Sequence can be use for many tables/relations

Operations on sequence:

1) Create sequence :

Create sequence sequence_name

start with initial_value
increment by increment_value
Minvalue minimum_value
Maxvalue maximum_value
cycle/ nocycle;

where,

SEQUENCE_NAME: name of sequence

INITIAL_VALUE: starting value of sequence
INCREMENT BY i.e increment_value: Specifies the interval value between sequence
number. It can be any positiveor negative value but not zero.
Sou.Venutai Chavan Polytechnic, Pune Page 40
 Ms.S.A.Kshirsagar DMS Notes SYCM-3-K

MINIMUM_VALUE : Specifies the sequence minimum value i.e MINVALUE.

MAXIMUM_VALUE: Specifies the maximum value that a sequence can generate.
CYCLE: when sequence reaches its limit it start from beginning i.e MAXVALUE
NOCYCLE: Specifies that a sequence cannot generate more values after reaching the
maximum value.

Example :
create sequence seq_1increment by 1 start with 1 minivalue 1 maxvalue 999 cycle;

Insert Command using sequence

By using sequence we can insert records/values in the table of numbered

field/attribute.
nextval keyword, used to place/insert values sequentially for numbered field
automatically.

Syntax:
Insert into table_name values(sequence_name.nextval,value2,value3,…value_n);

Example: Suppose table is student(rollno,name)

insert into student values(seq_1.nextval,’riya’);

insert into student values(seq_1.nextval,’rohit’);

in above example, automatically Insert values for rollno field sequentially in student
by using sequence seq_1 as:

Rollno name
1 riya
2 rohit

Note: Currval keyword is used to return the current value of sequence.

It is used as sequencename.Currval

Example:
Select seq_1.currval from student;

Sou.Venutai Chavan Polytechnic, Pune Page 41

 Ms.S.A.Kshirsagar DMS Notes SYCM-3-K

Alter sequence
A sequence can be altered using alter sequence command. A sequence once created
can be altered. By using this we can alter/change minimum_value, maximum_value
i.e value of MINVALUE, MAXVALUE varables. This is achieved by using the
ALTER SEQUENCE statement.

SYNTAX:

ALTER SEQUENCE Sequence_Name variable_name value;

Here sequence_name is name of sequence.

Variable_name is MINVALUE, MAXVALUE

Example:
alter sequence seq_1 maxvalue 75;

It will change the maximum value of seq_1 from 999 to 75

Drop/Delete Sequence

The DROP SEQUENCE command is used to remove the sequence from database.

Syntax:

DROP SEQUENCE Sequence_Name;

Example:
Drop sequence seq_1;

Sou.Venutai Chavan Polytechnic, Pune Page 42

 Ms.S.A.Kshirsagar DMS Notes SYCM-3-K

Q. Create sequence for ‘student’ table.

Sequence for ‘student’ table

create sequence student_seq start with 1 increment by 1

minvlaue 1 maxvalue 60 nocycle;

Q.create sequence

i)sequence name is seq_1,start with 1,increment by 1,minimum value 1,maximum

value 20.
Ans: create sequence seq_1 start with 1
increment by1
minvalue 1
maxvalue 20
cycle;

ii)use a seq_1 to insert the values into table student(ID number(10),name

char(20));
ans: insert into student values(seq_1.nextval,’arjun’);

iii)change the seq_1 max value 20 to 50.

Ans: alter sequence seq_1 maxval 50;

iv)drop the sequence

Ans: drop sequence seq_1;

Sou.Venutai Chavan Polytechnic, Pune Page 43

 Ms.S.A.Kshirsagar DMS Notes SYCM-3-K

Index:

Definition: An index is a schema object that can speed up the retrieval of rows by
using pointer.
 Indexes are used to speed up searches/queries.
 An index provides direct and fast access to rows in a table.
 Indexes are created explicitly Or automatically.

Types of index:

a) Simple Index
b) Composite Index
c) Unique Index.

A) SIMPLE INDEX:

An index which is is created on only one/ single column of a table is called as simple
index.

Syntax:

CREATE INDEX index_name ON table_name (column_name);

Example:
CREATE INDEX index1 on employee (empno);

b) Composite Index:
An index which is created on two or more columns of a table is called composite index

Syntax :

CREATE INDEX index_name ON table_name (column1, column2);

Example:
CREATE INDEX index2 on employee (ename,salary);

c) Unique Indexes:

An index which is created on column which contains unique values(i.e column does
not contain duplicate values) is called as unique index.
Sou.Venutai Chavan Polytechnic, Pune Page 44
 Ms.S.A.Kshirsagar DMS Notes SYCM-3-K

Unique indexes are used not only for performance, but also for data integrity. A unique
index does not allow any duplicate values to be inserted into the table.

Syntax :

CREATE UNIQUE INDEX index_name on table_name (column_name);

Example:
CREATE UNIQUE INDEX index3 on employee (eno);
Create unique index index4 on student(rollno);

Q create unique ndex on hospital (h_no, h_name,city).

Create unique index index5 on hospital(h_no);

Alter index:

By using alter index we can rename/change name of index.

Syntax:
Alter index old_index_name rename to new_index_name;

Example:
alter index index1 rename to index9;

Drop Index:
By using drop index, Indexes can be deleted permanently
Syntax:
Drop index index_name;

Example:
Drop index index9;

Sou.Venutai Chavan Polytechnic, Pune Page 45

 Ms.S.A.Kshirsagar DMS Notes SYCM-3-K

Synonyms:
Synonym is another name given to the table, view, sequence, stored procedure,
function or packages for the user’s convenience to use it.

Create Synonyms:
Syntax:
Create synonym synonym_name for object_name;
Example:
Create synonym emp for employee;

Drop Synonyms:
Syntax:
Drop synonym synonym_name;
Example: Drop synonym emp;

Differentiate between view and index.

Parameter View Index

Definition A view is a virtual table, An index is a schema
through which a selective object that can speed up
portion of data from one the retrieval of rows by
or more tables can be using pointer
seen
Function View restrict access to Index provides direct
data such that user can and fast access to rows
see and modify exactly in a table
what they need and no
more.
Syntax CREATE VIEW Create index
view_name AS index_name on
SELECT column1, Table_name
column2..... (coloumn_name);
FROM table_name
WHERE [condition];
Example CREATE VIEW create index s_index
CUSTOMERS_VIEW on Student(roll_no);
AS SELECT name, age
FROM CUSTOMERS;

Sou.Venutai Chavan Polytechnic, Pune Page 46

 Ms.S.A.Kshirsagar DMS Notes SYCM-3-K

ASSIGNMENT NO 3A
PREVIOUS MSBTE QUESTIONS
Question Exam Mark
s
Explain DCL commands with syntax and example.
List DCL commands. S-19 2M
1 List DCL commands S-19 2M
Describe Grant and Revoke commands with example. S-23 4M
Explain TCL Commands with syntax and example.
2 Explain savepoint and rollback command with example. S-23 4M
Describe commit and rollback with syntax and example. W-18 4M
Explain commit and rollback with syntax and example. W-23 4M
3 Explain any four aggregate functions with example.
Enlist four aggregate functions. W-18 2M
Explain any four aggregate functions with example. S-19 4M
Explain any four aggregate function with example. S-23 4M
State the use of Avg function with example. (S-22) 2M
4 List date and time functions with example.
List date and time functions. S-24 2M
5 Explain string functions with example.
List any four string functions in SQL. W-19 2M
Explain any four string functions with example. W-18 4M
Explain any four string functions. S-22 4M
Explain any four string functions with example. S-23 4M
List any four string functions in SQL. W-23 2M
6 Enlist arithmetic and logical SQL operators with example.
Enlist arithmetic and logical SQL operators. W-22 2M
7 Explain Set operators with example.
Explain set operators with example. S-24 4M
Explain any four set operators in SQL with example. W-19 4M
Explain set operators with example. W-18 4M
8 List the SQL operations and explain range searching operation S-19 4M
‘between’ and pattern matching operator ‘like’ with example.
9 Define View. Write and explain syntax to create , update and
drop view commands with example. State view purpose.

Sou.Venutai Chavan Polytechnic, Pune Page 47

 Ms.S.A.Kshirsagar DMS Notes SYCM-3-K

Write syntax for creating and dropping views. W-22 2M

Write and explain syntax for creating view with example. S-19 4M
Describe Views and write a command to create view. W-19 4M
Describe the concept of view with example. State its purpose S-22 4M
Explain views with example. S-23 4M
Write and explain create, update and drop view commands W-23 4M
10 Define index. Explain Its types. Write how to create index an
drop index syntax with example.
Create simple and composite index. Write command to drop W-19 4M
above index. S-24 6M
Create simple and composite index. Write command to drop S-19 6M
index. W-19 4M
Create simple and composite index. Write command to drop S-22 4M
above index. W-22 4M
Define index. Explain it’s types.
Write syntax for i) Create Index ii) Drop Index
Write and Explain the syntax for creating and dropping
indexes with an example.
11 Explain all DDL commands with syntax and example.
Describe create & alter command with syntax & example. W-19 4M
List four DDL commands with syntax. W-18 2M
Explain any two DDL commands along with example. S-22 4M
List any two DDL commands with syntax S-23 2M
Write syntax for creating and Renaming a table. W-22 2M
12 Explain difference between delete and truncate command with S-19 4M
example. S-24 4M
Differentiate between delete and truncate command with
example
13 Explain DML commands with syntax and example.
Explain any four DML commands with syntax and example. S-23 4M
List DML commands with its syntax. (Any Four) S-23 4M
Enlist DML commands. W-19 2M
14 Explain types of joins with syntax and example.
Explain joins in SQL with examples.(W-18) W-18 4M
Enlist types of SQL Joins. W-23 2M
Explain any two types of join with example S-23 6M
Explain any four joins if SQL with examples. S-24 4M
15 Explain group by,having,where,order by clause with syntax
and example

Sou.Venutai Chavan Polytechnic, Pune Page 48

 Ms.S.A.Kshirsagar DMS Notes SYCM-3-K

Write query using group by and having clause (use students S-24 4M
database) W-22 4M
State the use of group by and order by clauses.
16 Define Synonym. Explain how to create and drop synonym
with syntax and example.
Write and explain syntax for creating and dropping synonyms S-24 4M
with example S-19 2M
Write syntax for creating synonyms with example.
17 Write and Explain the syntax for creating, Altering and W-22 6M
dropping the sequence.
18 Explain subquery/nested query concept with example. S-23 2M
19 List any four data types in SQL. S-22 2M

ASSIGNMENT 3B(TUTORIAL)
Sr. Question Exam Marks
No
Consider the table Student (name, marks, dept, age, place, S-19 6M
phone, birthdate) Write SQL query for following :
(i) To list students having place as ‘Pune’ or ‘Jalgaon’.
(ii) To list students having same department (dept) as
1 that of ‘Rachana’.
(iii) To change marks of ‘Rahul’ from 81 to 96.
(iv) To list student name and marks from ‘Computer’
dept.
(v) To list student name who have marks less than 40.
(vi) To list students who are not from ‘Mumbai’.
(i) Write a command to create table student (RNo., S-19 6M
2 name, marks, dept.) with proper datatype and RNo
as primary key.
(ii) Write a command to create and drop sequence.
Write a command to create table student (rollno, Stud_name, W-19 6M
branch, class, DOB, City, Contact_no) and write down
queries for following :
(i) Insert one row into the table
3 (ii) Save the data
(iii) Insert second row into the table
(iv) Undo the insertion of second row
(v) Create save point S1 .
(vi) Insert one row into the table.
Sou.Venutai Chavan Polytechnic, Pune Page 49
 Ms.S.A.Kshirsagar DMS Notes SYCM-3-K

Consider following schema : EMP (empno, deptno, ename, W-19 6M

salary, designation, join_date, DOB, dept_location). Write
down SQL queries for following :
(i) Display employees name & number in decreasing
order of salary.
4 (ii) Display employee name & employee number
whose designation is Manager.
(iii) Display age of employees with ename.
(iv) Display total salary of all employees.
(v) Display employee names having deptno as 20 and
dept_location is Mumbai.
(vi) Display name of employee who earned lowest
salary.
Consider the structure for book table as Book-Master W-19 6M
(bookid, bookname, author, no_of copies, price) Write down
SQL queries for following
(i) Write a command to create Book_master table.
5 (ii) Get authorwise list of all books.
(iii) Display all books whose price is between ` 500 & `
800.
(iv) Display all books with details whose name start
with ‘D’.
(v) Display all books whose price is above ` 700.
(vi) Display all books whose number of copies are less
than 10.
Consider the following database Employee(emp_id, W-18 6M
emp_name, emp_city, emp_addr, emp_dept, join_date) (W-
6 18)
i) Display the emp_id, of employee who live in city
‘Pune’ or ‘Nagpur’.
ii) Change employee name, ‘Aayush’ to ‘Aayan’.
iii) Display the total number of employee whose dept is
50.
Consider the following schema Depositor (ACC_no, Name, W-18 6M
7 PAN, Balance) Create a view on Depositor having attributes
(ACC_no, PAN) where balance is greater than 100000 (W-
18)
Create a sequence (W-18) W-18 6M
i) Sequence name is Seq _ 1, Start with 1, increment by 1,
8 minimum value 1, maximum value 20.
Sou.Venutai Chavan Polytechnic, Pune Page 50
 Ms.S.A.Kshirsagar DMS Notes SYCM-3-K

ii) Use a seq_1 to insert the values into table Student (ID
Number (10), Name char (20));
iii) Change the seq_ 1 max value 20 to 50. iv) Drop the
sequence
Consider the schema Customer (Cust-id, Cust_name, W-22 6M
Cust_addr, Cust_city)
9 i) Create a view on Customer (Cust_id, Cust_name)
where Cust_city is ‘Pune’
ii) Create a sequence on Cust_id
i) Create table Student (S_id, S_name, S_addr, S_marks) S-22 6M
with proper data type and size.
10 ii) Insert row (5, ‘ABC’, ‘RRRRR’, 79) into student table.
iii) Update marks of student 85 where S_id is 5
Consider the following table employee (Emp_id, Emp_name, S-22 6M
Emp_age)
i) Display details of employees whose age is less than 30.
ii) Display details of employees whose age is in between
the range 30 to 60.
11 iii) Display total number of employee whose age is 60.
iv) Display names of employees whose name starts with 'S'.
v) Display details of employees whose name end with 'd'.
vi) Display details of employees whose age is greater than 50
and whose name contain 'e'
Consider following schema” S-24 6M
Book-master (bookid, bookname, author, no_of_copies,
price)
write down SQL queries for following:
12 i) Write a command to create Book_master table.
ii) Increase the price of all books by 20%
iii) Display all books whose price ranges from 500 to 800.
iv) Display all books with details whose name start with ‘D’
v) Dsipaly all books whose number of copies are less than 10.
vi) Display all books whose price is above 700.
13 Create simple and composite index. Write command to drop S-24 6M
index.
i) Write a query to create table emp-details (Emp_ID, name, S-24 6M
dept) with proper data types and Emp_ID as primary key and
14 name as NOT NULL constrain.
ii) Perform all DML command on above created table.
Write SQL queries for following :– W-22 4M
Sou.Venutai Chavan Polytechnic, Pune Page 51
 Ms.S.A.Kshirsagar DMS Notes SYCM-3-K

i) Create table student with following attributes using suitable

15 data types. Roll no., as primary key, name, marks as not
null and city.
ii) Add column Date of Birth in above student table.
iii) Increase the size of attribute name by 10 in above student
table.
iv) Change name of Student table to stud
Write the SQL queries for following EMP table. Emp W-22 6M
(empno, deptno, ename, salary, designation, city.)
i) Display average salary of all employee.
ii) Display names of employees who stay in Mumbai or
Pune.
16 iii) Set the salary of employee ‘Ramesh’ to 50000.
iv) Display names of employees whose salaries are less than
50000.
v) Remove the Record of employees whose deptno is 10.
vi) Remove the column deptno from EMP table.
Write SQL queries for following. Consider table stud (roll no, W-22 6M
name, sub1, sub2, sub3)
i) Display names of student who got minimum mark in sub1.
ii) Display names of students who got above 40 marks in
sub2.
iii) Display count of Students failed in sub2.
17 iv) Display average marks of sub1 of all students.
v) Display names of students whose name start with ‘A’ by
arranging them in ascending order of sub1 marks.
vi) Display student name whose name ends with ‘h’ and
subject 2 marks are between 60 to 75
Write SQL queries for following. W-22 6M
i) Create user named ‘user1’ having Password ‘1234’
18 ii) Assign ‘insert’ and ‘update’ Privilege to ‘user1’.
iii) Remove update Privilege assigned to the user1.
iv) Assign the resource Permission to user1
Write SQL query for the following consider table. S-23 6M
Emp(emp_id, dept, emp_name, salary, designation,
joining_date,
DOB, city)
i) List employees with having alphabate ‘A’ as second letter
19 in their name.
ii) Set salary of all ‘project leaders’ to 50,000.
Sou.Venutai Chavan Polytechnic, Pune Page 52
 Ms.S.A.Kshirsagar DMS Notes SYCM-3-K

iii) Display average salary of all employees.

iv) Display employee-Id of employees who live in city ‘Pune’
or ‘Nagpur’.
v) Display employee name, salary and city from ‘HR’
department who having salary greater than 70,000.
vi) Display details of all employee who’s salary same as that
of salary of ‘Shashank’.
Consider schema of book table as Book_Master (book_id, S-23 6M
book_name, author, no_of_copies, price)
Write down the SQL queries for the following.
20 i) Write a query to create table Book_Master table.
ii) Write a command to create composite index on
Book_Master table.
iii) Write a command to drop above index.
iv) Display book name and author in decreasing order of
price.
v) Display all books whose number of copies are greater than
50.
vi) Create synonym for relation Book_Master as
Book_Information
Write a command to create table Customer (Custo_id, W-23 6M
Cust_name,
address, Contact_no, City). And write down the queries for
21 the
following:
i) Insert one row into table.
ii) Save the data.
iii) Insert Second row into the table.
iv) Create save point S1.
v) Insert one row into the table

Sou.Venutai Chavan Polytechnic, Pune Page 53