KALLAM HARANADHAREDDY

INSTITUTE OF TECHNOLOGY
(Approved by AICTE New Delhi & Affiliated to JNTUK, Kakinada)
NH- 5, Chowdavaram, Guntur-522 019
An ISO 9001:2015 Certified Institution, Accredited by NAAC & NBA

KHIT

PRACTICAL RECORD

Name:……………………………………………………………………..

Roll No:……………..…... Year & Semester:…………..………..

Branch:…………………. Section:…………………………........

Lab:……………………………………………………………………..…
 KALLAM HARANADHAREDDY
INSTITUTE OF TECHNOLOGY
(APPROVED BY AICTE NEW DELHI, AFFLIATED TO
JNTUK, KAKINADA) CHOWDAVARAM, GUNTUR-19

Roll No:

CERTIFICATE

This is to Certify that the Bonafide Record of the Laboratory Work done by

Mr/Ms…………………………………………………………………………………………..

of……..B.Tech/M.Tech/Diploma……...Semester in ………..Branch has completed…..…..

experiments in ……………………………………………………….………………………..

Laboratory during the Academic year 20 -20

Faculty-in-charge Head of the Department

Internal Examiner External Examiner

 INDEX
EX. PAGE
NO DATE NAME OF THE EXPERIMENT FROM TO MARKS SIGNATURE

.
EX. PAGE
NO DATE NAME OF THE EXPERIMENT FROM TO MARKS SIGNATURE

.
 INTRODUCTION
Structured Query Language (SQL)

SQL is a database language designed for the retrieval and management of data in a relational
database. SQL stands for Structured Query Language.
SQL includes database creation, deletion, fetching rows, modifying rows, etc.

Applications of SQL
As mentioned before, SQL is one of the most widely used query language over the databases. Allows
users to access data in the relational database management systems.
• Allows users to describe the data.
• Allows users to define the data in a database and manipulate that data.
• Allows to embed within other languages using SQL modules, libraries & pre-compilers.
• Allows users to create and drop databases and tables.
• Allows users to create view, stored procedure, functions in a database.
• Allows users to set permissions on tables, procedures and views.

SQL databases:
• Microsoft SQL
• MySQL
• MariaDB
• Oracle
• PostgreSQL
• MSSQL

SQL Command categories:

SQL has the following categories of commands used for managing the databases
1. Data Definition Language (DDL)
2. Data Manipulation Language (DML)
3. Data Query Language (DQL)
4. Transactional Control Language (TCL)
5. Data Control Language (DCL)

DATABASE SCHEMA:
A database schema is the skeleton structure of the database object that is a table, view or an index

DDL(Data Definition Language)

DDL commands are used to work on database objects like TABLE, VIEW, SYNONYM, etc.
CREATE – is used to create the database or its objects (like table, index, function, views, store
procedure and triggers).
DROP – is used to delete objects from the database.
ALTER-is used to alter the structure of the database.
TRUNCATE–is used to remove all records from a table, including all spaces allocated for the
records are removed.
RENAME –is used to rename an object existing in the database.
Example:
Create table: Used to create a table structure
Alter table: Used to modify the structure of a table

DML(Data Manipulation Language):

These commands that deals with the manipulation of data present in the database.

INSERT – is used to insert data into a table.

UPDATE – is used to update existing data within a table.
DELETE – is used to delete records from a database table.

DQL (Data Query Language) :

DQL commands are used for performing queries on the data within schema objects(Tables).

SELECT – is used to retrieve data from the database.

DCL(Data Control Language):

DCL includes commands deal with the rights, permissions and other controls of the database system.

GRANT-gives user’s access privileges to the database.

REVOKE-withdraw user’s access privileges given by using the GRANT command.

TCL(transaction Control Language):

TCL commands deal with the transaction within the database.

COMMIT– commits a Transaction.

ROLLBACK– rollbacks a transaction in case of any error occurs.
SAVEPOINT–sets a savepoint within a transaction.
SET TRANSACTION–specify characteristics for the transaction.

DATA TYPES:

1. CHAR(Size): This data type is used to store character string values of fixed length. The
maximum number of character is 255 characters.

2. VARCHAR(Size) / VERCHAR2(Size): This data type is used to store variable length string
data. The maximum character can hold is 2000 character.

3. NUMBER(P, S): The NUMBER data type is used to store number (fixed or floating point).
P is precision and S is scale.
• The precision is the number of digits in a number. It ranges from 1 to 38.
• The scale is the number of digits to the right of the decimal point in a number. It ranges from
-84 to 127.
For example, the number 1234.56 has a precision of 6 and a scale of 2. So to store this number, you
need NUMBER(6,2).
P and S are optional.
4. INT(size) or INTEGER(size) , FLOAT, DOUBLE, DECIMAL are also used
5. DATE: This data type is used to represent date and time. The standard format is DD-MM-
YY as in 17-SEP-2009.
6. RAW: The RAW data type is used to store binary data, such as digitized picture or image.
7. BOOL or BOOLEAN : True or false

Practice Day 1: Create different table:

Syntax of create table command:

CREATE TABLE tablename

(
column1 datatype(size),
column2 datatype(size),
column3 datatype(size),
.....
columnN datatype(size)
);

CREATE TABLE is the keyword.

Practice 1: Create a PERSON table with ( name,city and state columns)

SQL>Create table person
(
name varchar2(10),
city varchar2(10),
state varchar2(10)
);
Desc:
Desc command is useful to see the description of the database table.

Example:
SQL> desc person;
Name Null? Type
----------------------------------- ------ ------------------
name varchar2(10)
city varchar2(10)
state varchar2(10)
Inserting Records into PERSON TABLE:
SQL> insert into person values(‘RAMU’, ‘GUNTUR’, ‘AP’);

SQL> insert into person values(‘ANIL’, ‘GUNTUR’, ‘AP’);

SQL> insert into person values(‘SIVA’, ‘VIZAG’, ‘AP’);

SQL> insert into person values(‘HARI’, ‘HYD’, ‘TS’);

Display Records of PERSON TABLE:

SQL> select * from person ;

SQL> select name from person ;

SQL> select name,city from person ;

Practice 2: create student table with ( rollno, name, branch , mobile)

SQL> Create table student
(
rollno varchar2(10),
name varchar2(10),
branch varchar2(10),
mobile number(10)
);

SQL> desc student;

Inserting Records into STUDENT TABLE:

SQL> insert into STUDENT values(‘208X1A0566’ ‘RAMU’, ‘CSE’, 999999999);
SQL> insert into STUDENT values(‘208X1A0567’ ‘ANIL’, ‘CSE’, 888999999);
SQL> insert into STUDENT values(‘208X1A0401’ ‘KUMAR’, ‘ECE’, 779999999);
SQL> insert into STUDENT values(‘208X1A0402’ ‘BHANU’, ‘ECE’, 669999999);
Display Records of STUDENT TABLE:
SQL> select * from STUDENT ;

SQL> select rollno,name from STUDENT ;

SQL> select name,branch from STUDENT ;

SQL> select name,mobile from STUDENT ;

Select command to display table names

SQL> select * from tab;
Where select, from and tab are keywords. * symbol indicates all

Practice 4: create children table with ( name, dob)

SQL> create table children (
Name varchar2(10),
DOB date );
SQL> Desc children;
SQL> select * from tab;
 Exercise - 1

Aim: Creation, altering and dropping of tables and inserting

rows,display records using SELECT command.
Creating Tables:-

Syntax:

CREATE TABLE tablename

(
column1 datatype(size),
column2 datatype(size),
column3 datatype(size),
.....
columnN datatype(size)
);

Task 1.1: Create a table with STUDENT(ROLLNO,NAME, DOB, CITY)

SQL> CREATE TABLE STUDENT

(
ROLLNO VARCHAR2(10),
NAME VARCHAR2(10),
DOB DATE,
CITY VARCHAR2(10)
);

SQL>desc student;

Altering Tables:-
To alter a table use the alter table command:
Syntax1: To add new columns
Alter table tablename add (column datatype(size), ...);
Syntax2: To modify size(increase only) or change data type
Alter table tablename modify (column datatype(size));

Syntax3: To delete an empty column

Alter table tablename drop column columnname;

Syntax4:To rename a column

Alter table tablename rename column oldcolumn_name to Newcolumn_name;

Task 1.2: Write a query to add new column ‘PINCODE’ to the STUDENT table

SQL>ALTER TABLE STUDENT ADD (PINCODE NUMBER (10));

SQL> DESC STUDENT;

Task 1.3 : Write a query to modify size of the NAME attribute in the table ‘STUDENT’
SQL>ALTER TABLE STUDENT MODIFY (NAME VARCHAR2 (15));

SQL> DESC STUDENT;

Task 1.4 : Write a query to delete ‘pincode’ column

SQL>ALTER TABLE STUDENT DROP COLUMN PINCODE;

