SRK INSTITUTE OF TECHNOLOGY

ENIKEPADU, VIJAYAWADA

SRK INSTITUTE OF TECHNOLOGY

VIJAYAWADA

DBMS LAB MANUAL

Department of MCA
(I MCA II SEM SRK23 regulation)

Prepared by

[Link]
Objective:
To understand the different issues involved in the design and implementation of a database system
To understand and use data definition language to write query for a database

Theory:
Oracle has many tools such as SQL * PLUS, Oracle Forms, Oracle Report Writer, Oracle Graphics
etc.
SQL * PLUS: The SQL * PLUS tool is made up of two distinct parts. These are
 Interactive SQL: Interactive SQL is designed for create, access and manipulate data structures
like tables and indexes.
 PL/SQL: PL/SQL can be used to developed programs for different applications.

Oracle Forms: This tool allows you to create a data entry screen along with the suitable menu
objects. Thus it is the oracle forms tool that handles data gathering and data validation in a
commercial application.

Report Writer: Report writer allows programmers to prepare innovative reports using data
from the oracle structures like tables, views etc. It is the report writer tool that handles the reporting
section of commercial application.

Oracle Graphics: Some of the data can be better represented in the form of pictures. The oracle
graphics tool allows programmers to prepare graphs using data from oracle structures like tables,
views etc.

SQL (Structured Query Language):

Structured Query Language is a database computer language designed for managing data in relational
database management systems (RDBMS), and originally based upon Relational Algebra. Its scope
includes data query and update, schema creation and modification, and data access control.
SQL was one of the first languages for Edgar F. Codd's relational model and became the most widely
used language for relational databases.
 IBM developed SQL in mid of 1970’s.
 Oracle incorporated in the year 1979.
 SQL used by IBM/DB2 and DS Database Systems.
 SQL adopted as standard language for RDBS by ASNI in 1989.
DATA TYPES:
1. CHAR (Size): This data type is used to store character strings values of fixed length. The size in
brackets determines the number of characters the cell can hold. The maximum number of character is
255 characters.

2. VARCHAR (Size) / VARCHAR2 (Size): This data type is used to store variable length
alphanumeric 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).
Number of virtually any magnitude may be stored up to 38 digits of precision. Number as large as
9.99 * 10 124. The precision (p) determines the number of places to the right of the decimal. If scale
is omitted then the default is zero. If precision is omitted, values are stored with their original
precision up to the maximum of 38 digits.

4. DATE: This data type is used to represent date and time. The standard format is DD- MM-YY as
in 17-SEP-2009. To enter dates other than the standard format, use the appropriate functions. Date
time stores date in the 24-Hours format. By default the time
in a date field is 12:00:00 am, if no time portion is specified. The default date for a date field is the
first day the current month.

5. LONG: This data type is used to store variable length character strings containing up to 2GB.
Long data can be used to store arrays of binary data in ASCII format. LONG values cannot be
indexed, and the normal character functions such as SUBSTR cannot be applied.

6. RAW: The RAW data type is used to store binary data, such as digitized picture or image. Data
loaded into columns of these data types are stored without any further conversion. RAW data type
can have a maximum length of 255 bytes. LONG RAW data type can contain up to 2GB.

SQL language is sub-divided into several language elements, including:

Clauses, which are in some cases optional, constituent components of statements and queries.

Expressions, which can produce either scalar values or tables consisting of columns and rows of
data.

Predicates which specify conditions that can be evaluated to SQL three-valued logic

(3VL) Boolean truth values and which are used to limit the effects of statements and queries, or to
change program flow.
Queries which retrieve data based on specific criteria.

Statements which may have a persistent effect on schemas and data, or which may control
transactions, program flow, connections, sessions, or diagnostics.

SQL statements also include the semicolon (";") statement terminator. Though not required on
every platform, it is defined as a standard part of the SQL grammar.
Insignificant white space is generally ignored in SQL statements and queries, making it easier to
format SQL code for readability.

There are five types of SQL statements. They are:

1. DATA DEFINITION LANGUAGE (DDL)

2. DATA MANIPULATION LANGUAGE (DML)

3. DATA RETRIEVAL LANGUAGE (DRL)

4. TRANSATIONAL CONTROL LANGUAGE (TCL)

5. DATA CONTROL LANGUAGE (DCL)

1. DATA DEFINITION LANGUAGE (DDL): The Data Definition Language (DDL) is used
to create and destroy databases and database objects. These commands will primarily be used
by database administrators during the setup and removal phases of a database project. Let's
take a look at the structure and usage of four basic DDL commands:

1. CREATE 2. ALTER 3. DROP 4. RENAME

1. CREATE:

(a)CREATE TABLE: This is used to create a new relation (table)

Syntax: CREATE TABLE <relation_name/table_name > (field_1 data_type(size),field_2
data_type(size), .. . );

Example:
SQL> CREATE TABLE Student (sno NUMBER (3), sname CHAR (10), class CHAR (5));

2. ALTER:
(a) ALTER TABLE ...ADD...: This is used to add some extra fields into existing relation.

Syntax: ALTER TABLE relation_name ADD (new field_1 data_type(size), new field_2
data_type(size),..);

Example: SQL>ALTER TABLE std ADD (Address CHAR(10));

(b) ALTER TABLE...MODIFY...: This is used to change the width as well as data type of fields of
existing relations.

Syntax: ALTER TABLE relation_name MODIFY (field_1 newdata_type(Size), field_2

newdata_type(Size), ... field_newdata_type(Size));

Example:SQL>ALTER TABLE student MODIFY(sname VARCHAR(10),class VARCHAR(5));

c) ALTER TABLE..DROP .... This is used to remove any field of existing relations.

Syntax: ALTER TABLE relation_name DROP COLUMN (field_name);

Example:SQL>ALTER TABLE student DROP column (sname);

d)ALTER TABLE..RENAME...: This is used to change the name of fields in existing relations.

Syntax: ALTER TABLE relation_name RENAME COLUMN (OLD field_name) to (NEW

field_name);

Example: SQL>ALTER TABLE student RENAME COLUMN sname to stu_name;

3. DROP TABLE: This is used to delete the structure of a relation. It permanently deletes the
records in the table.

Syntax: DROP TABLE relation_name;

Example: SQL>DROP TABLE std;

4. RENAME: It is used to modify the name of the existing database object.

Syntax: RENAME TABLE old_relation_name TO new_relation_name; Example: SQL>RENAME

TABLE std TO std1;
 LIST OF PROGRAMS

SQL

1. Queries to facilitate acquaintance of Built-In Functions, String Functions, Numeric Functions,

Date Functions and Conversion Functions.

2. Queries using operators in SQL

3. Queries to Retrieve and Change Data: Select, Insert, Delete, and Update

4. Queries using Group By, Order By, and Having Clauses

5. Queries on Controlling Data: Commit, Rollback, and Save point

6. Queries for Creating, Dropping, and Altering Tables, Views, and Constraints

7. Queries on Joins and Correlated Sub-Queries

8. Write a PL/SQL Code using Basic Variable, Anchored Declarations, and Usage of
Assignment Operation

9. Write a PL/SQL Code Bind and Substitution Variables. Printing in PL/SQL

10. Write a PL/SQL block using SQL and Control Structures in PL/SQL

11. Write a PL/SQL Code using Cursors, Exceptions and Composite Data Types

12. Write a PL/SQL Code using Procedures, Functions, and Packages FORMS
 Program 1

Aim: Queries to facilitate acquaintance of Built-In Functions, String Functions, Numeric

Functions, Date Functions and Conversion Functions.

1 .Character Functions:

Upper Returns char with all letters into upper case

lower Converts the mixed case or uppercase character strings to lowercase

Converts the first letter of each word to upper case and remaining letters to
Initcap
lowercase

Concat Joins values together you are limited to two arguments with concat

Substr This extracts a string of determined length

Length Shows the length of a string as a numeric value

Instr Finds numeric position of named character

Lpad Pads the character value right justified

rpad Pads the character value left justified

Trim Trims heading or trailing characters from a character string

Raplace To replace a set of character (String based)

Translate Change a character to a new described character(character based)

SQL> select upper ('oracle') "UPPER" from dual;
UPPER
------
ORACLE

SQL> select lower ('ORACLE') "LOWER" from dual;

LOWER
------
Oracle

SQL> select initcap('sql functions') "initcap" from dual;

initcap
-------------
Sql Functions

SQL> select concat('sql','functions') from dual;

