Waiting for answer This question has not been answered yet. You can hire a professional tutor to get the answer.

QUESTION

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...

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;

Show more
LEARN MORE EFFECTIVELY AND GET BETTER GRADES!
Ask a Question