Assignment on Park Lane College Leeds.

Park Lane College Leeds
[2019]
PARK LANE COLLEGE
[]

Table of Contents
Question A).........................................................................................................................................................5
Question b).........................................................................................................................................................6
Question c).........................................................................................................................................................7
Quest d)..............................................................................................................................................................9
Question e).......................................................................................................................................................10
Question f)........................................................................................................................................................11
Question g)(Total fine).....................................................................................................................................12
Question h).......................................................................................................................................................14
References........................................................................................................................................................15
2 | P a g e

Create table campus(
ID int,
Name varchar2(50));
Here we are creating table Campus with various datatypes.
insert into campus values(1,'Technology');
insert into campus values(2,'Park Lane');
insert into campus values(3,'Print Works');
insert into campus values(4, 'Keighley');
Here we have used insert query to insert records in table Campus.
3 | P a g e

Create table resources(
ID int,
Name varchar2(50));
Here we have created the table resources.
insert into resources values(1,'Digital record');
insert into resources values(2,'Ebooks');
insert into resources values(3,'Novel');
insert into resources values(4, 'Other resources');
Here we have insert records in table.
Create table Campus_resources(
CampusID int,
ResourceID int);
Here we have created the table Campus_resources.
insert into Campus_resources values(1,1);
insert into Campus_resources values(1,2);
insert into Campus_resources values(2,3);
insert into Campus_resources values(3, 4);
insert into Campus_resources values(4, 1);
insert into Campus_resources values(4, 3);
Here we have insert ‘re odds in table Campus_resource by using above query.
Create table Campus_Fine(
CampusID int,
ResourceID int,
fine decimal);
By using above query we have created table Campus_Fine.
insert into Campus_Fine values(1,1,100);
insert into Campus_Fine values(1,2,150);
insert into Campus_Fine values(2,3,200);
insert into Campus_Fine values(3, 4,120);
insert into Campus_Fine values(4, 1,300);
insert into Campus_Fine values(4, 3,200);
4 | P a g e

Here we have inserted records in table Campus_Fine.
Question A)
Here we are displaying the complete records in Book table.
5 | P a g e

Here we are displaying all the records from the table BORROWER.
Question b)
select * from BOOK where CATEGORY='SCIFI';
Here we are displaying the records from the table BOOK where Category is SCIFI.
6 | P a g e

Question c)
Here we are displaying the total records of the table BOOK_COPY.
7 | P a g e

Quest d)
select BOOKING_DATE,BOOK_COPY,COPY_ID,BORROWER_ID,STATUS,EXP_RET_DATE from
rental,BOOK_COPY where rental.BOOK_ID=BOOK_COPY.BOOK_ID and STATUS='RENTED'
and EXP_RET_DATE ='17-AUG-19';
Here we have displayed the records from the table rental,BOOK_COPY whose rental.BOOK_ID is
BOOK_COPY.BOOK_ID and status is Rented and expected return date is 17 August 19.
8 | P a g e

9 | P a g e

Question e)
Select CAMPUSID,NAME ,RESOURCEID from Campus_resources,campus where
ID=Campus_resources.ResourceID ;
Here we are displaying records by using select command CAMPUSID, NAME, RESOURCEID from the table
Campus_resources,campus whose ID is Campus_resources. ResourceID.
10 | P a g e

Question f)
select CAMPUSID,NAME,FINE from Campus_Fine,resources where resources.ID=ResourceID ;
Here we have displayed only thos4pe records by using select query such as CAMPUSID, NAME , FINE
form the table
Campus_Fine,resources whose resources.ID is ResourceID.
11 | P a g e

Question g)(Total fine)
select CampusID,Sum(Fine) from Campus_Fine,campus where CampusID =ID group by CAMPUSID;
Here we are showing only those records from the table Campus_Fine,campus whose CampusID is ID And
the records are grouped depending on CAMPUSID by using group by clause.
12 | P a g e

Question h)
FUNCTION Book_availibity(BookNonumber, borrowID number) return number is
CURSOR Cursor_Bookcopy IS
select STATUS , copy_id from BOOK_COPY ;
CheckAvailable number:=0;
ST BOOK_COPY.STATUS %TYPE := '';
copyid BOOK_COPY.copy_id%TYPE := '';
BEGIN
OPEN Cursor_Bookcopy;
LOOP
FETCH Cu Cursor_Bookcopy into ST,copyid ;
EXIT WHEN Cur_Bookcopy%notfound;
if ST='AVAILABLE' then
dbms_output.put_line(ST || ' ' || copyid );
INSERT INTO rental VALUES (sysdate , copyid , borrowID, BookNo, null,
sysdate+13);
Update BOOK_COPY set status='RENTED' where BOOK_ID=BookNo and
copy_id=copyid ;
CheckAvailable:=1;
13 | P a g e

end if ;
END LOOP;
if CheckAvailable <1 then
dbms_output.put_line(Sorry not available book id : ' || BookNo);
end if;
CLOSE Cursor_Bookcopy;
end;
END returnBookID ;
Here we have created the function Book_availibity and we have created the cursor Cursor_Bookcopy this cursor
get the records whose status is available. If the record is available then insert the record into rental and update the
status as rented.
References
Guru99,.2017,.Oracle.PL/SQL.Package.Type,.Specification,.Body.[Example],.available.on.https://www.guru99.com/
packages-pl-sql.html,.access.on.[8/17/2019].
javaTpoint,.2017,.oracle.function,.available.on.https://www.javatpoint.com/oracle-function.access.on.[8/17/2019].
14 | P a g e