CONCAT('SQL'
------------
Sqlfunctions

SQL> select substr('sqlfunctions',1,5) from dual;

SUBST
-----
Sqlfu

SQL> select substr('sqlfunctions',2,5) from dual;

SUBST
-----
qlfun
SQL> select substr('sqlfunctions',4,5) from dual;
SUBST
-----
funct

SQL> select length('sqlfunctions') from dual;

LENGTH('SQLFUNCTIONS')
----------------------
12

SQL> select instr('sqlfunctions','f') from dual;

INSTR('SQLFUNCTIONS','F')
-------------------------
4

SQL> select lpad(sal,15,'*') from emp;

LPAD(SAL,15,'*'
---------------
************800
***********1600
 ***********1250
***********2975
***********1250
***********2850
SQL> select rpad(sal,15,'*') from emp;
RPAD(SAL,15,'*'
---------------
800************
1600***********
1250***********
2975***********

SQL> select trim('s' from 'ssmiths') from dual;

TRIM
----
mith
SQL> select ltrim('ssmiths','s') from dual;
LTRIM
-----
miths
SQL> select rtrim('ssmiths','s') from dual;
RTRIM(
------
ssmith
SQL> select replace('jack and jue','j','bl') from dual;
REPLACE('JACKA
--------------
black and blue
SQL> select translate('jack','j','b') from dual;
TRAN
----
Back

Number Functions:

Round Rounds the value to specified decimal

Trunc Truncates the column, expression, or value to n decimal places

Power Calculates the power of the given value

Mod Finds the remainder of value1 divided by value1

Ceil Takes the height decimal value

Floor Takes the lowest decimal value

 SQL> select round(35.823,2), round(35.823,0), round(35.823,-1) from dual;
ROUND(35.823,2) ROUND(35.823,0) ROUND(35.823,-1)
--------------- --------------- ----------------
35.82 36 40

SQL> select trunc(35.823,2), trunc(35.823), trunc(35.823,-2) from dual;

TRUNC(35.823,2) TRUNC(35.823) TRUNC(35.823,-2)
--------------- ------------- ----------------
35.82 35 0

SQL> select mod(5,2) from dual;

MOD(5,2)
----------
1
SQL> select mod(sal,2000) from emp where job like 'SALESMAN';
MOD(SAL,2000)
-------------
1600
1250
1250
1500
SQL> select sal from emp where job like 'SALESMAN';
SAL
----------
1600
1250
SQL> select ceil(35.23), ceil(35.5), ceil(35.6) from dual;

CEIL(35.23) CEIL(35.5) CEIL(35.6)

----------- ---------- ----------
36 36 36

SQL> select floor(35.23), floor(35.5), floor(35.6) from dual;

FLOOR(35.23) FLOOR(35.5) FLOOR(35.6)

------------ ----------- -----------
35 35 35

Date Functions:

SYSDATE is a pseudo column that returns the current date and time. When we select sysdate it
will display in a dummy table called DUAL. Oracle date range between 1st jan 4712 BC and 31st
Dec 4712 AD.

Months_between It returns the numeric value. Finds the no. of months between date1
and date2, result may be positive or negative.
 Add_months It returns the date datatype. Adds n number of calendar months to
date, n must be an integer and it can be negative

Last_day It returns the date datatype. Date of the

Next_day It returns the date datatype. Date of the next specified day of the week
following date1, char may be number representing a day, or a
character

SQL> select sysdate from dual;

SYSDATE
---------
08-JUL-18

SQL> select months_between(sysdate, hiredate) from emp;

MONTHS_BETWEEN(SYSDATE,HIREDATE)
--------------------------------
354.728983
352.632208
352.567692
351.212854
345.374144
14 rows selected.

SQL> select months_between('01-jan-2010', sysdate) from dual;

MONTHS_BETWEEN('01-JAN-2010',SYSDATE)
-------------------------------------
-6.2451325

SQL> select last_day(sysdate) from dual;

LAST_DAY(
---------
31-JUL-18

SQL> select last_day(hiredate),last_day('15-feb-88') from emp;

LAST_DAY( LAST_DAY(
--------- ---------
31-DEC-80 29-FEB-88
28-FEB-81 29-FEB-88
28-FEB-81 29-FEB-88
30-APR-81 29-FEB-88

Sunday 1

Monday 2

Tuesday 3
Wednesday 4

Thursday 5

Friday 6

Saturday 7

SQL> select last_day(hiredate),last_day('15-feb-88') from emp;

LAST_DAY( LAST_DAY(
--------- ---------
31-DEC-80 29-FEB-88
28-FEB-81 29-FEB-88
28-FEB-81 29-FEB-88
30-APR-81 29-FEB-88
30-SEP-81 29-FEB-88
SQL> select next_day(sysdate, 'friday') from dual;
NEXT_DAY(
---------
09-JUL-18

SQL> select next_day(hiredate,'friday'), next_day(hiredate,6) from emp where deptno=10;

NEXT_DAY( NEXT_DAY(
--------- ---------
12-JUN-81 12-JUN-81
20-NOV-81 20-NOV-81
29-JAN-82 29-JAN-82

Month mid value 1-15

Day mid value Sunday

Year mid value 30-jun

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

ROUND(SYS
---------
11-JUL-18

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

ROUND(SYS
---------
01-JAN-19

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

ROUND(SYS
---------
01-JUL-18
SQL> select trunc(sysdate,'month'), trunc(sysdate,'year') from dual;
TRUNC(SYS TRUNC(SYS
--------- ---------
01-JUL-10 01-JAN-18

Conversion Functions:
To_char(number | date,[‘fmt’] Converts numbers or date to character format fmt

To_number(char) Converts char, which contains a number to a NUBER

To_date Converts the char value representing date, into a date value
according to fmt specified. If fmt is omitted, format is DD-
MM-YYYY

SQL> select to_char(3000, '$9999.99') from dual;

TO_CHAR(3
---------
$3000.00

SQL> select to_char(sysdate, 'fmday, ddth month yyyy') from dual;

TO_CHAR(SYSDATE,'FMDAY,DDTHMON
------------------------------
thursday, 8th july 2018

SQL> select to_char(sysdate, 'hh:mi:ss') from dual;

TO_CHAR(
--------
03:04:27

SQL> select to_char(sal,'$9999.99') from emp;

TO_CHAR(S
---------
$800.00
$1600.00
$1250.00
$2975.00
$1250.00

SQL> select empno,ename, job,sal from emp where sal>to_number('1500');

EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7499 ALLEN SALESMAN 1600
7566 JONES MANAGER 2975
7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 2450
7788 SCOTT ANALYST 3000
7839 KING PRESIDENT 5000
7902 FORD ANALYST 3000
 Program 2

AIM: Queries using operators in SQL

Q1) Create table student(sid,sname,mob_no)

SQL> create table student(sid number(2),sname varchar2(10) mob_no number(10));

Table created.

Q2) Insert records into student table

SQL> insert into student values(11,'rayudu',9988776655);

1 row created.

SQL> select * from student;

SID SNAME MOB_NO

---------- ---------- ----------
11 rayudu 9988776655

SQL> insert into student values(&id,'&name',&mob);

Enter value for id: 22
Enter value for name: sudha
Enter value for mob: 9977886655
old 1: insert into student values(&id,'&name',&mob)
new 1: insert into student values(22,'sudha',9977886655)

1 row created.

SQL> /
Enter value for id: 33
Enter value for name: sridhar
Enter value for mob: 9966778855
old 1: insert into student values(&id,'&name',&mob)
new 1: insert into student values(33,'sridhar',9966778855)

1 row created.

SQL> select * from student;

SID SNAME MOB_NO

---------- ---------- ----------
11 rayudu 9988776655
22 sudha 9977886655
33 sridhar 9966778855

Q3) Insert the values for the columns sid,sname only.

SQL> insert into student(sid,sname) values(44,'sudheer');

1 row created.

SQL> select * from student;

SID SNAME MOB_NO

---------- ---------- ----------
11 rayudu 9988776655
22 sudha 9977886655
33 sridhar 9966778855
44 sudheer

SQL> insert into student(sid,mob_no) values(55,9988667755);

1 row created.

SQL> select * from student;

SID SNAME MOB_NO

---------- ---------- ----------
11 rayudu 9988776655
22 sudha 9977886655
33 sridhar 9966778855
44 sudheer
55 9988667755

Q4) create a table std_mob (sid,mob_no) from student table with records also.

SQL> create table std_mob(sid number(2),mob_no number(10));

Table created.

SQL> insert into std_mob (select sid,mob_no from student);

5 rows created.

SQL> select * from std_mob;

SID MOB_NO
---------- ----------
11 9988776655
22 9977886655
33 9966778855
44
55 9988667755

Q5) create a duplicate table from student table without records

SQL> create table dup_std_all as (select * from student);

Table created.

SQL> select * from dup_std_all;

SID SNAME MOB_NO

---------- ---------- ----------
11 rayudu 9988776655
22 sudha 9977886655
33 sridhar 9966778855
44 sudheer
 55 9988667755

Q6) create table sailors(sid,sname,rating,age) with sid as primary key.

SQL >create table sailors(sid number(3),sname varchar2(15),rating number(2),age number(5,2),primary

key(sid));
Table created.

Q7) insert values into sailors.

SQL> insert into sailors (sid, sname, rating, age)

2 values (22, 'Dustin', 7, 45.0);

1 row created.

SQL> insert into sailors (sid, sname, rating, age)

2 values (29, 'Brutus', 1, 33.0);

1 row created.

SQL> insert into sailors (sid, sname, rating, age)

2 values (31, 'Lubber', 8, 55.5);

1 row created.

SQL> insert into sailors (sid, sname, rating, age)

2 values (32, 'Andy', 8, 25.5);

1 row created.

SQL> insert into sailors (sid, sname, rating, age)

2 values (58, 'Rusty', 10, 35.0);

1 row created.

SQL> insert into sailors (sid, sname, rating, age)

2 values (64, 'Horatio', 7, 35.0);

1 row created.

SQL> insert into sailors (sid, sname, rating, age)

2 values (71, 'Zorba', 10, 16.0);

1 row created.

SQL> insert into sailors (sid, sname, rating, age)

2 values (74, 'Horatio', 9, 35.0);

1 row created.

SQL> insert into sailors (sid, sname, rating, age)

2 values (85, 'Art', 3, 25.5);

1 row created.

SQL> insert into sailors (sid, sname, rating, age)

2 values (95, 'Bob', 3, 63.5);
1 row created.

Q8) Find all sailor names from sailors.

SQL> select sname from sailors;

SNAME
---------------
Dustin
Brutus
Lubber
Andy
Rusty
Horatio
Zorba
Horatio
Art
Bob

10 rows selected.

Q9) Find all distinct sailor names from sailors.

SQL> select distinct(sname) from sailors;

SNAME
---------------
Andy
Art
Bob
Brutus
Dustin
Horatio
Lubber
Rusty
Zorba

9 rows selected.

Q10) Find sailor names and ratings whose rating is 7.

SQL> select sname,rating from sailors where rating=7

SNAME RATING
--------------- ----------
Dustin 7
Horatio 7

Q11) Find sailor names and ratings whose rating is greater than 7.

SQL> select sname,rating from sailors where rating>7;

SNAME RATING
--------------- ----------
Lubber 8
Andy 8
Rusty 10
Zorba 10
Horatio 9

Q12) Find sailor names and rating whose rating is less than 6 using not.

SQL> select sname,rating from sailors where not(rating>6);

SNAME RATING
--------------- ----------
Brutus 1
Art 3
Bob 3

Q13) Find sailor names and rating whose rating is between 3 and 7.

SQL> select sname,rating from sailors where rating between 3 and 7;

SNAME RATING
--------------- ----------
Dustin 7
Horatio 7
Art 3
Bob 3

Q14) Find sailor names and rating whose name starts with B.

SQL> select sname,rating from sailors where sname like 'B%';

SNAME RATING
--------------- ----------
Brutus 1
Bob 3

Q15) Find sailor names and rating whose name ends with y.

SQL> select sname,rating from sailors where sname like '%y';

SNAME RATING
--------------- ----------
Andy 8
Rusty 10

Q15) Find sailor names and rating whose name contains the substring or.

SQL> select sname,rating from sailors where sname like '%or%';

SNAME RATING
--------------- ----------
Horatio 7
Zorba 10
Horatio 9

Q16) Find sailor names and rating whose name contains exactly 3 characters.
SQL> select sname,rating from sailors where sname like '_ _ _';

SNAME RATING
--------------- ----------
Art 3
Bob 3

Q17) Find sailor names and rating whose name does not contains letter r.

SQL> select sname,rating from sailors where sname not like '%r%';

SNAME RATING
--------------- ----------
Dustin 7
Andy 8
Rusty 10
Bob 3

Q18) Diasplay all sailor names and rating whose name does not contains letter r in increasing order of
rating

SQL> select sname,rating from sailors where sname not like '%r%' order by rating;

SNAME RATING
--------------- ----------
Bob 3
Dustin 7
Andy 8
Rusty 10

Q18) Display sailor names and rating whose name does not contains letter r and display in decreasing
order of rating.

SQL>select sname,rating from sailors where sname not like '%r%' order by rating desc;

SNAME RATING
--------------- ----------
Rusty 10
Andy 8
Dustin 7
Bob 3

Q19) Display sailor names and rating whose rating is 8 in alphabetical order.

SQL> select sname,rating from sailors where rating=8 order by sname;

SNAME RATING
--------------- ----------
Andy 8
Lubber 8

Q20) select sailor name and rating whose rating is either greater than 7 or rating less than 3

SQL> select sname,rating from sailors where rating>7 or rating<3;

SNAME RATING
--------------- ----------
Brutus 1
Lubber 8
Andy 8
Rusty 10
Zorba 10
Horatio 9

6 rows selected.

Q21) create table boats(bid,bname,color) with bid as primary key.

SQL> create table boats(bid integer, bname varchar(10),color varchar(10),

2 primary key(bid));

Table created.

Q22) insert values into boats.

SQL> insert into boats (bid, bname, color)

2 values (101, 'Interlake', 'blue');
1 row created.
SQL> insert into boats (bid, bname, color)
2 values (102, 'Interlake', 'red');
1 row created.
SQL> insert into boats (bid, bname, color)
2 values (103, 'Clipper', 'green');

1 row created.

SQL> insert into boats (bid, bname, color)

2 values (104, 'Marine', 'red');

1 row created.

Q23) Create table reserves(sid,bid,day) where sid,bid,day as primary key and sid,did as foreign key
referencing the tables sailors and boats respectively.

SQL> create table reserves(sid integer,bid integer,day date,

2 primary key(sid,bid,day),
3 foreign key(sid) references sailors,
4 foreign key(bid) references boats);

Table created.

Q24) Insert values into reserves.

SQL> insert into reserves (sid, bid, day)

2 values (22, 101, '10/oct/98');

1 row created.

SQL> insert into reserves (sid, bid, day)

2 values (22, 102, '10/oct/98');
1 row created.

SQL> insert into reserves (sid, bid, day)

2 values (22, 103, '10/aug/98');

1 row created.
SQL> insert into reserves (sid, bid, day)
2 values (22, 104, '10/jul/98');

1 row created.

SQL> insert into reserves (sid, bid, day)

2 values (31, 102, '11/oct/98');

1 row created.

SQL> insert into reserves (sid, bid, day)

2 values (31, 103, '11/jun/98');

1 row created.
SQL> insert into reserves (sid, bid, day)
2 values (31, 104, '11/dec/98');
1 row created.
SQL> insert into reserves (sid, bid, day)
2 values (64, 101, '9/may/98');

1 row created.

SQL> insert into reserves (sid, bid, day)

2 values (64, 102, '9/aug/98');

1 row created.

SQL> insert into reserves (sid, bid, day)

2 values (74, 103, '9/aug/98');
1 row created.

Q25) Find the sailor names who have reserved atleast one boat.

SQL> select [Link],[Link] from sailors a, reserves b where [Link]=[Link];

SNAME BID
--------------- ----------
Dustin 101
Dustin 102
Dustin 103
Dustin 104
Lubber 102
Lubber 103
Lubber 104
Horatio 101
Horatio 102
Horatio 103

10 rows selected.
Q26) Find the sailors name who have reserved boat number 103.

SQL> select [Link],[Link] from sailors a, reserves b where [Link]=[Link] and [Link]=103;

SNAME BID
--------------- ----------
Dustin 103
Lubber 103
Horatio 103

Q27) Find the sailors name who have reserved boat ‘Marine’.

SQL> select [Link],[Link] from sailors a, reserves b,boats c where [Link]=[Link] and [Link]=[Link] and
[Link]='Marine';

SNAME BID
--------------- ----------
Dustin 104
Lubber 104
Q28) Find the sailors who have reserved red color boat.

SQL> select [Link], [Link], [Link] from sailors a, reserves b,boats c where [Link]=[Link] and
[Link]=[Link] and [Link]='red'
SNAME RATING BNAME
--------------- ---------- ----------
Dustin 7 Interlake
Dustin 7 Marine
Lubber 8 Interlake
Lubber 8 Marine
Horatio 7 Interlake

Q29) Find the sailors who have reserved either red or blue color boat.

SQL> (select [Link],[Link],[Link] from sailors a, reserves b,boats c

2 where [Link]=[Link] and [Link]=[Link] and [Link]='red')
3 union
4 (select [Link],[Link],[Link] from sailors a, reserves b,boats c
5 where [Link]=[Link] and [Link]=[Link] and [Link]='blue');

SNAME BNAME COLOR