SQL> DESC STUDENT;

Task 1.5 : Write a query to change attribute name from NAME to STNAME
SQL>ALTER TABLE STUDENT RENAME COLUMN NAME TO STNAME;

SQL> DESC STUDENT;

INSERTING ROWS INTO A TABLE:-
SYNTAX:
INSERT INTO Tablename(Col1_name,Col2_name,...) VALUES (VALUE LIST);

Here (Col1_name,Col2_name,...) is used to insert values to specific columns. It can be omitted if you
want insert values to all columns.
String and Date values should be enclosed in single quotes.

Task 1.6: Write a query to insert the values into the table ‘STUDENT’
SQL>INSERT INTO STUDENT VALUES (01,’ANIL’,’12-FEB-90’,’HYD’);

SQL>INSERT INTO STUDENT VALUES (43,’BHAVYA’,’16-OCT-91’,’DELHI’);

SQL> INSERT INTO STUDENT VALUES (&ROLLNO,’&STNAME’,’&DOB’,’&CITY’);

Enter value for rollno: 12
Enter value for sname: RAJENDRA
Enter value for dob: 23-AUG-1984
Enter value for city: CHENNAI
1 row created.
SQL> /

SQL> /

Displaying data in Table:-

Task 1.7: Write a query to display all records of a table ‘STUDENT’

SQL> SELECT * FROM STUDENT;

Task 1.8: Write a query to display only ROLLNO & STNAME columns of a table ‘STUDENT’
SQL> SELECT ROLLNO, STNAME FROM STUDENT;

Task 1.9: Write a query to display only ROLLNO & CITY columns of a table ‘STUDENT’
SQL> SELECT ROLLNO, CITY FROM STUDENT;
Dropping Tables:-
To delete a table use the following:
Syntax: DROP TABLE TABLENAME;

Task 1.10: Write a query to delete the table ‘STUDENT’

SQL>DROP TABLE STUDENT;

SQL> desc student;

Object student does not exist
 Exercise - 2

Aim: Queries using Numeric, String, Date, Conversion Functions.

DUAL table: The DUAL is special one row, one column table present by default in all Oracle
databases. The owner of DUAL is SYS (SYS owns the data dictionary, therefore DUAL is part of the
data dictionary.) but DUAL can be accessed by every user.

Numberic Functions

Function Value Returned

ABS ( m ) Absolute value of m
MOD ( m, n ) Remainder of m divided by n
POWER ( m, n ) m raised to the nth power
ROUND ( m , n ) m rounded to the nth decimal place
TRUNC ( m, n ) m truncated to the nth decimal place
SQRT ( n ) positive square root of n
EXP ( n ) e raised to the power n
LN ( n ) natural logarithm of n
LOG ( n2, n1 ) logarithm of n1, base n2
CEIL ( n ) smallest integer greater than or equal to n
FLOOR ( n ) greatest integer smaller than or equal to n

Task 2.1: Write a query to find the absolute value of -65

SQL> select ABS(-65) from dual;

Task 2.2: Write a query to find the ceil value of 18.2

SQL> select CEIL(18.2) from dual;

Task 2.3: Write a query to find the floor value of 18.2

SQL> select FLOOR(18.2) from dual;

Task 2.4: Write a query to find the 10 power 2 value

SQL> select POWER(10,2) from dual;

Task 2.5: Write a query to find the square root of 16

SQL> select SQRT(16) from dual;

Task 2.6: Write a query to find the truncate value of 90.723

SQL> select TRUNC(90.723,1),TRUNC(90.723,-1),TRUNC(90.723) from dual;
STRING Functions

String Functions

Function Value Returned

INITCAP ( s ) First letter of each word is changed to uppercase and
all other letters are in lower case.
LOWER ( s ) All letters are changed to lowercase.
UPPER ( s ) All letters are changed to uppercase.
CONCAT ( s1, s2 ) Concatenation of s1 and s2.
LTRIM ( s , set] ) Returns s with characters removed up to the first
character not in set; defaults to space
RTRIM ( s , set ) Returns s with final characters removed after the last
character not in set; defaults to space
REPLACE ( s, s1,s2 ) Returns s with every occurrence of s1 in s replaced by
s2 ; default removes s1
SUBSTR ( s, m , n ) Returns a substring from s, beginning in position m
and n characters long; default returns to end of s.
LENGTH ( s ) Returns the number of characters in s.

Task 2.7: Write a query to find the character of ASCII value 65 & 100
SQL> select CHR(65), CHR(100) from dual;

Task 2.8: Write a query to add two strings Tamota & Soup
SQL> select CONCAT('Tamotat','Soup') "Starter" from dual;

Task 2.9: Write a query to padding ‘$’ symbols to the name in student table
Assume ‘student’ existed in the data base
SQL> select RPAD(name,5,'$') from student;

Task 2.10: Write a query to REMOVES THE SPCAES

SQL> select RTRIM(‘JNTUK ’), LTRIM(‘ UNIVERSITY’) FROM DUAL;

Task 2.11: Write a query to replace ‘Th’ with ‘B’ in the string 'This and That'
SQL> select REPLACE('This and That','Th','B') from dual;
Task 2.12: Write a query to work with translate function
SQL> SELECT TRANSLATE('123', '321', '456') FROM DUAL;

Task 2.13: Write a query to find the length of names in student table
Assume ‘student’ existed in the data base
SQL> SELECT NAME,LENGTH(NAME) FROM STUDENT;

Date and Time Functions

Function Value Returned
ADD_MONTHS ( d, n ) Date d plus n months
LAST_DAY ( d ) Date of the last day of the month containing d
MONTHS_BETWEEN ( d, e ) Number of months by which e precedes d
NEXT_DAY ( d, day ) Date of the first day of the week after d
SYSDATE Current date and time
GREATEST ( d1, d2, ..., dn ) Latest of the given dates
LEAST ( d1, d2, ..., dn ) Earliest of the given dates
Task 2.14: Write a query to find system date
SQL> SELECT SYSDATE FROM DUAL;

Task 2.15: Write a query to add 4 month to system date

SQL> SELECT ADD_MONTHS(SYSDATE, 4) FROM DUAL ;

Task 2.16: Write a query to find the no.of months between two dates:
SQL> SELECT MONTHS_BETWEEN('05-JAN-99','05-JAN-98') FROM DUAL;

Date Conversion Functions

Function Input Argument Value Returned
d = date value, fmt = format for The date d converted to a string in the
TO_CHAR ( d [, fmt ] )
string given format
s = character string, fmt =
TO_DATE ( s [, fmt ] ) String s converted to a date value
format for date

Task 2.17: Write a query to convert a string date ’2022/OCT/10’ as date type:
SQL> SELECT TO_DATE(’2022/OCT/10’, 'YYYY/MMM/DD') FROM DUAL;

Task 2.18: Write a query to convert a system date as string in 'MONTH DD, YYYY format:
SQL> SELECT TO_CHAR(SYSDATE, 'MONTH DD, YYYY') FROM DUAL;
 Exercise - 3

Aim: Queries using Aggregate functions (COUNT, SUM, AVG, MAX

and MIN), GROUP BY, HAVING and Creation and dropping of
Views.
Create the following tables:
DEPT TABLE

DEPTNO DNAME LOC

10 ACCOUNTING NEWYORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

EMP TABLE

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7369 SMITH CLERK 7902 17-Dec-80 800 20
7499 ALLEN SALESMAN 7698 20-Feb-81 1600 300 30
7521 WARD SALESMAN 7698 22-Feb-81 1250 500 30
7566 JONES MANAGER 7839 02-Apr-81 2975 20
7654 MARTIN SALESMAN 7698 28-Sep-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-May-81 2850 30
7782 CLARK MANAGER 7839 09-Jan-81 2450 10
7788 SCOTT ANALYST 7566 19-Apr-87 3000 20
7839 KING PRESIDENT 17-Nov-81 5000 10
7844 TURNER SALESMAN 7698 08-Sep-81 10000 0 30
7876 ADEMS CLERK 7788 23-May-87 1100 20
7900 JAMES CLERK 7698 03-Dec-81 950 30
7902 FORD ANALYST 7566 03-Dec-81 3000 20
7934 MILLER CLERK 7782 23-Jan-82 1300 10

Task 3.1: Create a table with the name DEPT(DEPTNO,DNAME,LOC) AND INSERT
RECORDS
SQL>CREATE TABLE DEPT
( DEPTNO NUMBER(2) PRIMARY KEY,
DNAME VARCHAR2(10) NOT NULL,
LOC VARCHAR2(8));
SQL> desc dept

SQL> INSERT INTO DEPT VALUES(10,'ACCOUNTING','NEWYORK');

SQL> INSERT INTO DEPT VALUES(20,'RESEARCH','DALLAS');

SQL> INSERT INTO DEPT VALUES(30,'SALES','CHICAGO');

