Pn0FDdrk

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