--------------- ---------- ----------
Dustin Interlake blue
Dustin Interlake red
Dustin Marine red
Horatio Interlake blue
Horatio Interlake red
Lubber Interlake red
Lubber Marine red
7 rows selected.

Q30) Find the sailor names who have reserved both red and blue color boats.

SQL> (select [Link] from sailors a, reserves b,boats c

2 where [Link]=[Link] and [Link]=[Link] and [Link]='red')
3 intersect
4 (select [Link] from sailors a, reserves b,boats c
5 where [Link]=[Link] and [Link]=[Link] and [Link]='blue');
SNAME
---------------
Dustin
Horatio

Q31) Find the sailor names who have reserved red color boat but not blue color boat.
SQL> (select [Link] from sailors a, reserves b,boats c
2 where [Link]=[Link] and [Link]=[Link] and [Link]='red')
3 minus
4 (select [Link] from sailors a, reserves b,boats c
5 where [Link]=[Link] and [Link]=[Link] and [Link]='blue');
SNAME
---------------
Lubber
Q32) Find all the sailor names who reserved either red or blue color boats.
SQL> (select [Link] from sailors a, reserves b,boats c
2 where [Link]=[Link] and [Link]=[Link] and [Link]='red')
3 union
4 (select [Link] from sailors a, reserves b,boats c
5 where [Link]=[Link] and [Link]=[Link] and [Link]='blue');

SNAME
---------------
Dustin
Horatio
Lubber

Q33) Find all the sailor names(with duplicates) who reserved either red or blue color boats.

SQL> (select [Link] from sailors a, reserves b,boats c

2 where [Link]=[Link] and [Link]=[Link] and [Link]='red')
3 union all
4 (select [Link] from sailors a, reserves b,boats c
5 where [Link]=[Link] and [Link]=[Link] and [Link]='blue');

SNAME
---------------
Dustin
Dustin
Lubber
Lubber
Horatio
Dustin
Horatio

7 rows selected.

Q34) Find the sailors who have reserved atleast one boat using nested query.
SQL> select * from sailors where sid in (select sid from reserves);

SID SNAME RATING AGE

---------- --------------- ---------- ----------
22 Dustin 7 45
31 Lubber 8 55.5
64 Horatio 7 35
74 Horatio 9 35
Q35) Find the sailors who have not reserved atleast one boat using nested query.

SQL> select * from sailors where sid not in (select sid from reserves);

SID SNAME RATING AGE

---------- --------------- ---------- ----------
29 Brutus 1 33
32 Andy 8 25.5
58 Rusty 10 35
71 Zorba 10 16
85 Art 3 25.5
95 Bob 3 63.5

6 rows selected.

Q36) Find the sailors whose sid is 22 or 32.

SQL> select * from sailors where sid in (22,32);

SID SNAME RATING AGE

---------- --------------- ---------- ----------
32 Andy 8 25.5
22 Dustin 7 45

Q36) Find the sailors whose who have reserved boat number 103 using nested query.

SQL> select * from sailors where sid in

2 (select sid from reserves where bid=103);

SID SNAME RATING AGE

---------- --------------- ---------- ----------
22 Dustin 7 45
31 Lubber 8 55.5
74 Horatio 9 35

Q37) Find the sailors who have not reserved red color boat.

SQL> select * from sailors where sid not in

2 (select sid from reserves where bid in
3 (select bid from boats where color='red'));

SID SNAME RATING AGE

---------- --------------- ---------- ----------
29 Brutus 1 33
32 Andy 8 25.5
58 Rusty 10 35
71 Zorba 10 16
74 Horatio 9 35
85 Art 3 25.5
95 Bob 3 63.5

7 rows selected.

Q38) Find the sailor names who reserved boat number 103

SQL> select [Link] from sailors a where exists

2 (select * from reserves b where [Link]=103 and [Link]=[Link]);
SNAME
---------------
Dustin
Lubber
Horatio

Q39) Find the sailor names who do not reserved boat number 103

SQL> select [Link] from sailors a where not exists

2 (select * from reserves b where [Link]=103 and [Link]=[Link]);

SNAME
---------------
Brutus
Andy
Rusty
Horatio
Zorba
Art
Bob

7 rows selected.

Q40) Find the sailor names who reserved all the boats.

SQL> select [Link] from sailors a where not exists

2 ((select [Link] from boats b)
3 minus
4 (select [Link] from reserves c where [Link]=[Link]));

SNAME
---------------
Dustin
 Program 3
Aim: Queries to Retrieve and Change Data: Select, Insert, Delete, and Update

Q1) Create table student(sid,sname,mob_no)

SQL> create table student(sid number(2),sname varchar2(10), mob_no number(10));

Table created.

Q2) Insert records into student table

SQL> insert into student values(11,'rayudu',9988776655);

1 row created.

SQL> select * from student;

SID SNAME MOB_NO

---------- ---------- ----------
11 rayudu 9988776655

SQL> insert into student values(&id,'&name',&mob);

Enter value for id: 22
Enter value for name: sudha
Enter value for mob: 9977886655
old 1: insert into student values(&id,'&name',&mob)
new 1: insert into student values(22,'sudha',9977886655)

1 row created.

SQL> /
Enter value for id: 33
Enter value for name: sridhar
Enter value for mob: 9966778855
old 1: insert into student values(&id,'&name',&mob)
new 1: insert into student values(33,'sridhar',9966778855)

1 row created.

SQL> select * from student;

SID SNAME MOB_NO

---------- ---------- ----------
11 rayudu 9988776655
22 sudha 9977886655
33 sridhar 9966778855

Q3) Insert the values for the columns sid,sname only.

SQL> insert into student(sid,sname) values(44,'sudheer');

1 row created.

SQL> select * from student;

SID SNAME MOB_NO

---------- ---------- ----------
11 rayudu 9988776655
22 sudha 9977886655
33 sridhar 9966778855
44 sudheer

SQL> insert into student(sid,mob_no) values(55,9988667755);

1 row created.

SQL> select * from student;

SID SNAME MOB_NO

---------- ---------- ----------
11 rayudu 9988776655
22 sudha 9977886655
33 sridhar 9966778855
44 sudheer
55 9988667755

Q4) create a table std_mob (sid,mob_no) from student table with records also.

SQL> create table std_mob(sid number(2),mob_no number(10));

Table created.

SQL> insert into std_mob (select sid,mob_no from student);

5 rows created.

SQL> select * from std_mob;

SID MOB_NO
---------- ----------
11 9988776655
22 9977886655
33 9966778855
44
55 9988667755

Q5) create a duplicate table from student table without records

SQL> create table dup_std_all as (select * from student);

Table created.

SQL> select * from dup_std_all;

SID SNAME MOB_NO

---------- ---------- ----------
11 rayudu 9988776655
22 sudha 9977886655
33 sridhar 9966778855
44 sudheer
55 9988667755

Q6) create table sailors(sid,sname,rating,age) with sid as primary key.

SQL> create table sailors(sid number(3),sname varchar2(15),rating number(2),age number(5,2),primary

key(sid));

Table created.

Q7) insert values into sailors.

SQL> insert into sailors (sid, sname, rating, age)

2 values (22, 'Dustin', 7, 45.0);

1 row created.

SQL> insert into sailors (sid, sname, rating, age)

2 values (29, 'Brutus', 1, 33.0);

1 row created.

SQL> insert into sailors (sid, sname, rating, age)

2 values (31, 'Lubber', 8, 55.5);

1 row created.

SQL> insert into sailors (sid, sname, rating, age)

2 values (32, 'Andy', 8, 25.5);

1 row created.

SQL> insert into sailors (sid, sname, rating, age)

2 values (58, 'Rusty', 10, 35.0);

1 row created.

SQL> insert into sailors (sid, sname, rating, age)

2 values (64, 'Horatio', 7, 35.0);

1 row created.

SQL> insert into sailors (sid, sname, rating, age)

2 values (71, 'Zorba', 10, 16.0);

1 row created.

SQL> insert into sailors (sid, sname, rating, age)

2 values (74, 'Horatio', 9, 35.0);

1 row created.

SQL> insert into sailors (sid, sname, rating, age)

2 values (85, 'Art', 3, 25.5);
1 row created.

SQL> insert into sailors (sid, sname, rating, age)

2 values (95, 'Bob', 3, 63.5);

1 row created.

Q8) create table boats(bid,bname,color) with bid as primary key.

SQL> create table boats(bid integer, bname varchar(10),color varchar(10),

2 primary key(bid));

Table created.

Q9) insert values into boats.

SQL> insert into boats (bid, bname, color)

2 values (101, 'Interlake', 'blue');
1 row created.
SQL> insert into boats (bid, bname, color)
2 values (102, 'Interlake', 'red');
1 row created.
SQL> insert into boats (bid, bname, color)
2 values (103, 'Clipper', 'green');

1 row created.

SQL> insert into boats (bid, bname, color)

2 values (104, 'Marine', 'red');

1 row created.

Q10) Create table reserves(sid,bid,day) where sid,bid,day as primary key and sid,did as foreign key
referencing the tables sailors and boats respectively.

SQL> create table reserves(sid integer,bid integer,day date,

2 primary key(sid,bid,day),
3 foreign key(sid) references sailors,
4 foreign key(bid) references boats);

Table created.

Q11) Insert values into reserves.

SQL> insert into reserves (sid, bid, day)

2 values (22, 101, '10/oct/98');

1 row created.

SQL> insert into reserves (sid, bid, day)

2 values (22, 102, '10/oct/98');
1 row created.

SQL> insert into reserves (sid, bid, day)

2 values (22, 103, '10/aug/98');
1 row created.

SQL> insert into reserves (sid, bid, day)

2 values (22, 104, '10/jul/98');

1 row created.

SQL> insert into reserves (sid, bid, day)

2 values (31, 102, '11/oct/98');

1 row created.

SQL> insert into reserves (sid, bid, day)

2 values (31, 103, '11/jun/98');

1 row created.
SQL> insert into reserves (sid, bid, day)
2 values (31, 104, '11/dec/98');
1 row created.
SQL> insert into reserves (sid, bid, day)
2 values (64, 101, '9/may/98');

1 row created.

SQL> insert into reserves (sid, bid, day)

2 values (64, 102, '9/aug/98');

1 row created.

SQL> insert into reserves (sid, bid, day)

2 values (74, 103, '9/aug/98');
1 row created.

Q12) Find all sailor names from sailors.

SQL> select sname from sailors;

SNAME
---------------
Dustin
Brutus
Lubber
Andy
Rusty
Horatio
Zorba
Horatio
Art
Bob

10 rows selected.

Q13) Find all distinct sailor names from sailors.

SQL> select distinct(sname) from sailors;

SNAME
---------------
Andy
Art
Bob
Brutus
Dustin
Horatio
Lubber
Rusty
Zorba

9 rows selected.

Q14) Find sailor names and ratings whose rating is 7.

SQL> select sname,rating from sailors where rating=7

SNAME RATING
--------------- ----------
Dustin 7
Horatio 7

Q15) Find sailor names and ratings whose rating is greater than 7.

SQL> select sname,rating from sailors where rating>7;

SNAME RATING
--------------- ----------
Lubber 8
Andy 8
Rusty 10
Zorba 10
Horatio 9

Q16) Find sailor names and rating whose rating is less than 6 using not.

SQL> select sname,rating from sailors where not(rating>6);

SNAME RATING
--------------- ----------
Brutus 1
Art 3
Bob 3

Q17) Find sailor names and rating whose rating is between 3 and 7.

SQL> select sname,rating from sailors where rating between 3 and 7;

SNAME RATING
--------------- ----------
Dustin 7
Horatio 7
Art 3
Bob 3
Q18) Find sailor names and rating whose name starts with B.

SQL> select sname,rating from sailors where sname like 'B%';

SNAME RATING
--------------- ----------
Brutus 1
Bob 3

Q19) Find sailor names and rating whose name ends with y.

SQL> select sname,rating from sailors where sname like '%y';

SNAME RATING
--------------- ----------
Andy 8
Rusty 10

Q20) Find sailor names and rating whose name contains the substring or.

SQL> select sname,rating from sailors where sname like '%or%';

SNAME RATING
--------------- ----------
Horatio 7
Zorba 10
Horatio 9

Q21) Find sailor names and rating whose name contains exactly 3 characters.

SQL> select sname,rating from sailors where sname like '_ _ _';

SNAME RATING
--------------- ----------
Art 3
Bob 3

Q22) Find sailor names and rating whose name does not contains letter r.

SQL> select sname,rating from sailors where sname not like '%r%';

SNAME RATING
--------------- ----------
Dustin 7
Andy 8
Rusty 10
Bob 3

Q23) select sailor name and rating whose rating is either greater than 7 or rating less than 3

SQL> select sname,rating from sailors where rating>7 or rating<3;

SNAME RATING
--------------- ----------
Brutus 1
Lubber 8
Andy 8
Rusty 10
Zorba 10
Horatio 9

6 rows selected.
Q24) Modify the color to yellow whose boat id=101.

SQL> update boats set color='yellow' where bid=101;

