GONNA INSTITUTE OF INFROMATION TECHNOLOGY AND SCIENCES,
VISAKHAPATNAM.
DATABASE MANAGEMENT SYSTEM
R20
LAB MANUAL
1) Creation, altering and droping of tables and inserting rows into a table (use
constraints while creating tables) examples using SELECT command.
Create table st
SQL> create table st(sid int primary key,sname varchar(20),sdept varchar(20),sgrade varchar(5));
Table created.
Alter table st by dropping column SQL> alter
table st drop column sdept; Table altered.
Desc st
SQL> desc st;
Name Null? Type
SID NOT NULL NUMBER(38)
SNAME VARCHAR2(20)
SGRADE VARCHAR2(5)
Insert into table st
SQL> insert into st values(101,'sai','a'); 1 row
created.
SQL> insert into st values(102,'ayisha','a+'); 1 row
created.
Select from table st
SQL> select * from st;
SID SNAME SGRAD
101 sai a
102 ayisha
a+ Drop table st
SQL>
drop table st; Table
dropped.
Then check select then table will not exist
SQL> select * from st;
select * from st
* ERROR
at line 1:
ORA-00942: table or view does not exist
3) Queries using Aggregate functions (COUNT,SUM, AVG, MAX and MIN), GROUP BY,
HAVING and Creation and dropping of Views.
Create table product
SQL> create table product(pno int ,pname varchar(30),price float,quantity int); Table
created.
Insert into product
SQL> insert into product values(1,'dairy milk',60,2); 1 row
created.
SQL> insert into product values(2,'good day',25,4); 1 row
created.
SQL> insert into product values(3,'boost',10,6); 1 row
created.
SQL> insert into product values(4,'maggi',5,10); 1 row
created.
SQL> insert into product values(5,'book',20,20); 1 row
created.
Select from product
SQL> select * from product;
PNO PNAME PRICE QUANTITY
1 dairy milk 60 2
2 good day 25 4
3 boost 10 6
4 maggi 5 10
5 book 20 20
Count function
SQL> select count(price) from product;
COUNT(PRICE)
SQL> select count(quantity) from product;
COUNT(QUANTITY)
Sum function
SQL> select sum(price) from product;
SUM(PRICE)
120
SQL> select sum(quantity) from product;
SUM(QUANTITY)
42
Avg function
SQL> select avg(price) from product;
AVG(PRICE)
24
SQL> select avg(quantity) from product;
AVG(QUANTITY)
8.4
Max function
SQL> select max(price) from product;
MAX(PRICE)
60
SQL> select max(quantity) from product;
MAX(QUANTITY)
20
Min function
SQL> select min(price) from product;
MIN(PRICE)
SQL> select min(quantity) from product;
MIN(QUANTITY)
Group by
Create table employ(sid int,name varchar(20),dept varchar(10),sal float);
Table created
Select * from employ
Sid name dept sal
1 ayisha ece 6000
2 sindhu it 50000
3 sai it 80000
4 lalli ece 8000
SQL>select dept,sum(sal) from employ group by dept; Dept
sum(sal)
It 130000
Ece 14000
Having
SQL>select dept,sum(sal) from employ group by dept having sum(sal)>25000;
Dept sum(sal)
It 130000
Creating views
Create table st(sid int,sname varchar(10));
Create table marks(sid int,sub1 int,sub2 int,sub3 int);
Create view stu as
Select st.sid,st.sname,marks.sub1,marks.sub2,marks.sub3 From
st,marks
Where st.sid=marks.sid;
Select * from stu;
Sid sname sub1 sub2 sub3
101 sai 100 90 95
4) Queries using Conversion functions (to_char,to_number
and to_date),stringfunctions(Concatenation, lpad, rpad, ltrim, rtrim, lower,upper,
initcap, length, substr and instr), datefunctions (Sysdate, next_day, add_months,last_day,
months_between, least, greatest, trunc,round, to_char, to_date)
Conversion
functions 1.to_char
SQL> select to_char(sysdate,'dd month year') from dual;
;
TO_CHAR(SYSDATE,'DDMONTHYEAR)
28 october twenty twenty-one
2. to_number
SQL> select to_number('234.87') from dual;
TO_NUMBER('234.87')
234.87
3. to_date
SQL> select to_date('jan 21 1998','month dd,yy') from dual; TO_DATE('
21-JAN-98
String functions
1.lower
SQL> select lower('SAI') from dual;
LOW
---
sai
2. upper
SQL> select upper('sai') from dual; UPP
--- SAI
3. concat
SQL> select concat('hello','sai') from dual;
CONCAT('
hellosai
4. initcap
SQL> select initcap('sai') from dual; INI
---
Sai
5. length
SQL> select length('sai') from dual;
LENGTH('SAI')
3
6. instr
SQL> select instr('ruhanika','a') from dual;
INSTR('RUHANIKA','A')
7. substr
SQL> select substr('ruhanika',5) from dual; SUBS
nika
8. lpad
SQL> select lpad('ruhanika',10,'****') from dual;
LPAD('RUHA
**ruhanika
9. rpad
SQL> select rpad('ruhanika',10,'****') from dual;
RPAD('RUHA
ruhanika**
10. ltrim
SQL> select ltrim(' ruhanika') from dual;
LTRIM('R
ruhanika
11. rtrim
SQL> select rtrim(' ruhanika ') from dual;
RTRIM('RUHANIK
ruhanika
date functions
1.sysdate
SQL> select sysdate from dual;
SYSDATE
20-NOV-15
2. last_day
SQL> select last_day(sysdate) from dual;
LAST_DAY(
30-NOV-15
3. next_day
SQL> select next_day('20-nov-2015','friday') from dual;
NEXT_DAY(
27-NOV-15
4. add_months
SQL> select add_months(sysdate,2) from dual;
ADD_MONTH
20-JAN-16
5. months_between
SQL> select months_between('20-nov-2015','20-jan-2016') from dual;
MONTHS_BETWEEN('20-NOV-2015','20-JAN-2016')
-2
6. least
SQL> select least(10,11,12) from dual;
LEAST(10,11,12)
10
SQL> select least('s','f','a') from dual;
L
-
a
7. greatest
SQL> select greatest(10,11,12) from dual;
GREATEST(10,11,12)
12
SQL> select greatest('s','f','a') from dual; G
-
s
8. ground
SQL> select round(21.088) from dual;
ROUND(21.088)
21
SQL> select trim(21.088) from dual;
TRIM(2
21.088
5) i)Creation of simple PL/SQL program which includes declaration section, executable
section and exception –Handling section (Ex. Student marks can be selected from the
table and printed for those who secured first class and an exception can be raised if no
records were found)
Create table student (sid, sname, sclass);
Program:
declare
stu_id number; stu_name
varchar(20); cursor
stu_cur is
select sid,sname
from student
where sclass='first';
Begin
Open stu_cur;
Loop
fetch stu_cur into stu_id,stu_name; exit
when stu_cur%notfound;
dbms_output.put_line('student_id: '|| stu_id || 'student_name:'|| stu_name); end loop; close
stu_cur;
end;
/
Output
student_id: 1student_name:abhi
student_id: 2student_name:sai
student_id: 5student_name:ish
PL/SQL procedure successfully completed.
ii) Insert data into student table and use COMMIT, ROLLBACK and SAVEPOINT in
PL/SQL block.
Create table stu(name varchar(10),branch varchar(10)); Insert
into stu values(‘sai’,’it’);
Savepoint h;
Savepoint created
SQL> set serveroutput on;
SQL> begin
savepoint g;
insert into stu values('ruhi','cse');
exception
when dup_val_on_index then
rollback to g;
commit;
end;
/
SQL>Rollback to h;
Rollback completed
6) Aim: Develop a program that includes the features Nested if,Case and case
expression.The Program can be extended using the NULL if, and COALESCE functions.
(1) CASE:
Syntax:
select case("column_name")
when "value1" then "result1"
when "value2" then "result2"
....
[else "resultN"] end
from "table-name";
program:
select store_name,case store_name
when 'Newyork' then sales*2
when 'chicago' then sales*3
else sales
end
"new sales"
txn_date
from store_information;
(2) SEARCHED CASE EXPRESSION:
program:
select store_name,txn_date,case
when sales>=8000 then 'congrats get a gift coupon' when
sales>=2000 then 'Thanks for shopping'
else 'Good day'
end
"sales status"
from store_information;
(3) COALESCE:
This returns the first non-NULL expression among its arguements.
syntax:
coalesce("expression1","expression2",...);
(4) NULL IF:
Takes two arguements.If the two arguments are equal,then NULL is returned.otherwise the first
arguement is returned.
syntax:
select column_name,NULLIF(argument1,arguement2) from tabe_name;
(5) PROGRAM FOR CASE:
declare
grade char(1);
begin grade:='a';
case
when grade='a' then
dbms_output.put_line('Excellent'); when
grade='b' then
dbms_output.put_line('very good'); when
grade='c' then
dbms_output.put_line('good'); when
grade='d' then
dbms_output.put_line('fair');
when grade='f' then
dbms_output.put_line('poor'); else
dbms_output.put_line('No such grade'); end case;
end;
/
7) Program development using WHILE LOOPS, numeric FOR LOOPS, nested loops
using ERROR Handling, BUILT –IN Exceptions, USE defined Exceptions, RAISE-
APPLICATION ERROR.
PL/SQL
1) AIM: Addition at run
time Declare
a number; b
number; c
number;
Begin a:=&a;
b:=&b;
c:=&c;
dbms_output.put_line('sum of' || a || 'and' || b || 'is' || c); end;
/
output:
Enter value for a: 10 old
6: a:=&a;
new 6: a:=10; Enter
value for b: 10
old 7: b:=&b;
new 7: b:=10; Enter
value for c: 10 old 8:
c:=&c;
new 8: c:=10; sum
of10and10is10
PL/SQL procedure successfully completed.
2) AIM: Simple loop to get sum of 100
numbers Declare
a number;
s1 number default 0;
begin
a:=1;
loop
s1:=s1+a;
exit when(a=100);
a:=a+1;
end loop;
dbms_output.put_line('sum between 1 to 100 is' || s1); end;
/
output:
sum between 1 to 100 is5050
PL/SQL procedure successfully completed.
3) AIM: While Loop for sum of 100 odd numbers Program:
SQL> declare n
number;
endvalue number; sum1
number default 0; begin
endvalue:=&endvalue; n:=1;
while(n<endvalue)
loop sum1:=sum1+n;
n:=n+2;
end loop;
dbms_output.put_line('sum of odd numbers between 1 and ' || endvalue || 'is' || sum1); end;
/
Output
:
Enter value for endvalue: 19 old
6: endvalue:=&endvalue;
new 6: endvalue:=19;
sum of odd numbers between 1 and 19 is 81
PL/SQL procedure successfully completed.
4) AIM: if else for finding maximum of three numbers Program:
SQL> declare a
number;
b number; c
number;
begin a:=&a;
b:=&b;
c:=&c;
if(a>b)and(a>c)then
dbms_output.put_line(' a is maximum');
elsif(b>a)and(b>c)then
dbms_output.put_line('b is maximum'); else
dbms_output.put_line('c is maximum'); end if;
end;
/
Output
Enter value for a: 10 old
6: a:=&a;
new 6: a:=10; Enter
value for b: 4 old 7:
b:=&b;
new 7: b:=4; Enter
value for c: 19 old 8:
c:=&c;
new 8: c:=19;
c is maximum
PL/SQL procedure successfully completed.
5) AIM: select column from table employ by using memory variable
Program: Declare
Mvsalary number(10,2);
Begin
Select salary into mvsalary
From
Employ
Where ename=’sai’;
Dbms_output.put_line(‘the salary of employ is’ || to_char9mvsalary)); End;
/
Output
The salary of employ is 50000
PL/SQL procedure successfully completed.
8) AIM: Programs development using creation of
procedures,passing paramneters IN and OUT of procedures.
->create table enquiry(enqno1 number,fname varchar2(30));
->insert into enquiry values(111,'sai');
->insert into enquiry values(112,'sindhu');
/*program*/
create procedure findname(enquiryno1 IN number,fname1 OUT varchar2) is
fname2 varchar2(30);
begin
select fname into fname2
from enquiry
where enqno1=enquiryno1;
fname1:=fname2; exception
when no_data-found then
raise_application_error(-20100,'The given number is not present'); end;
/*calling procedure*/
declare
enqno2 number(5);
fname2 varchar2(30);
begin
enqno2:=111;
findname(enqno2,fname2);
dbms_output.put_line(fname2); end;
/
output: sai
9) AIM:Program development using creation of stored functions,invoke functions in SQL
statements and write complex functions.
->create table dept(deptno int,dname varchar(10));
->insert into dept values(1219,'sai');
/*program*/
create or replace function getname(dno number) return
varchar2 as
fname1 varchar2(30);
begin
select dname into
fname1 from dept
where deptno=dno;
return(fname1);
exception
when no_data_found then
raise_application_error(-20100,'The dno is present'); end;
/
/*calling function*/
declare
fname2 varchar2(30);
deptno2 number(5);
begin
deptno:=1219;
fname2:=getname(dno);
dbms_output.put_line(fname2); end;
/
output: sai
10) AIM:Program development using creation of package specification,package
bodies,private objects,package variables and cursors and
calling stored packages.
(1) create a table dept1
->create tabke dept1(dname varchar2(10),deptno number);
->insert into dept values('accounting',10);
->insert into dept values('hr',20);
(2) create a table dept
->create table dept(dno number,vt varchar2(10),dloc varcar2(20));
(3) creating package header
create or replace package test
is procedure savedept
(dno in number,dloc in varchar); end;
/
(4) creating package body
create or replace package body test is
function getdno(dno in number)
return varchar
is
dnum varchar(20);
begin
select dname into dnum from dept
where deptno=dno;
return dnum;
end;
procedure savedept
(dno in number,dloc in varchar) is
vt varchar(20)
begin
vt:=getno(dno);
insert into dept values(dno,vt,dloc);
exception
when dup_val_on_index then
raise_application_error(-2007,'duplicate'); end;
end;
/
(5) Executing procedure
exec test.savedept(10,'vijayawada');
(6) Display the table
->select * from dept;
11)Develop programs using features parameters in a CURSOR, FOR
UPDATE CURSOR, WHERE CURRENT of clause and CURSOR variables.
CURSORS
AIM: create an employ table and retrieve the name of employ whose salary is Greater than
25000 by PL/SQL
Create table employ(eid,ename,salary);
Program:
declare
emp_rec varchar(30);
cursor emp_cur is select
ename
from employ where salary>25000;
Begin
Open emp_cur;
Loop
fetch emp_cur into emp_rec; exit
when emp_rec%notfound;
dbms_output.put_line(emp_rec); end
loop;
close emp_cur;
end;
/
Output:
Sindhu
Sai
Satya
PL/SQL procedure successfully completed.
12) AIM:Develop programs using before and after triggers,row and statement triggers and
instead of triggers.
(1) Create a trigger
create or replace trigger trg2 after
insert or delete or update on dept1
for each row
when(new.deptno>0)
begin
dbms_output.put_line('trigger fired'); end;
/
(2) Insert
->insert into dept values('sindhu',30);
*trigger fired*
*1 row created*
(3) UPADTE
->udpate dept1 set deptno=19 where dname='sindhu';
*trigger fired*
*1 row updated*
(4) DELETE
->delete from dept where deptno=30;
*trigger fired*
*1 row deletd*