SQL> INSERT INTO DEPT VALUES(40,'OPERATIONS','GOSTON');

SQL> SELECT *FROM DEPT;

Task 3.2: Create a table with the name EMP and set foreign key with DEPTNO which
references to DEPT table and insert rocords
SQL> CREATE TABLE EMP
(EMPNO NUMBER(5) PRIMARY KEY,
ENAME VARCHAR2(10) NOT NULL,
JOB VARCHAR2(10),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2) CHECK(SAL>=500 AND SAL<=10000),
COMM NUMBER(7,2),
DEPTNO NUMBER(2),
FOREIGN KEY(DEPTNO) REFERENCES DEPT);

SQL> INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO)VALUES

(7369,'SMITH','CLERK',7902,'17-DEC-80',800,20);

SQL> INSERT INTO EMP

(EMPNO,ENAME,JOB, MGR,HIREDATE,SAL,COMM,DEPTNO)VALUES
(7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
.

SQL> INSERT INTO EMP

(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)VALUES
(7521,'WARD','SALESMAN',7698,'22-FEB-81',1250,500,30);

SQL> INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO)VALUES

(7566,'JONES','MANAGER',7839,'2-APR-81',2975,20);
SQL> INSERT INTO EMP
(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)VALUES
(7654,'MARTIN','SALESMAN',7698,'28-SEP-81',1250,1400,30);

SQL> INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO)VALUES

(7698,'BLAKE','MANAGER',7839,'1-MAY-81',2850,30);

SQL> INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO)VALUES

(7782,'CLARK','MANAGER',7839,'9-JUN-81',2450,10);

SQL> INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO)VALUES

(7788,'SCOTT','ANALYST',7566,'19-APR-87',3000,20);

SQL> INSERT INTO EMP (EMPNO,ENAME,JOB,HIREDATE,SAL,DEPTNO)VALUES

(7839,'KING','PRESIDENT','17-NOV-81',5000,10);

SQL> INSERT INTO EMP

(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)VALUES
(7844,'TURNER','SALESMAN',7698,'8-SEP-81',1000,0,30);

SQL> INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO)VALUES

(7876,'ADEMS','CLERK',7788,'23-MAY-87',1100,20);

SQL> INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO)VALUES

(7900,'JAMES','CLERK',7698,'3-DEC-81',950,30);

SQL> INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO)VALUES

(7902,'FORD','ANALYST',7566,'3-DEC-81',3000,20);

SQL> INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO)VALUES

(7934,'MILLER','CLERK',7782,'23-JAN-82',1300,10);

SQL> SELECT * FROM EMP;

AGGREGATE FUNCTIONS
The following are the most commonly used SQL aggregate functions:
• AVG – calculates the average of a set of values.
• COUNT – counts rows in a specified table or view.
• MIN – gets the minimum value in a set of values.
• MAX – gets the maximum value in a set of values.
• SUM – calculates the sum of values.

SQL | GROUP BY
The GROUP BY Statement in SQL is used to arrange identical data into groups with the help of
some functions. i.e if a particular column has same values in different rows then it will arrange these
rows in a group.
Important Points:
• GROUP BY clause is used with the SELECT statement.
• In the query, GROUP BY clause is placed after the WHERE clause.
• In the query, GROUP BY clause is placed before ORDER BY clause if used any.
Syntax:
SELECT column1, function_name(column2)
FROM table_name WHERE condition
GROUP BY column1, column2
ORDER BY column1, column2;
function_name: Name of the function used for example, SUM() , AVG().
table_name: Name of the table.
condition: Condition used.

HAVING Clause
We can use HAVING clause to place conditions to decide which group will be the part of final
result-set. Also we can not use the aggregate functions like SUM(), COUNT() etc. with WHERE
clause. So we have to use HAVING clause if we want to use any of these functions in the conditions
Syntax:
SELECT column1, function_name(column2)
FROM table_name
WHERE condition
GROUP BY column1, column2
HAVING condition
ORDER BY column1, column2;

Task 3.3: Write a query to display count of different jobs in the table ‘EMP’:
SQL> SELECT COUNT(DISTINCT JOB) FROM EMP;

Task 3.4: Write a query to display TOTAL salary of all employees in the table ‘EMP’:
SQL> SELECT SUM(SAL) FROM EMP;

Task 3.5: Write a query to display maximum of salary in the table ‘EMP’:
SQL> SELECT MAX(SAL) FROM EMP;
 Task 3.6: Write a query to display maximum of salary among job type ‘SALESMAN’ in the
table ‘EMP’:
SQL> SELECT MAX(SAL) FROM EMP WHERE JOB='SALESMAN';

Task 3.7: Write a query to display minimum of salary in the table ‘EMP’:
SQL> SELECT MIN(SAL) FROM EMP;

Task 3.8: Write a query to display average salary from DEPTNO is 20 in the table ‘EMP’
SQL> SELECT AVG(SAL) FROM EMP WHERE DEPTNO=20;

Task 3.9: Write a query to display departmentwise count of employees in the table ‘EMP’
SQL> SELECT DEPTNO,COUNT(*) FROM EMP GROUP BY DEPTNO;

Task 3.10: Write a query to display departmentwise total salary of employees in the table
‘EMP’
SQL> SELECT DEPTNO,SUM(SAL) FROM EMP GROUP BY DEPTNO;

Task 3.11: Write a query to display jobwise count of employees in descending order in the table
‘EMP’
SQL> SELECT JOB,COUNT(*) FROM EMP GROUP BY JOB ORDER BY COUNT(*) DESC;

Task 3.12: Write a query to display jobwise sum,average, maximum and minimum salaries of
employees in the table ‘EMP’
SQL> SELECT JOB,SUM(SAL),AVG(SAL),MAX(SAL),MIN(SAL) FROM EMP GROUP BY
JOB;
Task 3.12: Write a query to display jobwise average salary of employees in the table ‘EMP’
except job category Manager
SQL> SELECT JOB,AVG(SAL) FROM EMP WHERE JOB!='MANAGER' GROUP BY JOB;

Task 3.13: Write a query to display jobwise maximum salary of employees whose maximum
salary having greater than 500 in the table ‘EMP’
SQL> SELECT JOB,MAX(SAL) FROM EMP GROUP BY JOB HAVING MAX(SAL)>=500;

Task 3.14: Write a query to display jobwise sum,average, maximum and minimum salary of
employees working in department no 20 whose average salary having greater than 1000 in the
table ‘EMP’
SQL> select job,sum(sal),avg(sal),max(sal),min(sal) from emp where deptno=20 group by job
having avg(sal)>1000 order by sum(sal);
VIEWS
Views:A view is a logical table based on a table or another view. A view contains no data of its own
but is like a window through which data from tables can be viewed or changed.
Consider EMP table.
Consider Dept table

Task 3.15: Write a query to create a view from the table ‘emp’ to display only
empno,ename,sal
SQL> CREATE OR REPLACE VIEW EMP_VIEW1 AS SELECT EMPNO,ENAME,SAL FROM
EMP;

SQL> SELECT * FROM EMP_VIEW1;

Task 3.16: Write a query to create a view from the table ‘emp’ to display only empno, ename,
salary, deptno of employees of department number 20
SQL> CREATE OR REPLACE VIEW EMP_VIEW2 AS SELECT EMPNO,ENAME,SAL,
DEPTNO FROM EMP WHERE DEPTNO=20;

SQL> SELECT *FROM EMP_VIEW2;

Task 3.17: Write a query to DELETE a view emp_view1

SQL> DROP VIEW EMP_VIEW1;

SQL> DESC EMP_VIEW1;

ERROR:
ORA-04043: object emp_view does not exist
 Exercise - 4

Aim: Queries using ANY, ALL, IN, EXISTS, NOTEXISTS, UNION,

INTERSET, Constraints.
IN operator : Allows you to specify multiple values in a WHERE clause.

Task 4.1: Write a query to display employees whose job is CLERK OR ANALYST from EMP
table:
SQL> SELECT * FROM EMP WHERE JOB IN ('CLERK','ANALYST');

NOT IN operator :
Task 4.2: Write a query to display employees whose job is NOT CLERK OR ANALYST from
EMP table:
SQL> SELECT *FROM EMP WHERE JOB NOT IN ('CLERK','ANALYST');

EXISTS operator : is used to test for the existence of any record in a subquery. The EXISTS
operator returns TRUE if the subquery returns one or more records.
Task 4.2: Write a query to display employees whose salary is greatherthan salary of other
employees:
SQL> SELECT EMPNO,ENAME, SAL FROM EMP E1 WHERE EXISTS(SELECT EMPNO
FROM EMP E2 WHERE E1.SAL >= E2.SAL);