1 row updated.

SQL> select * from boats;

BID BNAME COLOR

---------- ---------- ----------
101 Interlake yellow
102 Interlake red
103 Clipper green
104 Marine red

Q25)Modify the color to yellow whose boat id<=103

SQL> update boats set color='yellow' where bid<=103;

3 rows updated.

SQL> select * from boats;

BID BNAME COLOR

---------- ---------- ----------
101 Interlake yellow
102 Interlake yellow
103 Clipper yellow
104 Marine red
Q26)Modify the color to green whose boat id not less than or equal to 102

SQL> update boats set color='green' where not(bid<=102);

2 rows updated.

SQL> select * from boats;

BID BNAME COLOR

---------- ---------- ----------
101 Interlake yellow
102 Interlake yellow
103 Clipper green
104 Marine green
Q27) Create a table employee(eid,ename,salary) and insert values into it.

SQL> create table employee(eid number(2),ename varchar2(10),

2 salary number(10,2));

Table created.

SQL> insert into employee values(&id,'&name',&sal);

Enter value for id: 22
Enter value for name: sudhakar
Enter value for sal: 30000
old 1: insert into employee values(&id,'&name',&sal)
new 1: insert into employee values(22,'sudhakar',30000)

1 row created.

SQL> /
Enter value for id: 66
Enter value for name: praneeth
Enter value for sal: 32000
old 1: insert into employee values(&id,'&name',&sal)
new 1: insert into employee values(66,'praneeth',32000)

1 row created.

SQL> /
Enter value for id: 55
Enter value for name: rajesh
Enter value for sal: 33000
old 1: insert into employee values(&id,'&name',&sal)
new 1: insert into employee values(55,'rajesh',33000)

1 row created.

SQL> select * from employee;

EID ENAME SALARY

---------- ---------- ----------
22 sudhakar 30000
66 praneeth 32000
55 rajesh 33000

Q28) Delete an employee whose eid is 22.

SQL> delete from employee where eid=22;

1 row deleted.

SQL> select * from employee;

EID ENAME SALARY

---------- ---------- ----------
66 praneeth 32000
55 rajesh 33000

Q29)Delete all the employees.

SQL> delete from employee;

2 rows deleted.

SQL> select * from employee;

no rows selected
SQL> commit;

Commit complete.

Program 4
Aim:Queries using Group By, Order By, and Having Clauses

SQL> create table loan(lid number(3),amount number(10,2),

2 emi number(10,2),branch varchar2(10));

Table created.

SQL> insert into loan values(11,10000,1100,'vizag');

1 row created.

SQL> insert into loan values(77,70000,7100,'guntur');

1 row created.

SQL> insert into loan values(22,20000,2100,'eluru');

1 row created.

SQL> insert into loan values(99,90000,9100,'vizag');

1 row created.

SQL> insert into loan values(55,50000,5100,'guntur');

1 row created.

SQL> insert into loan values(66,60000,6100,'vizag');

1 row created.

SQL> select * from loan;

LID AMOUNT EMI BRANCH

---------- ---------- ---------- ----------
11 10000 1100 vizag
77 70000 7100 guntur
22 20000 2100 eluru
99 90000 9100 vizag
55 50000 5100 guntur
66 60000 6100 vizag

6 rows selected.
Q1.1)Find the number of records in the table loan

SQL> select count(*) from loan;

COUNT(*)
----------
6

Q1.2)Find the number of loans whose amount>50000

SQL> select count(*) from loan where amount>50000;

COUNT(*)
----------
3

Q1.3)Find the number of loans whose branch name starts with v

SQL> select count(*) from loan where branch like 'v%';

COUNT(*)
----------
3

Q1.4)Find the smallest loan amount

SQL> select min(amount) from loan;

MIN(AMOUNT)
-----------
10000

Q1.5)Find the largest loan amount

SQL> select max(amount) from loan;

MAX(AMOUNT)
-----------
90000
Q1.6)Find the sum of all emi’s

SQL> select sum(emi) from loan;

SUM(EMI)
----------
30600

Q1.7)Find the average of all emi’s

SQL> select avg(emi) from loan;

AVG(EMI)
----------
5100

Q1.8)Find the average of all emi’s from vizag branch

SQL> select avg(emi) from loan where branch='vizag';

 AVG(EMI)
----------
5433.33333

Q1.9)Find the average of emi’s for all branches

SQL> select avg(emi),branch from loan group by branch;

AVG(EMI) BRANCH
---------- ----------
2100 eluru
6100 guntur
5433.33333 vizag

Q1.10)Find the branches and avg(emi’s) whose average emi’s>2500

SQL> select avg(emi),branch from loan group by branch having avg(emi)>2500;

AVG(EMI) BRANCH
---------- ----------
6100 guntur
5433.33333 vizag

Q1.11)Find the average of emi’s for all the branches whose name conatins the letter u

SQL> select avg(emi),branch from loan where branch like '%u%'

2 group by branch;

AVG(EMI) BRANCH
---------- ----------
2100 eluru
6100 guntur

Q1.12)Display the first row of the loan table

select * from loan where rownum<=1;

LID AMOUNT EMI BRANCH

---------- ---------- ---------- ----------
11 10000 1100 vizag

Q1.13)Display the first three rows of the loan table

SQL> select * from loan where rownum<=3;

LID AMOUNT EMI BRANCH

---------- ---------- ---------- ----------
11 10000 1100 vizag
77 70000 7100 guntur
22 20000 2100 eluru
Q1.14)Display the first three rows of the loan table in the order of lid’s

SQL> select * from loan where rownum<=3 order by lid;

LID AMOUNT EMI BRANCH

---------- ---------- ---------- ----------
11 10000 1100 vizag
22 20000 2100 eluru
77 70000 7100 guntur

Q1.15)Find the last row number of the table loan

SQL> select max(rownum) from loan;

MAX(ROWNUM)
-----------
6

Q1.16)Display all the rows except the last row from the table loan

SQL> select * from loan where rownum<(select max(rownum) from loan);

LID AMOUNT EMI BRANCH

---------- ---------- ---------- ----------
11 10000 1100 vizag
77 70000 7100 guntur
22 20000 2100 eluru
99 90000 9100 vizag
55 50000 5100 guntur

Q1.17)Display the last row of the table loan

SQL> (select * from loan)
2 minus
3 (select * from loan where rownum<(select max(rownum) from loan));

LID AMOUNT EMI BRANCH

---------- ---------- ---------- ----------
66 60000 6100 vizag

SQL> commit;

Commit complete

SQL> exit;
 Program 5
AIM: Queries on Controlling Data: Commit, Rollback, and Save point
Q1) Execute save point,rollback for insertion operation

SQL> select * from employee;

EID ENAME SALARY AGE

---------- --------------- ---------- ----------
44 satish 24525.25 24
55 suresh 25005.25 28

SQL> savepoint for_insert;

Savepoint created.

SQL> insert into employee values(77,'praneeth',26000.24,31);

1 row created.

SQL> select * from employee;

EID ENAME SALARY AGE

---------- --------------- ---------- ----------
44 satish 24525.25 24
77 praneeth 26000.24 31
55 suresh 25005.25 28

SQL> rollback to for_insert;

Rollback complete

select * from employee;

EID ENAME SALARY AGE

---------- --------------- ---------- ----------
44 satish 24525.25 24
55 suresh 25005.25 28

Q2) Execute save point,rollback for deletion operation

SQL> savepoint for_delete;

Savepoint created.

SQL> delete from employee where eid=44;

1 row deleted.

SQL> select * from employee;

EID ENAME SALARY AGE

---------- --------------- ---------- ----------
 55 suresh 25005.25 28
SQL> rollback to for_delete;

Rollback complete.

SQL> select * from employee;

EID ENAME SALARY AGE

---------- --------------- ---------- ----------
44 satish 24525.25 24
55 suresh 25005.25 28

Q3) Execute save point,rollback for update opeartion

SQL> savepoint for_update;

Savepoint created.

SQL> update employee set ename='sudhakar' where eid=44;

1 row updated.

SQL> select * from employee;

EID ENAME SALARY AGE

---------- --------------- ---------- ----------
44 sudhakar 24525.25 24
55 suresh 25005.25 28

SQL> rollback to for_update;

Rollback complete.

SQL> select * from employee;

EID ENAME SALARY AGE

---------- --------------- ---------- ----------
44 satish 24525.25 24
55 suresh 25005.25 28

Q4)Execute commit,save point,rollback commands

SQL> savepoint one;

Savepoint created.

SQL> commit;

Commit complete.

SQL> rollback to one;

rollback to one
*
ERROR at line 1:
ORA-01086: savepoint 'ONE' never established

SQL> commit;
Commit complete.

SQL> exit;

Program 6
AIM: Queries for Creating, Dropping, and Altering Tables, Views, and Constraints

Q1) Create and employee table with the fields(eid,ename,salary,doj)

SQL> create table employee(eid number(3),ename varchar2(10),

2 salary number(10,2),doj date);

Table created.

Q2) Display the schema of employee table.

SQL> desc employee;

Name Null? Type
----------------------------------------- -------- ----------------
EID NUMBER(3)
ENAME VARCHAR2(10)
SALARY NUMBER(10,2)
DOJ DATE

Q3) Create table employee_1 without allowing null values to ename and salary not exceeding 100000.

SQL> create table employee_1(eid number(3),

2 ename varchar2(10) not null,
3 salary number(10,2) check(salary<=100000));

Table created.

SQL> desc employee_1;

Name Null? Type
----------------------------------------- -------- ---------------
EID NUMBER(3)
ENAME NOT NULL VARCHAR2(10)
SALARY NUMBER(10,2)

Q4) Create table employee_2 with unique constraint on eid and default value as 199 for age of an
employee.

SQL> create table employee_2(eid number(3) unique,ename varchar2(10),

2 age number(3) default 199);

Table created.

SQL> desc employee_2;

Name Null? Type
----------------------------------------- -------- ------------------
EID NUMBER(3)
ENAME VARCHAR2(10)
AGE NUMBER(3)
Q5) Create table student with sid as primary key.

SQL> create table student(sid number(2),sname varchar2(10),

2 age number(3),primary key(sid))

Table created.

SQL> desc student;

Name Null? Type
----------------------------------------- -------- ------------------
SID NOT NULL NUMBER(2)
SNAME VARCHAR2(10)
AGE NUMBER(3)

Q6) Create table course with cid as primary key and check constraint on dept name.

SQL> create table course(cid varchar2(3) primary key,

2 cname varchar2(10),dept varchar2(3),
3 check (dept in('cse','ece','eee')))
Table created.

SQL> desc course;

Name Null? Type
----------------------------------------- -------- --------------
CID NOT NULL VARCHAR2(3)
CNAME VARCHAR2(10)
DEPT VARCHAR2(3)

Q7.a) Select different constraint names and constraint types in the table student.

SQL> select constraint_name,constraint_type

2 from user_constraints
3 where table_name like 'STUDENT';

CONSTRAINT_NAME C
------------------------------ -
SYS_C006238 P

b)Select different constraint names and constraint types in the table enroll.

SQL> select constraint_name,constraint_type

2 from user_constraints
3 where table_name like 'ENROLL';

CONSTRAINT_NAME C
------------------------------ -
SYS_C006241 R

c) Select different constraint names and constraint types in the table course.
SQL> select constraint_name,constraint_type
2 from user_constraints
3 where table_name like 'COURSE';
CONSTRAINT_NAME C
------------------------------ -
SYS_C006239 C
SYS_C006240 P

Q8) create table std_dup_1 with the following constraints and names:
sid as primary key, sname as unique, age not greater than 150

SQL> create table std_dup_1(sid number(2),sname varchar2(10),

2 age number(3),
3 constraint pky primary key(sid),
4 constraint uni unique(sname),
5 constraint chk check(age<=150));

Table created.

SQL> select constraint_name,constraint_type

2 from user_constraints
3 where table_name like 'STD_DUP_1';

CONSTRAINT_NAME C
------------------------------ -
CHK C
PKY P
UNI U

Q9) create table enroll with foreign key as sid refers to sid in student table.

SQL> create table enroll(sid number(2),cid varchar2(3),

2 foreign key(sid) references student(sid)
3 on delete cascade);
Table created.
SQL> desc enroll;

Name Null? Type

----------------------------------------- -------- ----------------
SID NUMBER(2)
CID VARCHAR2(3)

Q10) Insert values into student table.

SQL> insert into student values(22,'sudhakar',32);

1 row created.

SQL> insert into student values(&id,'&name',&age);

Enter value for id: 11
Enter value for name: praneeth
Enter value for age: 28
old 1: insert into student values(&id,'&name',&age)
new 1: insert into student values(11,'praneeth',28)

1 row created.
SQL> /
Enter value for id: 33
Enter value for name: sumanth
Enter value for age: 27
old 1: insert into student values(&id,'&name',&age)
new 1: insert into student values(33,'sumanth',27)

1 row created.
SQL> select * from student;
SID SNAME AGE
---------- ---------- ----------
22 sudhakar 32
11 praneeth 28
33 sumanth 27

