CONTENTS

Ex.No. Exercise Page Date of Completion Signature

No of Staff

SQL INTRODUCTION
1 DDL COMMANDS

2 DML&TCL COMMANDS

3 INTEGRITY CONSTRAINTS

4 SQL OPERATORS

5 SQL FUNCTIONS

6 SQL GROUP FUNCTIONS

7 SET OPERATIONS & JOINS

8 SUBQUERIES

9 DCL COMMANDS

10 OTHER DB OBJECTS

PL/SQL

11 BASIC PL/SQL BLOCKS

12 CURSORS

13 TRIGGERS

14 EXCEPTION HANDLING

15 SUB PROGRAMS
16 PACKAGES
 Ex. No.1 DDL STATEMENTS Date:

1. CREATE TABLE COMMAND

● A table is a unit of storage which holds data in the form of rows and columns.
● In a table,
● A unique column name should be specified.
● Proper data type along with its width should be specified.
● ‘Not null’ condition can be included when needed, by default it is ‘Null’.
Syntax:create table <table name> (column definition1, column definition 2, …);

Example: create table student (regno number (11), name varchar2 (25), addr varchar2(25),
dept varchar2 (3));
Output:
Table created.

2. ALTER TABLE COMMAND

● This command is used to add a new column, modify the existing column definition,
include or drop integrity constraints.
Syntax: alter table <table name> modify (column definition …);
alter table <table name> add (column definition …);
alter table <table name> add (column definition …);

Example:
I. alter table student modify (name varchar2 (30));
Output:
Table altered.
II. alter table student add (comments long);
Output:
Table altered.

3. TRUNCATE TABLE COMMAND

● The truncate command deletes all rows from the table. Only the structure of the table
remains.
Syntax: Truncate table <table name>;

Example: Truncate table student;

Output:
Table truncated.

4. DESC COMMAND
 ● This command will display the structure of the table.
Syntax: Desc <table name>;

Example: Desc student;

Output:
Name Null? Type
---------------------------------- -------- ----------------------------
REGNO NUMBER(11)
NAME VARCHAR2(30)
DEPT VARCHAR2(3)
COMMENTS LONG

5. DROP TABLE COMMAND

● The drop table command is used to delete the table permanently from the database.
Syntax: Drop table <table name>;

Example: Drop table student;

Output:
Table dropped.

Q1. Create the tables DEPT and EMP as described below.

DEPT
Column Name DataType Description
DNO Number Department
DNAME Varchar Department Name
LOC Varchar Department Location

EMP
Column Name DataType Description
ENO Number Employee Number
ENAME Varchar Emp Name
JOB Char Designation
MGR Number Manager EMPno
HIREDATE Date Date of Joining
SAL Number Basic Salary
COMM Number Commission
DEPTNO Number Department Number

SQL> create table dept(deptno number,dname varchar2(25),loc varchar2(25));

Table created.

SQL> create table empl(empno number,ename varchar(25),job char(15),mgr number,hiredate

date, sal number(9,2),comm number(7,2),deptno number);

Table created.

Q2. Confirm Table Creation

SQL>desc dept

Name Null? Type

----------------------------------------- -------- ----------------------------
DEPTNO NUMBER
DNAME VARCHAR2(25)
LOC VARCHAR2(25)

SQL> desc empl

Name Null? Type

----------------------------------------- -------- ----------------------------
EMPNO NUMBER
ENAME VARCHAR2(25)
JOB CHAR(15)
MGR NUMBER
HIREDATE DATE
SAL NUMBER(9,2)
COMM NUMBER(7,2)
DEPTNO NUMBER
Q3.List names of tables created by user.
SQL>select * from tab;

Q4. Modify the size of the column LOC by 15 in the DEPT table.

SQL> alter table dept modify(loc char(30));

Table altered.

Q5.Add new columns COMNT and MISCEL in the DEPT table.

SQL> alter table dept add(comnt varchar(25),miscel varchar(25));

Table altered.

Q6. Drop the column COMNT from the table.

SQL> alter table dept drop column comnt;

Table altered.

Q7.Rename the table DEPT to DEPT1

SQL> rename dept to dept1;

Table renamed.

Q8. Delete the table from the database.

SQL> drop table dept1;

Table dropped.

Ex. No.2 DML & TCL Date:

DATA MANIPULATION LANGUAGE

1. INSERT COMMAND
● Used to add one or more rows to a table.
● While using this command the values are separated by commas and the data types char
and date are enclosed in apostrophes.
● The values must be entered in the same order as they are defined in the table.
Syntax:
● To insert all column values for each row
SQL> Insert into <table_name> values (a list of data values);
● To insert specific column values for each row
SQL> Insert into <table_name> (col_names) values (list of values);

2. SELECT COMMAND
● Used to perform a query. The query is a request for information.
Syntax: Select column_name … from table_name … where conditions [order by
column_name …];
● To select only specific columns, specify the column names instead of * in the select
command.

3. UPDATE COMMAND
● Used to alter the column values in a table.
● Specific rows could be updated based on a specific condition.
Syntax: Update table_name set field = value, … where condition;
● The ‘where’ clause and the ‘set’ clause can also include queries.

4. DELETE COMMAND
● Used to delete one or more rows to a table.
Syntax: Delete from <table_name> where conditions;

TRANSACTION CONTROL LANGUAGE (TCL) COMMANDS

● A transaction is a logical unit of work.
● All changes made to the database between commit and / or rollback operations can be
referred to as a transaction.
● A transaction begins with an executable SQL statement and ends explicitly with either
rollback or commit statements and implicitly, i.e., automatically, when a DDL statement is
used.
1. COMMIT – Used to end a transaction and transaction changes
are made permanent to the database.
– Erases all savepoints in the transaction thus releasing the transaction locks.
Syntax: Commit work; (or) Commit;
Output:
Commit complete.
2. ROLLBACK – Used to undo the work done in the current
transaction.
– Rollback the entire transaction so that all changes made by SQL statements
are undone or rollback a transaction to a savepoint so that the SQL
statements after the savepoint are rolled back.
Syntax:Rollback to savepoint savepoint_id;
Output:
Rollback complete.

3. SAVEPOINT – Savepoints are like markers to divide a very lengthy

transaction to smaller ones.
– Used to identify a point in a transaction to which it can be rolled back later.
– Used in conjunction with rollback, to rollback portions of the current
transaction.
Syntax:Savepoint savepoint_id;
Output:
Savepoint created.
Data for EMP table
Empno ENAME JOB MGR HIREDAT SAL COMM DEPTN
O
7369 SMITH CLLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMA 7698 20-FEB-91 1600 300 30
N
7521 WARD SALESMA 7698 22-FEB-81 1250 500 30
N
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTI SALESMA 7698 28-SEP-81 1250 1400 30
N N
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20

Data for DEPT table