Task 4.3: Write a query to display employees whose salary is not greatherthan salary of other
employees:
NOT EXISTS operator : is used to test for the existence of any record in a subquery
SQL> SELECT EMPNO,ENAME, SAL FROM EMP E1 WHERE NOT EXISTS(SELECT
EMPNO FROM EMP E2 WHERE E1.SAL >= E2.SAL);
ANY operator: It returns TRUE if ANY of the subquery values meet the condition. ANY means
that the condition will be true if the operation is true for any of the values in the range..
Task 4.4: Write a query to display employees whose salary is equal to the salaty of employees
working in department number 20 from EMP table:
SQL> SELECT ENAME FROM EMP WHERE SAL =ANY(SELECT SAL FROM EMP WHERE
DEPTNO=20);

ALL operator: It returns TRUE if ALL of the subquery values meet the condition

Task 4.5: Write a query to display employees whose salary is greater than the salaty of all
employees working as clerk from EMP table:
SQL> SELECT ENAME FROM EMP WHERE SAL > ALL (SELECT SAL FROM EMP WHERE
JOB=’CLERK’);

UNION operator: The UNION operator is used to combine the result-set of two or
more SELECT statements.
Task 4.6: Write a query to display employees who are working in DALLAS or NEWYORK
from EMP nad DEPT tables:

SQL> SELECT E.ENAME FROM EMP E, DEPT D

WHERE E.DEPTNO=D.DEPTNO AND D.LOC='DALLAS'
UNION
SELECT E.ENAME FROM EMP E,DEPT D
WHERE E.DEPTNO=D.DEPTNO AND D.LOC='NEWYORK';

INTERSECT operator: The SQL INTERSECT clause/operator is used to combine two SELECT
statements, but returns rows only from the first SELECT
Task 4.7: Write a query to display employees from EMP table whose departments in DEPT
table.
SQL> SELECT E.DEPTNO FROM EMP E INTERSECT SELECT D.DEPTNO FROM DEPT D;
Constraints:
You can place constraints to limit the type of data that can go into a table.
Common types of constraints include the following:
• UNIQUE Constraint : Ensures that all values in a column are distinct.
• NOT NULL Constraint : Ensures that a column cannot have NULL value.
• CHECK Constraint : Makes sure that all values in a column satisfy certain criteria.
• PRIMARY KEY Constraint : Ensures that all values in a column are distinct and a Column
can’t have NULL value.
• FOREIGN KEY Constraint : Used to ensure referential integrity of the data.
UNIQUE Constraint:-
Task 4.7: Write a query to create a table with name ‘CUSTOMER’ in this add UNIQUE
constraint to SID column and insert the following rows and test UNIQUE CONSTRAINTS
SQL> CREATE TABLE CUSTOMER
(SID INTEGER UNIQUE,
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(20));

SQL>INSERT INTO Customer values (1212,'RAJENDRA','PRASAD');

SQL>INSERT INTO Customer values (1201,'PURNA','CHANDRARAO');
SQL>INSERT INTO Customer values (1215,'LITHEN','KUMAR');

SID FIRST_NAME LAST_NAME

1212 RAJENDRA PRASAD
1201 PURNA CHANDRARAO
1215 LITHEN KUMAR

Executing the following SQL statement,

SQL> INSERT INTO CUSTOMER VALUES ('1201','RESHMA','LEE');

It will result in an error because '1201' already exists in the SID column, thus trying to insert another
row with that value violates the UNIQUE constraint.

NOT NULL Constraint:-

Task 4.8: Write a query to create a table with name ‘CUSTOMER1’ in this add NOT NULL
constraint to SID column and insert the following rows and test NOT NULL CONSTRAINTS
SQL>CREATE TABLE CUSTOMER2
( SID INTEGER NOT NULL,
LAST_NAME VARCHAR (20) ,
FIRST_NAME VARCHAR (20));

Insert the following data wiout SID:

SQL> INSERT INTO CUSTOMER2(LAST_NAME,FIRST_NAME) VALUES (‘RAMA’ , ‘RAO’);
It will result in an error
Check Constraint:-
Task 4.9: Write a query to create a table with name ‘EMP55’ in this add CHECK constraint
to SAL column and insert the following rows and test CHECK CONSTRAINTS

SQL> CREATE TABLE EMP55

(EMPNO NUMBER(5) ,
ENAME VARCHAR2(10),
SAL NUMBER(7,2) CHECK(SAL>=500 AND SAL<=10000));
Executing the following SQL statement,
SQL> INSERT INTO EMP VALUES (100, ‘RAMA’ , 20000);
IT WILL RESULT IN AN ERROR

Primary key and Foreign key Constraint:-