Q11) Create table std_dup_with_rec from existing table student with records also.

SQL> create table std_dup_with_rec as

2 (select sname,sid from student);

Table created.

SQL> select * from std_dup_with_rec;

SNAME SID
---------- ----------
sudhakar 22
praneeth 11
sumanth 33

Q12) Create table std_dup_no_rec from existing student table without records

SQL> create table std_dup_no_rec as

2 select sname,sid from student
3 where 11=22;

Table created.

SQL> select * from std_dup_no_rec;

no rows selected

Q13) Create table sailors with sid as primary key.

SQL> create table sailors(sid integer,sname varchar2(20),primary key(sid));

Table created.

SQL> desc sailors;

Name Null? Type
----------------------------------------- -------- --------------
SID NOT NULL NUMBER(38)
SNAME VARCHAR2(20)

Q14) Add a new column age to the existing table sailors

SQL> alter table sailors add age number(3);

Table altered.
SQL> desc sailors;
Name Null? Type
----------------------------------------- -------- --------------
SID NOT NULL NUMBER(38)
SNAME VARCHAR2(20)
AGE NUMBER(3)

Q15) Add new columns height as number,weight as real to existing table sailors
SQL> alter table sailors add (height number(3),weight real);

Table altered.

SQL> desc sailors;

Name Null? Type
----------------------------------------- -------- --------------
SID NOT NULL NUMBER(38)
SNAME VARCHAR2(20)
AGE NUMBER(3)
HEIGHT NUMBER(3)
WEIGHT FLOAT(63)

Q16) Modify the data type of the sid attribute in the sailors table.
SQL> alter table sailors modify sid number(3);

Table altered.
SQL> desc sailors;
Name Null? Type
----------------------------------------- -------- -------------
SID NOT NULL NUMBER(3)
SNAME VARCHAR2(20)
AGE NUMBER(3)
HEIGHT NUMBER(3)
WEIGHT FLOAT(63)

Q17) Modify the data types of name and weight in sailors table.

SQL> alter table sailors

2 modify(sname varchar2(10),weight number(5,2));

Table altered.

SQL> desc sailors;

Name Null? Type
----------------------------------------- -------- ------------
SID NOT NULL NUMBER(3)
SNAME VARCHAR2(10)
AGE NUMBER(3)
HEIGHT NUMBER(3)
WEIGHT NUMBER(5,2)

Q18) Add not not constraint to sname in sailors table.

SQL> alter table sailors

2 modify sname varchar2(20) not null;

Table altered.
SQL> desc sailors;
Name Null? Type
----------------------------------------- -------- --------------
SID NOT NULL NUMBER(3)
SNAME NOT NULL VARCHAR2(20)
AGE NUMBER(3)
HEIGHT NUMBER(3)
WEIGHT NUMBER(5,2)

Q19) Drop the column age in sailors table.

SQL> alter table sailors drop column age;

Table altered.

SQL> desc sailors;

Name Null? Type
----------------------------------------- -------- ----------------
SID NOT NULL NUMBER(3)
SNAME NOT NULL VARCHAR2(20)
HEIGHT NUMBER(3)
WEIGHT NUMBER(5,2)

Q20) Create table boats (bid,bname,color)

SQL> create table boats(bid number(3),bname varchar2(15),

2 color varchar2(10));

Table created.

SQL> desc boats;

Name Null? Type
----------------------------------------- -------- --------------
BID NUMBER(3)
BNAME VARCHAR2(15)
COLOR VARCHAR2(10)

Q21) Rename column bname to boat_name in boats table.

SQL> alter table boats

2 rename column bname to boat_name;

Table altered.

SQL> desc boats;

Name Null? Type
----------------------------------------- -------- -------------
BID NUMBER(3)
BOAT_NAME VARCHAR2(15)
COLOR VARCHAR2(10)

Q22) Add primary key constraint on bid in boats table.

SQL> alter table boats add constraint pky primary key(bid);

Table altered.
Q23) Add unique key constraint on bname in boats table

SQL> alter table boats add constraint uni unique(bname);

Table altered.

Q24) Add a check constraint on color which accepts either red, green or blue color in boats table.

SQL> alter table boats

2 add constraint chk check(color in('red','green','blue'));

Table altered.

Q25) Display all constraints on table boats

SQL> select constraint_name,constraint_type

2 from user_constraints
3 where table_name like 'BOATS';

CONSTRAINT_NAME C
------------------------------ -
PKY P
UNI U
CHK C

Q26) Drop a constraint whose name is chk from the table boats.

SQL> alter table boats drop constraint chk;

Table altered.

SQL> select constraint_name,constraint_type

2 from user_constraints
3 where table_name like 'BOATS';

CONSTRAINT_NAME C
------------------------------ -
PKY P
UNI U

Q27) Create table reserves(sid,bid,day)

SQL> create table reserves(sid number(3),bid number(3),

2 day date default sysdate);

Table created.

Q28) Add foreign key constraints on sid,bin in reserves referencing tables sailors and boats.

SQL> alter table reserves

2 add constraint fky foreign key(sid) references sailors(sid);

Table altered.

SQL> alter table reserves

2 add constraint fky2 foreign key(bid) references boats;
Table altered.

SQL> select constraint_name,constraint_type

2 from user_constraints
3 where table_name like 'RESERVES';

CONSTRAINT_NAME C
------------------------------ -
FKY R
FKY2 R

Q29) Display all the tables present the database.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

------------------------------ ------- ----------
BOATS TABLE
RESERVES TABLE
SAILORS TABLE

Q30) Rename sailors table to new_sailors.

SQL> rename sailors to new_sailors;

Table renamed.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

------------------------------ ------- ----------
BOATS TABLE
NEW_SAILORS TABLE
RESERVES TABLE

Q31) Drop the table reserves.

SQL> drop table reserves;

Table dropped.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

------------------------------ ------- ----------
BOATS TABLE
NEW_SAILORS TABLE

Q32) Insert values into table boats.

SQL> insert into boats values (&id,'&name','&color');

Enter value for id: 101
Enter value for name: rockz
Enter value for color: red
old 1: insert into boats values (&id,'&name','&color')
new 1: insert into boats values (101,'rockz','red')
1 row created.

SQL> /
Enter value for id: 103
Enter value for name: ranger
Enter value for color: blue
old 1: insert into boats values (&id,'&name','&color')
new 1: insert into boats values (103,'ranger','blue')

1 row created.

SQL> select * from boats;

BID BNAME COLOR

---------- --------------- ----------
101 rockz red
103 ranger blue

Q33) Remove all the rows in the table boats (by using ‘truncate’).

SQL> truncate table boats;

Table truncated.

SQL> select * from boats;

no rows selected

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

------------------------------ ------- ----------
BOATS TABLE
NEW_SAILORS TABLE

SQL> commit;

Queries related with views

Q34) Create a table employee(sid,sname,salary,age)and insert records

SQL> create table employee(eid number(2),ename varchar2(15),

2 salary number(10,2),age number(3));

Table created.

SQL> insert into employee values(&id,'&name',&sal,&age);

Enter value for id: 11
Enter value for name: sudhakar
Enter value for sal: 25000.55
Enter value for age: 32
old 1: insert into employee values(&id,'&name',&sal,&age)
new 1: insert into employee values(11,'sudhakar',25000.55,32)

1 row created.
SQL> /
Enter value for id: 66
Enter value for name: praneeth
Enter value for sal: 26005.25
Enter value for age: 28
old 1: insert into employee values(&id,'&name',&sal,&age)
new 1: insert into employee values(66,'praneeth',26005.25,28)

1 row created.

SQL> select * from employee;

EID ENAME SALARY AGE

---------- --------------- ---------- ----------
11 sudhakar 25000.55 32
66 praneeth 26005.25 28

Q35) Create a view emp(sname,age)from table employee

SQL> create view emp as (select ename,age from employee);

View created.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

------------------------------ ------- ----------
EMP VIEW
EMPLOYEE TABLE

SQL> select * from emp;

ENAME AGE
--------------- ----------
sudhakar 32
praneeth 28
Q36)Insert a new record in employee and verify it in view emp

SQL> insert into employee values(55,'suresh',25005.25,28);

1 row created.

SQL> select * from emp;

ENAME AGE
--------------- ----------
sudhakar 32
praneeth 28
suresh 28

Q37)Update a record in employee and verify it in view emp

SQL> update employee set ename='sudha' where eid=11;

1 row updated.

SQL> select * from emp;

ENAME AGE
--------------- ----------
sudha 32
praneeth 28
suresh 28

Q38)Delete a record in employee and verify it in view emp

SQL> delete from employee where ename='praneeth';

1 row deleted.

SQL> select * from emp;

ENAME AGE
--------------- ----------
sudha 32
suresh 28

Q39)Insert a new record in view emp and verify it in table employee

SQL> insert into emp values('ramesh',35);

1 row created.

SQL> select * from employee;

EID ENAME SALARY AGE

---------- --------------- ---------- ----------
11 sudha 25000.55 32
55 suresh 25005.25 28
ramesh 35
Q40)Update a record in view emp and verify it in table employee

SQL> update emp set ename='sudhakar reddy' where ename='sudha';

1 row updated.

SQL> select * from employee;

EID ENAME SALARY AGE

---------- --------------- ---------- ----------
11 sudhakar reddy 25000.55 32
55 suresh 25005.25 28
ramesh 35

Q41)Delete a record in view emp and verify it in table employee

SQL> delete from emp where ename='sudhakar reddy';

1 row deleted.

SQL> select * from employee;

EID ENAME SALARY AGE

---------- --------------- ---------- ----------
55 suresh 25005.25 28
 ramesh 35

Q42)Change schema of the view emp

SQL> create or replace view emp as (select eid,ename,age from employee);

View created.

SQL> desc emp;

Name Null? Type
----------------------------------------- -------- --------------
EID NUMBER(2)
ENAME VARCHAR2(15)
AGE NUMBER(3)

SQL> select * from emp;

EID ENAME AGE

---------- --------------- ----------
55 suresh 28
ramesh 35

Q43)Change the name of view emp to em

SQL> rename emp to em;

Table renamed.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

------------------------------ ------- ----------
EM VIEW
EMPLOYEE TABLE

Q44) Drop the view em

SQL> drop view em;

View dropped.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

------------------------------ ------- ----------
EMPLOYEE TABLE

SQL> commit;

Commit complete

SQL> exit;
 Program 7

SQL JOIN
A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

Different Types of SQL JOINs

Here are the different types of the JOINs in SQL:

 (INNER) JOIN: Returns records that have matching values in both tables
 LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from the right
table
 RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records from the
left table
 FULL (OUTER) JOIN: Return all records when there is a match in either left or right table

In this example, we have a table called customers with the following data:

customer_id last_name first_name favorite_website

4000 Jackson Joe [Link]
5000 Smith Jane [Link]
6000 Ferguson Samantha [Link]
7000 Reynolds Allen [Link]
8000 Anderson Paige NULL
9000 Johnson Derek [Link]

And a table called orders with the following data:

order_id customer_id order_date

1 7000 2016/04/18
2 5000 2016/04/18
3 8000 2016/04/19
4 4000 2016/04/20
5 NULL 2016/05/01

Inner Join:

SQL>SELECT customers.customer_id, orders.order_id, orders.order_date FROM customers INNER JOIN

orders ON customers.customer_id = orders.customer_id ORDER BY customers.customer_id;

customer_id order_id order_date

4000 4 2016/04/20
5000 2 2016/04/18
7000 1 2016/04/18
8000 3 2016/04/19

Left Outer Join:

SQL>SELECT customers.customer_id, orders.order_id, orders.order_date FROM customers LEFT OUTER

JOIN orders ON customers.customer_id = orders.customer_id ORDER BY customers.customer_id;

customer_id order_id order_date

4000 4 2016/04/20
5000 2 2016/04/18
6000 NULL NULL
7000 1 2016/04/18
8000 3 2016/04/19
9000 NULL NULL

Right Outer Join:

SQL>SELECT customers.customer_id, orders.order_id, orders.order_date FROM customers RIGHT OUTER

JOIN orders ON customers.customer_id = orders.customer_id ORDER BY customers.customer_id;

customer_id order_id order_date

NULL 5 2016/05/01
4000 4 2016/04/20
5000 2 2016/04/18
7000 1 2016/04/18
8000 3 2016/04/19

Full Outer Join:

SQL>SELECT customers.customer_id, orders.order_id, orders.order_date FROM customers FULL OUTER

JOIN orders ON customers.customer_id = orders.customer_id ORDER BY customers.customer_id;

customer_id order_id order_date

NULL 5 2016/05/01
4000 4 2016/04/20
5000 2 2016/04/18
6000 NULL NULL
7000 1 2016/04/18
8000 3 2016/04/19
9000 NULL NULL

Nested query
A Subquery or Inner query or a Nested query is a query within another SQL query and embedded within the
WHERE clause.

