Mrs.
Pranali Neeraj Kale SYCM-II (DMS-22319)
Unit 3: Interactive SQL and Advance SQL:SQL Performance Tunning
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-: a function that is built into an application and can be accessed by
end users.madefunction
a)Single row function:
i.String function
ii.Date and time function
iii.Arithmetic function
b)Multi row/group function
i. Aggregate function
String functions:
Sou.Venutai Chavan Polytechnic,Pune Page 1
Mrs.Pranali Neeraj Kale SYCM-II (DMS-22319)
Date and time functions:
Sou.Venutai Chavan Polytechnic,Pune Page 2
Mrs.Pranali Neeraj Kale SYCM-II (DMS-22319)
7) Sysdate: returns system date.
e.g select sysdate from dual;
Arithmetic function:
1)ABS-this function returns the absolute value of given number.
E .g: select abs(-250) from dual;
o/p:250
2)CEIL:
Returns the smallest integer greater than or equal to passed number.
Eg:select ceil(48.876) from dual;
o/p:49
3)floor:
Returns the greater integer smaller than or equal to passed number.
Eg:select ceil(48.876) from dual;
o/p:48
4)exp(n):
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
Sou.Venutai Chavan Polytechnic,Pune Page 3
Mrs.Pranali Neeraj Kale SYCM-II (DMS-22319)
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;
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: selectsign(12) from dual;
o/p: 1
10)SIN(b):
It returns the sine of angle
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
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(page no.5)
1)avg(expression):
this function computes average of column.
e.g:select avg(sal) from employee;
o/p:6166.67
Sou.Venutai Chavan Polytechnic,Pune Page 4
Mrs.Pranali Neeraj Kale SYCM-II (DMS-22319)
2)count(expression):
Counts the row 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(sal) from employee;
o/p:10000
5)sum(expression)
This function returns the sum of the whole column of table
e.g: select sum(sal) from employee;
o/p:370000
QUERIES USING GROUP BY, HAVING AND 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)Group By:
The group by clause can be used in a select statement to collect data across multiple records
and group by one or more columns.
It is used to divide the rows in a table into groups.
This clause allows multiple columns to be grouped so aggregate functions may be performed on
Multiple columns with one command.
The syntax is:
Sou.Venutai Chavan Polytechnic,Pune Page 5
Mrs.Pranali Neeraj Kale SYCM-II (DMS-22319)
1. Select col1,col2,….,colnaggregate_function (expression) From table_name Where
condition Group by column_name;
Example: Display average salary of each department o/p: deptn avg(sal)
o
Select deptno ,avg(sal) from emp group by deptno; 11 5000
22 6000
33 5000
44 10000
ena Count(e
2)Display number of employees with name o/p: me no)
Select ename, count(eno) from employee group by ename; a 2
b 2
c 1
3)display maximum,minimum salary ,sum and number of employees with d 1
name of for each name.
Select ename,count(eno),max(sal),min(sal) from employee group by ename;
o/p:
enam Count(eno) Max(sal) Min(sal)
e
A 2 5000 4000
B 2 6000 3000
C 1 9000 9000
D 1 10000 10000
2)Having clause
It can be used in a select statement to filter the records.
Having clause is used in combination with the group by clause
This like a where clause, except that it involves a summary value, rather than a column value.
The having clause operates after the summation has been done.
A where clause specifies the rows in the table to be included in the summation.
The syntax is :
Select col1,col2,….,col n aggregate_function (expression) From table_name Where condition
Group by column_nameHaving condition 1,condition2;
Example: Display the department number whose total salary is greater than
deptn sum(sal)
15000 Select deptno, sum(sal) from employee group by deptno having
o
sum(sal)>15000;
22 12000
33 10000
44 10000
Sou.Venutai Chavan Polytechnic,Pune Page 6
Mrs.Pranali Neeraj Kale SYCM-II (DMS-22319)
2)Display employee names having average salary is greater than 5000 ename avg(sal)
Select ename,avg(sal) from employee group by ename having avg(sal)>5000; C 9000
D 10000
3)Display department nos having total salary is more than 10000.
Select deptno,sum(sal) from employee group by deptno having sum(sal)>10000;
o/p:no rows are selected
3)Order by clause:
This clause is used for getting the result in particular order i.e. by
descending or ascending order. deptn Sum(sal)
By default the order is ascending. o
To sort the records in a descending order, you can use the DESC 22 12000
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: En Ename Deptno sal
1. SELECT * FROM employeeORDER BY deptno; o
or 1 a 11 5000
SELECT * FROM employee ORDER BY deptno ASC ;
2 b 22 3000
3 c 22 9000
It will display the details of employee table in ascending order of
4 a 33 4000
deptno.
5 b 33 6000
6 d 44 10000
En Ename Deptno sal
2. SELECT * FROM employee ORDER BY sal DESC; o
6 d 44 10000
3 c 22 9000
Sou.Venutai Chavan Polytechnic,Pune Page 7 5 b 33 6000
1 a 11 5000
4 a 33 4000
2 b 22 3000
Mrs.Pranali Neeraj Kale SYCM-II (DMS-22319)
It will display the details of employee table in descending order of salary.
SQL JOINS
A join clause is used to combine rows from two or more tables based on a related/same column
between them.
Consider the tables,
Student Result
Rollno Name Address Rollno Percentage
1 Priya Pune 1 90
2 Riya Mumbai 3 88
4 Raju Baner 4 85
6 Krushna Pune 9 75
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
1)INNER Join(equi joins):
This is a simple JOIN which is the result is based on matched data/equalities is called equi/inner
join.
In equi join comparison operator “=” is used to perform a join.
It returns records that have matching values in both the tables.
Sou.Venutai Chavan Polytechnic,Pune Page 8
Mrs.Pranali Neeraj Kale SYCM-II (DMS-22319)
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)TheSQL 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 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;
Rollno Name address Rollno Percentage
1 Priya Pune 1 90
4 Raju Baner 4 85
2 Riya Mumbai
6 Krushna pune
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:
Sou.Venutai Chavan Polytechnic,Pune Page 9
Mrs.Pranali Neeraj Kale SYCM-II (DMS-22319)
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;
Rollno Name address Rollno Percentage
1 Priya Pune 1 90
4 Raju Baner 4 85
3 88
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:
SELECTcolumn_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
6 Krushna Pune
3 88
9 75
Subquery:
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
Sou.Venutai Chavan Polytechnic,Pune Page 10
Mrs.Pranali Neeraj Kale SYCM-II (DMS-22319)
SYNTAX:
Select column_name_listFromtable_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
2)Asubquery 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)insubquery use multiple rows IN operator.
5)BETWEEN not used in subquery.
View:
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,andvise versa.
Advantages of Views:
1. Views restrict access to the data because the view can display selective columns from the
table.
Sou.Venutai Chavan Polytechnic,Pune Page 11
Mrs.Pranali Neeraj Kale SYCM-II (DMS-22319)
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.
Syntax for creating view.
Create view viewname as select query;
OR
Create view viewname as select col1,col2,…col_n from table_name where condition;
Example :
Create viewemp_info as select Eno, Ename from Employee;
Above will create emp_info view with eno and ename columns.
2) create view as empdetails with ename and salary of employee table whose salary more than
6000.
Create view empdetails as select ename,sal from employee where sal>6000;
Updating view:
View can be used to update data from the base table as well as view.
Syntax:
Update view_name set column_name=value where condition;
Example:
Update emp_info set sal=sal+1000 where eno=3;
It will update the salary of enmployee no 3 increasing by 1000 to its salary
DROP VIEW
It Drops the specified view.
Syntax:
DROP VIEWView_Name;
Sou.Venutai Chavan Polytechnic,Pune Page 12
Mrs.Pranali Neeraj Kale SYCM-II (DMS-22319)
Example:
DROP VIEW emp_info;
It will drops the emp_info 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;
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;
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:
A simple index is one that is created on only one/ single column of a table.
Syntax:
CREATE INDEX index_nameONtable_name (column_name);
Example:
Sou.Venutai Chavan Polytechnic,Pune Page 13
Mrs.Pranali Neeraj Kale SYCM-II (DMS-22319)
CREATE INDEX ename_idx on employee (ename);
b) Composite Index:
A composite index is an index created on two or more columns of a table.
Syntax :
CREATE INDEX index_name ONtable_name (column1, column2);
Example:
CREATE INDEX en_idxon employee (eno, ename,);
c) Unique Indexes:
An index which is created on column which contains unique values is called as unique index.
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 eno_idx on employee (eno);
Altering indexes:
In altering you can rename indexes as shown below:
Syntax:
Alter index index_name rename to new_index_name;
Example: alter index eno_idx rename to enum_idx;
Dropping Indexes:
Indexes can be deleted permanently
Sequences:
Sou.Venutai Chavan Polytechnic,Pune Page 14
Mrs.Pranali Neeraj Kale SYCM-II (DMS-22319)
Definition: A sequence is a database object that is capable of generating unique and sequential
integer values.
Oracle provides an object called as a Sequence that can generate numeric values.
Thevalue 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 a sequence :
Create sequence sequence_name
start with initial_value
increment by increment_value
Minvalueminimum_value
Maxvaluemaximum_value
cycle/ nocycle;
where,
SEQUENCE_NAME: name of sequence
INITIAL_VALUE: starting value of sequence
INCREMENT BY:Specifies the intervalvalue between sequence number. It can be any positive
or negative value but not zero.
MINIVALUE: Specifies the sequence minimum value.
MAXVALUE: Specifies the maximum value that a sequence can generate.
CYCLE: when sequence reaches its limit it start from beginning
NOCYCLE: Specifies that a sequence cannot generate more values after reaching the
maximum value.
Sou.Venutai Chavan Polytechnic,Pune Page 15
Mrs.Pranali Neeraj Kale SYCM-II (DMS-22319)
Example :
Create sequence seq_1increment by 1 start with 1 minivalue 1 maxvalue 999 cycle;
Insert Command in sequence
Syntax:
Insert into table_namevalues(sequence_name.nextval,value1,value2,…value_n);
Example:Suppose table is student(rollno,name)
Insert values into table student by using sequence as:
insert into student values(seq_1.nextval,’ram’); Rollno name
1 ram
insert into student values(seq_1.nextval,’neha’); 2 neha
Note:the nextval keyword is used to place values sequentially.
Currval keyword is used to return the current value.
Example:
Select seq_1 currval from student;
Altering a sequence
A sequence once created can be altered. This is achieved by using the ALTER
SEQUENCE statement.
SYNTAX:
ALTER SEQUENCE Sequence_Namevariable_name value;
Example:
Alter sequence seq_1 maxvalue 15;
It will change the maximum value of seq_1 from 999 to 15
.
Dropping A 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 16
Mrs.Pranali Neeraj Kale SYCM-II (DMS-22319)
Q. Create sequence for ‘student’ table.
Sequence for ‘student’ table
create sequence student_seqstart with 1 increment by 1
minvlaue 1maxvalue60 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;
Synonyms:
Synonym is another name given to the table, view, sequence, stored procedure, function or
packages for the user’s convenience to use it.
Creating Synonyms:
Syntax:
Create synonym synonym_name for object_name;
Create synonym employee1 for employee;
Dropping Synonyms:
Syntax:
Drop synonym synonym_name;
Drop synonym employee1;
Sou.Venutai Chavan Polytechnic,Pune Page 17
Mrs.Pranali Neeraj Kale SYCM-II (DMS-22319)
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;
Consider the structure for book table as Book-master = {bookid,
bookname, subcode-author, no_of copies, price} Write SQL
queries for following:
(i) Display total no. of books for subject ‘DBM’
(ii) Get authorwise list of all books.
(iii) Display all books whose prices are between Rs.200 and
Rs.500
(iv) Display all books with details whose name start with ‘S’
(Note: Queries with any other correct logic shall be considered)
(i)select sum(no_of Copies) from Book_master where subcode=‘ DBM’;
(ii) Select SUM(no_of Copies),author from Book_master group by (author);
(iii) Select bookname from book_master where price>=200 and price <=500;
(iv) select * from book_master where bookname like ‘S%’;
Sou.Venutai Chavan Polytechnic,Pune Page 18
Mrs.Pranali Neeraj Kale SYCM-II (DMS-22319)
MSBTE QUESTION:
Explain any four string ,date and time functions with example.
Explain any 4 aggregate functions.(refer notes)
Explain with example group by ,order by and having clause.
(For each clause - Explanation - 1 mark, syntax/example - 1 mark) Ans: Group by clause
Explain Inner join and Outer join with example.()
(Any one outer join should be considered)
How to create view?
(Syntax OR Example of view - 2 Marks)
What is view? 2M
With the help of example, explain DROP VIEW command.
What are sequence? Why it is used? Create sequence for STUDENT table.
(Definition - 1 mark; Use - 1 mark; creating valid sequence example/pattern - 2 marks)
What are the various operations with respect to sequences?
(Sequence explanation – 2 Marks, any two operations – 2 Marks)
What are synonyms? How to create and drop synonym?
(Definition - 1 Mark, Syntax / Example - 1 ½ Marks)
What is index ? Explain types of index.
(Index Definition - 2 marks; Any Two Types - 1 mark each)
Consider the structure for book table as Book-master = {bookid,
bookname, subcode-author, no_of copies, price} Write SQL
queries for following:
(i) Display total no. of books for subject ‘DBM’
(ii) Get authorwise list of all books.
(iii) Display all books whose prices are between Rs.200 and
Rs.500
(iv) Display all books with details whose name start with ‘S’
(Note: Queries with any other correct logic shall be considered)
Differentiate between view and index.
Sou.Venutai Chavan Polytechnic,Pune Page 19
Mrs.Pranali Neeraj Kale SYCM-II (DMS-22319)
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)
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.
ASSIGNMENT NO. 3
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.
Differentiate between view and index.
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.
Consider the structure for book table as Book-master = {bookid,
bookname, subcode-author, no_of copies, price} Write SQL
queries for following:
(i) Display total no. of books for subject ‘DBM’
(ii) Get authorwise list of all books.
(iii) Display all books whose prices are between Rs.200 and
Rs.500
(iv) Display all books with details whose name start with ‘S’
What are the various operations with respect to sequences?Create sequence for STUDENT
table.
What are synonyms? How to create and drop synonym?
What is view With the help of example, explain create view and DROP VIEW command.
Explain Inner join and Outer join with example.
Explain with example group by ,order by and having clause.
Sou.Venutai Chavan Polytechnic,Pune Page 20