Task 4.10: Write a query to create tables with the following:
DEPT100(DEPTNO,DNAME)
EMP100(ENO,ENAME,DEPTNO)
Where DEPTNO in DEPT100 is primary key and DEPTNO in EMP100 is Foreign key
SQL> CREATE TABLE DEPT100 (DEPTNO NUMBER(4) PRIMARY KEY,
DNAME VARCHAR2(10) );
SQL> INSERT INTO DEPT100 VALUES(10,'ACCOUNTING’);
SQL> INSERT INTO DEPT100 VALUES(20,'RESEARCH’);

SQL> CREATE TABLE EMP100

(EMPNO NUMBER(5) PRIMARY KEY,
ENAME VARCHAR2(10),DEPTNO NUMBER(2),
FOREIGN KEY(DEPTNO) REFERENCES DEPT100);
SQL> INSERT INTO EMP100 VALUES(7369,'SMITH‘,800,20);
SQL> INSERT INTO EMP100 VALUES (7499,'ALLEN‘,300,30);
Error because deptno 30 is not available in DEPT100 table.
 Exercise - 5

Aim: Queries (along with sub Queries) using the SAILORS,BOATS

AND RESERVES DATABASE TABLES:
The schema of the above tables is as follows:
Sailors(sid: integer, sname: string, rating: integer, age: real);
Boats(bid: integer, bname: string, color: string);
Reserves(sid: integer, bid: integer, day: date).
Based on the above schema, write the corresponding SQL queries for
the following?
i. Find the colors of boats reserved by Lubber.
ii. Find the names of sailors who have reserved at least one boat.
iii. Find the names of sailors who have reserved a red or green boat.
iv. Find the names of the sailors who have reserved both a Red boat and a Green boat.
v. Find names of sailors who have reserved all boats.

Task 5.1: Create SAILORS,BOATS and RESERVES tables

SQL> CREATE TABLE SAILORS
( SID INTEGER PRIMARY KEY,
SNAME VARCHAR(32),
RATING INTEGER,
AGE REAL );
SQL> INSERT INTO SAILORS VALUES ( 22, 'DUSTIN', 7, 45.0 );
SQL> INSERT INTO SAILORS VALUES ( 29, 'BRUTUS', 1, 33.0 );
SQL> INSERT INTO SAILORS VALUES ( 31, 'LUBBER', 8, 55.5 );
SQL> INSERT INTO SAILORS VALUES ( 32, 'ANDY', 8, 25.5 );
SQL> INSERT INTO SAILORS VALUES ( 58, 'RUSTY', 10, 35.0 );
SQL> INSERT INTO SAILORS VALUES ( 64, 'HORATIO', 7, 35 );
SQL> INSERT INTO SAILORS VALUES ( 71, 'ZORBA', 10, 16 );
SQL> INSERT INTO SAILORS VALUES ( 74,'HORATIO', 9, 40 );
SQL> INSERT INTO SAILORS VALUES ( 85, 'ART' ,3, 25.5 );
SQL> INSERT INTO SAILORS VALUES ( 95, 'BOB', 3, 63.5);

SQL> CREATE TABLE BOATS

(BID INTEGER PRIMARY KEY,
BNAME VARCHAR2(10),
COLOR VARCHAR2(10)
);

TABLE CREATED.

SQL> INSERT INTO BOATS VALUES ( 101 ,'INTERLAKE', 'BLUE');

SQL> INSERT INTO BOATS VALUES ( 102 ,'INTERLAKE', 'RED');
SQL> INSERT INTO BOATS VALUES ( 103 ,'CLIPPER', 'GREEN');
SQL> INSERT INTO BOATS VALUES ( 104 ,'MARINE', 'RED');

SQL> CREATE TABLE RESERVES

(
SID INTEGER NOT NULL,
BID INTEGER NOT NULL,
DAY DATE,
FOREIGN KEY (SID) REFERENCES SAILORS(SID),
FOREIGN KEY (BID) REFERENCES BOATS(BID) );

SQL> INSERT INTO RESERVES VALUES (22 ,101, TO_DATE('10/10/1998', 'DD/MM/YYYY'));

SQL> INSERT INTO RESERVES VALUES (22 ,102, TO_DATE('10/10/1998', 'DD/MM/YYYY'));
SQL> INSERT INTO RESERVES VALUES (22 ,103, TO_DATE('08/10/1998', 'DD/MM/YYYY'));
SQL> INSERT INTO RESERVES VALUES (22 ,104, TO_DATE('07/10/1998', 'DD/MM/YYYY'));
SQL> INSERT INTO RESERVES VALUES (31 ,102, TO_DATE('10/11/1998', 'DD/MM/YYYY'));
SQL> INSERT INTO RESERVES VALUES (31 ,103, TO_DATE('06/11/1998', 'DD/MM/YYYY'));
SQL> INSERT INTO RESERVES VALUES (64,101, TO_DATE('05/09/1998', 'DD/MM/YYYY'));
SQL> INSERT INTO RESERVES VALUES (64,102, TO_DATE('08/09/1998', 'DD/MM/YYYY'));
SQL> INSERT INTO RESERVES VALUES (74,103, TO_DATE('08/09/1998', 'DD/MM/YYYY'));
Task 5.2: Write a query find the colors of boats reserved by LUBBER.

SQL> SELECT B.COLOR FROM BOATS B WHERE B.BID IN (SELECT R.BID FROM
RESERVES R WHERE R.SID=(SELECT S.SID FROM SAILORS S WHERE S.SNAME
=’LUBBER’));

Task 5.3: Write a query to find the names of sailors who have reserved at least one boat.

SQL> SELECT S.SID, S.SNAME FROM SAILORS S

WHERE S.SID IN
(SELECT R.SID FROM RESERVES R);

Task 5.4: Write a query to find the names of sailors who have reserved a RED or
GREEN boat.
SQL> SELECT S.SID, S.SNAME FROM SAILORS S
WHERE S.SID IN (SELECT R.SID FROM RESERVES R
WHERE R.BID IN (SELECT B.BID FROM BOATS B
WHERE B.COLOR=’GREEN’ OR B.COLOR=’RED’));

or

SQL> SELECT DISTINCT S1.SID,S1.SNAME

FROM SAILORS S1, RESERVES R1, BOATS B1
WHERE S1.SID=R1.SID AND R1.BID=B1.BID AND ( B1.COLOR='RED' OR
B1.COLOR='GREEN') ;
Task 5.5: Write a query to find the names of the sailors who have reserved both a
RED BOAT AND A GREEN boat.
SQL> SELECT S1.SNAME FROM SAILORS S1, RESERVES R1, BOATS B1
WHERE S1.SID=R1.SID AND R1.BID=B1.BID AND B1.COLOR='RED’
INTERSECT
SELECT S2.SNAME FROM SAILORS S2, RESERVES R2, BOATS B2
WHERE S2.SID=R2.SID AND R2.BID=B2.BID AND B2.COLOR='GREEN’ ;

Task 5.6: Write a query to find names of sailors who have reserved all boats.
SELECT S.SNAME FROM SAILORS S
WHERE NOT EXISTS
(( SELECT B.BID FROM BOATS B )
MINUS
( SELECT R.BID FROM RESERVES R WHERE R.SID=S.SID ));
 Exercise - 6

Create a simple PL/SQL program which includes declaration section,

executable section and exception –Handling section (Ex. Student marks
can be selected from the table and printed for those who secured first
class and an exception can be raised if no records were found)

Pre-required: Create a table student(RNO ,NAME ,AVG_MARKS ,RES)

CREATE TABLE STUDENT
( RNO NUMBER(4),
NAME VARCHAR2(10),
AVG_MARKS NUMBER(8,2),
RES VARCHAR2(10)
);
INSERT INTO STUDENT VALUES(1,’RAMU’,65.00,’FIRST’);
INSERT INTO STUDENT VALUES(2,’SRINU’,55.00,’SECOND’);
INSERT INTO STUDENT VALUES(3,’RAJU’,45.00,’THIRD’);
INSERT INTO STUDENT VALUES(4,’RANI’,75.00,’FIRST’);
INSERT INTO STUDENT VALUES(5,’ANIL’,55.00,’SECOND’);

SELECT * FROM STUDENT;

Program:

DECLARE
X STUDENT%ROWTYPE;
BEGIN
SELECT RNO ,NAME ,AVG_MARKS ,RES INTO X.RNO, X.NAME,
X.AVG_MARKS, X.RES FROM STUDENT WHERE RNO=&RNO;

IF X.RES = 'FIRST' THEN

DBMS_OUTPUT.PUT_LINE('ROLL NO ' || X.RNO);
DBMS_OUTPUT.PUT_LINE('NAME ' || X.NAME);
DBMS_OUTPUT.PUT_LINE('AVG ' || X.AVG);
DBMS_OUTPUT.PUT_LINE('RESULT ' || X.RES);
ELSE
DBMS_OUTPUT.PUT_LINE('STUDENT RESULT IS NOT FIRST CLASS ' );
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('STUDENT NOT FOUND');
END;
/
 Exercise - 7

Insert data into student table and use COMMIT, ROLLBACK and
SAVEPOINT in PL/SQL block.

Program:
DECLARE
X STUDENT%ROWTYPE;
BEGIN
X.RNO:=&RNO;
X.AME:='&NAME';
X.AVG_MARKS:=&AVG_MARKS;
X.RES:=’&GRADE’;
INSERT INTO STUDENT VALUES
(X.RNO,X.NAME,X.AVG_MARKS,X.RESULT);
COMMIT;
X.RNO:=&RNO;
X.AME:='&NAME';
X.AVG_MARKS:=&AVG_MARKS;
X.RES:=’&GRADE’;
INSERT INTO STUDENT VALUES
(X.RNO,X.NAME,X.AVG_MARKS,X.RESULT);
SAVEPOINT A;
X.RNO:=&RNO;
X.AME:='&NAME';
X.AVG_MARKS:=&AVG_MARKS;
X.RES:=’&GRADE’;
INSERT INTO STUDENT VALUES
(X.RNO,X.NAME,X.AVG_MARKS,X.RESULT);
SAVEPOINT B;
X.RNO:=&RNO;
X.AME:='&NAME';
X.AVG_MARKS:=&AVG_MARKS;
X.RES:=’&GRADE’;
INSERT INTO STUDENT VALUES
(X.RNO,X.NAME,X.AVG_MARKS,X.RESULT);
ROLLBACK TO A;
COMMIT;
END;
/
 OUTPUT:

SELECT * FROM STUDENT;

 Exercise - 8
Develop a program that includes the features IF, NESTED IF, CASE
and CASE expression. The program can be extended using the
NULLIF functions.
PL/SQL - Nested IF-THEN-ELSE Statements
An IF statement in an other IF statement is called Nested IF.
It is always legal in PL/SQL programming to nest the IF-ELSE statements, which means you can
use one IF or ELSE IF statement inside another IF or ELSE IF statement(s).

Lab Exercise 8A) Nested IF program : to find biggest of three numbers

Program:
DECLARE
A NUMBER(3) := &A;
B NUMBER(3) := &B;
C NUMBER(3) := &C;
BIG NUMBER(3);
BEGIN
IF( A > B ) THEN
IF( A > C) THEN
BIG:= A;
ELSE
BIG:=C;
END IF;
ELSE
IF(B>C) THEN
BIG:=B;
ELSE
BIG:=C;
 END IF;
END IF;
DBMS_OUTPUT.PUT_LINE('BIGGEST IS ' || BIG);
END;
/

OUTPUT:

SQL CASE Statement

The SQL CASE Statement

The CASE statement goes through conditions and returns a value when the first condition is met (like
an if-then-else statement). So, once a condition is true, it will stop reading and return the result. If no
conditions are true, it returns the value in the ELSE clause.
If there is no ELSE part and no conditions are true, it returns NULL.
CASE Syntax
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END CASE;

Lab Exercise 8: b) CASE statement rogram : to display day name

PRGORAM:
DECLARE
X NUMBER(3):=&ENTER_DAY_OF_WEEK;
BEGIN

CASE X
WHEN 1 THEN
DBMS_OUTPUT.PUT_LINE('MONDAY');
WHEN 2 THEN
DBMS_OUTPUT.PUT_LINE('TUESDAY');
WHEN 3 THEN
 DBMS_OUTPUT.PUT_LINE('WEDNESDAY');
WHEN 4 THEN
DBMS_OUTPUT.PUT_LINE('THURSEDAY');
WHEN 5 THEN
DBMS_OUTPUT.PUT_LINE('FRIDAY');
WHEN 6 THEN
DBMS_OUTPUT.PUT_LINE('SATURDAY');
WHEN 7 THEN
DBMS_OUTPUT.PUT_LINE('SUNDAY');
ELSE
DBMS_OUTPUT.PUT_LINE('UNKNOWN DAY');
END CASE;

END;
/

OUTPUT:
NULLIF() Function
Definition and Usage
The NULLIF() function returns NULL if two expressions are equal, otherwise it returns the first
expression.
Syntax
NULLIF(expr1, expr2)
Example:
SELECT NULLIF(25, 25 from dual;
Ans: Null
SELECT NULLIF('Hello', 'Hello') from dual;
Ans: Null
SELECT NULLIF('Hello', 'world') from dual;
Ans: Hello
Exercise 8. C)PL/SQL program using NULLIF
Program:
DECLARE
X NUMBER(3);
Y VARCHAR2(10);
Z VARCHAR2(10);
BEGIN
SELECT NULLIF(25, 25) INTO X FROM DUAL;
DBMS_OUTPUT.PUT_LINE(' X IS '|| X);
SELECT NULLIF('HELLO', 'HELLO') INTO Y FROM DUAL;
DBMS_OUTPUT.PUT_LINE(' Y IS '|| Y);
SELECT NULLIF('HELLO', 'WORLD') INTO Z FROM DUAL;
DBMS_OUTPUT.PUT_LINE(' Z IS '|| Z);
END;
/
 Exercise - 9
Program development using WHILE LOOPS, numeric FOR LOOPS,
nested loops using ERROR Handling, BUILT IN Exceptions, USE
defined Exceptions, RAISE- APPLICATION ERROR.
SIMPLE LOOP

Basic loop structure encloses sequence of statements in between the LOOP and END LOOP
statements. With each iteration, the sequence of statements is executed and then control resumes at
the top of the loop.
Syntax
LOOP
Statements;
EXIT;
{or EXIT WHEN condition;}
END LOOP;

Example
DECLARE
X NUMBER := 10;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE(X);
X := X + 10;
EXIT WHEN X=50;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('AFTER EXIT X IS: ' || X);
END;
/
WHILE LOOP

A WHILE LOOP statement in PL/SQL programming language repeatedly executes a target

statement as long as a given condition is true.
Syntax
WHILE condition LOOP
sequence_of_statements
END LOOP;

Example
DECLARE
a number(2) := 1;
BEGIN
WHILE a <= 15 LOOP
insert into seq values(a);
a := a + 1;
END LOOP;
END;
/

Exercise 9A) WHILE LOOP PROGRAM – INSERT SEQUENCE

NUMBERS IN TABLE NAMED AS ‘SEQ’
Step1: Create SEQ table:
SQL> create table SEQ (sno number(4));
Table Created

Program:
DECLARE
A NUMBER(2) := 1;
BEGIN
WHILE A <= 15 LOOP
INSERT INTO SEQ VALUES(A);
A := A + 1;
END LOOP;
END;
/
FOR LOOP

A FOR LOOP is a repetition control structure that allows you to efficiently write a loop that needs
to execute a specific number of times.
Syntax
FOR counter IN initial_value .. final_value LOOP
sequence_of_statements;
END LOOP;
• Counter is control variable
• Initial_value is stating value of counter
• Final_value is stating value of counter
• The initial_value need not be 1; however, the loop counter increment (or decrement) must be
1.

Example:
DECLARE
a number(2);
BEGIN
FOR a in 10 .. 15 LOOP
dbms_output.put_line('value of a: ' || a);
END LOOP;
END;
/

Exercise 9B) for LOOP Program – INSERT reverse sequence