A subquery is used to return data that will be used in the main query as a condition to further restrict the data
to be retrieved.

Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the
operators like =, <, >, >=, <=, IN, BETWEEN, etc.

There are a few rules that subqueries must follow −

 Subqueries must be enclosed within parentheses.

 A subquery can have only one column in the SELECT clause, unless multiple columns are in the
main query for the subquery to compare its selected columns.

 An ORDER BY command cannot be used in a subquery, although the main query can use an ORDER
BY. The GROUP BY command can be used to perform the same function as the ORDER BY in a
subquery.

 Subqueries that return more than one row can only be used with multiple value operators such as the
IN operator.

 A subquery cannot be immediately enclosed in a set function.

 The BETWEEN operator cannot be used with a subquery. However, the BETWEEN operator can be
used within the subquery

Q1) create table sailors(sid,sname,rating,age) with sid as primary key.

SQL> create table sailors(sid number(3),sname varchar2(15),rating number(2),age number(5,2),primary

key(sid));

Table created.

Q2) insert values into sailors.

SQL> insert into sailors (sid, sname, rating, age)

2 values (22, 'Dustin', 7, 45.0);

1 row created.

SQL> insert into sailors (sid, sname, rating, age)

2 values (29, 'Brutus', 1, 33.0);

1 row created.

SQL> insert into sailors (sid, sname, rating, age)

 2 values (31, 'Lubber', 8, 55.5);

1 row created.

SQL> insert into sailors (sid, sname, rating, age)

2 values (32, 'Andy', 8, 25.5);

1 row created.

SQL> insert into sailors (sid, sname, rating, age)

2 values (58, 'Rusty', 10, 35.0);

1 row created.

SQL> insert into sailors (sid, sname, rating, age)

2 values (64, 'Horatio', 7, 35.0);

1 row created.

SQL> insert into sailors (sid, sname, rating, age)

2 values (71, 'Zorba', 10, 16.0);
1 row created.

SQL> insert into sailors (sid, sname, rating, age)

2 values (74, 'Horatio', 9, 35.0);

1 row created.

SQL> insert into sailors (sid, sname, rating, age)

2 values (85, 'Art', 3, 25.5);

1 row created.

SQL> insert into sailors (sid, sname, rating, age)

2 values (95, 'Bob', 3, 63.5);

1 row created.

Q3) create table boats(bid,bname,color) with bid as primary key.

SQL> create table boats(bid integer, bname varchar(10),color varchar(10),

2 primary key(bid));

Table created.

Q4) insert values into boats.

SQL> insert into boats (bid, bname, color)

2 values (101, 'Interlake', 'blue');
1 row created.
SQL> insert into boats (bid, bname, color)
2 values (102, 'Interlake', 'red');
1 row created.
SQL> insert into boats (bid, bname, color)
2 values (103, 'Clipper', 'green');

1 row created.
SQL> insert into boats (bid, bname, color)
2 values (104, 'Marine', 'red');

1 row created.

Q5) Create table reserves(sid,bid,day) where sid,bid,day as primary key and sid,did as foreign key
referencing the tables sailors and boats respectively.

SQL> create table reserves(sid integer,bid integer,day date,

2 primary key(sid,bid,day),
3 foreign key(sid) references sailors,
4 foreign key(bid) references boats);

Table created.

Q6) Insert values into reserves.

SQL> insert into reserves (sid, bid, day)

2 values (22, 101, '10/oct/98');
1 row created.

SQL> insert into reserves (sid, bid, day)

2 values (22, 102, '10/oct/98');
1 row created.

SQL> insert into reserves (sid, bid, day)

2 values (22, 103, '10/aug/98');

1 row created.

SQL> insert into reserves (sid, bid, day)

2 values (22, 104, '10/jul/98');

1 row created.

SQL> insert into reserves (sid, bid, day)

2 values (31, 102, '11/oct/98');

1 row created.

SQL> insert into reserves (sid, bid, day)

2 values (31, 103, '11/jun/98');

1 row created.
SQL> insert into reserves (sid, bid, day)
2 values (31, 104, '11/dec/98');
1 row created.
SQL> insert into reserves (sid, bid, day)
2 values (64, 101, '9/may/98');
1 row created.

SQL> insert into reserves (sid, bid, day)

2 values (64, 102, '9/aug/98');
1 row created.

SQL> insert into reserves (sid, bid, day)

2 values (74, 103, '9/aug/98');
1 row created.
Q7) Find the sailor names who have reserved atleast one boat.

SQL> select [Link],[Link] from sailors a, reserves b where [Link]=[Link];

SNAME BID
--------------- ----------
Dustin 101
Dustin 102
Dustin 103
Dustin 104
Lubber 102
Lubber 103
Lubber 104
Horatio 101
Horatio 102
Horatio 103
10 rows selected.

Q8) Find the sailors name who have reserved boat number 103.

SQL> select [Link],[Link] from sailors a, reserves b where [Link]=[Link] and [Link]=103;

SNAME BID
--------------- ----------
Dustin 103
Lubber 103
Horatio 103

Q9) Find the sailors name who have reserved boat ‘Marine’.

SQL> select [Link],[Link] from sailors a, reserves b,boats c where [Link]=[Link] and [Link]=[Link] and
[Link]='Marine';

SNAME BID
--------------- ----------
Dustin 104
Lubber 104
Q10) Find the sailors who have reserved red color boat.

SQL> select [Link], [Link], [Link] from sailors a, reserves b,boats c where [Link]=[Link] and [Link]=[Link]
and [Link]='red'
SNAME RATING BNAME
--------------- ---------- ----------
Dustin 7 Interlake
Dustin 7 Marine
Lubber 8 Interlake
Lubber 8 Marine
Horatio 7 Interlake

Q11) Find the sailors whose sid is 22 or 32.

SQL> select * from sailors where sid in (22,32);

SID SNAME RATING AGE

---------- --------------- ---------- ----------
32 Andy 8 25.5
 22 Dustin 7 45

Q12) Find the sailors who have reserved atleast one boat using nested query.
SQL> select * from sailors where sid in (select sid from reserves);

SID SNAME RATING AGE

---------- --------------- ---------- ----------
22 Dustin 7 45
31 Lubber 8 55.5
64 Horatio 7 35
74 Horatio 9 35
Q13) Find the sailors who have not reserved atleast one boat using nested query.

SQL> select * from sailors where sid not in (select sid from reserves);

SID SNAME RATING AGE

---------- --------------- ---------- ----------
29 Brutus 1 33
32 Andy 8 25.5
58 Rusty 10 35
71 Zorba 10 16
85 Art 3 25.5
95 Bob 3 63.5

6 rows selected.

Q14) Find the sailors whose sid is 22 or 32.

SQL> select * from sailors where sid in (22,32);

SID SNAME RATING AGE

---------- --------------- ---------- ----------
32 Andy 8 25.5
22 Dustin 7 45

Q15) Find the sailors whose who have reserved boat number 103 using nested query.

SQL> select * from sailors where sid in

2 (select sid from reserves where bid=103);

SID SNAME RATING AGE

---------- --------------- ---------- ----------
22 Dustin 7 45
31 Lubber 8 55.5
74 Horatio 9 35

Q16) Find the sailors who have not reserved red color boat.

SQL> select * from sailors where sid not in

2 (select sid from reserves where bid in
3 (select bid from boats where color='red'));

SID SNAME RATING AGE

---------- --------------- ---------- ----------
29 Brutus 1 33
32 Andy 8 25.5
 58 Rusty 10 35
71 Zorba 10 16
74 Horatio 9 35
85 Art 3 25.5
95 Bob 3 63.5

7 rows selected.

Q17) Find the sailor names who reserved boat number 103

SQL> select [Link] from sailors a where exists

(select * from reserves b where [Link]=103 and [Link]=[Link]);

SNAME
---------------
Dustin
Lubber
Horatio

Q18) Find the sailor names who do not reserved boat number 103
SQL> select [Link] from sailors a where not exists
2 (select * from reserves b where [Link]=103 and [Link]=[Link]);

SNAME
---------------
Brutus
Andy
Rusty
Horatio
Zorba
Art
Bob

7 rows selected.

Q19) select sailors details whose age greater any one of the sailors whose name starts with “pra”

SQL> select * from sailors where age>any(select age from sailors where sname like 'pra%');

SID SNAME RATING AGE

---------- -------------------- ---------- ----------
22 sudhakar 7 45
29 Brutus 1 33
31 prabakar 8 55.5
58 Rusty 10 35
64 sudhakar 7 35
74 Horataio 9 35
95 Bob 3 63.5
7 rows selected.
Q20) select sailors details whose age greater all of the sailors whose name starts with “pra”
SQL>select * from sailors where age>all(select age from sailors where sname like 'pra%');
 SID SNAME RATING AGE
---------- -------------------- ---------- ----------
95 Bob 3 63.5

SQL> commit;

Commit complete.

SQL> exit;

PL/SQL PROGRAMS

Description:
To develop the PL/SQL program we include the following tools which we prescribe are block structure,
execution statements.
PL/SQL-PROGRAMMING LANGUAGE
FEATURES:
 It is a procedure language.
 It is developed by oracle company.
 Used only with oracle.
 It process only row at time where non-procedural language process a set of rows at time.
BLOCK STRUCTURE:
>declare
<variable declaration>;
Begin
<executable statements>;
Exception;
<executable statements>;
End;
EXECUTABLE STATEMENTS:
1. DML allowed.
2. TCL allowed.
3. DDL and DCL are not allowed.
4. select<column list>into<variable list> from<table name> where<condition>;
5. –single line comment;
 /*Multi
Line
Comment*/
6. dbms_output.put_line(‘message’||variable);
Used to print variables and messages.
DBMS_OUTPUT->It is a package.

7. set serveroutput on
(used to activities [Link].)
:=it for external value
Into----used with select command.
Sal number(7,2);
Note:- select statements must return one and only one row.

SQL> select * from results;

SNO SNAME M1 M2 M3

-------------------- -------------------- ---------- ---------- ----------

09JM1A0501 DIVYA 85 90 78

09JM1A0502 HARSHITA 86 94 74

09JM1A0503 RUKMINI 92 89 75

SQL> select * from enroll3;

SNO CID GRADE

-------------------- ---------- -----

09JM1A0501 jm A

09JM1A0502 jm A
09JM1A0503 mf B

09JM1A0504 sd B

09JM1A0505 se C

09JM1A0506 ab D

5.A) WRITE A PL/SQL PROGRAM WHICH INCLUDES

DECLARATION SECTIONS, EXECUTABLE SECTION AND EXCEPTION HANDLING SECTION?

SQL> set serveroutput on

SQL> declare

2 ma1 results.m1% type;

3 ma2 results.m2% type;

4 ma3 results.m3% type;

5 percent number(6,2);

6 BEGIN

7 select m1,m2,m3 into ma1,ma2,ma3 from results where sno='&no';

8 percent:=(ma1+ma2+ma3)/300*100;

9 if percent>65 then

10 dbms_output.put_line('First class student');

11 else

12 dbms_output.put_line('normal student');

13 end if;

14 EXCEPTION

15 whenno_data_found then

16 dbms_output.put_line('No student found\n');

17 end;

18 /

Enter value for no: 09JM1A0502

old 7: select m1,m2,m3 into ma1,ma2,ma3 from results where sno='&no';

new 7: select m1,m2,m3 into ma1,ma2,ma3 from results where sno='09JM1A0502';

First class student

PL/SQL procedure successfully completed.

5.B) WRITE A PL/SQL PROGRAM TO INSERT DATA INTO STUDENT TABLE BY USING COMMIT,ROLEBACK
AND SAVEPOINT?

SQL> set serveroutput on

SQL> begin

2 insert into results values('09JM1A0504','MANJU',35,60,75);

3 insert into results values('09JM1A0505','VENI',66,76,86);

4 commit;

5 insert into results values('09JM1A0506','ANITHA',46,56,66);

6 rollback;

7 savepoint s1;

8 insert into results values('09JM1A0507','ANUSHA',65,89,79);

9 rollback to s1;

10 end;

11 /

PL/SQL procedure successfully completed.

SQL> select * from results;

SNO SNAME M1 M2 M3

-------------------- -------------------- ---------- ---------- ----------

09JM1A0501 DIVYA 85 90 78

09JM1A0502 HARSHITA 86 94 74

09JM1A0503 RUKMINI 92 89 75

09JM1A0504 MANJU 35 60 75

09JM1A0505 VENI 66 76 86

6.A) DEVELOP A PL/SQL PROGRAM THAT INCLUDES THE FEATURES NESTED IF FUNCTION?
SQL>set serveroutput on

SQL> declare

2 a number (5);

3 begin

4 selectsal into a from emp where empno='&no';

5 if a>2500 then

6 dbms_output.put_line('high_sal'||a);

7 elsif (a>1500) and (a<2500) then

8 dbms_output.put_line('normal_sal'||a);

9 else

10 dbms_output.put_line('low level'||a);

11 end if;

12 end;

13 /

Enter value for no: 7369

old 4: select sal into a from emp where empno='&no';

