RDBMS SOLUTION
ASSIGNMENT 2
SET A Q.1 SOLUTION
Write a function to accept lab number from user as parameter. ” if user
enters invalid lab number then raise an user defined exception “Invalid_Lab_No” otherwise display the student details of the same lab.
Source code:
/* set b Q 1
in this we have to first learn select command and ritriving the data from 2 tables using 3rd one
we will create 3 tables in this question
1 for student
1 for lab
and the last one for combining these 2 using foreign key
then will insert the records and then write the query */
/* creating tables*/
create table student
(
rollno number not null primary key,
sname varchar2(40),
sclass varchar2(40),
mobileno number
)
create table lab
(
labno number not null primary key,
labname varchar2(40),
lcapacity number,
equipnent varchar2(40)
)
/*in student table the column mobile number will contain number,we have by mistakenly added varchar which is now corrected already*/
/*now a table which will be 3rd table and connect student and lab */
create table s_l
(
std_id number references student(rollno),
lab_id number references lab(labno)
)
/* now for inserting records,we are going to create a pl/sql block*/
/*block for inserting student record*/
declare
rno number:=:rno;
sname varchar2(40):=:sname;
sclass varchar2(40):=:sclass;
mob number:=:mobno;
begin
insert into student values(rno,sname,sclass,mob);
end;
/* now inserting lab records in same way*/
declare
lno number:=:lno;
lname varchar2(40):=:sname;
cap number:=:cap;
equ varchar2(40):=:equ;
begin
insert into lab values(lno,lname,cap,equ);
end;
/*now its time to add records in 3rd table in such a way that
student 1 is in lab 3
student 2 is in lab 1
student 3 is in lab 2*/
insert into s_l values('1','3');
insert into s_l values('2','1');
insert into s_l values('3','2');
/*display record*/
select * from student,lab,s_l
/*PL/SQL BLOCK*/
/* logic here is to make an exception,make variables,compare records and lastly display them*/
declare
std_rno student.rollno%type;
std_name student.sname%type;
lab_lno lab.labno%type;
lab_name lab.labname%type;
lab_rec lab%rowtype;
sl_rec s_l%rowtype;
/*the above variables are connected to tables using %type and %rowtype*/
n number:=:labno; /*to input lab number*/
invalid_lab_no exception; /*exception name*/
begin
select * into lab_rec from lab where labno=n;/*this query will take records from table lab where the lab number is the inputted number*/
if(lab_rec.labno=n) then
select * into sl_rec from s_l where lab_id=n;
select student.rollno , student.sname , lab.labno , lab.labname
into std_rno,std_name,lab_lno,lab_name
from student,lab
where student.rollno = sl_rec.std_id and lab.labno = sl_rec.lab_id and sl_rec.lab_id = n;
dbms_output.put_line('roll no name lab no lab name');
dbms_output.put_line(std_rno||' '||std_name||' '||lab_lno||' '||lab_name);
else
raise invalid_lab_no;
end if;
exception
when invalid_lab_no then
dbms_output.put_line('not valid lab number');
end;
/*source code in description*/
0 Comments