numbers in table named as ‘REV_SEQ’ (10 TO 1)

Step1: create REV_SEQ table

SQL> create table REV_SEQ (sno number(4));
Table Created

Program:
DECLARE
A number(2);
BEGIN
FOR A in REVERSE 1 .. 10 LOOP
INSERT INTO REV_SEQ VALUES(A);
END LOOP;
END;
/
NESTED LOOP:

A loop statement within another Loop statement is called Nested loop.

LAB Exercise 9 C) nested loop – 1 to 5 multiplication tables

DECLARE
A NUMBER(2);
BEGIN
FOR N IN 1 .. 5 LOOP
FOR A IN 1 .. 10 LOOP
DBMS_OUTPUT.PUT_LINE(' '|| N||'X'||A||'='||N*A);
END LOOP;
END LOOP;
END;
/
Output:

EXCEPTIONS
An exception is an error condition during a program execution. PL/SQL supports programmers to
catch such conditions using EXCEPTION block in the program and an appropriate action is taken
against the error condition. There are two types of exceptions −
1. Built-in or System-defined exceptions
2. User-defined exceptions
Built-in or System-defined exceptions:
These are defined in SQL software. These exceptions will implicitly raised when evr occured.
Example: NO_DATA_FOUND
The general syntax for exception handling is as follows. Here you can list down as many exceptions
as you can handle. The default exception will be handled using WHEN others THEN −

Exercise 9 D) Built-in Eceptions – Program to input salary and display

the employ names whose salary is equal to input salary
DECLARE
X EMP.NAME%TYPE;
S EMP.SAL%TYPE;
BEGIN
S:=&SALARY;
SELECT NAME INTO X FROM EMP WHERE SAL=S;
DBMS_OUTPUT.PUT_LINE('ONLY ONE EMPLOY HAS SALARY <='||S);
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('EMPLOYEES FOUND MORE THAN ONCE');
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(X||'NO EMPLOY FOUND');
END;
/
User-defined exceptions
PL/SQL allows you to define your own exceptions according to the need of your program.
A user-defined exception must be declared and then raised explicitly, using either a RAISE
statement or the procedure DBMS_STANDARD.RAISE_APPLICATION_ERROR.
RAISE : Exceptions can be raised explicitly by the programmer by using the command RAISE
Syntax: RAISE exception_name
The syntax for declaring an exception is −
DECLARE my-exception EXCEPTION;

Exercise 9E) userexcep: This program asks for a Student RNo, when
the user enters an invalid RNo, the exception invalid_id is raised.
DECLARE
s_rno student.RNo%type := &student_RNo;
s_name student.Name%type;
s_avg student.avg%type;
-- user defined exception
ex_invalid_id EXCEPTION;
BEGIN
IF s_rno <= 0 THEN
RAISE ex_invalid_id;
ELSE
SELECT name, avg INTO s_name, s_avg FROM student WHERE RNo =
s_RNo;
DBMS_OUTPUT.PUT_LINE ('Name: '|| s_name);
DBMS_OUTPUT.PUT_LINE ('Avg: ' || s_avg);
END IF;
EXCEPTION
WHEN ex_invalid_id THEN
dbms_output.put_line('RNo must be greater than zero!');
WHEN no_data_found THEN
dbms_output.put_line('No such student!');
WHEN others THEN
dbms_output.put_line('Error!');
END;
/
 Exercise - 10

Programs development using creation of procedures, passing

parameters IN and OUT of PROCEDURES.

PL/SQL Procedures
PL/SQL subprograms are named PL/SQL blocks that can be invoked with a set of parameters.
PL/SQL provides two kinds of subprograms −
1. Functions − These subprograms return a single value; mainly used to compute and return a
value.
2. Procedures − These subprograms do not return a value directly; mainly used to perform an
action.

Creating a Procedure
A procedure is created with the CREATE OR REPLACE PROCEDURE statement.
Syntax:
CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])] {IS | AS}
BEGIN
< procedure_body >
END procedure_name;

• procedure-name specifies the name of the procedure.

• [OR REPLACE] option allows the modification of an existing procedure.
• The optional parameter list contains name, mode and types of the parameters. IN represents
the value that will be passed from outside and OUT represents the parameter that will be used
to return a value outside of the procedure.
• procedure-body contains the executable part.
• The AS keyword is used instead of the IS keyword for creating a standalone procedure.

Parameter Modes in PL/SQL Subprograms

1 IN
An IN parameter lets you pass a value to the subprogram. It is a read-only parameter. Inside the
subprogram, an IN parameter acts like a constant. It cannot be assigned a value. You can pass a
constant, literal, initialized variable, or expression as an IN parameter. You can also initialize it to a
default value; however, in that case, it is omitted from the subprogram call. It is the default mode of
parameter passing. Parameters are passed by reference.
2 OUT
An OUT parameter returns a value to the calling program. Inside the subprogram, an OUT parameter
acts like a variable. You can change its value and reference the value after assigning it. The actual
parameter must be variable and it is passed by value.
3 IN OUT
An IN OUT parameter passes an initial value to a subprogram and returns an updated value to the
caller. It can be assigned a value and the value can be read.
The actual parameter corresponding to an IN OUT formal parameter must be a variable, not a
constant or an expression. Formal parameter must be assigned a value. Actual parameter is passed
by value.

Example
The following example creates a simple procedure that displays the string 'Hello World!' on the
screen when executed.
CREATE OR REPLACE PROCEDURE greetings AS
BEGIN
dbms_output.put_line('Hello World!');
END;
When the above code is executed using the SQL prompt, it will produce the following result −
Procedure created.

Executing a Standalone Procedure:

A standalone procedure can be called in two ways −
1. Using the EXECUTE keyword
2. Calling the name of the procedure from a PL/SQL block
3. The procedure named 'greetings' can be called with the EXECUTE keyword as −
EXECUTE greetings;
The above call will display −
Hello World
PL/SQL procedure successfully completed.
2. The procedure can also be called from another PL/SQL block −
BEGIN
greetings;
END;
Output:
Hello World

