DMS Unit 3: Interactive SQL and Advance SQL: SQL Performance Tuning

(14 Marks)

CO3: C3-22319-3: Create and manage database using SQL functions and
advanced SQL commands.
Institute Vision:
To provide quality technical education to rural students.
Institute Mission:
M1: To impart trainings for developing hands on skills.
M2: To develop technical skills by practicing the best teaching-learning pedagogies.
M3: To enhance career opportunities through industry connect.

Department of Computer Technology: Vision and Mission

Program Vision:
To provide quality technical education in the computer engineering field that prepares the rural
students for socio-economic development
Program Mission:
M1: To impart technical skills to solve problems in the field of computer engineering.
M2: To mould the students as computer professionals through the teaching - learning process.
M3: To create career opportunities through industry interaction and higher education.

Department of Computer Technology: PSOs (Program Specific Outcomes)

PSO1: Use concepts of fundamental engineering, hardware and software to solve engineering
problems.
PSO2: Design and develop quality software product for multidisciplinary industry.
PSO3: Apply programming skills to meet the growing needs of computer profession.

Department of Computer Technology: PEOs (Program Educational Objectives)

PEO1: Provide programming skills to develop solutions for the ever changing information
technology field.
PEO2: Adapt broad based programming technologies to work in multidisciplinary work
environments.
PEO3: Create opportunities for higher education through teaching learning process.

1
 In-Built Functions in SQL:
A. STRING FUNCTIONS:
String is a collection of characters. Ex: computer, etc.

String functions are used to perform the operations on string.

Consider the stud table below:

ROLLNO FNAME LNAME FEES

1 anil dighe 1000
2 sunil verma 2000

1. Initcap:
Initcap function is used to convert first letter of each word to capital letter.
Syntax: select initcap (string) from dual;
select initcap (columname) from tablename;
Ex: select initcap ('computer department') from dual;
Output: Computer Department
Ex: select initcap (fname) from stud;
Output: Anil
Sunil

2. Concatenate:
Concat function is used to join two strings or two columns from table together.
Syntax: select concat (string1, string2) from dual;
select concat (column1, column2) from tablename;
Ex: select concat ('computer','-dept') from dual;
Output: computer-dept
Ex: select concat (fname,lname) from stud;
Output: anildighe
sunilverma

3. Replace:
Replace function is used to replace the old string with new string.
Syntax: select replace (oldstring, newstring) from dual;
2
 SQL> select replace('black','bl','p') from dual;
Output: pack

4. Translate:
Translate function converts the string from one-character set to another.
It replaces the occurrences of single character with another character.
Syntax: select translate (‘string’,’char1’,’char2’) from dual;
Ex: select translate('asadaf','a','z') from dual;
Output: zszdzf

5. Lower:
Lower function converts the uppercase characters to lowercase characters.
Syntax: select lower (string) from dual;
select lower (columnname) from tablename;
Ex: select lower('COMPUTER') from dual;
Output: computer
Ex: select lower(fname) from stud;
Output: anil
sunil

6. Upper:
converts the string to all uppercase characters.
Upper function converts the lowercase characters to uppercase characters.
Syntax: select upper (string) from dual;
select upper (columnname) from tablename;
Ex: select upper (‘computer’) from dual;
Output: COMPUTER
Ex: select upper (fname) from stud;
Output: ANIL
SUNIL

7. Substring (substr):
Substring function is used to display some portion of the main string.
Syntax: select substr (string, starting_position, length) from dual;

3
 select substr (columnname, starting_position, length) from tablename;
Ex: select substr ('computer',1,4) from dual;
Output: comp
Ex: select substr (fname,1,2) from dual;
Output: an
su

8. Ltrim:
Ltrim function used to trim (delete) the given left side characters.
Syntax: select ltrim (string, char) from dual;
select ltrim (columnname, char) from tablename;
Ex: select ltrim ('computer','comp') from dual;
Output: uter
Ex: select ltrim (fname,'a') from stud;
Output: nil

9. Rtrim:
Rtrim function used to trim (delete) the given right side characters.
Syntax: select rtrim (string, char) from dual;
select rtrim (columnname, char) from tablename;
Ex: select rtrim ('computer','uter') from dual;
Output: comp
Ex: select rtrim (fname,'il') from stud;
Output: an
sun

