Question

Please write an anonymous PL/SQL program which uses an implicit cursor to print out the score and rank of team 'Mad Scientists' in the competition 'Science Olympiad Regional Baltimore'. Please handle exceptions.
Problem 4: [15 points] Please write an anonymous PL/SQL program to print out the names of students and their school names for the team that won the first place (rank=1) in Science Olympiad Maryland State (name of a competition).

drop table school cascade constraints;
drop table student cascade constraints;
drop table team cascade constraints;
drop table competition cascade constraints;
drop table team_result cascade constraints;

create table school
(sid int, -- school id
sname varchar(50), -- school name
primary key(sid));

insert into school values(1, 'Catonsville High');
insert into school values(2, 'MarriottsRidge High');

create table team
(tid int, --- team id
tname varchar(50), --- team name
primary key(tid)
);

insert into team values(1,'Science Genius');
insert into team values(2,'Super robot');
insert into team values(3,'Mad Scientists');
insert into team values(4,'Little Eisenstein');

create table student
(stid int, -- student id
stname varchar(50), --- student name
sid int, --- school id
tid int, --- team id
grade int, --- grade
primary key(stid),
foreign key(sid) references school,
foreign key(tid) references team);

insert into student values(1, 'Anna', 1, 1, 11);
insert into student values(2, 'Erica', 1, 1, 12);
insert into student values(3, 'David', 1, 1, 11);
insert into student values(4, 'Ravi', 1, 2, 11);
insert into student values(5, 'Ali', 1, 2, 12);
insert into student values(6, 'Cathy', 1, 2, 11);
insert into student values(7, 'Grace', 2, 3, 11);
insert into student values(8, 'Megan', 2, 3, 12);
insert into student values(9, 'Jeff', 2, 3, 11);
insert into student values(10, 'Ryan', 2, 4, 11);
insert into student values(11, 'Ron', 2, 4, 12);
insert into student values(12, 'Ella', 2, 4, 11);

create table competition
(cid int, --- competition id
cname varchar(50), --- competition name
cdate date, --- competition date
primary key (cid));

insert into competition values (1, 'Science Olympiad Regional Baltimore',
date '2020-2-28');
insert into competition values (2, 'Science Olympiad Maryland State',
date '2020-4-1');

create table team_result
(tid int, --- team id
cid int, --- competition id
score number, --- score of the team in the competition
rank int, --- teams ranking in the competition, smaller the better
primary key(tid,cid),
foreign key(tid) references team,
foreign key(cid) references competition);
insert into team_result values(1, 1, 95, 2);
insert into team_result values(2, 1, 90, 3);
insert into team_result values(3, 1, 89, 4);
insert into team_result values(4, 1, 99, 1);
insert into team_result values(1, 2, 98, 2);
insert into team_result values(4, 2, 100, 1);  

Answer 1

DECLARE

l_score NUMBER;

l_rank INT;

BEGIN

//Implicit Cursor Is Created Internally By The System To Store DML Result

Select tr.score, tr.rank into l_score, l_rank from team_result tr inner join team t on (tr.tid=t.tid) inner join competition c on (c.cid=tr.cid) where c.cname='Science Olympiad Regional Baltimore' and t.tname='Mad Scientists';

dbms_output.put_line('Score is '||l_score||' and Rank is '||l_rank);

EXCEPTION

// Exception Handling By Printing Message

WHEN OTHERS THEN

dbms_output.put_line('Error Occured');

END;

/

DECLARE

BEGIN

//We use FOR loop to print the data in each returned record of query

FOR i in ( Select st.stname, s.sname from student st inner join school s on (st.sid=s.sid) inner join team t on (st.tid=t.tid) inner join team_result tr on (tr.tid=t.tid) inner join competition c on (tr.cid=c.cid) where c.cname = 'Science Olympiad Maryland State' and tr.rank=1)

LOOP

dbms_output.put_line(i.stname||' from '||i.sname);

END LOOP;

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line('Error Occured');

END;

/