Deleting a Standalone Procedure

A standalone procedure is deleted with the DROP PROCEDURE statement.
Syntax for deleting a procedure is −
DROP PROCEDURE procedure-name;
You can drop the greetings procedure by using the following statement −
DROP PROCEDURE greetings;
Exercise 10.A) Procedure to increment employ salary
CREATE TABLE EMP999(ENO NUMBER(5), SAL NUMBER(8,2));
INSERT INTO EMP999 VALUES(1,2000);
INSERT INTO EMP999 VALUES(2,2500);
INSERT INTO EMP999 VALUES(3,1500);
INSERT INTO EMP999 VALUES(4,6000);

CREATE OR REPLACE PROCEDURE INCREMENT_SAL(PENO NUMBER) IS

INCR EMP999.SAL%TYPE;
V_ENO EMP999.ENO%TYPE;
V_SAL EMP999.SAL%TYPE;
BEGIN
SELECT ENO,SAL INTO V_ENO,V_SAL FROM EMP999
WHERE ENO=P_ENO;
IF V_SAL<=5000 THEN
INCR:=0.20*V_SAL;
ELSIF V_SAL>5000 AND V_SAL<=10000 THEN
INCR:=0.40*V_SAL;
ELSE
INCR:=0.50*V_SAL;
END IF;
UPDATE EMP999 SET SAL=SAL+INCR WHERE ENO=P_ENO;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employ Does Not Exist');
END INCREMENT_SAL;
/
SQL> SELECT * FROM EMP999;
Exercise 10.B) Procedure using IN,OUT
Program to create findMin() Procedure:
create or replace PROCEDURE findMin(x IN number, y IN number, z OUT number) IS
BEGIN
IF x < y THEN
z:= x;
ELSE
z:= y;
END IF;
END findMin;
/
PROCEDURE FINDMIN CALLING:
DECLARE
A NUMBER;
B NUMBER;
C NUMBER;
BEGIN
A:= &A;
B:= &B;
FINDMIN(A, B, C);
DBMS_OUTPUT.PUT_LINE(' MINIMUM OF ('|| A|| ', ' || B||') : ' || C);
END;
/
 Exercise - 11

Aim:Program development using creation of stored functions, invoke

functions in SQL Statements and write complex functions.

PL/SQL -Functions
Functions − These subprograms return a single value; mainly used to
compute and return a value.

Creating a Function
A standalone function is created using the CREATE FUNCTION statement:

CREATE [OR REPLACE] FUNCTION function_name

[(parameter_name [IN | OUT | IN OUT] type [, ...])]
RETURN return_datatype {IS | AS}
BEGIN
< function_body >
END [function_name];
• IN represents the value that will be passed from outside and OUT represents the parameter that
will be used to return a value outside of the procedure.
• The function must contain a return statement.
• The RETURN clause specifies the data type you are going to return from the function.
• function-body contains the executable part.
• The AS keyword is used instead of the IS keyword for creating a standalone function.

Example
This function returns the total number of Empoyees in the EMP table.
We will use the EMP table
SQL>Select * from EMP;

Program to create Function:

CREATE OR REPLACE FUNCTION totalEmps
RETURN number IS
total number(2) := 0;
BEGIN
 SELECT count(*) into total FROM emp;
RETURN total;
END;
/
When the above code is executed using the SQL prompt, it will produce the following result −
Function created.
Calling a Function
To call a function, you simply need to pass the required parameters along with the function name and
if the function returns a value, then you can store the returned value.
Program to call the function totalEmps:
declare
c number;
begin
c:=count_emps();
dbms_output.put_line('Total number of employess are '|| c);
end;
/
Output:
Total no. of Employees: 3

Second way of calling function from select statement:

Example:
Select totalEmps() from dual;
Exercise 11.A) Write a PL/SQL Code create a function to calculate
factorial.
Program to create function:
CREATE OR REPLACE FUNCTION FACT(N NUMBER) RETURN NUMBER IS
I NUMBER;
F NUMBER:=1;
BEGIN
IF N<0 THEN
RETURN -1;
END IF;
FOR I IN 1..N
LOOP
F:=F*I;
END LOOP;
RETURN F;
END FACT;
/
Call a function from select:
SQL> SELECT FACT(4) FROM DUAL;
24

Program to call Fact() function

DECLARE
N NUMBER:=&N;
F NUMBER;
BEGIN
F:=FACT(N);
IF F<0 THEN
DBMS_OUTPUT.PUT_LINE('FACTORIAL FOR NEGATIVE NUMBERS DONOT
EXIST');
ELSE
DBMS_OUTPUT.PUT_LINE('FACTORIAL IS'||F);
END IF;
END;
/

SQL> @C:\USERS\SANJU\DESKTOP\DBMS\LAB\LAB9A_2.SQL
ENTER VALUE FOR N: 5
OLD 2: N NUMBER:=&N;
NEW 2: N NUMBER:=5;
FACTORIAL IS120
Pl/Sql Procedure Successfully Completed.
Exercise 11.B) Write a PL/SQL Code to increment sal using
Userdefined function
Program to create function:
CREATE OR REPLACE FUNCTION INCREMENT_SALARY(EID NUMBER) RETURN
NUMBER IS
INCR EMP999.SAL%TYPE;
VENO EMP999.ENO%TYPE;
VSAL EMP999.SAL%TYPE;
BEGIN
SELECT ENO,SAL INTO VENO,VSAL FROM EMP999 WHERE ENO=EID;
IF VSAL<=5000 THEN
INCR:=0.10*VSAL;
ELSIF VSAL>5000 AND VSAL<=10000 THEN
INCR:=0.20*VSAL;
ELSE
INCR:=0.30*VSAL;
END IF;
RETURN INCR;
END INCREMENT_SALARY;
/
Program to call Fact() function
DECLARE
INCR NUMBER;
VENO NUMBER:=&EMPLOY_NUMBER;
BEGIN
INCR := INCREMENT_SALARY(VENO);
UPDATE EMP999 SET SAL=INCR WHERE ENO=VENO;
DBMS_OUTPUT.PUT_LINE('THE EMPLOY SALARY AFTER INCREMNT IS ' || INCRE);
END;
/
 Exercise - 12

Aim:Develop programs using features parameters in a CURSOR,

for update CURSOR, where current of clause and CURSOR variables.
PL/SQL - CURSORS
What are Cursors?
• A cursor is a temporary work area created in the system memory when a SQL statement is
executed.
• A cursor holds the rows (one or more) returned by a SQL statement. The set of rows the
cursor holds is referred to as the active set.
• A cursor contains information on a select statement and the rows of data accessed by it.
• 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.

There are two types of cursors in PL/SQL:

Implicit cursors:
These are created by default when DML statements like, INSERT, UPDATE, and DELETE
statements are executed. They are also created when a SELECT statement that returns just one row is
executed.
In PL/SQL, you can refer to the most recent implicit cursor as the SQL cursor, which always has
attributes such as %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT.
Explicit cursors:
They must be created when you are executing a SELECT statement that returns more than one row.
Even though the cursor stores multiple records, only one record can be processed at a time, which is
called as current row. When you fetch a row the current row position moves to next row.
Both implicit and explicit cursors have the same functionality, but they differ in the way they
are accessed.

The General Syntax for creating a cursor is as given below:

CURSOR cursor_name IS select_statement;

• cursor_name – A suitable name for the cursor.

• select_statement – A select query which returns multiple rows.

How to use Explicit Cursor?

There are four steps in using an Explicit Cursor.

DECLARE the cursor in the declaration section.
OPEN the cursor in the Execution Section.
FETCH the data from cursor into PL/SQL variables or records in the Execution Section.
CLOSE the cursor in the Execution Section before you end the PL/SQL Block.
 1) Declaring a Cursor in the Declaration Section:
DECLARE CURSOR emp_cur IS
SELECT * FROM emp_tbl WHERE salary > 5000;
In the above example we are creating a cursor ‘emp_cur’ on a query which returns the records of
all the employees with salary greater than 5000. Here ‘emp_tbl’ in the table which contains
records of all the employees.
2) Accessing the records in the cursor:
Once the cursor is created in the declaration section we can access the cursor in the execution
section of the PL/SQL program.

These are the three steps in accessing the cursor.

1) Open the cursor.
2) Fetch the records in the cursor one at a time.
3) Close the cursor.

General Syntax to open a cursor is:

OPEN cursor_name;
General Syntax to fetch records from a cursor is:
FETCH cursor_name INTO record_name; OR
FETCH cursor_name INTO variable_list;
General Syntax to close a cursor is:
CLOSE cursor_name;