4
10. Lpad:
Lpad function used for left side padding with given symbol and convert the string in given
length.
Syntax: select lpad (string, length, symbol) from dual;
select lpad (columnname, length, symbol) from tablename;
Ex: select lpad ('computer',10,'*') from dual;
Output: **computer
Ex: select lpad (fname,10,'*') from stud;
Output: ******anil
*****sunil

11. Rpad:
Rpad function used for right side padding with given symbol and convert the string in
given length.
Syntax: select rpad (string, length, symbol) from dual;
select rpad (columnname, length, symbol) from tablename;
Ex: select rpad ('computer',10,'*') from dual;
Output: computer**
Ex: select rpad (fname,10,'*') from stud;
Output: anil******
sunil*****

12. Length:
Length function used to calculate the length of the given string.
Syntax: select length (string) from dual;
select length (columnname) from tablename;
Ex: select length('computer') from dual;
Output: 8
Ex: select length(fname) from stud;
Output: 4
5

5
B. ARITHMETIC FUNCTIONS:
1. ABS:
ABS function converts negative value in positive value (absolute).
Ex: select abs (-250) from dual;
Output: 250

2. CEIL:
CEIL function used to round off the given value to its next complete number.
Ex: select ceil (48.876) from dual;
Output: 49
Ex: select ceil (4.00001) from dual;
Output: 5

3. FLOOR:
FLOOR function used to round off the given value to its previous complete number.
Ex: select floor (48.876) from dual;
Output: 48
Ex: select floor (4.00001) from dual;
Output: 4

4. MOD:
MOD function is used to display remainder after division.
Ex: select mod (10,3) from dual;
Output: 1
Ex: select mod (19,5) from dual;
Output: 4

5. POWER:
POWER function used to calculate the power of the given value.
Ex: select power (5,2) from dual;
Output: 25
Ex: select power (4,3) from dual;
Output: 64

6
6. SQRT:
SQRT function used to calculate the square root of the given value.
Ex: select sqrt (25) from dual;
Output: 5
Ex: select sqrt (49) from dual;
Output: 7

C. AGGREGATE FUNCTIONS:
Aggregate functions are used to perform the operations on numeric values.
Aggregate functions are:
Sum
Avg
Count
Min
Max
Consider the emp table below:
EMPNO ENAME SAL
102 prathamesh patil 25000
101 ashish deshpande 20000
105 aditya adhav 15000

1. avg:

Avg function used to calculate average of given values in column.

Ex: select avg (sal) from emp;

Output: 20000

2. count / count(*):

Count function is used to calculate records of table (count(*)) or given column count().
Ex: select count (*) from emp;
Output: 3
Ex: select count (ename) from emp;
Output: 2

7
3. min:

Min function used to find out the least (minimum) value in given column.

Ex: select min (sal) from emp;

Output: 15000

4. max:

Max function used to find out the greatest (maximum) value in given column.

Ex: select max (sal) from emp;

Output: 25000

5. sum:

Sum function used to calculate summation of given values in column.

Ex: select sum (sal) from emp;

Output: 60000

Miscellaneous Functions

USER: displays the user name.

SQL> select user from dual;
Output: SYSTEM

NVL: is used with fields where instead of displaying null values we want to display zeros.

SQL> select nvl(sal,0) from emp_details;

VSIZE: shows the number of bytes used for expression.

SQL> select vsize(ename)from emp_details where empno=102;

8
D. DATE FUNCTIONS:
Date functions are used to display or perform the operation on date.

1. SYSDATE:

Sysdate function is used to display the current (today) date.

Ex: select sysdate from dual;

2. ADD_MONTHS:

Add_months function used to add months in the given date.

Ex: select add_months (sysdate,2) from dual; (add 2 months in the current date)

Ex: select add_months ('23-mar-2012',2) from dual;

3. LAST_DAY:

Last_day function used to display the last day of the month as per given date.

Ex: select last_day (sysdate) from dual;

Ex: select last_day ('23-aug-2023') from dual;

Ex: select last_day ('23-may-2003') from dual;

4. MONTHS_BETWEEN:

Months_between function used to find out the difference of the months between two
dates.

Ex: select months_between('23-may-2013',sysdate) from dual;

Ex: select months_between(sysdate,'23-may-2013') from dual;

5. NEXT_DAY:

Next_day function used to return the date of the given weekday after specified date.

Ex: select next_day (sysdate,'friday') from dual;

9
6. ROUND FUNCTION:

Rounds given date to the day or month or year as per specified date.

