Question

Suppose a student is taking IS 620 section 1 and HCC 629 section 1 for spring 2019. Use an explicit cursor to print out the titles and prices of textbooks for these two courses.

Sample code to create the tables:

drop table textbook_schedule cascade constraints;

drop table textbook_author cascade constraints;

drop table schedule cascade constraints;

drop table course cascade constraints;

drop table textbook cascade constraints;

drop table author cascade constraints;

drop table teacher cascade constraints;

drop table program cascade constraints;

create table program

(pid int, --- program id

pname varchar(50), --- program name

ptype int, --- program type (1: undergraduate, 2: graduate)

primary key (pid));

insert into program values(1, 'Information Systems', 1);

insert into program values(2, 'Information Systems', 2);

insert into program values(3, 'Human Centered Computing', 2);

create table teacher

(tid int,

tname varchar(50),

primary key (tid)

);

insert into teacher values(1,'Dr. Chen');

insert into teacher values(2, 'Dr. Karabatis');

insert into teacher values(3,'Komlodi');

insert into teacher values(4, 'Kuber');

insert into teacher values(5, 'Dr. Janeja');

insert into teacher values(6, 'Dr. Roy');

create table author

(

aid int,

aname varchar(50),

primary key (aid)

);

insert into author values

(1, 'Joel Murach');

insert into author values

(2, 'Dan Sullivan');

insert into author values

(3, 'Jeff Hoffer');

insert into author values

(4, 'Ramesh Venkataraman');

insert into author values

(5, 'Heikki Topi');

insert into author values

(6,'YVonne Rogers');

insert into author values

(7,'Helen Sharp');

insert into author values

(8,'Jenny Preece');

insert into author values

(9,'Ramez Elmasri');

insert into author values

(10,'Shamkant B. Navathe');

insert into author values

(11,'Mark A. Weiss');

--- add more

create table textbook

(

tbid int, --- text book

title varchar(50), ---- title of book

publisher varchar(50), --- publisher name

edition int, --- edition, e.g., 3 means 3rd edition

quantity int, --- number of copies in store

price number, --- price of the book

primary key (tbid)

);

insert into textbook values

(1, 'Oracle SQL and PL/SQL', 'Murach Press',2,100,50);

insert into textbook values

(2, 'NoSQL for Mere Mortals', 'Addison-Wesley',1,100,40);

insert into textbook values

(3, 'Modern Database Management','Pearson',12,100,225);

insert into textbook values

(4, 'Interaction Design','Wiley',3,30,45);

insert into textbook values

(5, 'Fundamentals of Database Systems', 'Pearson',7,30,153);

insert into textbook values