Exercise 12.A) Program to fetch employ data from EMP whose salary
> 5000 using cursor
Program:
DECLARE
VENO EMP999.ENO%TYPE;
VSAL EMP999.SAL%TYPE;
CURSOR C1 IS SELECT ENO,SAL FROM EMP999 WHERE SAL>5000;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO VENO,VSAL;
IF C1%FOUND THEN
DBMS_OUTPUT.PUT_LINE(VENO||' '||VNAME||' '||VSAL);
ELSE
EXIT;
END IF;
END LOOP;
CLOSE C1;
END;
/
Output
SQL> SELECT * FROM EMP999;

SQL> @E:\samson\lab10a.sql;
Exercise 12.B) Program to fetch employ salaries and calculate total
salary from EMP table using cursor
Program:

DECLARE
SALARY EMP999.SAL%TYPE;
S NUMBER:=0;
CURSOR C1 IS SELECT SAL FROM EMP999;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO SALARY;
S:=S+SALARY;
EXIT WHEN C1%NOTFOUND;
END LOOP;
DBMS_OUTPUT.PUT_LINE('TOTAL IS: ');
DBMS_OUTPUT.PUT_LINE(TO_CHAR(S));
DBMS_OUTPUT.PUT_LINE('ROWS FETCHED'||C1%ROWCOUNT);
CLOSE C1;
END;
/

Output:
SQL> SELECT * FROM EMP;

SQL> @E:\samson\lab10b.sql;
 Exercise - 13

Aim:Develop Programs using BEFORE and AFTER Triggers, Row

and Statement Triggers and INSTEAD OF Triggers
PL/SQL - Triggers

▪ A trigger is a PL/SQL block structure which is fired when a DML statements like Insert,
Delete, Update is executed on a database table.
▪ A trigger is triggered automatically when an associated DML statement is executed.
Benefits of Triggers
Triggers can be written for the following purposes −
• Generating some derived column values automatically
• Enforcing referential integrity
• Event logging and storing information on table access
• Auditing
• Synchronous replication of tables
• Imposing security authorizations
• Preventing invalid transactions
Syntax of Triggers
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)
DECLARE
Declaration-statements
BEGIN
Executable-statements
EXCEPTION
Exception-handling-statements
END;

CREATE [OR REPLACE ] TRIGGER trigger_name -:

This clause creates a trigger with the given name or overwrites an existing trigger with the same
name.
{BEFORE | AFTER | INSTEAD OF } :
This clause indicates at what time should the trigger get fired. i.e for example: before or after
updating a table.
INSTEAD OF is used to create a trigger on a view.
{INSERT [OR] | UPDATE [OR] | DELETE} -:
This clause determines the triggering event. More than one triggering events can be used together
separated by OR keyword.
The trigger gets fired at all the specified triggering event.
[OF col_name] -:
This clause is used with update triggers. This clause is used when you want to trigger an event only
when a specific column is updated.
[ON table_name] :
- This clause identifies the name of the table or view to which the trigger is associated.
[REFERENCING OLD AS O NEW AS N] :-
This clause is used to reference the old and new values of the data being changed. By default, you
reference the values as :old.column_name or :new.column_name.
The reference names can also be changed from old (or new) to any other user-defined name. You
cannot reference old values when inserting a record, or new values when deleting a record, because
they do not exist.
[FOR EACH ROW] :-
This clause is used to determine whether a trigger must fire when each row gets affected ( i.e. a Row
Level Trigger) or just once when the entire sql statement is executed(i.e.statement level Trigger).
WHEN (condition) :-
This clause is valid only for row level triggers. The trigger is fired only for rows that satisfy the
condition specified.
Types of PL/SQL Triggers

There are two types of triggers based on the which level it is triggered.
1) Row level trigger - An event is triggered for each row upated, inserted or deleted.
2) Statement level trigger - An event is triggered for each sql statement executed.
PL/SQL Trigger Execution Hierarchy

The following hierarchy is followed when a trigger is fired.

1) BEFORE statement trigger fires first.
2) Next BEFORE row level trigger fires, once for each row affected.
3) Then AFTER row level trigger fires once for each affected row. This events will alternates
between BEFORE and AFTER row level triggers.
4) Finally the AFTER statement level trigger fires.

How To know Information about Triggers.

We can use the data dictionary view 'USER_TRIGGERS' to obtain information about any trigger.
The below statement shows the structure of the view 'USER_TRIGGERS'
DESC USER_TRIGGERS;
NAME Type
TRIGGER_NAME VARCHAR2(30)
TRIGGER_TYPE VARCHAR2(16)
TRIGGER_EVENT VARCHAR2(75)
TABLE_OWNER VARCHAR2(30)
BASE_OBJECT_TYPE VARCHAR2(16)
TABLE_NAME VARCHAR2(30)
COLUMN_NAME VARCHAR2(4000)
This view stores information about header and body of the trigger.

SELECT * FROM user_triggers WHERE trigger_name = 'Before_Update_Stat_product';

The above sql query provides the header and body of the trigger 'Before_Update_Stat_product'.
You can drop a trigger using the following command.
DROP TRIGGER trigger_name;

Exercise 13.A) Program to create trigger and display difference Salary

when ever updated EMP table
Program to create trigger:
CREATE OR REPLACE TRIGGER salary_update
BEFORE UPDATE ON emp
FOR EACH ROW
DECLARE
sal_diff number;
BEGIN
sal_diff := :NEW.sal - :OLD.sal;
dbms_output.put_line('Old salary: ' || :OLD.sal);
dbms_output.put_line('New salary: ' || :NEW.sal);
dbms_output.put_line('Salary difference: ' || sal_diff);
END;
/
Output:
SQL> @E:\samson\lab11A.sql
Trigger created.
SQL> update emp set sal=3000 where eno=1;
Old salary: 2000
New salary: 3000
Salary difference: 1000
1 row updated.
Exercise 13.B) Program to create trigger to store automatically the
deleted records from emp table in a separate table called
deleted_emps.
CREATE OR REPLACE TRIGGER emp_delete
after delete ON emp
for each row
DECLARE
veno EMP.eno%type;
vname EMP.name%type;
vsal EMP.sal%type;
BEGIN
veno:= :OLD.eno;
vname:= :OLD.name;
vsal:= :old.sal;
insert into deleted_emps values(veno,vname,vsal);

END;
/
SQL> create table deleted_emps as select * from emp;
Table created.
SQL> delete from deleted_emps;
SQL> @E:\samson\lab11b.sql
Trigger created.
SQL> delete from emp where eno=1;
1 row deleted.
SQL> select * from deleted_emps;
ENO NAME SAL
---------- ---------- ----------
1 TEJA 3000
SQL> select * from emp;
ENO NAME SAL
---------- ---------- ----------
2 HARI 3000
3 ANIL 10000
STATEMENT-LEVEL TRIGGERS
A statement-level trigger is fired whenever a trigger event occurs on a table regardless of how many
rows are affected. In other words, a statement-level trigger executes once for each transaction.
For example, if you update 1000 rows in a table, then a statement-level trigger on that table
would only be executed once.
Due to its features, a statement-level trigger is not often used for data-related activities like auditing
the changes of the data in the associated table. It’s typically used to enforce extra security measures
on the kind of transaction that may be performed on a table.
By default, the statement CREATE TRIGGER creates a statement-level trigger when you omit the
FOR EACH ROW clause.
Exercise 13.C) Program to create statement level trigger to raise an
application error when update credit_limit of Customer table between
28th to 31st of every month
SQL> create table customer
(id number(4),
name varchar2(20),
credit_limit number(10,2));
SQL> insert into customer values( 1, 'TEJA', 50000);
SQL> insert into customer values( 2, 'ANIL', 150000);
SQL> insert into customer values( 3, 'BHARAT', 250000);
Program to create trigger:
CREATE OR REPLACE TRIGGER customers_credit_trg
BEFORE UPDATE OF credit_limit
ON customer
DECLARE
day_of_month NUMBER;
BEGIN
-- determine the transaction type
day_of_month := EXTRACT(DAY FROM sysdate)
IF day_of_month BETWEEN 28 AND 31 THEN
raise_application_error(-20100,'Cannot update customer credit from 28th to 31st');
END IF;
END;
/
SQL> @E:\samson\lab11C.sql
Trigger created.
SQL> update customer set credit_limit=100000 where id=1;
1 row updated.
SQL> select sysdate from dual;
SYSDATE
---------
22-JUN-21

Day changed in program:

day_of_month := 29;
SQL> @E:\samson\lab11C.sql
Trigger created.
SQL> update customer set credit_limit=150000 where id=1;
update customer set credit_limit=150000 where id=1
*
ERROR at line 1:
ORA-20100: Cannot update customer credit from 28th to 31st
ORA-06512: at "SYSTEM.CUSTOMERS_CREDIT_TRG", line 9
ORA-04088: error during execution of trigger 'SYSTEM.CUSTOMERS_CREDIT_TRG'