DEPTNO DNAME LOC
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIOS BOSTON
Q1. Insert the rows of Dept table.
SQL> insert into dept values(10,'ACCOUNTING','NEW YORK');
1 row created.
SQL> insert into dept values(20,'RESEARCH','DALLAS');
1 row created.
Q2. Insert rows into EMP table using substitution variables.
SQL> insert into empl
values(&empno,'&ename','&job',&mgr,'&hiredate',&sal,&comm,&deptno);
Enter value for empno: 7369
Enter value for ename: smith
Enter value for job: clerk
Enter value for mgr: 7902
Enter value for hiredate: 17-dec-80
Enter value for sal: 800
Enter value for comm: 0
Enter value for deptno: 20
old 1: insert into empl
values(&empno,'&ename','&job',&mgr,'&hiredate',&sal,&comm,&deptno)
new 1: insert into empl values(7369,'smith','clerk',7902,'17-dec-80',800,0,20)
1 row created.

Q3. Create a table MANAGER with the columns mgr-id, name, salary and hiredate

SQL> create table mgr(mgr_id number(5) primary key,name varchar(25),sal number(5),hiredate date);

Table created.

Q4. Insert values into manager table by copying values from emp table where the
designation is ‘MANAGER’.
SQL> insert into mgr select mgr,ename,sal,hiredate from empl where job='manager';

1 row created.

Q5. Change the Loc of all the rows of DEPT table by ‘New York’.

SQL> update dept set loc='NEW YORK';

2 rows updated.

Q6. Change the LOC=’DALLAS” for deptno=20 in DEPT table.

SQL> update dept set loc='Dallas' where deptno=20;
1 row updated.

Q7.Delete the rows from EMP table whose employee name=’Paul’;

SQL> delete from empl where ename='Paul';
1 rows deleted.

Q8.List all the rows and columns of the table DEPT.

SQL>SQL> select * from dept;
DEPTNO DNAME LOC
---------- ------------------------- ------------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH Dallas

Q9. List the name and salary of the employees.

SQL> select ename,sal from empl;
ENAME SAL
------------------------- ----------
smith 800
jones 2975

Q10. Without duplication, list all the names of the department in DEPT table.
SQL> select distinct ename from empl;
ENAME
-------------------------
jones
smith
Q11. Find out the name of the employee whose empno is 7369.
SQL> select ename from empl where empno=7369;
ENAME
----------
smith

Q12. As a copy of DEPT table, create DEPT1 table using select command.

SQL> CREATE TABLE dept1 AS SELECT * FROM dept;

Table Created

Q13. List ename and sal of EMP table with the column headings NAME and SALARY

SQL> select ename as Name, sal as SALARY from empl;

NAME SALARY
------------------------- ----------
smith 800
jones 2975

Q14. Change LOC=’CHICAGO’ for deptno=30 in DEPT table and COMMIT the
transaction.

SQL> update dept set loc=’Chicago’ where detptno=30;

SQL>commit;

Q15.Delete all rows in EMP table and rollback the transaction.

SQL>Delete from emp;

SQL> Rollback

Q16. Do the following operations one after another

a) Change LOC=’BOSTON’ for deptno=40 in DEPT table

SQL>update dept set loc=’boston’ where deptno=40

b) Create SAVEPOINT in the name ‘update_over’

SQL>savepoint update_over;
 c) Insert another row in DEPT table with your own values

SQL>insert into dept values(50,’Purchase’,’Spain’);

SQL>1 Row inserted

d) Rollback the transaction upto the point ‘update_over’.

SQL>Rollback to update_over;
SQL>Rollback complete
 Ex. No.3 INTEGRITY CONSTRAINTS Date:

An integrity constraint is a mechanism used by Oracle to prevent invalid data entry into
the table, by enforcing rule for the column in a table.

1. Domain Integrity Constraints – Maintains value according to the specifications.

● Not Null Constraint
● Check Constraint
2. Entity Integrity Constraints – To identify each row in a table uniquely, this constraint is
used.
● Unique Constraint
● Primary Key Constraint –
3. Referential Integrity Constraints – To establish a ‘parent–child’ relationship between two
tables having a common column, make use of referential integrity constraints.

Q1.Add not null constraint to the columns ename and job of emp table.
SQL>alter table empl modify(ename varchar2(25) not null, job char(30) not null);
Table Altered
Q2. Add Primary key constraint to the column empno of emp table.
SQL>alter table empl add constraint emp_pk primary key(empno);
Table Altered
Q3. Add Primary key constraint to the column dno of dept table.
SQL> SQL>alter table dept add constraint dept_pk primary key(deptno);
Table Altered
Q4.Add unique constraint to the column dname of dept table.
SQL> alter table dept add constraint chk unique(deptno);
Table altered.
Q5.Add check constraints to the table EMP to restrict the values of empno lies between
7000 and 8000.
SQL>Alter table empl add constraint emp_ck check(empno between 7000 and 8000)
Table Alterd
Q6. Add Foreign key constraint to the column deptno of emp table references deptno of
dept table.
SQL>alter table emp add constraint emp_fk foreign key(deptno) references dept(deptno);
Table altered.
Q7. Remove the primary key constraint on the DEPT table and drop the associated foreign
key constraint on emp.deptno column.
SQL>alter table dept drop primary key cascade;
Table altered
Q8. Disable the primary key constraint of the EMP table.
SQL>alter table empl disable constraint emp_pk cascade;
Table alterd
Q9. View all the user constraint definitions and its names.
SQL>select * from user_constraints where table_name=’emp’;
 Ex. No.4 SQL OPERATORS Date:

Arithmetic Operators
+Addition
-Subtraction
*Multiplication
/Division