(6, 'Data Structures and Algorithm Analysis in C++', 'Addison-

Wesley',4,30,140);

create table course

(

cid int, --- course id

cname varchar(50), --- course name

pid int, --- program id

primary key (cid),

foreign key(pid) references program

);

insert into course values(1, 'IS 420: Database Application Development',1);

insert into course values(2, 'IS 410: Introduction to Database Design',1);

insert into course values(3, 'IS 620: Advanced Database Project',2);

insert into course values(4, 'IS 722: Database Integration',2);

insert into course values(5, 'HCC 629: Fundamentals of Human-Centered Computing',3);

insert into course values(6, 'HCC 729: Human-Centered Design',3);

insert into course values(7, 'IS 809: Computational Methods for IS Research',2);

create table schedule

(sid int, --- schedule id

cid int, --- course id

snumber int, --- section number

semester varchar(10), --- spring, fall, summer, winter,

year int, --- year of the class

tid int, --- teach id

num_registered int, --- number of registered students for this class section

primary key (sid),

foreign key (cid) references course,

foreign key(tid) references teacher

);

-- IS 420 scheduled fall 18 by Dr. Karabatis, 2 sections

insert into schedule values

(1,1,1, 'fall',2018,2,30);

insert into schedule values

(2,1,2, 'fall',2018,2,30);

--spring 19 2 sections by Dr. Chen

insert into schedule values

(3,1,1, 'spring',2019,1,30);

insert into schedule values

(4,1,2, 'spring',2019,1,30);

--- IS 620 scheduled for each semester one section

insert into schedule values

(5,3,1, 'fall',2018,1,40);

insert into schedule values

(6,3,1, 'spring',2019,2,40);

-- IS 410 scheduled for each semester

insert into schedule values

(7,2,1,'spring',2019,5,30);

insert into schedule values

(8,2,2,'spring',2019,5,30);

-- HCC 629 scheduled each semester

insert into schedule values

(9,5,1,'fall',2018,4,30);

insert into schedule values

(10,5,1,'spring',2019,3,30);

-- HCC 729 scheduled each year

insert into schedule values

(11,6,1,'fall',2018,4,20);

--- IS 809 scheduled once a year

insert into schedule values

(12,7,1,'fall',2018,6,20);

create table textbook_author

(

aid int, --- author id

tbid int, --- text book id

primary key(aid, tbid),

foreign key(aid) references author,

foreign key(tbid) references textbook

);

insert into textbook_author values

(1, 1);

insert into textbook_author values

(2, 2);

insert into textbook_author values

(3, 3);

insert into textbook_author values

(4, 3);

insert into textbook_author values

(5, 3);

insert into textbook_author values

(6, 4);

insert into textbook_author values

(7, 4);

insert into textbook_author values

(8, 4);

insert into textbook_author values

(9, 5);

insert into textbook_author values

(10, 5);

insert into textbook_author values

(11, 6);

create table textbook_schedule(

sid int, --- schedule id

tbid int, --- text book id

primary key(sid, tbid),

foreign key(sid) references schedule,

foreign key(tbid) references textbook

);

--- first 4 schdule use book 1 & 2

insert into textbook_schedule values(1,1);

insert into textbook_schedule values(1,2);

insert into textbook_schedule values(2,1);

insert into textbook_schedule values(2,2);

insert into textbook_schedule values(3,1);

insert into textbook_schedule values(3,2);

insert into textbook_schedule values(4,1);

insert into textbook_schedule values(4,2);

--- 620 use textbook 5

insert into textbook_schedule values(5,5);

insert into textbook_schedule values(6,5);

-- 410 use textbook 3

insert into textbook_schedule values(7,3);

insert into textbook_schedule values(8,3);

-- 629 use textbook 4

insert into textbook_schedule values(9,4);

insert into textbook_schedule values(10,4);

-- 809 use textbook 6

insert into textbook_schedule values(12,6);

commit;

Answer 1

Answer:

Cursor code:

set serveroutput ON; -- used to enable output on screen

DECLARE
book_title textbook.title%type;
book_price textbook.price%type;
CURSOR cr IS select title, price FROM textbook WHERE tbid IN(SELECT tbid FROM textbook_schedule WHERE sid IN
(select s.sid from schedule s join course c
ON c.cid = s.cid
WHERE c.cname LIKE 'HCC 629%' AND s.semester = 'spring' AND s.year = 2019))
UNION
select title, price FROM textbook
WHERE tbid IN(SELECT tbid FROM textbook_schedule WHERE sid IN
(select s.sid from schedule s join course c ON c.cid = s.cid
WHERE c.cname LIKE 'IS 620%'AND s.semester = 'spring' AND s.year = 2019));

BEGIN
OPEN cr; -- open cursor
LOOP
FETCH cr INTO book_title, book_price; -- everytime loop is executed, the next row is copied into book_title and book_price using FETCH command
EXIT when cr%NOTFOUND; -- implicit cursor is used for exit condition
dbms_output.put_line('Title: ' || book_title || ', Price: ' || book_price);
END LOOP;

CLOSE cr; -- close when work is finished
END;

OUTPUT :

C:Windows\system32\cmd.exe -sqlplus SQL output using normal sql query SQL> select title, price FROM textbook WHERE tbid IN(SELECT tbid FROM textbook_s chedule WHERE sid IN 2 3 4 AND S.year = 2019)) 5 6 7 8 (select s.sid from schedule s join course c ON C. cid= s.cid WHERE C.cname LIKE 'HCc 629%" AND s.semester = 'spring' UNION select title, price FROM textbook WHERE tbid IŃ(SELECT tbid FROM textbook_schedule WHERE sid IN (select s.sid from schedule s join course c ON c.cid = s.cid WHERE C. cname LIKE 'Is 620%'AND Ss.semester = 'spring' AND s.year 2019)); TITLE PRICE Fundamentals of Database Systems Interaction Design 153 45 SQL

X C:Windows\system32\cmd.exe-sqlplus SQL> -output using cursor SQL set serveroutput ON; SQL SQL> DECLARE 2 3 4 FROM textbook_schedule WHERE sidIN 5 6 7 AND S.year = 2019)) 8 9 10 11 12 2019)); 13 14 15 16 17 p is executed, the next row is copied into book_title and book_price using FETCH command 18 ed for exit condition 19 |ook_price); 20 21 22 23 24 Title: Fundamentals of Database Sys tems , Price: 153 Title: Interaction Design, Price: 45 textbook.title%type; textbook.price%type; book_title book_price CURSOR cr IS select title', price FRÓM textbook WHERE tbid IN(SELECT tbid| (select s.sid from schedule s join course c ON C. cid s.cid WHERE C.cname LIKE 'HCc 629%" AND s.semester = 'spring' UNION select title, price FROM textbook WHERE tbid IN(SELECT tbid FROM textbook_schedule WHERE sid IN (select s.sid from schedule s join course c ON c.cid = s.cid WHERE C. cname LIKE 'Is 620%'AND Ss.semester = 'spring' AND s.year = BEGIN OPEN cr; open cursor LOOP everytime loo FETCH cr INTO book_title, book_price; implicit cursor is us EXIT when cr%NOTFOUND; dbms_output.put_line('Title: ' || book_title || ', Price: ' || b| END LOOP; close when work is finished CLOSE cr; END; PL/SQL procedure successfully completed. SOL>

NOTE:

If you are satisfied with my answer please do upvote and if you have any kind of doubts please post in comment section. i'll surely help you there.
Thank You:)