SQL> select round(sysdate,'day')from dual; //sysdate: 01/09/2023

ROUND(SYSDATE)
--------------------------
01-SEP-23

SQL> select round(sysdate,'month')from dual;

ROUND(SYSDATE)
-----------------------
01-SEP-23

SQL> select round(sysdate,'year')from dual;

ROUND(SYSDATE)
-----------------------
01-JAN-24

7. TRUNC FUNCTION:

Used to return the date with the time portion of the day truncated to unit specified in the
format.

SQL> select trunc(sysdate,'day')from dual; //sysdate: 01/09/2023

TRUNC(SYSDATE)
-----------------------
01-SEP-23

SQL> select trunc(sysdate,'month')from dual;

TRUNC(SYSDATE)
----------------------
01-SEP-23

10
SQL> select trunc(sysdate,'year')from dual;

TRUNC(SYSDATE)
------------------------
01-JAN-23

8. GREATEST:

Used to find out the greatest date among the given dates.

SQL> select greatest(sysdate,'23-aug-2013')from dual;

GREATEST(sysdate,'23-aug-2013')
-------------------------------------------------
01-SEP-23

SQL> select greatest('23-aug-2013','23-aug-2012')from dual;

GREATEST('23-aug-2013','23-aug-2012')
--------------------------------------------------------
23-aug-2013

11
 DATE CONVERSION FUNCTIONS
TO_CHAR():

Used to convert given date into character

SQL> select sysdate from dual;

SYSDATE
---------------
25-AUG-23

SQL> select to_char(sysdate,'dd/mm/yy')from dual;

TO_CHAR(sysdate,'dd/mm/yy')
---------------------------------------------
25/08/23

SQL> select to_char(sysdate,'dd"th" Month YYYY') from dual;

TO_CHAR(SYSDATE,'DD"TH"MONTHYYYY')
----------------------------------------------
01th September 2023

TO_DATE():

Used to convert given date into standard date format.

SQL> select to_date('25 aug 2023','dd/mm/yy')from dual;

TO_DATE('25 aug 2013','dd/mm/yy')

--------------------------------------------------
25-AUG-23

* CONCATENATION OPERATOR (||):

Used to join the string(s) to column.

Ex: Display output as:

The employee anil joined on 30/08/2022 and working in HR department having salary 2000

Select ‘The employee’||ename||’ joined on ‘||joindate||’ and working in ‘||deptname||’

department having salary ‘||sal from emp where ename=’anil’;

12
 CLAUSES (where, Group by, having, order by)
SQL> create table emp_details(empno number(3) primary key,ename varchar(20),sal
number(7,2),comm number(7,2),deptno number(3),job varchar(20),hiredate date);
Table created.
SQL> select * from emp_details;

EMPNO ENAME SAL COMM DEPTNO JOB HIREDATE

102 prathamesh patil 25000 10000 2 director 10-jun-99
106 priyanka pawar 12000 2500 3 clerk 19-mar-03
101 ashish deshpande 20000 5000 1 manager 10-apr-00
105 aditya adhav 15000 2600 2 engineer 15-feb-01
107 bhakti joshi 15000 7000 4 engineer 27-sep-07
109 aniruddha thipse 18000 9000 5 sr. clerk 30-apr-09
110 rajesh oak 16000 10000 6 engineer 01-aug-10

GROUP BY
Group by clause is used to collect the data as multiple records and group them to produce the
result.

SQL> select avg(sal) from emp_details where deptno=2 group by deptno;

AVG(SAL)
----------------
20000

SQL> select avg(sal) from emp_details where deptno=1 group by deptno;

AVG(SAL)
-------------------
20000

SQL> select job,count(*) from emp_details group by job;

JOB COUNT(*)
-------------------- ----------
director 1
clerk 1
engineer 3
sr. clerk 1
manager 1

13
SQL> select deptno,count(*) from emp_details group by deptno;

DEPTNO COUNT(*)
---------- -------------------
1 1
6 1
2 2
4 1
5 1
3 1
6 rows selected.

HAVING CLAUSE
Having clause is used to execute the summary query. It is like the “where” clause, difference is
that it gives summary value (used with aggregate functions) instead of column value.

SQL> select job,count(*) from emp_details group by job having avg(sal)>15000;

JOB COUNT(*)
-------------------- ----------
director 1
engineer 3
sr. clerk 1
manager 1