Comparison Operators
=Equal to
<>Not Equal to
<Less than
>Greater than
<=Less than or equal to
>=Greater than or equal to
IN (List)Match any of list of values
LIKEMatch a character pattern (% any no. of characters, -
IS NULLIs a null value
BETWEEN…AND…Between two values

Logical Operators
ANDReturns TRUE if both component conditions are TRUE
ORReturns TRUE if either component condition is TRUE
NOTReturns TRUE if the following condition is FALSE

Concatenation Operator ( || )
Concatenates the Columns of any data type.
A Resultant column will be a Single column.

Order by Clause
Sort rows specified by the order ASC / DESC
SELECTcolumn1, column2, … …
FROM table
ORDER BY sort-column DESC;
Sorts table by sort-column in descending order
Omitting the keyword DESC will sort the table in ascending order

Q1. Update all the records of Manager table by increasing 10% of their salary as bonus.

SQL> update mgr set sal=sal+sal*10/100;

1 row updated.

Q2. Delete the records from Manager table whose salary less than 2750.

SQL> delete from mgr where sal<2750;

1 rows deleted.
Q3.List concatenated values of name and designation of each employee.

SQL> select ename || job from empl;

ENAME||JOB
----------------------------------------
smithclerk
jonesmanager

Q4.List the names of Clerk from emp table.

SQL>

Q5.List the details of employeed who joined before 30Sep81.

SQL> select ename from empl where job='clerk';

ENAME
-------------------------
Smith

Q6.List the names of employees whose empno are 7369,7839,7934,7788

SQL> select ename from empl where empno in (7369,7839,7934,7788);

ENAME
-------------------------
Smith

Q7.List the names of employees who are not managers.

SQL> select ename from empl where job<>'manager';

ENAME
-------------------------
smith

Q8. List different designations in the company.

SQL> select job from empl;

JOB
---------------
clerk
manager
salesman

Q9.List the names of employees who joined between 30Jun81 AND 31Dec81.

SQL> select ename from empl where hiredate between '30-JUN-81' and '31-DEC-81';

no rows selected
Q10. List the names of employees who are not eligible for commission.

SQL> select empno,ename from empl where comm=0 or comm is NULL;

EMPNO ENAME
---------- -------------------------
7369 smith
7566 jones

Q11.List the employees whose name either starts or ends with ‘s’.

SQL> select ename from empl where ename like 's%' or ename like '%s';

ENAME
-------------------------
smith
jones

Q12.List the names of employees who have second letter as ‘i’.

SQL> select ename from empl where ename like '_i%';

ENAME
-------------------------
Miller

Q13. Sort the emp table in ascending order by hiredate and display the details.

SQL> select * from empl order by hiredate;

EMPNO ENAME JOB MGR HIREDATE SAL COMM

------ ---------------------- --------------- ---------- --------- ---------- ---------- ----------
7369 smith clerk 7902 17-DEC-80 800 0
7566 jones manager 7839 02-APR-81 2975 0

Q14. Sort the emp table in descending order of the annual salary.

SQL> select * from empl order by sal desc;

EMPNO ENAME JOB MGR HIREDATE SAL

-------- ------------------------- --------------- ---------- --------- ------- -- --------
7566 jones manager 7839 02-APR-81 2975
7369 smith clerk 7902 17-DEC-80 800
Q15. List ename, deptno and sal after sorting emp table in ascending order by deptno
and then descending order by sal.

SQL> select ename,deptno,sal from empl order by deptno,sal desc;

ENAME DEPTNO SAL
------------------------- -------------------------
jones 20 2975
smith 20 800
 Ex. No.5 SQL FUCNTIONS Date:

SINGLE ROW FUNCTIONS

1. DATE FUNCTIONS

I. ADD_MONTHS –Returns a data after adding a specified date with the specified
number of months.

Syntax: add_months (d, n)

Example: select add_months ('07-Feb-08', 2) from dual;

Output:
ADD_MONTH
------------------
07-APR-08

II. LAST_DAY – Returns the date corresponding to the last day of the month.

Syntax: last_day (d)

Example: select sysdate, last_day (sysdate) from dual;

Output:
SYSDATE LAST_DAY
------------- --------------
07-FEB-08 29-FEB-08

III. MONTHS_BETWEEN – Returns the number of months between two dates.

Syntax: months_between (d1, d2)
Example: select months_between ('01-Apr-08', '01-Feb-08') from dual;
Output:
MONTHS_BETWEEN ('01-APR-08', '01-FEB-08')
--------------------------------------------------------------
2
IV. NEXT_DAY
Syntax: next_day (d, day)
Example: select next_day ('07-Feb-08', 'Thursday') from dual;
● The Thursday that immediately follows the date '07-Feb-08' will be displayed.
Output:
NEXT_DAY (
------------------
14-FEB-08
V. ROUND – Returns the date which is rounded to the unit specified by the format model.
Syntax: round (d, [fmt])

Example:1)select round (to_date ('01-Jul-08', 'dd-Mon-yy'), 'year') from dual;

 Output:
ROUND (TO_
------------------
01-JAN-09
2) select round (to_date ('16-Feb-08', 'dd-Mon-yy'), 'month') from dual;

Output:
ROUND (TO_
------------------
01-MAR-08
3) select round (to_date ('07-Feb-08', 'dd-Mon-yy'), 'day') from dual;

Output:
ROUND (TO_
------------------
10-FEB-08
4) select round (to_date ('07-Feb-08', 'dd-Mon-yy')) from dual;

Output:
ROUND (TO_
------------------
07-FEB-08

VI. TRUNCATE – Returns the date with the time portion of the day
truncated to the unit specified by format model.

Syntax: trunc (d, [fmt])

Example: 1) select trunc (to_date ('01-Jul-08', 'dd-Mon-yy'), 'year') from dual;

Output:
TRUNC (TO_
------------------
01-JAN-08
2) select trunc (to_date ('16-Feb-08', 'dd-Mon-yy'), 'month') from dual;
Output:
TRUNC (TO_
------------------
01-FEB-08
3) select trunc (to_date ('07-Feb-08', 'dd-Mon-yy'), 'day') from dual;
Output:
TRUNC (TO_
------------------
03-FEB-08
4) select trunc (to_date ('07-Feb-08', 'dd-Mon-yy')) from dual;
Output:
TRUNC (TO_
------------------
07-FEB-08
 VII. GREATEST – Returns the latest date present in the argument.

Syntax:greatest (d1, d2, …)

Example: select greatest (to_date ('07-Feb-08', 'dd-Mon-yy'), to_date ('10-Jul-08',

'dd-Mon-yy'), to_date ('15-Jun-08', 'dd-Mon-yy')) from dual;
Output:

GREATEST
-----------------
10-JUL-08

VIII. Arithmetic Operations on Date Values:

1) date + number (adds number of days to date)
2) date – number (subtracts number of days from date)
3) date – date (subtracts dates, producing number of days)

2. CHARACTER FUNCTIONS

FUNCTION INPUT OUTPUT

Initcap (char) select initcap (‘hello’) from dual; Hello

Lower (char) select lower (‘FUN’) from dual; fun

Upper (char) select upper (‘sun’) from dual; SUN

Ltrim (char, set) select ltrim (‘xyzadams’, ‘xyz’) from dual; adams

Rtrim (char, set) select rtrim (‘xyzadams’, ‘ams’) from dual; xyzad

Translate (char, from, to) select translate (‘jack’, ‘j’, ‘b’) from dual; back

Replace (char, select replace (‘jack and jue’, ‘j’, ‘bl’) from
black and blue
searchstring, [rep string]) dual;

Substr (char, m, n) select substr (‘abcdefg’, 3, 2) from dual; cd

I. SOUNDEX – Returns words that are spelled differently, but sound alike.

Example: select * from sample where soundex (name) = soundex ('rajaa');

● Returns ‘Raja’, if ‘Raja’ is present in sample table. The structure of sample table is:
Name Null? Type
---------------------------------- -------- ----------------------------
ROLLNO NUMBER(3)
NAME VARCHAR2(30)
 Output:
ROLLNO NAME
------------ ------------------------------
1 Raja
3 Raja
I. CHR – Returns the character value for the number given within the braces.

Example: select chr (68) from dual;

● Returns the value ‘D’ which is the character equivalent of the number 68.
Output:
C
--
D
II. LPAD

Example: select lpad ('function', 15, '=') from dual;

