Problem 1: Write an anonymous PL/SQL program to compute the sum of 2, 4, 6, 8, 1
ID: 3908787 • Letter: P
Question
Problem 1: Write an anonymous PL/SQL program to compute the sum of 2, 4, 6, 8, 10. You must use a loop. Tip: consider how to update your loop variable.
Problem 2: Please write an anonymous PL/SQL program which uses an implicit cursor to print out the start time of the annual physical for Ella (Ella is patient name and annual physical is reason for visit). Please do not include canceled appointments. Please handle exceptions. You can only use conditions listed above (e.g., do not manually look up pid or aid).
Problem 3: Please write an anonymous PL/SQL program which uses an explicit cursor to print out the start time of appointments and names of patients Dr. Adam needs to see on Feb 1 2018. Please exclude canceled appointments. You can only use conditions listed above (e.g., do not manually look up did).
--- Sample code to create the tables
drop table appointment_service cascade constraints;
drop table appointment cascade constraints;
drop table service cascade constraints;
drop table patient cascade constraints;
drop table doctor cascade constraints;
create table doctor
(did int,
dname varchar(50),
primary key(did));
insert into doctor values(1,'Dr. Rao');
insert into doctor values(2,'Dr. Adam');
insert into doctor values(3,'Dr. Smith');
create table patient
(pid int,
pname varchar(50),
pphone varchar(20),
primary key(pid));
insert into patient values(1,'Susan', '410-456-1122');
-- son of susan
insert into patient values(2,'Nathan','410-456-1122');
insert into patient values(3,'Ella', '410-456-8876');
insert into patient values(4,'Carl', '410-456-8876');
create table service
(sid int,
sdecription varchar(200),
primary key(sid));
insert into service values(1,'office visit');
insert into service values(2,'flu shot');
insert into service values(3,'physical exam');
create table appointment
(aid int,
pid int,
did int,
reason_for_visit varchar(200),
start_time timestamp, end_time timestamp,
status int, -- 1 booked, 2 happended, 3 canceled
primary key(aid),
foreign key (pid) references patient,
foreign key (did) references doctor);
-- done
insert into appointment values(1,1,1,'flu and low fever', timestamp '2018-2-1 9:00:00.00',
timestamp '2018-2-1 9:30:00.00',2);
-- done
insert into appointment values(2,2,1,'flu shot', timestamp '2018-2-1 9:30:00.00',
timestamp '2018-2-1 9:40:00.00',2);
-- canceled
insert into appointment values(3,3,2,'annual physical', timestamp '2018-2-1 9:00:00.00',
timestamp '2018-2-1 9:30:00.00',3);
-- booked
insert into appointment values(4,4,2,'annual physical', timestamp '2018-2-1 16:00:00.00',
timestamp '2018-2-1 16:30:00.00',1);
-- booked
insert into appointment values(5,1,1,'follow up', timestamp '2018-2-3 12:00:00.00',
timestamp '2018-2-3 12:30:00.00',1);
-- booked
insert into appointment values(6,3,2,'annual physical', timestamp '2018-2-3 9:00:00.00',
timestamp '2018-2-3 9:30:00.00',1);
insert into appointment values(7,3,2,'flu like symptom', timestamp '2018-2-3 11:00:00.00',
timestamp '2018-2-3 11:30:00.00',1);
insert into appointment values(8,2,1,'follow up', timestamp '2018-2-3 09:00:00.00',
timestamp '2018-2-3 12:00:00.00',1);
insert into appointment values(9,2,1,'follow up', timestamp '2018-2-3 12:30:00.00',
timestamp '2018-2-3 16:30:00.00',1);
create table appointment_service
(
aid int,
sid int,
primary key(aid, sid),
foreign key(aid) references appointment,
foreign key(sid) references service
);
insert into appointment_service values(1, 1);
insert into appointment_service values(2, 2);
insert into appointment_service values(4, 2);
insert into appointment_service values(4, 3);
insert into appointment_service values(5, 1);
insert into appointment_service values(6, 2);
insert into appointment_service values(6, 3);
insert into appointment_service values(7, 1);
insert into appointment_service values(8, 1);
insert into appointment_service values(9, 1);
commit;
Explanation / Answer
Problem 1:
Declare
total integer;
i integer;
Begin
i := 2;
total := 0;
loop
total := total + i;
i := i + 2;
exit when i > 10;
End loop;
dbms_output.put_line('Total is ' || total);
End;
Problem 2:
Declare
s_time timestamp;
Begin
select start_time into s_time from appointment
where reason_for_visit = "annual physical" and pid IN
(select pid from patient where pname = "Ella") AND status <> 3;
dbms_output.put_line('Start time is: ' || s_time);
Exception
when no_data_found then
dbms_output.put_line('No category found');
when too_many_rows then
dbms_output.put_line('Too many categories found');
End;
Problem 3:
select A.start_time, P.pname from appointment as A, patient as P
where A.pid = P.pid and A.status <> 3 and A.did IN
(select did from doctor where dname = "Dr. Adam")
group by a.aid