DBMS Query
1. Compound Statement
Give names of customer who are borrowers as well as
depositors and having living city Nagpur.
Query: select c1.CNAME from deposite d1, CUSTOMER c1, BORROW
br1 where c1.CITY='NAGPUR' AND d1.CNAME=c1.CNAME AND
d1.CNAME=br1.CNAME;
1. Using substring
select substr('Mumbai',2,3) from dual;
Output: SUB
--umb
1. Give names of depositors having the same living city as that of shivani
and having deposit amount>2000.
SQL> SELECT D1.CNAME FROM DEPOSIT D1, CUSTOMER C1 WHERE D1.AMOUNT>2000
AND D1.CNAME=C1.CNAME AND C1.CITY IN (SELECT C2.CITY FROM CUSTOMER C2
WHERE C2.CNAME='SHIVANI');
1. List all the customers who are borrowers but not customers.
SQL> SELECT CNAME FROM DEPOSIT
2 MINUS
3 (SELECT CNAME FROM BORROW);
2. List all the customers who are both depositors and borrowers.
SQL> SELECT CNAME FROM DEPOSIT INTERSECT(SELECT CNAME FROM BORROW);
3. List all the customers, along with their amount,
borrowers or depositors and living in city NAGPUR.
who
are
either
SQL> SELECT D1.CNAME, D1.AMOUNT FROM DEPOSIT D1,CUSTOMER C1 WHERE
C1.CITY='NAGPUR' AND D1.CNAME=C1.CNAME
2 UNION ALL
3
SELECT BR1.CNAME, BR1.AMOUNT FROM BORROW BR1, CUSTOMER C2 WHERE
C2.CITY='NAGPUR' AND BR1.CNAME=C2.CNAME;
SWITCH CASE
SQL> declare
2 grade char(1);
3 begin
4 grade:='&sgrade';
5 case grade
6
when 'O' then dbms_output.put_line('Distinction');
when 'A' then dbms_output.put_line('First class');
when 'B' then dbms_output.put_line('Passed');
else dbms_output.put_line('Invalid grade');
10 end case;
11 end;
12 /
IF..ELSE
SQL> declare
2 no1 number;
3 no2 number;
4 no3 number;
5 begin
6 no1:= &num1;
7 no2:= &num2;
8 no3:= &num3;
9 if(no1 > no2) and (no1 > no3) then
10 dbms_output.put_line(no1 || ' is greatest');
11 elsif (no2> no3) then
12 dbms_output.put_line(no2 || ' is greatest');
13 else
14 dbms_output.put_line(no3 || '
15 is greatest');
16 end if;
17 end;
FOR LOOP
SQL> declare
2 no number;
3 begin
4 no:=&number;
5 for i in 1..12
6 loop
7 dbms_output.put_line(no || '*' ||i||'=' ||(no*i));
8 end loop;
9 end;
10 /
WHILE LOOP
SQL> declare
2
no number;
i number;
begin
i:=1;
no:=&number;
while i<10
loop
dbms_output.put_line(no || '*' ||i||'=' ||(no*i));
10
i:=i+1;
11
end loop;
12
13
end;
/
IMPORTANT TOPICS :Procedure :-
SQL> create or replace procedure lab as
2 Begin
3 insert into Doctor
values(103,'Rohit','Vishal','Sharma','Malad','Male','rohitsharma@gmail.com
'
4 ,'M.B.B.S.','Haematology');
5 End;
6 /
Procedure created.
SQL> execute lab
Functions:-
1. Write a PL/SQL code using Functions to add to numbers.
Function Declaration
create or replace function addn(a in number,b in number) return
number IS
s number;
Begin
s:= a+b;
return s;
End;
Output:
Function created.
To Run the Function
Declare
n1 number;
n2 number;
n3 number;
Begin
n1:= &number1;
n2:= &number2;
n3:=addn(n1,n2);
dbms_output.put_line(n1||'+'||n2||'='||n3);
End;
/
2. Write a PL/SQL code using Functions to find factorial of a
number.
Function Declaration
Create or Replace Function factorial(no number) return number IS
f number;
Begin
f:=1;
for i in Reverse 1..no loop
f:=f*i;
end loop;
return f;
End;
Output:
Function created.
To Run the Function
Declare
n number;
f number;
Begin
n:= &number;
f:= factorial(n);
dbms_output.put_line('Factorial of '||n||' is '||f);
End;
Procedure :-
1. Write a PL/SQL code using Procedure to find greatest of
three numbers.
Procedure Declaration
Create or Replace Procedure greater3 (n1 In number, n2 In number, n3
In number, g Out number) Is
Begin
if n1>=n2 and n1>=n3 then
g:= n1;
elsif n2>=n1 and n2>=n3 then
g:= n2;
else
g:= n3;
end if;
End;
/
Output: Procedure created.
To Run the Procedure
Declare
a number;
b number;
c number;
g number;
Begin
a:= &number1;
b:= &number2;
c:= &number3;
greater3(a,b,c,g);
dbms_output.put_line(g||' is the greatest of the 3 entered
numbers');
End;
Triggers:-
1. Write a PL/SQL code using Before Insert Trigger.
Function Declaration
Create or replace trigger emp_trig
before insert on emp
for each row
when (new.sal>3800)
Begin
raise_application_error(-20003,'Error: Salary cannot be more than
3800');
End;
/
Output:
Trigger created.
insert into emp values(7421,'robert','salesman',7341,'25-Oct1982',4650,Null,10);
Output:
ERROR at line 1:
ORA-20003: Error: Salary cannot be more than 3800
ORA-06512: at "SCOTT.EMP_TRIG", line 2
ORA-04088: error during execution of trigger 'SCOTT.EMP_TRIG'
2. Write a PL/SQL code using Before Update Trigger.
Function Declaration
create or replace trigger emp_up_trig
before update on emp
for each row
when (new.sal<old.sal)
Begin
raise_application_error(-20002,'Error:New salary cannot be
less than original salaray');
End;
/
Output:
Trigger created.
update emp set sal=720
where mgr=7341;
Output:
ERROR at line 1:
ORA-20002: Error:New salary cannot be less than original salaray
ORA-06512: at "SCOTT.EMP_UP_TRIG", line 2
ORA-04088: error during execution of trigger 'SCOTT.EMP_UP_TRIG'
Cursors :-
1. Write a Code PL/SQL which uses Implicit Cursors.
Code:
DECLARE
var_rows number(5);
BEGIN
UPDATE emp SET sal= sal + 1000 where comm is not null;
IF SQL%NOTFOUND THEN
dbms_output.put_line('None of the salaries where updated');
ELSIF SQL%FOUND THEN
var_rows := SQL%ROWCOUNT;
dbms_output.put_line('Salaries for ' || var_rows || 'employees are
updated');
END IF;
END;
Output:
Salaries for 9employees are updated
2. Write a Code PL/SQL which uses Explicit Cursors.
Code:
DECLARE
emp_rec emp%rowtype;
CURSOR emp_cur IS SELECT * FROM emp WHERE sal > 800;
BEGIN
OPEN emp_cur;
FETCH emp_cur INTO emp_rec;
dbms_output.put_line (emp_rec.ename || '
CLOSE emp_cur;
' || emp_rec.sal);
END;
/
Output:
SMITH
820
Ex: Write a Code PL/SQL which uses Explicit Cursors and Loop to
print the values.
Code:
DECLARE
emp_rec emp%rowtype;
CURSOR emp_cur IS SELECT * FROM emp WHERE sal > 2000;
BEGIN
OPEN emp_cur;
LOOP
FETCH emp_cur INTO emp_rec;
EXIT WHEN emp_cur%NOTFOUND;
dbms_output.put_line (emp_rec.ename || ' ' || emp_rec.sal);
END LOOP;
CLOSE emp_cur;
END;
/
Packages :-
1. Write a PL/SQL code which uses package to add 2 numbers.
Package Declaration
Create or Replace Package pkg1 as
Procedure p_add(n1 In number, n2 In number, n3 Out number);
Function f_add(n1 In number, n2 In number) return number;
End;
/
Output: Package created.
Package Body
Create or Replace Package body pkg1 as
procedure p_add(n1 In number,n2 In number,n3 Out number) IS
Begin
n3:= n1 + n2;
End;
function f_add(n1 in number,n2 in number) return number
is
n3 number;
begin
n3:=n1+n2;
return n3;
end;
End pkg1;
/
Exception :1. Write a PL/SQL code to raise an exception if the value of the
employee number being inserted is less than 0.
Code:
Declare
eno number;
ename varchar2(30);
exp1 EXCEPTION;
Begin
eno:= &emp_no;
ename:= '&ename';
if eno<0 then
raise exp1;
else
insert into details values(eno,ename);
dbms_output.put_line('Data inserted');
end if;
EXCEPTION
when exp1 then
dbms_output.put_line('Eno should not be negative');
End;
/
