Book (bno, bname, pubname, price)
Department (dno, dname)
***************************************************************************
SQL>
create table department52(dno number(5) primary key,dept_name varchar2(10));
Table
created.
SQL>
desc department52;
SQL> insert into department52
values(11,'Computer');
1
row created.
SQL> insert into department52 values(12,'Civil');
1
row created.
SQL> insert into department52
values(13,'Electronic');
1
row created.
SQL> insert into department52 values(14,'IT');
1
row created.
SQL> insert into department52 values(15,'Mech');
1
row created.
SQL>
select * from department52;
SQL> create table book52(bno number(5) primary
key,b_name varchar2(10),pub_name varchar2(10),price
number(5) check (price>0),dno number(5) references department52);
Table
created.
SQL>
desc book52;
SQL> insert into book52 values(1,'VB','BPB',200,11);
1
row created.
SQL> insert into book52
values(2,'Graphics','Nirali',350,12);
1
row created.
SQL> insert into book52
values(3,'DS','vision',250,11);
1
row created.
SQL> insert into book52
values(4,'Design','Thakur',150,13);
1
row created.
SQL> insert into book52
values(5,'Micro','Bill',400,14);
1
row created.
SQL> insert into book52
values(6,'RDBMS','BPB',100,11);
1
row created.
SQL> insert into book52
values(7,'Math','Tech-max',220,15);
1
row created.
SQL>
select * from book52;
1)create or replace pl/sql function to return total no of books
purchased by given department
SQL>
create or replace function f3
2
(deptname varchar2)
3
return number
4 is
5
v_bno number(5);
6 begin
7
select count(book52.bno) into v_bno from department52,book52
8
where department52.dno=book52.dno and
9
department52.dept_name=deptname;
10
return v_bno;
11 END
f3;
12 /
Function
created.
SQL>
DECLARE
2
v_deptname varchar2(10);
3
c number;
4 BEGIN
5
v_deptname:='&v_deptname';
6
c:=f3(v_deptname);
7
dbms_output.put_line('The total no.of books-'||c);
8 END;
9 /
***Output***
Enter
value for v_deptname: Computer
old 5:
v_deptname:='&v_deptname';
new 5:
v_deptname:='Computer';
The
total no.of books-3
PL/SQL
procedure successfully completed.
2)To return total expenditure on
book of given department
SQL>
create or replace function f4
2
(deptname varchar2)
3
return number
4 is
5
v_price number(5);
6 begin
7
select sum(book52.price) into v_price from department52,book52
8
where department52.dno=book52.dno and
9
department52.dept_name=deptname;
10
return v_price;
11 END
f4;
12 /
Function
created.
SQL>
DECLARE
2
v_deptname varchar2(10);
3
c number;
4 BEGIN
5
v_deptname:='&v_deptname';
6
c:=f3(v_deptname);
7
dbms_output.put_line('Total Expenditure is-’||c);
8 END;
9 /
***OUTPUT***
Enter
value for v_deptname: Computer
old 5:
v_deptname:='&v_deptname';
new 5: v_deptname:='Computer';
Total
Expenditure is-550
PL/SQL
procedure successfully completed.