SQL> select deptno,sum(sal) as "Total salary" from emp_details group by deptno having
sum(sal)>15000;

DEPTNO Total salary

---------- ----------------------------------
1 20000
6 16000
2 40000
5 18000

SQL> select deptno,count(*) as "No. of emp" from emp_details group by deptno having
count(*)>1;
DEPTNO No. of emp
---------- ------------------------
2 2

14
SQL> select deptno,count(*) as "No. of emp" from emp_details group by deptno having
count(*)=1;

DEPTNO No. of emp

---------- -----------------------
1 1
6 1
4 1
5 1
3 1

ORDER BY CLAUSE
To view the data in sorted order, order by clause is used. By default, the data is sorted in
ascending order.

SQL> select empno,ename from emp_details order by deptno;

EMPNO ENAME
---------- ----------------------------------
101 ashish deshpande
102 prathamesh patil
105 aditya adhav
106 priyanka pawar
107 bhakti joshi
109 aniruddha thipse
110 rajesh oak
7 rows selected.

SQL> select deptno from emp_details order by deptno;

DEPTNO
----------------
1
2
2
3

7 rows selected.

SQL> select deptno from emp_details order by deptno desc;

DEPTNO
----------
6
5
4
3
15
 JOINS
Joins are used to join two or more tables

SQL> create table emp_details(empno number(3) primary key,ename varchar(20),sal

number(7,2),comm number(7,2),deptno number(3),job varchar(20),hiredate date);

Table created.

SQL> select * from emp_details;

EMPNO ENAME SAL COMM DEPTNO JOB HIREDATE

102 prathamesh patil 25000 10000 2 director 10-jun-99
106 priyanka pawar 12000 2500 3 clerk 19-mar-03
101 ashish deshpande 20000 5000 1 manager 10-apr-00
105 aditya adhav 15000 2600 2 engineer 15-feb-01
107 bhakti joshi 15000 7000 4 engineer 27-sep-07
109 aniruddha thipse 18000 9000 5 sr. clerk 30-apr-09
110 rajesh oak 16000 10000 6 engineer 01-aug-10

SQL> create table dept_details(deptno number(1),dname varchar(10),city varchar(10),minsal

number(7,2),maxsal number(7,2));

Table created.

SQL> select * from dept_details;

DEPTNO DNAME CITY MINSAL MAXSAL

---------- ---------- ---------- ---------- ------------------------------------------------------------
7 account pune 10000 12000
3 computer loni 13000 15000
5 IT mumbai 18000 22000
4 mechanical mumbai 12000 16000
8 sales loni 22000 28000
9 design nagpur 25000 29000
6 r&d chennai 23000 27000

16
SIMPLE JOIN
It joins two tables based on the common fields.

SQL> select empno,ename,dname,city from emp_details,dept_details where

emp_details.deptno=dept_details.deptno;

EMPNO ENAME DNAME CITY

---------- -------------------- ---------- --------------------------------------------------
106 priyanka pawar computer loni
107 bhakti joshi mechanical mumbai
109 aniruddha thipse IT mumbai
110 rajesh oak r&d chennai

NON-EQUI JOIN (INNER JOIN)

Joins two or more tables. Matches the column values from different tables based on inequality
values.

SQL> select a.empno,a.ename,b.city from emp_details a,dept_details b where a.sal>=b.minsal

and a.sal<=b.maxsal;

EMPNO ENAME CITY

---------- -------------------- ----------------------------------------------
106 priyanka pawar pune
105 aditya adhav loni
107 bhakti joshi loni
101 ashish deshpande mumbai
109 aniruddha thipse mumbai
106 priyanka pawar mumbai
105 aditya adhav mumbai
107 bhakti joshi mumbai
110 rajesh oak mumbai
102 prathamesh patil loni
102 prathamesh patil nagpur
102 prathamesh patil chennai

12 rows selected.

17
SELF JOIN
Joining the table to itself.

SQL> select a.empno,a.ename,a.sal from emp_details a,emp_details b where

a.comm+10000>b.sal and a.empno=b.empno;

EMPNO ENAME SAL

---------- -------------------- -------------------------------------
106 priyanka pawar 12000
107 bhakti joshi 15000
109 aniruddha thipse 18000

OUTER JOIN
It is extension to the simple join. It joins two tables same as simple join but with + operator.

SQL> select empno,ename,dname,city from emp_details,dept_details where

emp_details.deptno=dept_details.deptno(+);

