· 5 years ago · Nov 11, 2020, 06:18 PM
1/* Interaction 1 */
2/* Query 1 */
3/* List of book(s) in the Adventure genre. */
4SELECT P.title
5FROM Publication P, Books B
6WHERE P.context = 'Adventure' AND P.pid=B.pid;
7
8/* Interaction 2 */
9/* Query 2 */
10/* Find the PersonID and author(s) name that are users. */
11SELECT A.PersonID, A.name
12FROM Author A
13WHERE A.name IN
14 (Select U.name
15 FROM Lib_User U
16 WHERE U.name = A.name AND U.PersonID = A.PersonID)
17ORDER BY A.PersonID;
18
19/* Interaction 3 */
20/* Query 3 */
21/* Find the publications and Publishing Companies written and ordered by Author name. */
22SELECT A.name, A.Publish_Company, P.title
23FROM Author A, Publication P
24WHERE A.AuthorID = P.AuthorID
25ORDER BY A.name, P.title;
26
27/* Interaction 4 */
28/* Query 4 */
29/* Find the usernames, their email address, */
30/* and clearence levels ordered by clearence levels. */
31SELECT U.clearance, P.name, P.emailID
32FROM Lib_User U, Person P
33WHERE P.PersonID = U.PersonID
34ORDER BY U.clearance;
35
36/* Interaction 5 */
37/* Query 5/ Stored Procedure 1 */
38/* Displays the Count of the Publications that X has checked out. */
39/* X is to be an input paramter for a given Person ID. */
40CREATE or replace PROCEDURE HISTC (X in INTEGER) AS
41 cHist INTEGER;
42
43BEGIN
44 SELECT COUNT(*) INTO cHist
45 FROM History H
46 WHERE H.PersonID = X;
47
48 DBMS_OUTPUT.PUT_LINE('COUNT = ' || cHist);
49END;
50
51EXEC HISTC (1000);
52
53/* Interaction 6 */
54/* Query 6/ Stored Procedure 2 */
55/* Displays the count of person(s) that are not users. */
56CREATE or replace PROCEDURE PCOUNT AS
57 personCount INTEGER;
58
59BEGIN
60 SELECT COUNT(P.name) INTO personCount
61 FROM Person P
62 WHERE P.name NOT IN
63 (SELECT U.name
64 FROM Lib_User U
65 WHERE U.name = P.name);
66
67 DBMS_OUTPUT.PUT_LINE('COUNT = ' || personCount);
68END;
69
70EXEC PCOUNT;
71
72/* Interaction 7 */
73/* Query 7/ Stored Procedure 3 */
74/* Find the Publications that reference themselves by title. */
75CREATE or replace PROCEDURE RITSELF AS
76 t1 CHAR (30);
77 t2 CHAR (30);
78 t3 CHAR (30);
79 t4 CHAR (30);
80 t5 CHAR (30);
81
82 CURSOR pub1 IS
83 SELECT B.title
84 FROM Books B
85 WHERE B.RefID = B.Pid;
86
87 CURSOR pub2 IS
88 SELECT C.title
89 FROM Conference C
90 WHERE C.RefID = C.Pid;
91
92 CURSOR pub3 IS
93 SELECT J.title
94 FROM Journals J
95 WHERE J.RefID = J.Pid;
96
97 CURSOR pub4 IS
98 SELECT M.title
99 FROM Magazine M
100 WHERE M.RefID = M.Pid;
101
102 CURSOR pub5 IS
103 SELECT T.title
104 FROM Transaction T
105 WHERE T.RefID = T.Pid;
106
107BEGIN
108 OPEN pub1;
109 LOOP
110 FETCH pub1 INTO t1;
111 IF pub1%NOTFOUND THEN EXIT; END IF;
112 DBMS_OUTPUT.PUT_LINE(t1);
113 END LOOP;
114 CLOSE pub1;
115
116 OPEN pub2;
117 LOOP
118 FETCH pub2 INTO t2;
119 IF pub2%NOTFOUND THEN EXIT; END IF;
120 DBMS_OUTPUT.PUT_LINE(t2);
121 END LOOP;
122 CLOSE pub2;
123
124 OPEN pub3;
125 LOOP
126 FETCH pub3 INTO t3;
127 IF pub3%NOTFOUND THEN EXIT; END IF;
128 DBMS_OUTPUT.PUT_LINE(t3);
129 END LOOP;
130 CLOSE pub3;
131
132 OPEN pub4;
133 LOOP
134 FETCH pub4 INTO t4;
135 IF pub4%NOTFOUND THEN EXIT; END IF;
136 DBMS_OUTPUT.PUT_LINE(t4);
137 END LOOP;
138 CLOSE pub4;
139
140 OPEN pub5;
141 LOOP
142 FETCH pub5 INTO t5;
143 IF pub5%NOTFOUND THEN EXIT; END IF;
144 DBMS_OUTPUT.PUT_LINE(t5);
145 END LOOP;
146 CLOSE pub5;
147END;
148
149EXEC RITSELF;
150
151/* Interaction 8 */
152/* Query 8/ Stored Procedure 4 */
153/* Search the Library for the Specified Author */
154/* X is the given Author name */
155/* Outputs Author and Title */
156CREATE or replace PROCEDURE SEARCHAUTHOR (X in CHAR) AS
157 title CHAR(30);
158
159 CURSOR name IS
160 SELECT P.title
161 FROM Publication P, Author A
162 WHERE X = A.name AND A.AuthorID = P.AuthorID
163 ORDER BY P.title;
164
165BEGIN
166 OPEN name;
167 LOOP
168 FETCH name INTO title;
169 IF name%NOTFOUND THEN EXIT; END IF;
170 DBMS_OUTPUT.PUT_LINE('Author: ' || X || ' Title: ' ||title);
171 END LOOP;
172 CLOSE name;
173END;
174
175EXEC SEARCHAUTHOR ('Chad Caddy');
176
177/* Interaction 9 */
178/* Query 9/ Stored Procedure 5 */
179/* Search the Library for the Specified Title */
180/* X is the given publication title */
181/* Outputs Title and Author */
182CREATE or replace PROCEDURE SEARCHTITLE (X in CHAR) AS
183 name CHAR(30);
184
185 CURSOR author IS
186 SELECT A.name
187 FROM Publication P, Author A
188 WHERE X = P.title AND A.AuthorID = P.AuthorID;
189
190BEGIN
191 OPEN author;
192 LOOP
193 FETCH author INTO name;
194 IF author%NOTFOUND THEN EXIT; END IF;
195 DBMS_OUTPUT.PUT_LINE('Title: ' || X || ' Author: ' ||name);
196 END LOOP;
197 CLOSE author;
198END;
199
200EXEC SEARCHTITLE ('Book 1');
201
202/* Interaction 10 */
203/* Stored Procedure 6 */
204/* Create a procedure to add an user to the user table.*/
205CREATE or replace PROCEDURE ADDUSER (person INTEGER, uname char, email char,
206 usern char, clearanc char, pw char) AS
207BEGIN
208 INSERT INTO Lib_User (PersonID, name, emailID, username, clearance, password)
209 VALUES (person, uname, email, usern, clearanc, pw);
210 DBMS_OUTPUT.PUT_LINE(usern || ' has been added to the user database.');
211END ADDUSER;
212
213EXEC ADDUSER(1009, 'Leo Lang', 'LeoLang@hotmail.com', 'leo', 'all', '150');
214
215/* Interaction 11 */
216/* Stored Procedure 7 */
217/* Create a procedure to remove an user from the user table.*/
218CREATE or replace PROCEDURE REMOVEUSER (Person INTEGER) AS
219BEGIN
220 DELETE FROM Lib_User WHERE personID = Person;
221 DBMS_OUTPUT.PUT_LINE(Person || ' has been deleted from the user database.');
222END REMOVEUSER;
223
224EXEC REMOVEUSER(1009);
225
226/* Interaction 12 */
227/* Query 10/ Stored Procedure 8 */
228CREATE or replace PROCEDURE PUBLISH_DATE (PubDate IN CHAR) AS
229Ptitle CHAR(20);
230Pid Integer;
231pub_date CHAR(10);
232
233 CURSOR publish IS
234 SELECT P.title, P.Pid
235 FROM Publication P
236 WHERE P.Pdate= PubDate;
237
238BEGIN
239 DBMS_OUTPUT.PUT_LINE('Publication year '||PubDate );
240 DBMS_OUTPUT.PUT_LINE(' ');
241 DBMS_OUTPUT.PUT_LINE('Publication ID Publication title' );
242
243 OPEN publish;
244
245 LOOP
246 FETCH publish INTO Ptitle, Pid;
247 IF publish%NOTFOUND THEN EXIT; END IF;
248 DBMS_OUTPUT.PUT_LINE(Pid || ' ' || Ptitle);
249 END LOOP;
250
251 CLOSE publish;
252END PUBLISH_DATE;
253
254EXEC PUBLISH_DATE('01/20/1985');
255
256/* Interaction 13 */
257/* Query 11/ Stored Procedure 9 */
258/* Display the author that had a second author associated with him/her. */
259/* Display author name and second author name */
260CREATE or replace PROCEDURE SECONDAUTHOR AS
261 aName CHAR(30);
262 sName CHAR(30);
263
264 CURSOR secondA IS
265 SELECT A.name, A.Co_Author
266 FROM Author A
267 WHERE A.Co_Author != ' ';
268
269BEGIN
270 OPEN secondA;
271 LOOP
272 FETCH secondA INTO aName, sName;
273 IF secondA%NOTFOUND THEN EXIT; END IF;
274 DBMS_OUTPUT.PUT_LINE('Author: ' || aName);
275 DBMS_OUTPUT.PUT_LINE('Secondary Author: ' ||sName);
276 END LOOP;
277
278 CLOSE secondA;
279END;
280
281EXEC SECONDAUTHOR;
282
283/* Interaction 14 */
284/* Query 12/ Stored Procedure 10 */
285/* Display the person(s) that have checked an item out on November 1st, 2020. */
286/* Order by name and display the publication as well. */
287CREATE or replace PROCEDURE HISTCHECK AS
288 pName CHAR(30);
289 title CHAR(20);
290
291 CURSOR hCheck IS
292 SELECT P.name, Pb.title
293 FROM Person P, Publication Pb, History H
294 WHERE Pb.Pid = H.Pid AND
295 P.PersonID = H.PersonID AND
296 H.Hdate = '11/01/2020'
297 ORDER BY P.name;
298
299BEGIN
300 OPEN hCheck;
301 LOOP
302 FETCH hCheck INTO pName, title;
303 IF hCheck%NOTFOUND THEN EXIT; END IF;
304 DBMS_OUTPUT.PUT_LINE('Person: ' || pName || 'Title: ' ||title);
305 END LOOP;
306
307 CLOSE hCheck;
308END;
309
310EXEC HISTCHECK;
311
312/* Interaction 15 */
313/* Query 13 */
314/* Find the email address(s) and username(s) that have full access to the library database. */
315/* Order by clearance. */
316SELECT U.clearance, U.emailID, U.username
317FROM Lib_User U
318WHERE U.clearance = 'All'
319ORDER BY U.clearance;
320
321/* Interaction 16 */
322/* Query 14 */
323/* Find the transactions that are less than 100 pages and have been checked out. */
324SELECT T.Title, T.PgCount
325FROM Transaction T
326WHERE T.PgCount < 100 AND EXISTS (
327 SELECT H.Pid
328 FROM History H
329 WHERE H.Pid = T.Pid);
330
331/* Interaction 17 */
332/* Stored Procedure 11 */
333/* Create a procedure to add history to the history table.*/
334CREATE or replace PROCEDURE ADDHISTORY (person INTEGER, ppid char, hidate char,
335 hitime char) AS
336BEGIN
337 INSERT INTO History (PersonID, Pid, Hdate, Htime)
338 VALUES (person, ppid, hidate, hitime);
339 DBMS_OUTPUT.PUT_LINE('History has been updated.');
340END ADDHISTORY;
341
342EXEC ADDHISTORY(1004, 121, '11/09/2020', '19:15');
343
344/* Interaction 18 */
345/* Stored Procedure 12 */
346/* Create a procedure to remove history from the history table.*/
347CREATE or replace PROCEDURE REMOVEHISTORY (hidate char) AS
348BEGIN
349 DELETE FROM History WHERE HDate = hidate;
350 DBMS_OUTPUT.PUT_LINE('History has been updated.');
351END REMOVEHISTORY;
352
353EXEC REMOVEHISTORY('11/09/2020');
354
355/* Interaction 19 */
356/* Query 15 */
357/* Find the user name and passwords used to check out items on October 31st. */
358SELECT U.username, U.password
359FROM History H, Lib_User U
360WHERE H.PersonID = U.PersonID AND
361 H.Hdate = '10/31/2020'
362ORDER BY U.username;
363
364/* Interaction 20 */
365/* Stored Procedure 13 */
366/* Find the publications that are early issues/editions. */
367/* IE. Less than 10th issue/edition. Order by Title. */
368CREATE or replace PROCEDURE FINDPUB AS
369 title CHAR(20);
370 issue INTEGER;
371
372 CURSOR pubfind1 IS
373 SELECT J.title, J.editions
374 FROM Journals J
375 WHERE J.editions < 10
376 ORDER BY J.title;
377
378 CURSOR pubfind2 IS
379 SELECT M.title, M.issue
380 FROM Magazine M
381 WHERE M.issue < 10
382 ORDER BY M.title;
383
384BEGIN
385 DBMS_OUTPUT.PUT_LINE('Title Issue/Edition');
386 OPEN pubfind1;
387 LOOP
388 FETCH pubfind1 INTO title, issue;
389 IF pubfind1%NOTFOUND THEN EXIT; END IF;
390 DBMS_OUTPUT.PUT_LINE(Title || ' ' || issue);
391 END LOOP;
392 CLOSE pubfind1;
393
394 OPEN pubfind2;
395 LOOP
396 FETCH pubfind2 INTO title, issue;
397 IF pubfind2%NOTFOUND THEN EXIT; END IF;
398 DBMS_OUTPUT.PUT_LINE(Title || ' ' || issue);
399 END LOOP;
400 CLOSE pubfind2;
401END;
402
403EXEC FINDPUB;