new 4: select sal into a from emp where empno='7369';

low level800

PL/SQL procedure successfully completed.

6.B) DEVELOP A PL/SQL PROGRAM THAT INCLUDES CASE AND CASE EXCEPTIONS?

SQL> set serveroutput on

SQL> declare

2 a number(5);

3 begin

4 selectsal into a from emp where empno=&empno;

5 case when a>2500 then

6 dbms_output.put_Line('High-sal'||a);

7 when(a>1500)and(a<2500)then
8 dbms_output.put_Line('Normal-sal'||a);

9 else

10 dbms_output.put_Line('low-sal'||a);

11 end case;

12 end;

13 /

Enter value for empno: 7839

old 4: select sal into a from emp where empno=&empno;

new 4: select sal into a from emp where empno=7839;

High-sal5000

PL/SQL procedure successfully completed.

7.A) DEVELOP A PL/SQL PROGRAM USING WHILE LOOP?

SQL> set serveroutput on

SQL> declare

2 a number(2);

3 BEGIN

4 a:=1;

5 while(a<=10)

6 loop

7 dbms_output.put_line(a);

8 a:=a+1;

9 end loop;

10 end;

11 /

7
8

10

PL/SQL procedure successfully completed.

7.B) DEVELOP A PL/SQL PROGRAM USING FOR LOOP?

SQL> set serveroutput on

SQL> declare
2 a number(2);

3 BEGIN

4 for a in 1..10

5 loop

6 dbms_output.put_line(a);

7 end loop;

8 end;

9 /

10

PL/SQL procedure successfully completed.

7.C) DEVELOP A PL/SQL PROGRAM USING BUILT-IN-EXPRESSION?

SQL> set serveroutput on

SQL> declare

2 a number(5);

3 begin

4 selectsal into a from emp where empno=&empno;

5 if a>2500 then

6 dbms_output.put_line('High-sal'||a);

7 else

8 dbms_output.put_line('low-sal'||a);

9 end if;

10 exception

11 whenno_data_found then

12 dbms_output.put_line('No emp found');

13 end;

14 /

Enter value for empno: 7788

old 4: select sal into a from emp where empno=&empno;

new 4: select sal into a from emp where empno=7788;

High-sal3000

PL/SQL procedure successfully completed.

8) DEVELOP A PL/SQL PROGRAM USING CREATION OF PROCEDURE,PASSING PARAMETERS IN AND
OUT OF PROCEDURES?

SQL> set serveroutput on

SQL> create or replace procedure p1 (a in number,b out number) is

2 begin

3 b:=a*0.10;

4 end;

5 /

Procedure created.

SQL> declare

2 [Link]%type;

3 bonus number(6);

4 begin

5 selectsal into salary from emp where empno=&empno;

6 p1(salary,bonus);

7 dbms_output.put_line('bonus is'||bonus);

8 end;

9 /

Enter value for empno: 7839

old 5: select sal into salary from emp where empno=&empno;

new 5: select sal into salary from emp where empno=7839;

bonus is500

PL/SQL procedure successfully completed.

9) DEVELOP A PL/SQL PROGRAMUSINGCREATION OF STORED FUNCTIONS,INVOKE FUNCTIONS IN SQL

STATEMENTS AND WRITE COMPLEX FUNCTIONS?

SQL> set serveroutput on

SQL> create or replace function fact(a number) return number is

2 product number;
3 i number default 1;

4 begin

5 product:=1;

6 whilei<=a

7 loop

8 product:=product*i;

9 i:=i+1;

10 end loop;

11 return product;

12 end;

13 /

Function created.

SQL> declare

2 no number default 1;

3 res number;

4 begin

5 no:=&no;

6 res:=fact(no);

7 dbms_output.put_line('factorial value is'||res);

8 end;

9 /

Enter value for no: 6

old 5: no:=&no;

new 5: no:=6;

factorial value is720

 PL/SQL procedure successfully completed.

10) DEVELOP A PL/SQL PROGRAM USING CREATION OF PACKAGE BODIES, PRIVATE OBJECTS,
PACKAGE VARIABLES AND CURSOR CALLING STORE A PACKAGE?

SQL> set serveroutput on

SQL> create or replace package mypack1 is

2 proceduresum_sal;

3 procedureavg_sal;

4 end mypack1;

5 /

Package created.

SQL> create or replace package body mypack1 as

2 procedure sum_sal is

3 salaries number;

4 cursor a is select sum(nvl(sal,0)) from emp;

5 BEGIN
6 open a;

7 fetcha into salaries;

8 dbms_output.put_line('Sum of salaries is'||salaries);

9 endsum_sal;

10 procedureavg_sal is

11 avg_sal number;

12 cursor b is select avg(nvl(sal,0)) from emp;

13 BEGIN

14 open b;

15 fetch b into avg_sal;

16 dbms_output.put_line('avg of salaries'||avg_sal);

17 end avg_sal;

18 end mypack1;

19 /

Package body created.

SQL> declare

2 begin

3 [Link];

4 [Link];

5 end;

6 /

sum of salaries is 29025

avg of salaries is 2073.214285714285714285714285714285714286

PL/SQL procedure successfully completed.

11)DEVELOP A PL/SQL PROGRAM USING FEATURES PARAMETERS IN CURSOR,FOR UPDATE
CURSOR,WHERE CURRENT OF CLAUSE AND CURSOR VARIABLES?

SQL> set serveroutput on

SQL> declare

2 cursor details is

3 select * from results for update of m1;

4 BEGIN

5 for m2 in details

6 loop

7 update results set m1=m2.m1+8 where current of details;

8 end loop;

9 end;

10 /

PL/SQL procedure successfully completed.

SQL> select * from results;

SNO SNAME M1 M2 M3

-------------------- -------------------- ---------- ---------- ----------

09JM1A0501 DIVYA 93 90 78

09JM1A0502 HARSHITA 94 94 74

09JM1A0503 RUKMINI 100 89 75

09JM1A0504 MANJU 43 60 75

09JM1A0505 VENI 74 76 86
12.A) DEVELOP A PROGRAM USING AFTER TRIGGER WITH STATEMENT TRIGGER IN PL/SQL BLOCK?

SQL> set serveroutput on

SQL> create table marks1 as select sno,M1,M2,M3 from results;

SQL> create or replace trigger t10 after insert on results

2 BEGIN

3 delete marks1;

4 insert into marks1(select sno,m1,m2,m3 from results);

5 end;

6 /
Trigger created.

SQL> insert into marks1 values('A',35,45,65);

1 row created.

SQL> select * from marks1;

SNO M1 M2 M3

-------------------- ---------- ---------- ----------

09JM1A0501 85 90 78

09JM1A0502 86 94 74

09JM1A0503 92 89 75

09JM1A0504 35 60 75

09JM1A0505 66 76 86

A 35 45 65

6 rows selected.
12.B) DEVELOP A PROGRAM USING BEFORE AND AFTER TRIGGERS NOW AND COLUMN STATEMENTS
AND INSTEAD OF TRIGGERS?

SQL> create or replace trigger enroll3 before insert or update on enroll3 for each row

2 declare

3 total number;

4 name varchar(10);

5 BEGIN

6 select count(*) into total from enroll3 where sno=:[Link];

7 if total>=3 then

8 selectsname into name from results where sno=:[Link];

9 raise_application_error(-20000,'student name'||name||'is registered already');

10 end if;

11 EXCEPTION

12 whenno_data_found then

13 raise_application_error(-20000,'No student found');

14 end;

15 /

Trigger created.

SQL> insert into enroll3 values('&sno','&cid','&grade');

Enter value for sno: 09JM1A0501

Enter value for cid: 12

Enter value for grade: A

old 1: insert into enroll3 values('&sno','&cid','&grade')

new 1: insert into enroll3 values('09JM1A0501','12','A')

1 row created.

12.C) DEVELOP A PL/SQL PROGRAM USING INSTEADOF TRIGGER.

SQL> create view student_a(sname,sno,cid)as(select [Link],[Link],[Link] from results s,enroll3 e where

[Link]=[Link] and [Link]='A');
View created.

SQL> select * from student_a;

SNAME SNO CID

-------------------- -------------------- ----------

DIVYA 09JM1A0501 123

HARSHITA 09JM1A0502 124

RUKMINI 09JM1A0503 125

SQL>delete from student_a where sname like 'rukmini';

one row deleted

 Program 9

Aim: Write a PL/SQL Code Bind and Substitution Variables. Printing in PL/SQL

Bind Variable:

SQL> variable v_bind1 varchar2(10);

SQL> Exec:v_bind1:='rani';

PL/SQL procedure successfully completed.

SQL> print :v_bind1;

V_BIND1

--------------------------------
Rani

SQL> ed bindd

BEGIN

:v_bind1 := 'Ramu';

DBMS_OUTPUT.PUT_LINE(:v_bind1);

END;

OutPut 1:
SQL> set serveroutput on;

SQL> @ bindd

Ramu

Output2:
SQL> set serveroutput off;

SQL> set autoprint on;

SQL> @ bindd

PL/SQL procedure successfully completed.

V_BIND1

--------------------------------

Ramu

Substitution Variable
DECLARE

name varchar2(10):='&name';

age number:=&age;

begin

dbms_output.put_line('name is '||name);
dbms_output.put_line('age is '||age);

end;

Enter value for name: rani

old 2: name varchar2(10):='&name';

new 2: name varchar2(10):='rani';

Enter value for age: 28

old 3: age number:=&age;

new 3: age number:=28;

name is rani

age is 25

PL/SQL procedure successfully completed.

SQL> set verify off;

SQL> /

Enter value for name: rani

Enter value for age: 28

name is rani

age is 28

PL/SQL procedure successfully completed.

Program 10

AIM: Write a PL/SQL block using SQL and Control Structures in PL/SQL

THE NULLIF FUNCTION

The NULLIF function compares two expressions. If they are equal, the function returns NULL;
otherwise, it returns the value of the first expression.

BEGIN

v_reminder := NULLIF(MOD(v_num, 2), 0);

DBMS_OUTPUT.PUT_LINE(v_reminder); –If the v_reminder is null, it will not be displayed

END;
THE COALESCE FUNCTION

The COALESCE function compares each expression to NULL from the list of expressions and
returns the value of the first non-null expression.

SELECT e.student_id, e.section_id, e.final_grade, g.numeric_grade,

COALESCE(e.final_grade, g.numeric_grade, 0) grade

FROM enrollment e, grade g

WHERE e.student_id = g.student_id

AND e.section_id = g.section_id

AND e.student_id = 102

AND g.grade_type_code = ‘FI’;

[Link]

declare

fname varchar2(10);

lname varchar2(10);

ch number;

res varchar2(10);

res1 varchar2(10);

begin

fname:='&fname';

lname:='&lname';

ch:=&ch;

case ch

when 1 then

res:=nullif(fname,lname);

dbms_output.put_line('res is:'||res);

if res=fname and lname is null then

dbms_output.put_line('lname is null');
 elsif res=lname and fname is null then

dbms_output.put_line('fname is null');

elsif res is not null then

dbms_output.put_line('They are different');

else

dbms_output.put_line('both are null');

end if;

when 2 then

res:=COALESCE(fname,lname);

res1:=coalesce(lname,fname);

dbms_output.put_line('res is:'||res);

if res=lname then

dbms_output.put_line('Fname is null');

elsif res1=fname then

dbms_output.put_line('Lname is null');

elsif res is null and res1 is null then

dbms_output.put_line('Both are nulls');

else

dbms_output.put_line('Both are different');

end if;

else

dbms_output.put_line('enter right option');

end case;

end;

Output:

SQL> @ 6

Enter value for fname: chp

old 8: fname:='&fname';

new 8: fname:='chp';

Enter value for lname: abc

old 9: lname:='&lname';

new 9: lname:='abc';

Enter value for ch: 1

old 10: ch:=&ch;

new 10: ch:=1;

res is:chp

They are different

PL/SQL procedure successfully completed.

SQL> @ 6

Enter value for fname: abc

old 8: fname:='&fname';

new 8: fname:='abc';

Enter value for lname: chp

old 9: lname:='&lname';

new 9: lname:='chp';

Enter value for ch: 2

old 10: ch:=&ch;

new 10: ch:=2;

res is:abc

Both are different

PL/SQL procedure successfully completed.

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) := 10;

BEGIN

WHILE a < 20 LOOP

dbms_output.put_line('value of a: ' || a);

a := a + 1;

END LOOP;

END;

When the above code is executed at the SQL prompt, it produces the following result −

value of a: 10

value of a: 11

value of a: 12

value of a: 13

value of a: 14

value of a: 15

value of a: 16

value of a: 17

value of a: 18

value of a: 19

PL/SQL procedure successfully completed.

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;

Following is the flow of control in a For Loop −

The initial step is executed first, and only once. This step allows you to declare and initialize any loop
control variables.

Next, the condition, i.e., initial_value .. final_value is evaluated. If it is TRUE, the body of the loop is
executed. If it is FALSE, the body of the loop does not execute and the flow of control jumps to the next
statement just after the for loop.