EMPNO ENAME DNAME CITY

---------- -------------------- ---------- ------------------------------------------------
106 priyanka pawar computer loni
109 aniruddha thipse IT mumbai
107 bhakti joshi mechanical mumbai
110 rajesh oak r&d chennai
101 ashish deshpande
105 aditya adhav
102 prathamesh patil

7 rows selected.

SUB-QUERIES (NESTED QUERIES)

The queries written in queries are called as sub-queries.

Ex: Display the records from table whose deptno belongs to city chennai.

SQL> select * from emp_details where deptno=(select deptno from dept_details where
city='chennai');

18
 **For below topics use the following table
SQL> create table emp_details(empno number(3) primary key,ename varchar(20),sal
number(7,2),comm number(7,2),deptno number(3),job varchar(20),hiredate date);
Table created.
SQL> select * from emp_details;

EMPNO ENAME SAL COMM DEPTNO JOB HIREDATE

102 prathamesh patil 25000 10000 2 director 10-jun-99
106 priyanka pawar 12000 2500 3 clerk 19-mar-03
101 ashish deshpande 20000 5000 1 manager 10-apr-00
105 aditya adhav 15000 2600 2 engineer 15-feb-01
107 bhakti joshi 15000 7000 4 engineer 27-sep-07
109 aniruddha thipse 18000 9000 5 sr. clerk 30-apr-09
110 rajesh oak 16000 10000 6 engineer 01-aug-10

VIEWS
Once the table is created and records are inserted, the access to that table cannot be given
partially.
i.e. if user want to access selected columns or records from the table it is not possible. For this
the number of tables with the selected columns needs to be created. But this is the lengthy
process.
Instead of creating number of new tables, a table can be made accessible to several users at a
time by using VIEWS.
View can be defined as a logical part in which the user can have selected columns or records.
View can be created with the help of SELECT statement.
Operations (insert, delete, update, select) executed on VIEW can be same as Table.

CREATE VIEW
Syntax: create view <view_name> as select <column_name1>,<column_name2>,….. from
<table_name>;

SQL> create view v1 as select empno, ename from emp_details;

View created.

SQL> select * from v1;

EMPNO ENAME
---------- -----------------------------------
102 prathamesh patil
106 priyanka pawar
101 ashish deshpande
105 aditya adhav
107 bhakti joshi
109 aniruddha thipse
110 rajesh oak

19
SQL> create view v2 as select sal, comm, deptno from emp_details;

View created.

SQL> select * from v2;

SAL COMM DEPTNO

---------- ---------- -------------------
25000 10000 2
12000 2500 3
20000 5000 1
15000 2600 2
15000 7000 4
18000 9000 5
16000 2600 6

RENAME the column in View

The columns of the view can be renamed.

SQL> create view v3 as select ename "Employee Name", job "Designation" from emp_details;

View created.

SQL> select * from v3;

Employee Name Designation
-------------------- -----------------------------
prathamesh patil director
priyanka pawar clerk
ashish deshpande manager
aditya adhav engineer
bhakti joshi engineer
aniruddha thipse sr. clerk
rajesh oak engineer

20
INSERT record into View
Views are used to insert the records in the base tables.
If new record is inserted in the view that same record can also be inserted in base table.

SQL> insert into v4 values (112,'parag joshi', 21000, 3000, 7,'director','19-dec-2013');

1 row created.

SQL> select * from v4;

EMPNO ENAME SAL COMM DEPTNO JOB HIREDATE

---------- -------------------- ---------- ---------- ------------------------------ ----------------------------------------------------
102 prathamesh patil 25000 10000 2 director 10-JUN-99
106 priyanka pawar 12000 2500 3 clerk 19-MAR-03
101 ashish deshpande 20000 5000 1 manager 10-APR-00
105 aditya adhav 15000 2600 2 engineer 15-FEB-01
107 bhakti joshi 15000 7000 4 engineer 27-SEP-07
109 aniruddha thipse 18000 9000 5 sr. clerk 30-APR-09
110 rajesh oak 16000 6 engineer 01-AUG-10
112 parag joshi 21000 3000 7 director 19-DEC-13

SQL> select * from emp_details;

EMPNO ENAME SAL COMM DEPTNO JOB HIREDATE

