1. Creating a table with constraints. Problem: Create a table student with following constraints.
i)A CHECK constraint on the stud_id, so that value must start with 'S' ii)A CHECK constraint on stud_name so that name is entered in uppercase.
iii)A CHECK constraint on class so that only the groups “B.SC” , “B.COM”, and “B.B.M” iv) The default student age is 21 and college is “sv arts degree and pg college”.
Problem Solution: Table Creation : Create table student(
stud_id varchar2(10) CONSTRAINT sno_cons check(stud_id like 's%') primary key,
stud_name varchar2(20) CONSTRAINT sname_cons check(stud_name=upper(stud_name)), age number(2) default 21,
class varchar2(10) check(class in('B.SC','B.COM','M.C.A',’M.B.A’)), college varchar2(30) default ('sv arts degree and pg college'));
Rows Insertion:
insert into student(stud_id,stud_name,class) values('S121','HARI','B.SC'); insert into student(stud_id,stud_name,class) values('S122','RAVI','B.COM'); insert into student(stud_id,stud_name,class) values('S123','SIVA','M.B.A');
insert into student(stud_id,stud_name,age,class) values('S124','RAJESH',22,'B.SC'); TABLE DATA:
SQL> select * from student;
Stud_id Stud_name Age Class College
S121 HARI 21 B.SC sv arts degree and pg college
S122 RAVI 21 B.COM sv arts degree and pg college S123 SIVA 21 B.B.M sv arts degree and pg college S124 RAJESH 22 B.SC sv arts degree and pg college
2. JOIN OPERATORS Problem: Write the select the statements for joining two tables. Table Creation:
Create table employee(eno number(4) primary key,ename varchar2(20), sal number(8,2), deptno number(2) references department(deptno));
create table department(deptno number(2) primary key,
dname varchar2(10),loc varchar2(20)); Rows insertions
insert into employee values(&eno,’&ename’,&sal,&deptno); insert into department values(&deptno,’&dname’,’&loc’); Table data:
DEPTNO DNAME LOC
10 Sales Gudur
20 Marketing Nellore 30 Production Chennai 40 purchase Hyderabad SQL> select * from employee;
ENO ENAME SAL DEPTNO
121 ravi 6500 20 122 siva 7600 10 123 rahul 8900 30 124 Scott 22000 INNER JOIN SQL>select eno,ename,sal,employee.deptno,dname,loc from employee NATURAL JOIN department;
ENO ENAME SAL DEPTNO DNAME LOC
121 ravi 6500 20 marketing nellore 122 siva 7600 10 sales gudur 123 rahul 8900 30 production chennai OUTER JOIN
1. LEFT OUTER JOIN
SQL>select eno,ename,sal,employee.deptno,dname,loc from employee left outer join department on employee.deptno=department.deptno;
ENO ENAME SAL DEPTNO DNAME LOC
121 ravi 6500 20 marketing nellore
122 siva 7600 10 sales gudur
123 rahul 8900 30 production chennai 124 Scott 22000
2. RIGHT OUTER JOIN
SQL>select eno,ename,sal,employee.deptno,dname,loc from employee RIGHT JOIN department on employee.deptno=department.deptno;
ENO ENAME SAL DEPTNO DNAME LOC
ENO ENAME SAL DEPTNO DNAME LOC
121 ravi 6500 20 marketing nellore
122 siva 7600 10 sales gudur
123 rahul 8900 30 production chennai
124 Scott 22000
40 purchase hyderabad 3. MULTIPLICATION TABLE
Problem: Write a program to display the Multiplication table for a given number. Problem Solution Algorithm 1. Begin 2. input n 3. for x=1 to 10 4. res=n*x; 5. print n,”*”,x,”=”,res 6. end for 7. end PL/SQL Program declare num number(5); res number(5); begin num:=# for i in 1..10 loop res:=num*i; dbms_output.put_line(num||'*'||i||'='||res); end loop; end; output:
4. BIGGEST AMONG THREE NUMBERS
Problem: Write a program to find biggest among 3 three numbers Problem Solution
Algorithm 1. Begin 2. input a,b,c
3. if a>b and a>c then 4. big=a
5. else
6. if b>c and b>a then 7. big=b
8. else 9. big=c
10. print “biggest number=”,big 11. end PL/SQL Program declare num1 number(5); num2 number(5); num3 number(5); begin num1:=&num1; num2:=&num2; num3:=&num3;
if (num1>num2) and (num1>num3)then
dbms_output.put_line('The biggest number in given 3 is '||num1); elsif (num2>num3) and (num2>num1)then
dbms_output.put_line('The biggest number in given 3 is '||num2); else
dbms_output.put_line('The biggest number in given 3 is '||num3); end if;
end;
5. AMSTRONG NUMBER
PROBLEM: Write a program to check the given number is amstrong number or not Problem Solution
Algorithm 1. begin 2. input num 3. num1=num
4. Repeat while num>0 5. rem=num mod 10 6. s=s+power(rem,3) 7. num=num/10 8. end while 9. if num1=s then
10. print ‘ the given is amstrong’ 11. else
12. print ‘not amstrong’ 13. end if 14. end PL/SQL PROGRAM declare num number(5); rem number(5); s number(5):=0; num1 number(5); begin num:=# num1:=num; while(num>0) loop rem:=mod(num,10); s:=s+power(rem,3); num:=trunc(num/10); end loop; if(num1=s) then
dbms_output.put_line('The given number is Amstrong'); else
dbms_output.put_line('The given number is not a Amstrong'); end if;
end; OUTPUT
6. PALINDROME
Problem: Write a PL/SQL program to check the given number palindrome or not. Problem Solution
Algorithm:
1. declare n, rnum, rem, n1 2. input n 3. n1=n 4. rnum = 0 5. repeat while n>0 rem = mod(n,10) rnum = rnum*10+rem n= n/10
6. print ‘reverse number=’, rnum 7. if n1=rnum then
8. print ‘palindrome’ 9. else
10. print ‘not palindrome’ 11. stop PL/SQL Program declare n number(4); rnum number(5); rem number; begin n:=&n; n1:=n; rnum:=0; dbms_output.put_line(‘given number=’||n); while n>0 loop rem:=mod(n,10); rnum:=rnum * 10 +rem; n:=floor(n/10); end loop; dbms_output.put_line(‘reverse number=’||rnum); if n1=rnum then dbms_output.put_line(‘palindrome’); else dbms_output.put_line(‘not palindrome’); end;
7. STRING PALINDROME
PROBLEM: Write a program to check the given string is palindrome or not.
Problem Solution
ALGORITHM 1. Begin 2. input string 3. len=length(string) 4. repeat while len>0
5. revstr=concate(revstr,substr(string,len,1)) 6. len=len-1
7. end while
8. print “the reverse string is:”,revstr 9. if string=revstr then
10. print “the given string is palindrome” 11. else
12. print “the given strong is not palindrome” 13. end if 14. end PL/SQL PROGRAM declare string varchar2(10); revstr varchar2(10); len number(5); begin string:='&string'; len:=length(string); while(len>0) loop revstr:=revstr||substr(string,len,1); len:=len-1; end loop;
dbms_output.put_line('The reverse of given string is'||revstr); if(string=revstr) then
dbms_output.put_line('The given string is a pallindrome'); else
dbms_output.put_line('The given string is not a pallindrome'); end if;
8. FACTORIAL
Problem Definition Write a PL/SQL program to find Factorial for a given Number. Problem Solution: Algorithm 1. declare n, fact 2. input n 3. fact = 0 4. repeat while n>0 a. fact=fact*n b. n= n-1
5. print ‘factorial =’, fact 6. stop PL/SQL Program declare n number(3); fact number(3); begin n:=&n; fact:=1; while n>0 loop fact:=fact*n; n:=n-1; end loop;
dbms_output.put_line('factorial for a number='||fact); end;
output
9. PRIME NUMBER CHECKING
Problem: write a program to check the given number is prime or not using procedures. Problem Solution: Algorithm: 1. Begin 2. input x 3. for i=1 to x 4. if mod(x,i)=0 then
Procedure definition:
create or replace procedure prime(x in number) as c number(5):=0; begin for i in 1..x loop if(mod(x,i)=0) then c:=c+1; end if; end loop; if(c=2) then
dbms_output.put_line('The given number is Prime number'); else
dbms_output.put_line('The given number is not a Prime number'); end if;
end;
executing procedure output
SQL>exec prime(7);
10. Calculating EmployeeNetSalay Problem Definition :
Write a PL-SQL program to evaluate the DA, HRA, INCOME TAX, GROSS SALARY, and
NETSALARY from the base table PAY. We have Input in the base table only the fields ENUMBER, ENAME and BASIC. Conditions are
1. If basic >=10000 then da=25% of basic hra= 15% of basic it=10% of basic Otherwise da=15% of basic hra= 10% of basic it=5% of basic Create the base table with following specifications.
Field Name Data Type Length Constraints
Eno Number 6 Primary Key
Ename Varchar2 15 Basic Number 8,2 Da Number 6,2 Hra Number 6,2 Itax Number 6,2 Gsal Number 8,2 Netsal Number 8,2 Problem Solution: Table Creation
create table pay(eno number(6) Primary Key, ename varchar2(15), bsal number(8,2), da number(6,2), hra number(6,2), itax number(6,2), gsal number(8,2), netsal number(8,2));
Rows Insertion
insert into pay (eno,ename,bsal) values(11,’Radha’8000); insert into pay (eno,ename,bsal) values(22,’Krishna’,4500); insert into pay (eno,ename,bsal) values(33,’Veena’,15000); insert into pay (eno,ename,bsal) values(44,’Sita’,9000);
Table Contents Before Execution
Eno Ename Bsal Da Hra Itax Gsal Netsal
11 Radha 8000 - - - - -22 Krishna 4500 - - - - -33 Veena 15000 - - - - -44 Sita 9000 - - - - -PL/SQL Program Declare da1 number(6,2); hra1 number(6,2); itax1 number(6,2); gs number(8,2); nsal number(8,2);
Cursor c1 is select * from pay; begin for i in c1 loop if i.bsal>10000 then da1= i.bsal*0.25; hra1=i.bsal*0.15; itax1=i.bsal*0.1; else da1= i.bsal*0.15; hra1=i.bsal*0.1; itax1=i.bsal*0.05; end if; gs:=i.bsal+hra1+da1; nsal:=gs-itax1;
update pay set da=da1,hra=hra1,itax=itax1,gsal=gs,netsal=nsal where eno = i.eno; end loop;
end;
Table Data:
Eno Ename Basic Da Hra Itax Gsal Netsal
11 Radha 8000 320 240 200 8560 8360
22 Krishna 4500 157.5 112.5 90 4770 4680
33 Veena 15000 750 600 450 16350 15900
11.Student Result Processing Problem Definition
Write a PL/SQL program to process the X class results for the following rules. If Eng, tel, hin, mat, sci, soc >=35 then pass and total is >=360 I class,
if total is >=300 II class, otherwise III class.
The base table is Tenth and fields are HTNO, ENG, TEL,HIN,MAT,SCI,SOC,TOT,RESULT. Problem Solution: Table Creation
Create table ssc(htno number(8),eng number(3),tel number(3),hin number(3),mat number(3),sci number(3),soc number(3), total number(3),result varchar2(10));
Rows Insertion
insert into ssc(htno,eng,tel,hin,mat,sci,soc) values(10011,44,45,56,67,78,45); insert into ssc(htno,eng,tel,hin,mat,sci,soc) values(10012,64,55,65,67,78,55); insert into ssc(htno,eng,tel,hin,mat,sci,soc) values(10013,48,55,56,75,78,45); insert into ssc(htno,eng,tel,hin,mat,sci,soc) values(10014,33,65,56,67,86,65); Table Data:
Htno Eng Tel Hin Mat Sci Soc Total Result 10011 44 45 56 67 78 45 10012 64 55 65 67 78 55 10013 48 55 56 75 78 45 10014 63 65 56 67 86 65 PL/SQL Program declare tot number(3); res varchar2(10);
cursor c1 is select * from ssc; begin
for i in c1 loop
total:=i.eng+i.tel+i.hin+i.mat+i.sci+i.soc;
if i.eng>34 and i.tel>34 and i.hin>34 and i.mat>34 and i.sci>34 and i.soc>34 then if total>=360 then
res:='First’;
elsif total>=300 then res:='Second’; else res:='Third’; end if; else res:=’fail’; end if;
update ssc set total=tot, result=res where htno=i.htno; end loop;
end;
Table Data: SQL>Select * from ssc;
Htno Eng Tel Hin Mat Sci Soc Tot Result
10011 44 45 56 67 78 45
10012 64 55 65 67 78 55
10013 48 55 56 75 78 45
12. Finding Electrical Charges Problem Definition :
Write a PL/SQL program to calculate Electrical charges as per the rates given below: 1. Industry Rs 7.50,
2. Agricultural Rs 0.5p 3. Domestic Rs 3.50. Problem Solution: Table Creation
create table apseb(cno varchar2(6),ctype char(15),pmr number(7),omr number(7),nu number(4), rate number(8,2), tcharge number(10,2));
Rows Insertion
insert into apseb(cno,ctype,pmr,omr) values('c001','agriculture',700,450); insert into apseb(cno,ctype,pmr,omr) values('c002','domestic',500,420); insert into apseb(cno,ctype,pmr,omr) values('c003','industry',1500,450); Table Data:
SQL> select * from apseb;
Cno Ctype Pmr Omr Nu Rate Tcharge
C001 Agriculture 700 450 C002 Domestic 500 420 C003 Industry 1500 450 PL/SQL Program declare price apseb.rate%type; amt apseb.tcharge%type; cu apseb.nu%type;
cursor c1 is select * from apseb; begin
for i in c1 loop
if i.ctype='agricultural’ then price:=0.5;
elsif ctype='industry' then price:=7.50; else price:=3.50; end if; cu:=i.pmr - i.omr; amt:=cu * price;
update apseb set rate=price, nu=cu, tcharge=amt where cno=i.cno; end loop;
13. Calculating Commission Problem Definition:
Write a PL-SQL program to calculate the commission for a given basic to be calculated as follows. If salary is greater than 7000 commission is 20%
If salary is lessthan 7000 and greater than 5000 commission is 15% Other wise commission is 10%
Problem Solution: Table Creation
create table empcomm (acno number(5),basic number(10,2),comm number(6,2)); Rows Insertion:
insert into empcomm (acno,basic) values(101,6000); insert into empcomm (acno,basic) values(111,4200); insert into empcomm (acno,basic) values(121,6500); insert into empcomm (acno,basic) values(131,3500); Table Data:
Select * from comm;
PL/SQL Program declare
com number(6,2)
cursor c1 is select * from empcomm; begin
for i in c1 loop if i.basic>=7000 then com:=i.basic*0.2;
elsif i.basic>=5000 and i.basic<7000 then com:=i.basic*0.15;
else
com:=i.basic*0.1; end if;
update empcomm set comm=com where acno=i.acno; end loop;
close c1; end;
TABLE DATA
select * from empcomm;
Acno Basic Comm.
101 6000
111 4200
121 6500
131 3500
Acno Basic Comm
101 6000 1200
111 4200 420
121 6500 1300
14. TRIGGER Problem Definition:
Create a trigger to insert values from one table to another table automatically when salary is greater than 6000
Table creation
create table emp1(eno number primary key,ename varchar2(20),sal number(5)); create table emp2(eno number primary key,ename varchar2(20),sal number(5)); problem solution:
Trigger creation
create or replace trigger tr1 after insert on emp1 for each row when(new.sal>=6000) begin
insert into emp2 values(:new.eno,:new.ename,:new.sal); end;
/
Rows insertion
insert into emp1 values(100,'Suneel',5000); insert into emp1 values(101,'Hari',7000); insert into emp1 values(102,'Sree',9000); insert into emp1 values(103,'Siri',5800); Table data
SQL> select * from emp2;
ENO ENAME SAL 101 Hari 7000 102 Sree 9000
15. CREATING PROCEDURE
PROBLEM: Create a procedure to insert student details in 3 different tables based on their course. Procedure
Procedure definition:
create or replace procedure student(s in number, name in varchar2, cou in varchar2) as begin
if cou='bsc' then
insert into student1 values(s,name,cou); elsif cou=’bcom’ then
insert into student2 values(s,name,cou); else
insert into student3 values(s,name,cou); end if;
end; /
Procedure execution
SQL> exec student(121,'rahul','bsc'); PL/SQL procedure successfully completed. SQL> exec student(122,'ravi','bcom'); PL/SQL procedure successfully completed. SQL> exec student(123,'ajay','mba'); PL/SQL procedure successfully completed. SQL> select * from student1;
SNO SNAME COURSE 121 rahul bsc
SQL> select * from student2; SNO SNAME COURSE --- 122 ravi bcom SQL> select * from student3; SNO SNAME COURSE 123 ajay mba
16. FUNCTION
Problem Definition: Create a function to find square of given number. Problem Solution:
Function creation:
create or replace function squar(x in number) return number as z number; begin z:=x*x; return(z); end; / Function calling declare n number(5); begin n:=&n;
dbms_output.put_line('The square of given number is'||squar(n)); end;
/ Output
Enter value for n: 5 old 4: n:=&n; new 4: n:=5;