● The output gives the sign ‘=’ before the word function. The entire string is 15 in
length after the padding is done.
Output:
LPAD ('FUNCTION'
--------------------------
= = = = = = =function
III. RPAD – Pads the value given to the right of the given string.

Example: select rpad ('function', 15, '=') from dual;

Output:
RPAD ('FUNCTION'
--------------------------
function= = = = = = =
3. NUMERIC FUNCTIONS
FUNCTION INPUT OUTPUT
Abs select abs (–15) from dual; 15
Ceil (n) select ceil (44.778) from dual; 45
Cos (n) select cos (180) from dual; –.59846007
Cosh (n) select cosh (0) from dual; 1
Exp (n) select exp (4) from dual; 54.59815
Floor (n) select floor (100.75) from dual; 100
Power (m, n) select power (4, 2) from dual; 16
Mod (m, n) select mod (10, 3) from dual; 1
Round (m, n) select round (100.256, 2) from dual; 100.26
Trunc (m, n) select trunc (100.256, 2) from dual; 100.25
Sqrt (n) select sqrt (4) from dual; 2
 LN – Returns the natural logarithmic value from the system table dual.
Example: select ln (2) from dual;
Output:
LN (2)
--------------
.693147181
4. CONVERSION FUNCTIONS
I. TO_CHAR
Syntax: to_char (d [, fmt])
● Converts date to a value of varchar2 data type in a form specified by date format
fmt. If fmt is neglected then it converts date to varchar2 in the default date format.

Example: select to_char (sysdate, 'ddth "of" Month yyyy') from dual;

Output:
TO_CHAR (SYSDATE,'DDTH"
----------------------------------------
09th of February 2008
II. TO_DATE

Syntax: to_date (char [, fmt])

● Converts char or varchar data type to date data type. Format model, fmt specifies
the form of character.

Example: select to_date ('January 15 2008', 'Month dd yyyy') from dual;

Output:
TO_DATE ('
----------------
15-JAN-08
5. MISCELLANEOUS FUNCTIONS

I. UID – Returns the integer value corresponding to the user currently logged in.

Example: select uid from dual;

Output:
UID
----------
62
II. USER – Returns the login’s user name, which is in varchar2 data type.

Example: select user from dual;

Output:
USER
------------------------------
PRINCE
III. NVL

Syntax: nvl (expression1, expression2)

● If expression1 is Null, nvl will return expression2.

● If expression1 is Not Null, nvl will return expression1.
● If expressions 1 and 2 are of different data type, then Oracle converts expression2 to
the data type of expression1 and then compares it.

Example: select nvl(rollno,0),name from sample;

Output:

NVL(ROLLNO,0) NAME
---------------------- ------------------------------
1 Raja
2 Prince
3 Raja
0 James
IV. VSIZE – Returns the number of bytes in the expression. If expression is null,
it returns null.

Example: select vsize ('hello') from dual;

Output:

VSIZE ('HELLO')
----------------------
5
 Ex. No.6 SQL GROUP FUNCTIONS Date:

Group functions Operate on sets of rows to give one result per group

FUNCTION EXAMPLE RESULT

Avg select avg (total) from table_name; average value of total

select max (custid) from table_name; highest value of custid

Max
Min select min (ordid) from table_name; lowest value of ordid
Sum select sum (total) from table_name; summation of total
Count all rows
Count Select count(*) from table_name; inclusive of duplicates
and null

Syntax:
Select column,groupfunction(column)
From table
[Where condition]
[Group by expression]
[Having group condition]
[Order by column name]
Q1. Find the number of rows in table EMP?
SQL>select count(*) from emp;
COUNT(*)
---------
6
Q2.Find the number of designations available in emp?
SQL> SQL> select count(distinct job) from empl;
COUNT(DISTINCTJOB)
-----------------------------
2
Q3.Find the number of employees who earn commission in emp table?
SQL> select count(*) from empl where comm<>0 or comm<>null;
COUNT(*)
----------
4
Q4. Find total salary paid to employees.
SQL> select sum(sal) from empl;
SUM(SAL)
-------------
3775
Q5.Find maximum, minimum and average salary of the Employees?
SQL> select max(sal),min(sal),avg(sal) from emp;
MAX(SAL) MIN(SAL) AVG(SAL)
------------ ---------- ----------
9000 6000 7725
Q6.Find the number of employees who work in deptno 20.
SQL> select count(empno) from empl where deptno=20;
COUNT(EMPNO)
------------
2
Q7. Find the max salary paid to a clerk?
SQL> select max(sal) from empl where job='clerk';

MAX(SAL)
----------
800
Q8.List the department numbers and no of employees in each department.
SQL> select deptno,count(empno) from empl group by deptno;

DEPTNO COUNT(EMPNO)
---------- ------------
10 5
20 2

Q9. List the jobs and number of employees in each job. The result should be in the
descending order of the number of employees.

SQL> select job,count(*) from emp

2 group by job
3 order by count(*) desc;
JOB COUNT(*)
--------- ------------
CLERK 4
SALESMAN 4
MANAGER 3
ANALYST 2
PRESIDENT 1

Q10. List the total salary, maximum and minimum salary and average salary of the
employees jobwise, for department 20 and display only those rows having an
average salary > 1000.

SQL> select job,sum(sal),min(sal),max(sal),avg(sal)

from emp
group by job,deptno
having deptno=20 and avg(sal)>1000;

JOB SUM(SAL) MIN(SAL) MAX(SAL) AVG(SAL)

--------- ------------ ------------- --- ---------- ------------
ANALYST 6000 3000 3000 3000
MANAGER 2975 2975 2975 2975

Q11. List the job and total salary of employees jobwise, for jobs other than
‘PRESIDENT’ and display only those rows having total salary > 5000.

SQL> select job,sum(sal) from emp

group by job
having job<>'PRESIDENT' and sum(sal)>5000;

JOB SUM(SAL)
--------- ------------
ANALYST 6000
MANAGER 8275
SALESMAN 5600

Q12. List the job, number of employees and average salary of employees jobwise.
Display only the rows where the number of employees in each job is more than
two.

SQL> select job,count(empno),avg(sal)

2 from emp
3 group by job having count(empno)>2;

JOB COUNT(EMPNO) AVG(SAL)

--------- --------------------- ------------
CLERK 4 1037.5
MANAGER 3 2758.33333
SALESMAN 4 1400
 Ex. No.7 SET OPERATION & JOINS Date:

Set Operations
Combines the results of two queries into a single one.

Operator Function
Union Returns all distinct rows selected by either
query
Union all Returns all distinct rows selected by either
query including duplicates
Intersect Returns only rows that are common to both the
queries
Minus Returns all rows selected only by the first
query but not by the second.

Q1. Create the following tables :

depositor(cus_name,accno) & borrower(cus_name,loanno)

SQL>create table depositor(cus_name varchar2(25),accno number(9));

SQL>create table borrower(cus)name varchar2(25),lno number(9));

Q2.List the names of distinct customers who have either loan or account

SQL> select distinct(cus_name) from depositor union select distinct(cus_name) from borrower;

CUS_NAME
-------------------------
Jones
paul
smith

Q3. List the names of customers (with duplicates) who have either loan or account

SQL> select cus_name from depositor union all select cus_name from borrower;

CUS_NAME
-------------------------
Jones
smith
paul
smith
paul

Q4.List the names of customers who have both loan and account
SQL> select cus_name from depositor intersect select cus_name from borrower;
CUS_NAME
-------------------------
paul
smith
Q5.List the names of customers who have loan but not account

SQL> select cus_name from depositor minus select cus_name from borrower;

CUS_NAME
-------------------------
Jones

Joins:
They are used to combine the data spread across the tables.A JOIN Basically involves
more than one Table to interact with.Where clause specifies the JOIN Condition.
Ambiguous Column names are identified by the Table name.
If join condition is omitted, then a Cartesian product is formed. That is all rows in
the first table are joined to all rows in the second table

Types of Joins

Inner Join (Simple Join) : Retrieves rows from 2 tables having common columns.
o Equi Join : A join condition with =.
o Non Equi Join :A join condition other than =
Self Join : Joining table to itself.
Outer Join : Returns all the rows returned by simple join as well as
those rows from one table that do not match any row from
another table

Q1. List empno, ename, deptno from emp and dept tables.
SQL> select e.empno,e.ename,e.deptno
from emp e,dept d
where e.deptno=d.deptno;

EMPNO ENAME DEPTNO

------ ------------------------- ----------
7369 smith 20
7566 jones 20
7499 ALLEN 30
7521 WARD 30
7654 MARTIN 30
7698 BLAKE 30
7902 FORD 20
7876 ADAMS 20
7900 JAMES 30
7788 SCOTT 20
Q2. List ename, deptno and deptname from emp and dept tables, including the rows of
emp table that does not match with any of the rows in dept table.

SQL> select e.empno,e.ename,d.deptno,d.dname

2 from emp e, dept d;

EMPNO ENAME DEPTNO DNAME

--------- ----------------------- ---------- ------------
7369 smith 10 ACCOUNTING
7566 jones 10 ACCOUNTING
7499 ALLEN 10 ACCOUNTING
7521 WARD 10 ACCOUNTING
7654 MARTIN 10 ACCOUNTING
7698 BLAKE 10 ACCOUNTING
7902 FORD 10 ACCOUNTING
7876 ADAMS 10 ACCOUNTING
7900 JAMES 10 ACCOUNTING
7788 SCOTT 10 ACCOUNTING
7369 smith 20 RESEARCH

EMPNO ENAME DEPTNO DNAME

--------- -------------- ----------- ------------
7566 jones 20 RESEARCH
7499 ALLEN 20 RESEARCH
7521 WARD 20 RESEARCH
7654 MARTIN 20 RESEARCH
7698 BLAKE 20 RESEARCH
7902 FORD 20 RESEARCH
7876 ADAMS 20 RESEARCH
7900 JAMES 20 RESEARCH
7788 SCOTT 20 RESEARCH
7369 smith 30 SALES
7566 jones 30 SALES

EMPNO ENAME DEPTNO DNAME

---------- ------------------------- ---------- -----------
7499 ALLEN 30 SALES
7521 WARD 30 SALES
7654 MARTIN 30 SALES
7698 BLAKE 30 SALES
7902 FORD 30 SALES
7876 ADAMS 30 SALES
7900 JAMES 30 SALES
7788 SCOTT 30 SALES
7369 smith 40 OPERATIONS
7566 jones 40 OPERATIONS
7499 ALLEN 40 OPERATIONS
 EMPNO ENAME DEPTNO DNAME
---------- ------------------------- ---------- ----------------------
7521 WARD 40 OPERATIONS
7654 MARTIN 40 OPERATIONS
7698 BLAKE 40 OPERATIONS
7902 FORD 40 OPERATIONS
7876 ADAMS 40 OPERATIONS
7900 JAMES 40 OPERATIONS
7788 SCOTT 40 OPERATIONS

40 rows selected.

Q3. List the names of the employee with name of his/her manager from emp table.

SQL> select d.empno,d.ename,e.ename "manager"

2 from emp e,emp d
3 where d.mgr=e.empno;

EMPNO ENAME manager

---------- ---------- ----------
7369 SMITH FORD
7499 ALLEN BLAKE
7521 WARD BLAKE
7566 JONES KING
7654 MARTINBLAKE
7698 BLAKE KING
7782 CLARK KING
7788 SCOTT JONES
7844 TURNER BLAKE
7876 ADAMS SCOTT
7900 JAMES BLAKE

EMPNO ENAME manager

---------- ---------- -------------
7902 FORD JONES
7934 MILLER CLARK

EMPNO ENAME ENAME

---------- ---------- ---------------
7902 FORD JONES
7934 MILLER CLARK

13 rows selected.
 Ex. No.8 SUB QUERIES Date:

Nesting of Queries one within the other is termed as SubQueries.

Syntax:
Select select-list from table
Where expr operator (select select-list from tablename);

The subquery (inner query) executes once before the main query.The result of the subquery is
used by the main query (outer query).

Q1. List the name of the employees whose salary is greater than that of employee with
empno 7566.

SQL> SELECT ENAME FROM EMPWHERE SAL >(SELECT SAL FROM EMP WHERE
EMPNO=7566);

ENAME
-------------------------
FORD
Q2. List the name of the employees whose job is equal to the job of employee with
empno 7499 and salary is greater than that of employee with empno 7521.

SQL> SELECT ENAME FROM EMP WHERE JOB=(SELECT JOB FROM EMP WHERE
EMPNO=7499) and SAL>(SELECT SAL FROM EMP WHERE EMPNO=7521);

ENAME
-------------------------
ALLEN

Q3. List the ename,job,sal of the employee who get minimum salary in the company.

SQL> select ename,job,sal from empl where sal=(select min(sal) from empl);

ENAME JOB SAL

------------------------- --------------- ----------
smith clerk 800

Q4. List deptno & min(salary) departmentwise, only if min(sal) is greater than the
min(sal) of deptno 20.

SQL>select deptno,min(sal) from emp group by deptno

2 having min(sal) > (select min(sal) from emp where deptno=20);

DEPTNO MIN(SAL)
---------- ----------
30 1250
Q5. List empno, ename, job of the employees whose job is not a ‘CLERK’ and whose
salary is less than at least one of the salaries of the employees whose job is
‘CLERK’.

SQL>select empno,ename,job from emp where sal < any(select sal from emp where
job='CLERK') and job <>'CLERK';

EMPNO ENAME JOB

---------- ------------------------- ---------------
7369 smith clerk

Q6.List empno, ename, job of the employees whose salary is greater than the average
salary of each department.

SQL>select empno,ename,job from emp where sal > all(select avg(sal) from emp group by
deptno);

EMPNO ENAME JOB

---------- ------------------------- ---------------
7566 jones manager
7698 BLAKE MANAGER
7902 FORD ANALYST

Q7. List ename, job, sal of the employees whose salary is equal to any one of the
salary of the employee ‘SCOTT’ or ‘WARD’.

SQL> select ename,job,sal from emp where sal =any(select sal from emp where ename='SCOTT'
or ename='WARD');

ENAME JOB SAL

------------------------- --------------- -------------
WARD SALESMAN 1250
MARTIN SALESMAN 1250
FORD ANALYST 3000
SCOTT ANALYST 3000

Q8. List ename, job, sal of the employees whose salary and job is equal to theemployee
‘FORD’.

SQL> select ename,job,sal from emp where job=(select job from emp where ename='FORD')
and sal=(select sal from emp where ename='FORD');

ENAME JOB SAL

------------------------- --------------- -------------
FORD ANALYST 3000
SCOTT ANALYST 3000
 Ex. No.9 DCL COMMANDS Date:

1. CREATING USERS

Create user <username> identified by <password>

2. GRANT PRIVILEGE COMMAND – Used to grant database object’s privileges [i.e. right
to access another user’s objects (table, view, …)] to other users.
Syntax: Grant privileges on <object-name> to <username>;
3. REVOKE PRIVILEGE COMMAND – Used to withdraw the privilege which has been
granted to a user.
Syntax: Revoke privileges on <object-name> from <username>;

Q1. Create user ‘scott’ with password ‘tiger’

SQL> create user scott identified by tiger;

Q2. Grant some system privileges to the user scott.

SQL> grant create table, create view, create sequence to scott;

Q3. Change the password of the user scott by lion.

SQL> alter user scott identified by lion;

Q4. Grant query privilege to the users user1,user2;

SQL> grant select on emp to user1,user2;

Q5. Grant privileges to update the columns dname & loc of dept table to the user scott

SQL> grant update(dname,loc) on dept to scott;

Q6. Grant insert and select privileges on dept table to the user scott with the authority
topass along the privileges.

SQL>grant insert,select on dept to scott with grant option;

Q7. As user Alice, revoke the select and insert privileges given to user scott on the
dept table.

SQL> revoke select, insert on dept from scott;

 Ex. No.10 OTHER DATABASE OBJECTS Date:

. I. VIEWS
An Imaginary table contains no data and the tables upon which a view is based
are called base tables.
Logically represents subsets of data from one or more tables

Advantages of view
To restrict database access
To make complex queries easy
To allow data independence
To present different views of the same data

SYNTAX:

CREATE [OR REPLACE] VIEW view [col1 alias, col2 alias,...]

AS subquery
[ WITH CHECK OPTION [ CONSTRAINT constraint ] ]
[ WITH READ ONLY ]

Q1. Create a view empv10 that contains empno, ename, job,sal of the employees who
work in dept 10. Also describe the structure of the view.

SQL> create view empv10 as

select empno, ename, job,sal from emp
where deptno=10;

View Created

Q2.Display the contents of the view

SQL>select * from empv10;

select * from empv10;

EMPNO ENAME JOB

----------------------------------------
7369 smith clerk
7566 jones manager
7902 FORD ANALYST
7876 ADAMS CLERK
7788 SCOTT ANALYST

Q3. Update the view empv10 by increasing 10% salary of the employees who work as
‘CLERK’. Also confirm the modifications in emp table.

SQL > update empv10 set sal = sal+0.10*sal

where job=’CLERK’;
II.SEQUENCE
● Automatically generates unique numbers
● Is a sharable object
● Is typically used to create a primary key value
● Replaces application code
● Speeds up the efficiency of accessing sequence values when cached in memory

SYNTAX:

CREATE SEQUENCE sequence

[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}];

Increment by n- interval between sequence numbers. The default is 1.

Start with n - first sequence number

Minvalue - minimum value of the sequence.

Maxvalue - maximum value of the sequence.

Cycle - continues to generate values after reaching either its max or min
value. The default is ‘nocycle’

Cache -Oracle pre allocates sequence numbers and keep the in memory
for faster access. The default is ‘nocache’.

Pseudo columns

NEXTVAL returns the next available sequence value.

CURRVAL obtains the current sequence value.
ROWID uniquely identify the rows in your table.
LEVEL – a special column you can reference only in a hierarchical query

Q1. Create a sequence dept_seq to be used for the primary key of dept table.

SQL> create sequence dept_seq

Increment by 1
Start with 91
Maxvalue 100
Nocache
Nocycle;
Q2. View the current value of the sequence
SQL> select dept_seq.CURRVAL from dual;

Q3. Insert a new department named ‘MARKETING’ in San Diego using the sequence
dept_seq.

SQL> insert into dept(deptno, dname, loc)

values (dept_seq.NEXTVAL, 'marketing', 'san diego');
PL/SQL
 Ex. No.11 BASIC PL/SQL BLOCKS Date:

Q1. Write a PL/SQL Block for addition of two numbers.

PROGRAM

Declare
a number;
b number;
c number;
Begin
a:=&a;
b:=&b;
c:=a+b;
dbms_output.put_line('Sum of ' || a || ' and ' || b || ' is ' || c);
End;
/
OUTPUT

SQL>Enter value for a: 45

old 6: a:=&a;
new 6: a:=45;
Enter value for b: 67
old 7: b:=&b;
new 7: b:=67;
Sum of 45 and 67 is 112

PL/SQL procedure successfully completed.

Q2. Write a PL/SQL block to find the maximum of 3 numbers.

PROGRAM

Declare
a number;
b number;
c number;
Begin
a:=&a;
b:=&b;
c:=&c;
if (a>b) and (a>c) then
dbms_output.put_line('A is Maximum');
elsif (b>a) and (b>c) then
dbms_output.put_line('B is Maximum');
else
dbms_output.put_line('C is Maximum');
end if;
End;
/

OUTPUT

SQL> /
Enter value for a: 4
old 6: a:=&a;
new 6: a:=4;
Enter value for b: 3
old 7: b:=&b;
new 7: b:=3;
Enter value for c: 5
old 8: c:=&c;
new 8: c:=5;
C is Maximum

PL/SQL procedure successfully completed.

Q3.PL/SQL block to find the sum of odd numbers upto 100 using simple loop.

Declare
a number;
s1 number default 0;
Begin
a:=1;
loop
s1:=s1+a;
exit when (a>100);
a:=a+2;
end loop;
dbms_output.put_line('Sum of odd numbers from 1 to 100 is '||s1);
End;
/

OUTPUT

Sum of odd numbers from 1 to 100 is 2601

PL/SQL procedure successfully completed.

Q4. Check whether the given number is prime or not.
Declare
no number (3) := &no;
a number (4) :=1;
i number(3);
Begin
for i in 2..no - 1
loop
a := mod(no, i);
if a = 0 then
goto out;
end if;
end loop;
<<out>>
if a = 1 or no=2 then
dbms_output.put_line (no || ' is a prime number');
else
dbms_output.put_line (no || ' is not a prime number');
end if;
End;
/

OUTPUT

SQL> /
Enter value for no: 34
old 2: no number (3) := &no;
new 2: no number (3) := 34;
34 is not a prime number

PL/SQL procedure successfully completed.

Q5. Write a PL/SQL block to update the salary as specified below for the employee
who has empno=7369.
- if his/her salary < 2500, then increase salary by 25%
- otherwise if salary lies between 2500 and 5000, then increase
salary by 20%
- otherwise increase salary by adding commission amount to the
salary.

declare
salary number(5);
begin
select sal into salary from emp where empno=7369;
if salary < 2500 then
update emp set sal=sal+sal*25/100 where empno=7369;
elsif salary between 2500 and 5000 then
update emp set sal=sal+sal*20/100 where empno=7369;
else
update emp set sal=sal+comm where empno=7369;
end if;
end;
SQL> /

OUTPUT

PL/SQL procedure successfully completed.

Updatations made in the emp table

Q6. Write a PL/SQL Block to list ename, sal & deptno all information about the
employee whose empno is given as input.

PROGRAM
1 declare
2 emp_rec emp%rowtype;
3 n number;
4 begin
5 n:=&n;
6 select * into emp_rec from emp where empno=n;
7 dbms_output.put_line('Ename: '||emp_rec.ename||' Salary: '||emp_rec.sal||'
Deptno: ' ||emp_rec.deptno);
8* end;

OUTPUT
SQL> /
Enter value for n: 7369
old 5: n:=&n;
new 5: n:=7369;
Ename: smith Salary: 1000 Deptno: 20
Q7. Write a PL/SQL Block to find the number of employees (say n) in emp table and
Print the numbers1 to 10 in descending order.

PROGRAM

1 declare
2 n number;
3 i number;
4 begin
5 select count(*) into n from emp ;
6 dbms_output.put_line('No of employees : ' || n);
7 dbms_output.put_line('Printing numbers 1..10 in descending order');
8 for i in reverse 1..10
9 loop
10 dbms_output.put_line(i);
11 end loop;
12* end;

OUTPUT

No of employees : 10
Printing numbers 1..10 in descending order
10
9
8
7
6
5
4
3
2
1

PL/SQL procedure successfully completed.

 Ex. No.12 CURSORS Date:

Cursor

A cursor is a temporary work area created in the system memory when a SQL
statement is executed. A cursor contains information on a select statement and the
rows of data accessed by it. This temporary work area is used to store the data
retrieved from the database, and manipulate this data. A cursor can hold more
than one row, but can process only one row at a time. The set of rows the cursor
holds is called the active set.

Q1. Write a PL/SQL Block, to update salaries of all the employees who work in
deptno 20 by 15%. If none of the employee’s salary are updated display a
message 'None of the salaries were updated'. Otherwise display the total number
of employee who got salary updated.

PROGRAM

Declare
num number(5);
Begin
update emp set sal = sal + sal*0.15 where deptno=20;
if SQL%NOTFOUND then
dbms_output.put_line('none of the salaries were updated');
elsif SQL%FOUND then
num := SQL%ROWCOUNT;
dbms_output.put_line('salaries for ' || num || 'employees are updated');
end if;
End;
OUTPUT
SQL> /
salaries for 5employees are updated
PL/SQL procedure successfully completed.
Q2. Write a PL/SQL block to find the name and salary of first five highly paid
employees.

PROGRAM

1 declare
2 emp_rec emp%rowtype;
3 cursor c is select * from emp order by sal desc;
4 begin
5 open c;
6 dbms_output.put_line('Highest paid first 5 employees');
7 loop
8 fetch c into emp_rec;
9 dbms_output.put_line(emp_rec.ename||' ' ||emp_rec.sal);
10 exit when c%rowcount=5;
11 end loop;
12* end;

SQL> /

Highest paid first 5 employees

FORD 3450
SCOTT 3450
jones 3421.25
BLAKE 2850
ALLEN 1600

PL/SQL procedure successfully completed.

 Ex. No.13 TRIGGERS Date:

A trigger is a PL/SQL block structure which is fired when DML statements like
Insert,Delete and Update is executed on a database table. A trigger is triggered
automaticallywhen an associated DML statement is executed.

SYNTAX
CREATE [OR REPLACE ] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
BEGIN
-- SQL Statements
END;

Q1. Create a trigger which will not allow you to enter duplicate or null values in
column empno of emp table.

PROGRAM

create or replace trigger dubb

before insert on emp
for each row
declare
cursor c1 is select * from emp;
x emp%rowtype;
begin
open c1;
loop
fetch c1 into x;
if :new.empno=x.empno then
raise_application_error(-20001,'You entered duplicate number');
elsif :new.empno is null then
raise_application_error(-20002,'You entered nulll values');
end if;
exit when c1%notfound;
end loop;
close c1; end;
OUTPUT
SQL> /
Trigger created.

SQL> insert into emp values(7369,'ss','ss',444,'10-sep-80',4500,0,20);

insert into emp values(7369,'ss','ss',444,'10-sep-80',4500,0,20)
*
ERROR at line 1:
ORA-20001: You entered duplicate number
ORA-06512: at "SCOTT.DUBB", line 9
ORA-04088: error during execution of trigger 'SCOTT.DUBB'OUTPUT

Q2. Create a database trigger that allows changes to employee table only during the
business hours(i.e. from 8 a.m to 5 p.m.) from Monday to Friday. There is no
restriction on viewing data from the table

PROGRAM

Create or replace trigger time_chk

Before insert or update or delete on emp
Begin
if to_number(to_char(sysdate,'hh24'))<=8 or
to_number(to_char(sysdate,'hh24')) >= 17 or
trim(to_char(sysdate,'DAY')) = 'SATURDAY'
or trim(to_char(sysdate,'day')) = 'SUNDAY' then
raise_application_error(-20004,'you can access only between 8am
to 5pm on Monday to Friday');
end if;
end;
/
OUTPUT:

SQL> insert into emp values(1,'AAA','ANALYST',4520,'12-dec-80',9000,0,20);

insert into emp values(1,'AAA','ANALYST',4520,'12-dec-80',9000,0,20)
*
ERROR at line 1:
ORA-20004: you can access only between 8am
to 5pm on Monday to Friday
ORA-06512: at "SCOTT.TIME_CHK", line 5
ORA-04088: error during execution of trigger 'SCOTT.TIME_CHK'
Q3. Create a copy of DEPT table as DEPTOLD. Create trigger which will insert the row
into second table whenever a record in first table is deleted.

SQL> create or replace trigger hist

after delete on dept
for each row
begin
insert into deptold values(:old.deptno,:old.dname,:old.loc);
end;
/

Trigger created.

SQL> select * from dept;

DEPTNO DNAME LOC

----------- ------------------------- --------------------
10 ACCOUNTING NEW YORK
20 RESEARCH Dallas
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL> delete from dept where deptno=40;

1 row deleted.

SQL> select * from deptold;

DEPTNO DNAME LOC

---------- ------------------------- ------------------------------
40 OPERATIONS BOSTON
 Ex. No.14 EXCEPTION HANDLING Date:

Exceptions can beinternally defined (by the runtime system) or user-defined. Examples of
internallydefined exceptions include division by zero and out of memory.

When an error occurs, an exception is raised. That is, normal execution stops and control
transfers to the exception handling part of your PL/SQL block or subprogram. Internal
exceptions are raised implicitly (automatically) by the runtime system. User-defined exceptions
must be raised explicitly by RAISE statements, which can also raise predefined exceptions.

Syntax
EXCEPTION
WHEN ... THEN
- handle the error differently
WHEN ... OR ... THEN
- handle the error differently
...
WHEN OTHERS THEN
- handle the error differently
END;

Q1. Handling NO_DATA_FOUND and ZERO_DIVIDE Exceptions

PROGRAM
Declare
n1 number;
n2 number;
Begin
n2 := &n2;
Select sal into n1 from empl where empno=7654;
n1 := n1/n2;
Exception
when zero_divide then
dbms_output.put_line('Zero Divide Error !');
when no_data_found then
dbms_output.put_line('No such Row in EMP table');
when others then
dbms_output.put_line('Unknown exception');
end;
OUTPUT
SQL> /
Enter value for n2: 2
Old 5: n2 := &n2;
New 5: n2 := 2;
PL/SQL procedure successfully completed.
SQL> /
Enter value for n2: 0
Old 5: n2 := &n2;
New 5: n2 := 0;
Zero Divide Error !
PL/SQL procedure successfully completed.

Q2. Write PL/SQL block to raise ‘out-of-balance’ exception if balance fall below 100.

PROGRAM
DECLARE
out_of_balance EXCEPTION;
bal NUMBER:=&bal;
BEGIN
IF bal < 100 THEN
RAISE out_of_balance;
END IF;
EXCEPTION
WHEN out_of_balance THEN
dbms_output.put_line('Low balance. Unable to do Transactions');
END;
OUTPUT
SQL> /
Enter value for bal: 50
old 3: bal NUMBER:=&bal;
new 3: bal NUMBER:=50;
Low balance. Unable to do Transactions
PL/SQL procedure successfully completed.
 Ex. No.15 SUB PROGRAMS Date:

Subprograms are named PL/SQL blocks that can take parameters and be invoked.
PL/SQL has two types of subprograms called procedures and functions. Generally, we
use a procedure to perform an action and a function to compute a value.

I. PROCEDURES

A procedure is a subprogram that performs a specific action.

Syntax :

PROCEDURE name [ (parameter, [, parameter, ...]) ] IS

[local declarations]
BEGIN
executable statements
[EXCEPTION]
exception-handlers]
END [name];
To Execute the Procedure from SQL Prompt :

SQL> EXECUTE procedure_name(value1,value2,…);

To call this procedure from another program and execute it :

….
procedure_name(value1,value2,…);
….
Q1. Write PL/SQL procedure to find the minimum of two numbers.

PROGRAM

Declare
a number;
b number;
c number;
procedure findmin(x in number,y in number,z out number) is
begin
if x < y then
z:=x;
else
x:=y;
end if;
end;
begin
a:=&a;
b:=&b;
findmin(a,b,c);
dbms_output.put_line(‘Minimum is ‘ || c);
end;
/

OUTPUT
Enter value for a: 34
Enter value for b: 12
Minimum is 12
PL/SQL procedure successfully completed.
Q2. Write a standalone PL/SQL procedure to check for palindrome.
Create or replace procedure palin(str in varchar(20),revstr out varchar(20) ) as
c number;
begin
c:=length(str);
while (c>0) loop
revstr:=revstr || substr(str,c,1);
c:=c-1;
end loop;
end;
/
OUTPUT:
SQL> /
Procedure created.
Execute the procedure from another program
Declare
str varchar(20);
rev varchar(20);
begin
str:=’&str’;
palin(str,rev);
if (str=rev) then
dbms_output.put_line(‘It is a palindrome’);
else
dbms_output.put_line(‘It is not a palindrome’);
end if;
end;
II. FUNCTIONS

A function is a subprogram that computes a value. Functions and procedure are structured
alike, except that functions have a RETURN clause.

Syntax :
FUNCTION name [ (parameter, [, parameter, ...]) ] RETURN datatype IS
[local declarations]
BEGIN
executable statements
[EXCEPTION
exception-handlers]
END [name];

Q1. Write PL/SQL Function to find the Factorial of a number.

PROGRAM

create or replace function fact(n in number) return number is

f number:=1;
i number;
begin
for i in 1..n
loop
f:=f*i;
end loop;
return f ;
end ;

OUTPUT:
SQL> /
Function created.
Function Calling Program
declare
n number;
f number;
begin
n:=&n;
f:=fact(n);
dbms_output.put_line('Factorial of ' || n || ' is '||f);
end;

OUTPUT
SQL> /
Enter value for n: 8
old 5: n:=&n;
new 5: n:=8;
Factorial of 8 is 40320

PL/SQL procedure successfully completed.

 Ex. No.16 PACKAGES Date:

Packages
A package is a database object that groups logically related PL/SQL types, objects, and
subprograms. Packages usually have two parts, a specification and a body,
.
Syntax
PACKAGE name IS -- specification (visible part)
-- public type and object declarations
-- subprogram specifications
END [name};
PACKAGE BODY name IS -- body (hidden part)
-- private type and object declarations
-- subprogram bodies
[BEGIN
-- initialization statements]
END [name];
Q1. Create a package with a procedure to retrieve the salary of the employee taking empno
as input.
Creating Package Specification
SQL> Create package p as
Procedure find_sal(no emp eno%type);
End p;
/
Package created.
Creating Package Body:
SQL> create or replace package body p as
Procedure find_sal(no emp eno%type)
Sal emp.salary%type;
Begin
Select salary into sal from emp where eno=no;
Dbms_output.put_line(‘Salary of the employee is ‘ || sal);
End;
/
Package body created.
 Calling the Package:
SQL> declare
Id emp.eno%type;
Begin
Id:=&id;
p.find_sal(id);
end;
/
OUTPUT:
Enter Id: 7326
Salary of the employee is 5000

Q2. Create a package to pack a record, a cursor and two employment procedures.

PROGRAM
SQL> CREATE PACKAGE emp_actions AS-- specification
TYPE EmpRecTyp is RECORD (eno INTEGER, salary REAL);
CURSOR desc_salary (eno NUMBER) RETURN EmpRecTyp;
PROCEDURE hire_employee
(ename CHAR, job CHAR, mgr NUMBER, sal NUMBER,
comm NUMBER, deptno NUMBER );
PROCEDURE fire_employee (eno NUMBER);
END emp_actions;
/
Package created.

SQL> CREATE PACKAGE BODY emp_actions AS-- body

CURSOR desc_salary (eno NUMBER) RETURN EmpRecTyp IS
SELECT empno, sal FROM empl ORDER BY sal DESC;
PROCEDURE hire_employee
(ename CHAR, job CHAR, mgr NUMBER, sal NUMBER,
comm NUMBER, deptno NUMBER ) is
BEGIN
INSERT INTO emp VALUES (empno_seq.NEXTVAL, ename,
 job, mgr, SYSDATE, sal, comm, deptno );
END hire_employee;
PROCEDURE fire_employee (eno NUMBER) IS
BEGIN
DELETE FROM emp WHERE empno = eno;
END fire_employee;
END emp_actions;
/
Package body created.
OUTPUT
Executing of the Package from SQL Prompt :

SQL> EXECUTE pakage_name.subprogram_name(value1, value2, …);

SQL> execute emp_actions.fire_employee(7655);
PL/SQL procedure successfully completed.