---------- -------------------- ---------- ---------- ------------------------------ ----------------------------------------------------
102 prathamesh patil 25000 10000 2 director 10-JUN-99
106 priyanka pawar 12000 2500 3 clerk 19-MAR-03
101 ashish deshpande 20000 5000 1 manager 10-APR-00
105 aditya adhav 15000 2600 2 engineer 15-FEB-01
107 bhakti joshi 15000 7000 4 engineer 27-SEP-07
109 aniruddha thipse 18000 9000 5 sr. clerk 30-APR-09
110 rajesh oak 16000 6 engineer 01-AUG-10
112 parag joshi 21000 3000 7 director 19-DEC-13

The same record can be inserted in other related views as well as base table. As below-

SQL> select * from v2;

SAL COMM DEPTNO

---------- ---------- ----------------------
25000 10000 2
12000 2500 3
20000 5000 1
15000 2600 2
15000 7000 4
18000 9000 5
16000 6
21000 3000 7

21
SQL> select * from v1;

EMPNO ENAME
---------- --------------------
102 prathamesh patil
106 priyanka pawar
101 ashish deshpande
105 aditya adhav
107 bhakti joshi
109 aniruddha thipse
110 rajesh oak
112 parag joshi

SQL> select * from v3;

Employee Name Designation

-------------------- --------------------
prathamesh patil director
priyanka pawar clerk
ashish deshpande manager
aditya adhav engineer
bhakti joshi engineer
aniruddha thipse sr. clerk
rajesh oak engineer
parag joshi director

DELETE the record from view

To delete the record from the view the DELETE command is used.
If the record is deleted from the specific view then that record will be deleted from the base table as
well as the related views.

SQL> select * from v2;

SAL COMM DEPTNO

---------- ---------- -------------------------
25000 10000 2
12000 2500 3
20000 5000 1
15000 2600 2
15000 7000 4
18000 9000 5
16000 6

SQL> delete from v2 where sal =25000;

1 row deleted.

22
SQL> select * from v2;

SAL COMM DEPTNO

---------- ---------- -------------------
12000 2500 3
20000 5000 1
15000 2600 2
15000 7000 4
18000 9000 5
16000 6

UPDATE the record in view

To update the existing record in the view the UPDATE command is used.
That updated record in the specific view can be reflected in other related views as well as in the
base table.

SQL> update v2 set sal=sal+2000;

DROP the view

To drop the view, DROP command is used.

SQL> drop view v2;

View dropped.

23
 SEQUENCES
A sequence generates unique and sequential integer values.
A sequence may have different orders like ascending or descending.

CREATE Sequence
Syntax: create sequence <sequence_name> incremented by <number> start with <number>
maxvalue <number> minvalue <number> cycle/nocycle cache <number>/nocache

incremented by <number> used to specify the interval between sequence numbers.

start with <number> states the first number of the sequence.
maxvalue <number> states the maximum value of sequence.
minvalue <number> states the minimum value of sequence.
Cycle indicates that the sequence will be continued for generating the values after it reaches
the maximum value.
Cache is used to pre-allocate a set of sequence numbers and keep numbers in the memory to
access.
Nocache cannot pre-allocate a set of sequence numbers.

SQL> create sequence seq1 increment by 1 start with 1 maxvalue 10 minvalue 1 cycle cache 9;
Sequence created.

SQL> select seq1.nextval from dual;

NEXTVAL
---------------
1

SQL> select seq1.nextval from dual;

NEXTVAL
------------------
2
nextval is used to determine the next value in the sequence.

SQL> select seq1.currval from dual;

CURRVAL
----------------
2
currval is used to determine the current value in the sequence.

Use of sequence in SQL statement

SQL> create table emp1(empno number(5),ename varchar(30),sal number(7,2));
Table created.

SQL> insert into emp1 values(seq1.nextval,'abhishek',25000);

1 row created.

24
SQL> insert into emp1 values(seq1.nextval,'avinash',24000);
1 row created.

SQL> insert into emp1 values(seq1.nextval,'vasim',23000);

1 row created.

Instead of inserting the empno value in INSERT INTO command, we use the created sequence
(seq1.nextval). It automatically assign the values to empno as per the sequence.

SQL> select * from emp1;

EMPNO ENAME SAL
---------- ------------------------------ --------------------
3 abhishek 25000
4 avinash 24000
5 vasim 23000

ALTERING the sequence

To modify the created sequence we use the ALTER command.

SQL> alter sequence seq1 maxvalue 12;

Sequence altered.

SQL> select seq1.nextval from DUAL;

