V8SqNG0B

1DBMS LAB EXTERNAL ©MR.STARK
2DBMS LAB EXTERNAL
31. Suppose a movie_studio has several film crews. The crews might be designated by a
4given studio as crew1, crew2, and so on. However, other studios might use the same
5designations for crews, so the attribute crew_number is not a key for crews.
6Movie_studio holds the information like name, branch and several locations. Each
7crew holds information like sector, and strength.
8a. Establish the database by normalising up to 3NF and considering all schema level
9constraints.
10b. Write SQL insertion query to insert few tuples to all the relations.
11c. List all movies studios which are not used a single crews.
12d. Retrieve the movie_studio which uses highest strength crew.
13e. Write a before insert trigger to check maximum number of crews to any studio is
14limited to 5.
15f. Write a procedure retrieve all crews used by specific studio.
16a)
17create table movie
18(
19name varchar(10),
20branch varchar(10),
21constraint pki1 primary key(name)
22);
23create table crew
24(
25crew_no number(10),
26name varchar(10),
27strength number(10),
28sector varchar(20),
29constraint pki2 primary key(name,crew_no),
30constraint fki1 foreign key(name) references movie(name)
31);
32create table locations
33(
34DBMS LAB EXTERNAL ©MR.STARK
35DBMS LAB EXTERNAL
36name varchar(10),
37location varchar(20),
38constraint pki3 primary key(name,location)
39);
40b) insert into movie values('&name','&branch');
41insert into movie values('pixar','blore');
42insert into movie values('warnerbro','pune');
43insert into movie values('redchill','goa');
44insert into movie values('legendary','usa');
45insert into crew values(&crew_no,'&name',&strength,'&sector');
46insert into crew values(11111,'pixar',14,'thrill');
47insert into crew values(11112,'pixar',11,'sijd');
48insert into crew values(11113,'pixar',6,'sakjs');
49insert into crew values(11114,'pixar',2,'sawe');
50insert into crew values(11115,'pixar',12,'ssesd');
51insert into crew values(11115,'warnerbro',4,'ssesd');
52insert into crew values(11112,'legendary',2,'thrill');
53insert into crew values(11116,'pixar',11,'ssessd');
54insert into crew values(11117,'pixar',11,'ssessd');
55insert into locations values('&name','&location');
56insert into locations values('pixar','pune');
57insert into locations values('pixar','goa');
58insert into locations values('legendary','blore');
59c)
60select name
61from movie
62where name not in (select name
63 from crew);
64DBMS LAB EXTERNAL ©MR.STARK
65DBMS LAB EXTERNAL
66d)
67select name
68from crew
69where strength >=all(select strength
70 from crew);
71select name
72from crew c
73where c.strength in(select max(strength) from crew);
74e)
75create or replace trigger max_crew
76before insert on crew
77for each row
78declare
79 cnt number;
80begin
81select count(*) into cnt from crew
82 where name=:NEW.name;
83if(cnt>5) then
84raise_application_error(-20009,'MAX CREW LIMIT REACHED');
85end if;
86end;
87/
88f)
89create or replace procedure display_crew(sname varchar)
90is
91X crew%rowtype;
92cursor c is select c1.* from crew c1,movie m
93 where m.name=sname and c1.name=m.name;
94begin
95DBMS LAB EXTERNAL ©MR.STARK
96DBMS LAB EXTERNAL
97for X in c loop
98sys.dbms_output.put_line(X.crew_no||' '||X.strength||' '||X.sector);
99end loop;
100end;
101/
1022. The production company is organised into different studios. We store each studio’s
103name, branch and location; every studio must own at least one movie. We store each
104movie’s title, censor_number and year of production. star may act in any number of
105movies and we store each actors name and address.
106a. Establish the database by normalising up to 3NF and considering all schema level
107constraints.
108b. Write SQL insertion query to insert few tuples to all the relations.
109c. List all the studios of the movie “xyz”.
110d. List all the actors, acted in a movie “xyz”.
111e. Write a procedure to list all movies produced during the specific year.
112f. Write a deletion trigger, does not allow to deleting the current year movies.
113a)
114create table studio
115(
116st_name varchar(20),
117branch varchar(20),
118location varchar(20),
119constraint pk22 primary key(st_name));
120create table movie1
121(
122sensor_no varchar(20),
123title varchar(20),
124year number(5),
125constraint pk23 primary key(sensor_no)
126);
127create table star
128DBMS LAB EXTERNAL ©MR.STARK
129DBMS LAB EXTERNAL
130(
131star_name varchar(20),
132address varchar(20),
133constraint pk24 primary key(star_name)
134);
135create table owns
136(
137st_name varchar(20),
138sensor_no varchar(20),
139constraint pk25 primary key(st_name,sensor_no),
140constraint fk21 foreign key(st_name) references studio(st_name),
141constraint fk22 foreign key(sensor_no) references movie1(sensor_no)
142);
143create table acted_by
144(
145star_name varchar(20),
146sensor_no varchar(20),
147constraint pk26 primary key(star_name,sensor_no),
148constraint fk23 foreign key(star_name) references star(star_name),
149constraint fk24 foreign key(sensor_no) references movie1(sensor_no)
150);
151b)
152insert into studio values('&st_name','&branch','&location');
153insert into studio values('pixar','blore','dsds');
154insert into studio values('warnerbro','pune','wewew');
155insert into studio values('legendary','new york','szxzx');
156insert into movie1 values('&sensor_no','&title','&date');
157insert into movie1 values('s1111','xyz',2011);
158insert into movie1 values('s1112','dark knight',2009);
159DBMS LAB EXTERNAL ©MR.STARK
160DBMS LAB EXTERNAL
161insert into movie1 values('s1113','hurt locker',2009);
162insert into star values('&star_name','&address');
163insert into star values('heath ledger','california');
164insert into star values('caprio','LA');
165insert into star values('clooney','NY');
166insert into owns values('&st_name','&sensor_no');
167insert into owns values('legendary','s1112');
168insert into owns values('warnerbro','s1111');
169insert into owns values('pixar','s1113');
170insert into acted_by values('&star_name','&sensor_no');
171insert into acted_by values('heath ledger','s1112');
172insert into acted_by values('caprio','s1111');
173insert into acted_by values('clooney','s1113');
174c)
175select s.st_name
176from studio s,movie1 m,owns o
177where m.title='xyz' and s.st_name=o.st_name and m.sensor_no=o.sensor_no;
178d)
179select s.star_name
180from star s,acted_by a,movie1 m
181where m.title='xyz' and s.star_name=a.star_name and m.sensor_no=a.sensor_no;
182e)
183create or replace procedure pr3(s number)
184is
185x movie1.title%type;
186cursor c is select title
187 from movie1 c
188 where c.year=s;
189DBMS LAB EXTERNAL ©MR.STARK
190DBMS LAB EXTERNAL
191begin
192for x in c loop
193sys.dbms_output.put_line(x.title);
194end loop;
195end;
196/
197f)
198create or replace trigger tr2
199before delete on movie1
200for each row
201declare
202cur number;
203begin
204select to_char(sysdate,'YYYY') into cur
205from dual;
206if(:OLD.year=cur) then
207raise_application_error(-20009,'Cannot delete');
208end if;
209end;
210/
2113. The production company is organised into different studios. We store each studio’s
212name, branch and location; a studio own any number of cartoon-serials. We store each
213cartoon-serials’s title, censor_number and year of production. star may do voices in
214any number of cartoon-serials and we store each actors name and address.
215a. Establish the database by normalising up to 3NF and considering all schema level
216constraints.
217b. Write SQL insertion query to insert few tuples to all the relations.
218c. Find total no. of actors, do voiced in a cartoon-serials “xyz”.
219d. Retrieve name of studio, location and cartoon-serials title in which star “abc’ is
220voiced.
221e. Write a procedure to list all cartoon-serials produced during the specific year.
222f. Write a deletion trigger, does not allow to deleting the current year cartoon-serials.
223a)
224create table studio1
225DBMS LAB EXTERNAL ©MR.STARK
226DBMS LAB EXTERNAL
227(
228st_name varchar(20),
229branch varchar(20),
230location varchar(20),
231constraint pk51 primary key(st_name)
232);
233create table cartoon
234(
235sensor_no varchar(20),
236title varchar(20),
237year number(5),
238constraint pk53 primary key(sensor_no)
239);
240create table star_cat
241(
242star_name varchar(20),
243address varchar(20),
244constraint pk64 primary key(star_name)
245);
246create table owncat
247(
248st_name varchar(20),
249sensor_no varchar(20),
250constraint pk55 primary key(st_name,sensor_no),
251constraint fk56 foreign key(st_name) references studio1(st_name),
252constraint fk57 foreign key(sensor_no) references cartoon(sensor_no)
253);
254DBMS LAB EXTERNAL ©MR.STARK
255DBMS LAB EXTERNAL
256create table voiced_by
257(
258star_name varchar(20),
259sensor_no varchar(20),
260constraint pk58 primary key(star_name,sensor_no),
261constraint fk59 foreign key(star_name) references star_cat(star_name),
262constraint fk60 foreign key(sensor_no) references cartoon(sensor_no)
263);
264b)
265insert into studio1 values('wb','pop','aa');
266insert into studio1 values('gb','pio','dd');
267insert into studio1 values('fg','uiy','kk');
268insert into studio1 values('un','rus','pr');
269insert into studio1 values('nb','rty','fg');
270insert into cartoon values(201,'tj',1990);
271insert into cartoon values(202,'ju',1991);
272insert into cartoon values(203,'gt',1991);
273insert into cartoon values(204,'tu',2012);
274insert into cartoon values(206,'tr',1998);
275insert into cartoon values(209,'tun',2012);
276insert into star_cat values('james','dbn');
277insert into star_cat values('tom','adjk');
278insert into star_cat values('ross','jjk');
279insert into star_cat values('peter','jkj');
280insert into star_cat values('joe','ahfa');
281insert into owncat values('wb',201);
282insert into owncat values('gb',201);
283insert into owncat values('fg',201);
284insert into owncat values('nb',202);
285DBMS LAB EXTERNAL ©MR.STARK
286DBMS LAB EXTERNAL
287insert into owncat values('nb',204);
288insert into voiced_by values('james',201);
289insert into voiced_by values('ross',201);
290insert into voiced_by values('tom',203);
291insert into voiced_by values('peter',206);
292insert into voiced_by values('joe',201);
293c)
294select count(*)
295from cartoon c,voiced_by v
296where c.title='tj'and c.sensor_no=v.sensor_no;
297d)
298select s.st_name,s.location,c.title
299from studio1 s,cartoon c,owncat o,voiced_by sr
300where s.st_name=o.st_name and c.sensor_no=o.sensor_no and sr.star_name='james' and
301sr.sensor_no=c.sensor_no;
302e)
303create or replace procedure pr3(s number)
304is
305x cartoon.title%type;
306cursor c is select title
307 from cartoon c
308 where c.year=s;
309begin
310for x in c loop
311sys.dbms_output.put_line(x.title);
312end loop;
313end;
314/
315f)
316DBMS LAB EXTERNAL ©MR.STARK
317DBMS LAB EXTERNAL
318create or replace trigger tr3
319before delete on cartoon
320for each row
321declare
322cur number;
323begin
324select to_char(sysdate,'YYYY')into cur
325from dual;
326if(:OLD.year=cur) then
327raise_application_error(-20019,'Cannot delete');
328end if;
329end;
330/
3314. Car marketing company wants keep track of marketed cars and their owner. Each car
332must be associated with a single owner and owner may have any number of cars. We
333store car’s registration number, model & colour and owner’s name, address & SSN.
334We also store date of purchase of each car.
335a. Establish the database by normalising up to 3NF and considering all schema level
336constraints.
337b. Write SQL insertion query to insert few tuples to all the relations.
338c. Find a person who owns highest number of cars.
339d. Retrieve persons and cars information purchased on day dd/mm/yyyy.
340e. Write a procedure to list all cars and owner information purchased during the
341specific year.
342f. Write a insertion trigger to check date of purchase must be less than current date
343(must use system date).
344a)
345create table owner2
346(
347ssn number(10),
348name varchar(20),
349address varchar(20),
350constraint pk41 primary key(ssn)
351);
352create table car2
353DBMS LAB EXTERNAL ©MR.STARK
354DBMS LAB EXTERNAL
355(
356rgno number(10),
357model varchar(10),
358color varchar(10),
359ssn number(10),
360dop date,
361constraint pk42 primary key(rgno),
362constraint fk41 foreign key(ssn) references owner2(ssn)
363);
364b)
365insert into owner2 values(&ssn,'&name','&address');
366insert into owner2 values(4441,'josh','sfsdf');
367insert into owner2 values(4442,'john','sfsdf');
368insert into owner2 values(4443,'rose','sfsdf');
369insert into owner2 values(4444,'robert','sfsdf');
370insert into car2 values(&rgno,'&model','&color',&ssn,'&dop');
371insert into car2 values(55551,'dfdf','red',4441,'10-jan-2011');
372insert into car2 values(55552,'xcdf','black',4441,'11-nov-2011');
373insert into car2 values(55553,'dfdfcx','blue',4441,'10-dec-2011');
374insert into car2 values(55554,'asdfdf','white',4442,'10-jul-2011');
375insert into car2 values(55555,'dfasdf','black',4443,'14-feb-2011');
376c)
377select o.name,c.ssn,count(c.ssn)
378from owner2 o,car2 c
379where o.ssn=c.ssn
380group by o.name,c.ssn
381having count(c.ssn) >=all(select count(m.ssn)
382 from car2 m
383 group by (m.ssn));
384DBMS LAB EXTERNAL ©MR.STARK
385DBMS LAB EXTERNAL
386d)
387select o.ssn,o.name,c.rgno,c.model
388from owner2 o,car2 c
389where c.ssn=o.ssn and dop='11-nov-2011';
390e)
391create or replace procedure pr4(pur_date date)
392is
393X owner2%rowtype;
394X1 car2%rowtype;
395cursor c is select o.* from owner2 o,car2 c1 where c1.ssn=o.ssn and dop=pur_date;
396cursor f is select c1.* from owner2 o,car2 c1 where c1.ssn=o.ssn and dop=pur_date;
397begin
398sys.dbms_output.put_line('OWNER DETAILS');
399for X in c loop
400sys.dbms_output.put_line(X.name||' '||X.ssn||' '||X.address);
401end loop;
402sys.dbms_output.put_line('CAR DETAILS');
403for X1 in f loop
404sys.dbms_output.put_line(X1.rgno||' '||X1.model||' '||X1.color);
405end loop;
406end;
407/
408f)
409create or replace trigger tr4
410before insert on car2
411for each row
412declare
413cur date;
414begin
415select sysdate into cur from dual;
416if(cur<:NEW.dop) then
417DBMS LAB EXTERNAL ©MR.STARK
418DBMS LAB EXTERNAL
419raise_application_error(-20009,'incorrect date');
420end if;
421end;
422/
4235. Puppy pet shop wants to keep track of dogs and their owners. The person can buy
424maximum three pet dogs. we store person’s name, SSN and address and dog’s name,
425date of purchase and sex. The owner of the pet dogs will be identified by SSN since
426the dog’s names are not distinct.
427a. Establish the database by normalising up to 3NF and considering all schema level
428constraints.
429b. Write SQL insertion query to insert few tuples to all the relations.
430c. List all pets owned by a person “abhiman”.
431d. List all persons who are not owned a single pet.
432e. Write a trigger to check the constraints that person can buy maximum three pet
433dogs.
434f. Write a procedure to list all dogs and owner details purchased on the specific date.
435a)
436create table ownerofdog
437(
438oname varchar(10),
439ssn number(10),
440address varchar(30),
441constraint pk1 primary key(ssn));
442create table doggg
443(
444ssn number(10),
445dname varchar(10),
446sex varchar(5),
447dop date,
448constraint pk2 primary key(ssn,dname),
449constraint fk1 foreign key(ssn) references ownerofdog(ssn));
450b)
451insert into ownerofdog values('james',1234,'xyz');
452DBMS LAB EXTERNAL ©MR.STARK
453DBMS LAB EXTERNAL
454insert into ownerofdog values('manoj',1235,'pqr');
455insert into ownerofdog values('monika',1236,'yrs');
456insert into ownerofdog values('gunda',1237,'rti ');
457insert into ownerofdog values('harsh',1238,'rjs');
458insert into ownerofdog values('Abhiman',1239,'stf');
459insert into doggg values (1234,'ab','M','10-jan-2010');
460insert into doggg values(1234,'bc','F','10-jan-2010');
461insert into doggg values(1235,'de','M','20-feb-2009');
462insert into doggg values(1236,'ef','F','21-mar-2010');
463insert into doggg values(1237,'jp','F','22-mar-2010');
464insert into doggg values(1239,'gh','M','09-jan-2010');
465insert into doggg values(1239,'fu','F','10-jan-2011');
466to check trigger
467insert into doggg values(1239,'gr','F','12-jan-2011');
468insert into doggg values(1239,'ks','M','14-jan-2012');
469insert into doggg values (1234,'abd','M','10-jun-2011');
470insert into doggg values(1234,'afg','F','09-jul-2012');
471c)
472select dname,sex,dop
473from doggg d,ownerofdog o
474where d.ssn=o.ssn and o.oname='Abhiman';
475d)select o.oname
476from ownerofdog o
477where not exists( select *
478 from doggg d
479 where o.ssn=d.ssn);
480e)
481create or replace trigger tr5
482DBMS LAB EXTERNAL ©MR.STARK
483DBMS LAB EXTERNAL
484before insert on doggg
485for each row
486declare
487cnt number;
488begin
489select count(*) into cnt from ownerofdog o,doggg d
490 where (o.ssn=d.ssn and o.ssn=:NEW.ssn);
491if(cnt>3) then
492raise_application_error(-20010,'Capacity of the owner crossed');
493end if;
494end;
495f)
496create or replace procedure pr5(pur_date date)
497is
498X1 dog%rowtype;
499X owner%rowtype;
500cursor c is select o.* from ownerofdog o,doggg d where o.ssn=d.ssn and
501dop=pur_date;
502cursor f is select d.* from ownerofdog o,doggg d where o.ssn=d.ssn and
503dop=pur_date;
504begin
505dbms_output.put_line('OWNER DETAILS');
506for X in c loop
507dbms_output.put_line(X.oname||' '||X.ssn||' '||X.address);
508end loop;
509dbms_output.put_line('DOG DETAILS');
510for X1 in f loop
511dbms_output.put_line(X1.dname||' '||X1.ssn||' '||X1.sex);
512end loop;
513end;
514/
515DBMS LAB EXTERNAL ©MR.STARK
516DBMS LAB EXTERNAL
5176. Education institute is managing the online course enrolment system. Students can
518enrol maximum of six courses of their choice and a maximum student to be enrolled
519to any course is 60. We store student details like name, USN, semester and several
520addresses, course details like unique title, unique id and credits.
521a. Establish the database by normalising up to 3NF and considering all schema level
522constraints.
523b. Write SQL insertion query to insert few tuples to all the relations.
524c. Find number of students enrolled for the course ‘DBMS’.
525d. Retrieve student names that are enrolled for data structure course but not enrolled
526for logic design.
527e. Write a trigger to establish the constraint that the students can enrol maximum of
528six course of their choice.
529f. Write a procedure to list all the courses enrolled by the seventh semester students.
530a)
531create table stud1
532(
533usn number(10),
534name varchar(20),
535sem varchar(10),
536constraint pkk primary key(usn)
537);
538create table course
539(
540title varchar(20) unique,
541cid number(10),
542credits number(10),
543constraint pk44 primary key(cid)
544);
545create table enroll
546(
547usn number(10),
548cid number(10),
549DBMS LAB EXTERNAL ©MR.STARK
550DBMS LAB EXTERNAL
551constraint pk54 primary key(usn,cid),
552constraint fk44 foreign key(cid) references course(cid),
553constraint fk54 foreign key(usn) references stud1(usn)
554);
555b)
556insert into stud1 values(901,7,'josh');
557insert into stud1 values(902,5,'guru');
558insert into stud1 values(903,7,'srini');
559insert into stud1 values(904,3,'turre');
560insert into stud1 values(905,3,'rupa');
561insert into stud1 values(906,5,'piggi');
562insert into stud1 values(NULL,6,'jos');
563insert into course values('dbms',301,4);
564insert into course values('ld',302,5);
565insert into course values('ds',303,4);
566insert into course values('oops',304,4);
567insert into course values('ada',305,3);
568insert into course values('se',306,4);
569insert into course values('cn',307,4);
570insert into enroll values(901,301);
571insert into enroll values(902,301);
572insert into enroll values(903,303);
573insert into enroll values(904,304);
574insert into enroll values(901,302);
575insert into enroll values(901,303);
576insert into enroll values(901,304);
577insert into enroll values(901,305);
578DBMS LAB EXTERNAL ©MR.STARK
579DBMS LAB EXTERNAL
580insert into enroll values(903,305);
581insert into enroll values(901,306);
582insert into enroll values(901,307);
583c)
584select count(s.usn)
585from stud1 s,course c,enroll e
586where s.usn=e.usn and e.cid=c.cid and c.title='dbms';
587d)
588select s.name
589from stud1 s,course c,enroll e
590where s.usn=e.usn and e.cid=c.cid and c.title='ds'
591minus
592select s.name
593from stud1 s,course c,enroll e
594where s.usn=e.usn and e.cid=c.cid and c.title='ld';
595e)
596create or replace trigger tr6
597before insert on enroll
598for each row
599declare
600var1 number;
601begin
602select count(*) into var1 from enroll where usn=:new.usn;
603if(var1>6)
604then
605raise_application_error(-20009,'limit reached');
606end if;
607end;
608/
609DBMS LAB EXTERNAL ©MR.STARK
610DBMS LAB EXTERNAL
611f)
612create or replace procedure pr6
613is
614X course%rowtype;
615cursor c is select c1.title from course c1, stud1 s,enroll e where s.usn=e.usn and
616c1.cid=e,cid ans s.sem='7';
617begin
618for X in c loop
619sys.dbms_output.put_line(c.name);
620end loop;
621end;
622/
6237. The commercial bank wants to keep track of the customer’s account information.
624Each customer may have any number of accounts and account can be shared by any
625number of customers. The system will keep track of the date of last transaction. We
626store the following details:
627i. account: unique account number, type and balance.
628ii. customer: unique customer id, name and several addresses
629composed of street, city and state.
630a. Establish the database by normalising up to 3NF and considering all schema level
631constraints.
632b. Write SQL insertion query to insert few tuples to all the relations.
633c. Add 3% interest to the customer who have less than 10000 balances and 6%
634interest to remaining customers.
635d. List joint accounts involving more than three customers.
636e. Write a insertion trigger to allow only current date for date of last transaction
637field.
638f. Write a procedure to find the customer who has highest number of accounts, the
639customer who has lowest balance, the customer who involved in most of joint
640accounts.
641a) create table customer
642(
643c_id varchar(10),
644name varchar(10),
645DBMS LAB EXTERNAL ©MR.STARK
646DBMS LAB EXTERNAL
647constraint pk71 primary key(c_id)
648);
649create table account
650(
651acc_no number(10),
652type varchar(10),
653balance number(100000),
654constraint pk72 primary key(acc_no)
655);
656create table cust_acc
657(
658c_id varchar(10),
659acc_no number(10),
660last_tr date,
661constraint pk73 primary key(c_id,acc_no),
662constraint fk71 foreign key(c_id) references customer(c_id),
663constraint fk72 foreign key(acc_no) references account(acc_no)
664);
665create table address
666(
667c_id varchar(10),
668street varchar(20),
669city varchar(20),
670state varchar(20),
671constraint pk74 primary key(c_id,street,city,state)
672);
673b)
674insert into customer values('&c_id','&name');
675insert into customer values('c1111','alex');
676insert into customer values('c1112','john');
677DBMS LAB EXTERNAL ©MR.STARK
678DBMS LAB EXTERNAL
679insert into customer values('c1113','steve');
680insert into customer values('c1114','robert');
681insert into account values(&acc_no,'&type','&balance');
682insert into account values(2001,'savings','8000');
683insert into account values(2002,'RD','9000');
684insert into account values(2003,'joint','20000');
685insert into cust_acc values('&c_id',&acc_no,'&last_tr');
686insert into cust_acc values('c1111',2003,'20-jan-2011');
687insert into cust_acc values('c1112',2003,'12-feb-2011');
688insert into cust_acc values('c1113',2003,'13-jan-2011');
689insert into cust_acc values('c1114',2003,'20-mar-2011');
690insert into cust_acc values('c1111',2001,'20-dec-2011');
691insert into address values('&c_id','&street','&city','&state');
692insert into address values('c1111','fsfsf','ccxx','&hghg');
693insert into address values('c1111','fsfcc','ccqwq','&hgcxhg');
694c)
695update account
696set balance=case
697when balance<=10000 then balance*1.05
698else balance*1.06
699end;
700d)
701select max(distinct(count(acc_no)))
702from account
703group by acc_no;
704DBMS LAB EXTERNAL ©MR.STARK
705DBMS LAB EXTERNAL
706e)
707create or replace trigger last_trans
708before insert on cust_acc
709for each row
710declare
711cur_date date;
712begin
713select sysdate into cur_date from dual;
714if((:NEW.last_tr-cur_date)<=0) then
715raise_application_error(-20006,'INVALID DATE');
716end if;
717end;
718f)
719create or replace procedure pr7()
720is
721X customer%rowtype;
722cursor c is
723select ca.c_id,c1.name,count(ca.acc_no)
724from customer c1, cust_acc ca
725where c1.c_id=ca.c_id
726group by ca_cid,c1.name
727having count(ca.acc_no) >= all(select count(ca.acc_no)
728from cust_acc ca
729group by ca.c_id;
730cursor f is
731select name from customer c, account a and cust_acc ca
732where c.c_id=ca.c_id and a.acc_no=ca.acc_no and a.balance in(select max(balance)
733from account);
734DBMS LAB EXTERNAL ©MR.STARK
735DBMS LAB EXTERNAL
736begin
737sys.dbms_output.put_line('CUSTOMER DETAILS WITH MAX ACCOUNTS');
738for X in c loop
739sys.dbms_output.put_line(X.c_id||' '||X.name);
740end loop;
741sys.dbms_output.put_line('CUSTOMER DETAILS WITH LOWEST BALANCE');
742for X in f loop
743sys.dbms_output.put_line(X.c_id||' '||X.name);
744end loop;
745end;
746/
7478. The commercial bank wants to keep track of the customer’s loan information. Each
748customer can take any number of loans from the bank and loan will not be shared.
749The system will keep track of the date of last transaction. We store the following
750details:
751i. loan: unique loan number, type and amount.
752ii. customer: unique customer id, name, annual income and several addresses
753composed of street, city and state.
754a. Establish the database by normalising up to 3NF and considering all schema level
755constraints.
756b. Write SQL insertion query to insert few tuples to all the relations.
757c. Add 12% interest to the customer who have less than 50000 amount and 14%
758interest to remaining customers.
759d. Retrieve the customers who have single loan in a bank.
760e. Write a insertion trigger to loan, that does not allow if the loan amount is more
761than two times of customers annual income.
762f. Write a procedure to retrieve all the loans of a specific customer.
763Refer/get idea for A) and b) from previous question.
764c)
765update loans
766set amount=case
767when amount<=50000 then balance*1.12
768else balance*1.14
769end;
770DBMS LAB EXTERNAL ©MR.STARK
771DBMS LAB EXTERNAL
772d)
773select c.cname,l.cid,count(l.lno)
774from customer c, loans l
775where c.cid=l.cid
776group by c.cname,l.cid
777having count(lno=1);
778e)
779create or replace trigger tr8
780before insert on loans
781for each row
782begin
783select cincome from customer c where c.cid=:NEW.cid;
784if(:NEW.amount > 2*cincome) then
785raise_application_error(-20009,'LIMIT EXCEEDED');
786end if;
787end;
788/
789f)
790create or replace procedure pr8(name varchar)
791is
792X loans%rowtype;
793cursor c is select l.* from customer c1, loans l where c1.cname=name and
794l.cid=c1.cid;
795begin
796sys.dbms_output.put_line('LOAN DETAILS');
797for X in c loop
798sys.dbms_output.put_line(X.lno||' '||X.type||' '||X.amount);
799end loop;
800end;
801/
802DBMS LAB EXTERNAL ©MR.STARK
803DBMS LAB EXTERNAL
8049. The xyz book shop wants keep track of orders of the book. The book is composed of
805unique id, title, year of publication, single author and single publisher. Each order will
806be uniquely identified by order-id and may have any number of books. We keep track
807of quantity of each book ordered. We store the following details:
808i. author: unique author-id, name, city, country.
809ii. Publisher: unique publisher-id, name, city, country.
810a. Establish the database by normalising up to 3NF and considering all schema
811level constraints.
812b. Write SQL insertion query to insert few tuples to all the relations.
813c. Find the author who has published highest number of books.
814d. List the books published by specific publisher during the year 2011.
815e. Write a insertion trigger to book to check year of publication should allow
816current year only.
817f. Write a procedure to list all books published by a specific author during the
818specific year.
819a)
820create table order1(
821orderid varchar(10),
822constraint order_pk primary key(orderid)
823);
824create table book(
825bid varchar(10),
826btitle varchar(10),
827byop number,
828aid varchar(10),
829pid varchar(10),
830constraint book_pk primary key(bid),
831constraint book_fk1 foreign key(aid) references author(aid),
832constraint book_fk2 foreign key(pid) references publisher(pid)
833);
834create table author(
835aid varchar(10),
836aname varchar(10),
837DBMS LAB EXTERNAL ©MR.STARK
838DBMS LAB EXTERNAL
839acity varchar(10),
840acountry varchar(10),
841constraint author_pk primary key(aid)
842);
843create table publisher(
844pid varchar(10),
845pname varchar(10),
846pcity varchar(10),
847pcountry varchar(10),
848constraint publisher_pk primary key(pid)
849);
850create table order_book(
851orderid varchar(10),
852bid varchar(10),
853constraint ob_pk primary key(orderid,bid),
854constraint ob_fk1 foreign key(bid) references book(bid),
855constraint ob_fk2 foreign key(orderid) references order1(orderid)
856);
857b)
858insert into order1 values('1');
859insert into order1 values('2');
860insert into book values('1','abc','2001','1','1');
861insert into book values('2','xyz','2001','1','1');
862insert into book values('3','mno','2001','2','1');
863insert into author values('1','a','b','c');
864insert into author values('2','a','b','c');
865insert into publisher values('1','a','b','c');
866DBMS LAB EXTERNAL ©MR.STARK
867DBMS LAB EXTERNAL
868insert into order_book values('1','1');
869c)
870select a.aname,b.aid,count(b.bid)
871from author a,book b
872where a.aid=b.aid
873group by a.aname,b.aid
874having count(b.bid)>=all(select count(b.bid)
875from book b
876group by b.aid);
877d)
878select btitle
879from book
880where byop='2001' and pid='1';
881e)
882create or replace trigger tr9
883before insert on book
884for each row
885declare
886cur number;
887begin
888select to_char(sysdate,'YYYY')into cur
889from dual;
890if(:NEW.byop!=cur) then
891raise_application_error(-20019,'Cannot insert');
892end if;
893end;
894/
895DBMS LAB EXTERNAL ©MR.STARK
896DBMS LAB EXTERNAL
897f)
898create or replace procedure pr9(year number,name varchar)
899is
900X book%rowtype;
901cursor c is select b.* from book b,author a where a.aname=name and b.byop=year and
902a.aid=b.aid;
903begin
904sys.dbms_output.put_line('BOOK DETAILS');
905for X in c loop
906sys.dbms_output.put_line(X.bid||' '||X.btitle||' '||X.byop);
907end loop;
908end;
909/
910Disclaimer
911I am not responsible for wrong answers, system errors,
912thermonuclear war, or you getting fired because the database failed.
913Please do some research if you have any concerns about answers included
914in this document before reading it! YOU are choosing to read this
915document and if you point the finger at me for messing up your marks,
916I will laugh at you. although I did my BEST.