After the body of the for loop executes, the value of the counter variable is increased or decreased.

The condition is now evaluated again. If it is TRUE, the loop executes and the process repeats itself (body
of loop, then increment step, and then again condition). After the condition becomes FALSE, the FOR-
LOOP terminates.

Following are some special characteristics of PL/SQL for loop −

The initial_value and final_value of the loop variable or counter can be literals, variables, or expressions
but must evaluate to numbers. Otherwise, PL/SQL raises the predefined exception VALUE_ERROR.

The initial_value need not be 1; however, the loop counter increment (or decrement) must be 1.

PL/SQL allows the determination of the loop range dynamically at run time.

Example

DECLARE

a number(2);

BEGIN

FOR a in 10 .. 20 LOOP

dbms_output.put_line('value of a: ' || a);

END LOOP;

END;

When the above code is executed at the SQL prompt, it produces the following result −

value of a: 10

value of a: 11
value of a: 12

value of a: 13

value of a: 14

value of a: 15

value of a: 16

value of a: 17

value of a: 18

value of a: 19

value of a: 20

PL/SQL procedure successfully completed.

Reverse FOR LOOP Statement

By default, iteration proceeds from the initial value to the final value, generally upward from the lower
bound to the higher bound. You can reverse this order by using the REVERSE keyword. In such case,
iteration proceeds the other way. After each iteration, the loop counter is decremented.

However, you must write the range bounds in ascending (not descending) order. The following program
illustrates this −

DECLARE

a number(2) ;

BEGIN

FOR a IN REVERSE 10 .. 20 LOOP

dbms_output.put_line('value of a: ' || a);

END LOOP;

END;

When the above code is executed at the SQL prompt, it produces the following result −

value of a: 20

value of a: 19

value of a: 18

value of a: 17
value of a: 16

value of a: 15

value of a: 14

value of a: 13

value of a: 12

value of a: 11

value of a: 10

PL/SQL procedure successfully completed.

Program 11

AIM: Write a PL/SQL Code using Cursors, Exceptions and Composite Data Types

Cursor
Cursors are programmer-defined cursors for gaining more control over the context area. An
explicit cursor should be defined in the declaration section of the PL/SQL Block. It is created on
a SELECT Statement which returns more than one row.

The syntax for creating an explicit cursor is −

CURSOR cursor_name IS select_statement;

Working with an explicit cursor includes the following steps −

 Declaring the cursor for initializing the memory

 Opening the cursor for allocating the memory
 Fetching the cursor for retrieving the data
 Closing the cursor to release the allocated memory

Declaring the Cursor

Declaring the cursor defines the cursor with a name and the associated SELECT statement. For
example −

CURSOR c_customers IS
SELECT id, name, address FROM customers;

Opening the Cursor

Opening the cursor allocates the memory for the cursor and makes it ready for fetching the rows
returned by the SQL statement into it. For example, we will open the above defined cursor as
follows −

OPEN c_customers;

Fetching the Cursor

Fetching the cursor involves accessing one row at a time. For example, we will fetch rows from
the above-opened cursor as follows −

FETCH c_customers INTO c_id, c_name, c_addr;

Closing the Cursor

Closing the cursor means releasing the allocated memory. For example, we will close the above-
opened cursor as follows −

CLOSE c_customers;

Example

Following is a complete example to illustrate the concepts of explicit cursors &minua;

DECLARE
c_id [Link]%type;
c_name [Link]%type;
c_addr [Link]%type;
CURSOR c_customers is
SELECT id, name, address FROM customers;
BEGIN
OPEN c_customers;
LOOP
FETCH c_customers into c_id, c_name, c_addr;
 EXIT WHEN c_customers%notfound;
dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);
END LOOP;
CLOSE c_customers;
END;
/

When the above code is executed at the SQL prompt, it produces the following result −

1 Ramesh Ahmedabad
2 Khilan Delhi
3 kaushik Kota
4 Chaitali Mumbai
5 Hardik Bhopal
6 Komal MP

PL/SQL procedure successfully completed.

declare

cursor emp_cur is

select * from emp

for update;

cur_rec emp_cur%rowtype;

begin

for i in emp_cur loop

update emp set lname='chp'

where current of emp_cur;

end loop;

if not emp_cur%isopen then

Open emp_cur;

End if;

LOOP

Fetch emp_cur into cur_rec;

Exit when emp_cur%notfound;

dbms_output.put_line(cur_rec.eid || ' age is ' ||cur_rec.lname);

END LOOP;
Close emp_cur;

end;

Output:

SQL> set serveroutput on

SQL> @ cur;

age is chp

1 age is chp

PL/SQL procedure successfully completed.

Write a Pl/SQL procedure for zero divide exception

SQL> ed zd_excep.sql;

declare
x number(5);
d number(5);
q number(5);
begin
x:=&divident;
d:=&divisor;
if((d!=0)) then
q:=x/d;
dbms_output.put_line('the quotient is '|| q);
else
raise zero_divide;
end if;
exception
when zero_divide then dbms_output.put_line('division with 0 is not possible');
end;
/

SQL> @zd_excep.sql;
Enter value for divident: 22
old 6: x:=&divident;
new 6: x:=22;
Enter value for divisor: 4
old 7: d:=&divisor;
new 7: d:=4;
the quotient is 6

PL/SQL procedure successfully completed.

SQL> @zd_excep.sql;
Enter value for divident: 77
old 6: x:=&divident;
new 6: x:=77;
Enter value for divisor: 0
old 7: d:=&divisor;
new 7: d:=0;
division with 0 is not possible

PL/SQL procedure successfully completed.

Q2). Create a table student with attributes sid,sname,age

SQL> create table student(sid number(3),sname varchar2(15),age number(3));

Table created.

SQL> insert into student values(22,'sudhakar',32);

1 row created.

SQL> insert into student values(77,'praneeth',28);

1 row created.

SQL> insert into student values(55,'rajesh',27);

1 row created.

SQL> select * from student;

SID SNAME AGE

---------- --------------- ----------
22 sudhakar 32
77 praneeth 28
55 rajesh 27

Q3). Write a Pl/SQL procedure for no data found exception

SQL> ed ndf_excep.sql;

declare
name [Link]%type;
id number(3);
begin
id:=&id;
select sname into name from student where sid=id;
if (name is not null) then
dbms_output.put_line('Student name is' ||name);
else
raise no_data_found;
end if;
exception
when no_data_found then dbms_output.put_line('no studnet with the roll number ' ||id);
end;
/

SQL> @ndf_excep.sql;
Enter value for id: 22
old 5: id:=&id;
new 5: id:=22;
Student name issudhakar
PL/SQL procedure successfully completed.

SQL> @ndf_excep.sql;
Enter value for id: 88
old 5: id:=&id;
new 5: id:=88;
no studnet with the roll number 88

PL/SQL procedure successfully completed.

Q4). Write a Pl/SQL procedure for case not found exception

SQL> ed snf_excep.sql;

declare
x number(3);
p real;
begin
x:=&one_or_two;
case x
when 1 then dbms_output.put_line('you are number one');
when 2 then dbms_output.put_line('you are second');
else raise case_not_found;
end case;
exception
when case_not_found then dbms_output.put_line('enter 1 or 2 only. try again');
end;
/

SQL> @snf_excep.sql;
Enter value for one_or_two: 1
old 5: x:=&one_or_two;
new 5: x:=1;
you are number one

PL/SQL procedure successfully completed.

SQL> @snf_excep.sql;
Enter value for one_or_two: 2
old 5: x:=&one_or_two;
new 5: x:=2;
you are second

PL/SQL procedure successfully completed.

SQL> @snf_excep.sql;
Enter value for one_or_two: 22
old 5: x:=&one_or_two;
new 5: x:=22;
enter 1 or 2 only. try again

PL/SQL procedure successfully completed.

Q5). Write a Pl/SQL procedure for user defined exception

SQL> ed ud_excep.sql;
declare
name [Link]%type;
sage [Link]%type;
my_excep exception;
id number(3);
begin
id:=&sid;
select sname into name from student where sid=id;
if(name='chp') then
select age into sage from student where sid=id;
dbms_output.put_line('student age is' || age);
else
raise my_excep;
end if;
exception
when my_excep then dbms_output.put_line('the id you entered is not correct ');
when no_data_found then dbms_output.put_line('the id is not table');
end;
/

SQL> @ud_excep.sql;
Enter value for sudhakar_id: 22
old 7: id:=&sudhakar_id;
new 7: id:=22;
sudhakar age is32

PL/SQL procedure successfully completed.

SQL> @ud_excep.sql;
Enter value for sudhakar_id: 77
old 7: id:=&sudhakar_id;
new 7: id:=77;
the id you entered is not sudhakar id

PL/SQL procedure successfully completed.

SQL> @ud_excep.sql;
Enter value for sudhakar_id: 66
old 7: id:=&sudhakar_id;
new 7: id:=66;
the id is not table

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete. SQL> exit;

Program illustrating for loop

declare
i number(5);
n number(2);
s number(3);
a number(5);
begin
n:=&n;
 s:=0;

for i in 1..n loop

a:=&a;
s:=s+a;
end loop;
dbms_output.put_line('sum is'||s);
end;
/

output:
SQL> @ sun
Enter value for n: 3
old 7: n:=&n;
new 7: n:=3;
Enter value for a: 2
old 11: a:=&a;
new 11: a:=2;
sum is6

PL/SQL procedure successfully completed.

Program 12
Aim: Write a PL/SQL Code using Procedures, Functions, and Packages FORMS

SQL>set serveroutput on;

SQL> ed proced;

create or replace procedure factorial(n in number, f out number) is

i integer;

begin

f:=1;

for i in 1 .. n
loop

f:=f*i;

end loop;

end;

SQL> @proced;

Procedure created.

SQL> ed calprog;

declare

x number(10);

f number(10);

begin

x:=&x;

if(x>0) then

factorial(x,f);

dbms_output.put_line('the factorial of ' || x || ' is ' ||f);

elsif(x=0) then

dbms_output.put_line('the factorial of ' || x || ' is 1');

else

dbms_output.put_line('factorial is defined only non-negative numbers');

end if;

end;

/
SQL> @calprog;
Enter value for x: 5
old 5: x:=&x;
new 5: x:=5;
the factorial of 5 is 120

PL/SQL procedure successfully completed.

SQL> @calprog;
Enter value for x: 0
old 5: x:=&x;
new 5: x:=0;
the factorial of 0 is 1

PL/SQL procedure successfully completed.

SQL> @calprog;
Enter value for x: -1
old 5: x:=&x;
new 5: x:=-1;
factorial is defined only non-negative numbers

PL/SQL procedure successfully completed.

FUNCTION

SQL> create table student(sid number(3),sname varchar2(15),age number(3));

Table created.
SQL> insert into student values(11,'sudhakar',35);

1 row created.

SQL> insert into student values(22,'madhu',26);

1 row created.

SQL> insert into student values(33,'prabha',32);

1 row created.

SQL> insert into student values(55,'siri',22);

1 row created.

SQL>select * from student;

SID SNAME AGE

---------- -------------------- ----------
11 sudhakar 35
22 madhu 26
33 prabha 32
55 siri 22

SQL> set serveroutput on;

SQL>edfunc;

create or replace function stdname(id in number) return varchar2 as

name varchar2(15);

begin

selectsname into name from student where sid=id;

return name;

exception

whenno_data_found then

dbms_output.put_line('no student found with id ' || id);

return(-1);

end;

/
SQL> @func;

Function created.

SQL>edfuncal;

declare

id number(5);

name varchar2(15);

begin

id:=&student_id;

name:=stdname(id);

if (name!='-1') then

dbms_output.put_line('the student name with id ' || id || ' is '||name);

end if;

end;

SQL> @funcal;
Enter value for student_id: 11
old 5: id:=&student_id;
new 5: id:=11;
the student name with id 11 is sudhakar

PL/SQL procedure successfully completed.

SQL> @funcal;
Enter value for student_id: 44
old 5: id:=&student_id;
new 5: id:=44;
no student found with id 44

PL/SQL procedure successfully completed.

PACKAGE
SQL> set serveroutput on;
SQL>edpackdec;
create or replace package maths as

procedure square(n in number);

function cub(n in number)return number;

endmaths;

/
SQL> @packdec;

Package created.

SQL>edpackdef;
create or replace package body maths as

y number;

procedure square(n number) is

begin

y:=n*n;

dbms_output.put_line('The square of '||n||'is '||y);

dbms_output.put_line(chr(10));

end square;

function cub(n in number)return number as

begin

y:=n*n*n;

return y;

end cub;

endmaths;
/

SQL> @packdef;

Package body created.

SQL>edpackcal;
declare

n number;

y number;

begin

n:=&n;

[Link](n);

y:=[Link](n);

dbms_output.put_line('The cube of '||n||' is '||y);

end;

SQL> @packcal;
Enter value for n: 5
old 5: n:=&n;
new 5: n:=5;
The square of 5is 25

The cube of 5 is 125

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> exit;