NEXTVAL
---------------
11

SQL> select seq1.nextval from DUAL;

NEXTVAL
----------------
12

DROP the Sequence

To drop the sequence the DROP command is used.
SQL> DROP SEQUENCE SEQ1;

Sequence dropped.

25
 INDEX
Indexes are used to access the records from the tables easily and fast.

SIMPLE INDEX
The index created on single column is simple index.
Syntax: create index <index_name> on <table_name>(column_name);

SQL> create index idx on emp_details(ename);

Index created.

COMPOSITE INDEX
The index created on multiple columns is composite index.
Syntax: create index <index_name> on table_name>(column_name1,column_name2…);

SQL> create index idx3 on emp_details(comm,job);

Index created.

UNIQUE INDEX
Unique index is created on single or multiple columns which having the unique domain values.
Syntax: create unique index <index_name> on table_name>(column_name1,column_name2…);

SQL> create unique index idx1 on emp_details(sal);

Index created.
Here the index will be created if ‘sal’ column contains only unique values (no repeated values).

DROP Index
To drop the index from the datebase the DROP command is used.
Syntax: drop index <index_name>;

SQL> drop index idx;

Index dropped.

26
 SNAPSHOTS
Snapshots are used to create the recent copy of the table.
Snapshot may contain all records from the table or selected rows and columns from the table.
Using snapshots, we can easily monitor the changes made in the table step by step.

CREATE Snapshot
Syntax: create snapshot <snapshot_name> as <select query>;

SQL> create snapshot snap1 as select * from emp_details where deptno=10;

Materialized view created.

SQL> select * from snap1;

EMPNO ENAME SAL COMM DEPTNO
---------- -------------------- ---------- ---------- ------------------ ----------------------------
3 abhishek 17000 2000 10
4 abhishek 17000 2000 10

DROP Snapshot
To drop the snapshot from the database the DROP command is used.
Syntax: drop snapshot <snapshot_name>;

SQL> drop snapshot snap;

Materialized view dropped.

27
 SYNONYM
Synonym is another name given to the table, view, sequence, stored procedure, function for
the user’s convenience to use it.
Instead of typing the large length name we can use synonym to give short name to the table.

CREATE Synonym
Syntax: create synonym <synonym_name> for <table_name>;

SQL> create synonym e for emp_details;

Synonym created.

SQL> select * from e;

DROP Synonym
To drop the synonym the DROP command is used.
Syntax: drop synonym <synonym_name>;

SQL> drop synonym e;

Synonym dropped.

28
 Assignment

1. Consider the structure emp (empno, ename, job, joindate, sal, comm, deptno)
Dept (empno, ename, deptno)
Write SQL queries for the following:
a. Display the names of employee excluding ‘manager’.
b. Display average salaries of each department.
c. Display the details of employee whose names second letter is ‘a’.
d. Find the employee who belongs to deptno 10 and 30 (don’t use between operator)
e. Find out the number of employees who works as ‘manager’.
f. Change the salary of ‘rahul’ to 50000.
g. Display the output as
the employee RAHUL works as MANAGER in deptno 10 having salary 50000 rupees.
h. Display the details of employee whose salary greater than rahul’s salary.
i. Delete the records of employees whose joindate is before 10-dec-2007.
j. Display the joindate from table in the form of DECEMBER 16, 2006.
k. Display the names of employees in capital letter who have salary greater than
20000.
l. Create the user ABC and give him full access on ‘emp’ table.
m. Create the emp table as above and apply constraints as- empno primary key, ename
not null, deptno unique, sal check constraint.
n. Create dept table as above and apply foreign key.
o. Display annual salary of each employee.
p. Display the number of employees of deptno 10.
q. Display ename, empno from emp and ename, deptno from dept which having
deptno same.
r. Display the employees deptno wise.
s. Display the date and time of all employees from emp table.
2. What is the use of ‘where’ and ‘between’ clause? Explain with example.
3. Explain how aggregate function work with ‘group by’ clause.
4. Define the use of clauses in SQL. (2)
5. Explain the ‘order by’ clause in SQL with example.
6. List and explain four arithmetic operators in SQL with example.
7. Explain inner join and outer join.
8. Describe string function, date and time function.
9. What are views? Give its syntax and example.
10. What is snapshot? Explain with example.
11. Explain the importance of synonym with example.
12. Explain index with its types.
13. What are sequences? Explain with example.

29