eUjpbZka

1DBMS LABORATORY WITH MINI PROJECT
215CSL58
3LIBRARY DATABASE
4BOOK (BOOK_ID, TITLE, PUBLISHER_NAME, PUB_YEAR)
5BOOKAUTHORS(BOOK_ID,AUTHOR_NAME)
6PUBLISHER(NAME,ADDRESS,PHONE)
7BOOK_COPIES(BOOK_ID,BRANCH_ID,NO_OF_COPIES)
8BOOK_LENDING(BOOK_ID,BRANCH_ID,CARD_NO,DATE_OUT,DUE_DATE)
9LIBRARY_BRANCH(BRANCH_ID,BRANCH_NAME,ADDRESS)
10CREATE TABLE COMMANDS
111. CREATE TABLE PUBLISHER(NAME VARCHAR2(15),
12ADDRESS VARCHAR2(25),
13PHONE NUMBER(10),
14CONSTRAINT PK_PN PRIMARY KEY(NAME)
15);
162. CREATE TABLE LIBRARY_BRANCH(BRANCH_ID NUMBER(5),
17BRANCH_NAME NUMBER(5),
18ADDRESS VARCHAR2(15),
19CONSTRAINT PK_ID PRIMARY KEY(BRANCH_ID)
20);
213. CREATE TABLE BOOK(BOOK_ID NUMBER(5),
22TITLE VARCHAR2(25),
23PUBLISHER_NAME VARCHAR2(25),
24PUB_YEAR NUMBER(4),
25CONSTRAINT PK_BID PRIMARY KEY(BOOK_ID),
26CONSTRAINT FK_N FOREIGN KEY (PUBLISHER_NAME) REFERENCES
27PUBLISHER (NAME) ON DELETE CASCADE
28);
294. CREATE TABLE BOOK_AUTHORS(BOOK_ID NUMBER(5),
30AUTHOR_NAME VARCHAR2(25),
31CONSTRAINT FK_B FOREIGN KEY(BOOK_ID) REFERENCES BOOK(BOOK_ID)
32ON DELETE CASCADE
33);
345. CREATE TABLE BOOK_COPIES(BOOK_ID NUMBER(5),
35BRANCH_ID NUMBER (5),
36NO_OF_COPIES NUMBER (2),
37CONSTRAINT CPK_BBI PRIMARY KEY(BOOK_ID,BRANCH_ID),
38CONSTRAINT FK_BI FOREIGN KEY(BOOK_ID) REFERENCES BOOK(BOOK_ID)
39ON DELETE CASCADE,
40CONSTRAINT FK_I FOREIGN KEY(BRANCH_ID) REFERENCES
41LIBRARY_BRANCH(BRANCH_ID) ON DELETE CASCADE
42);
436. CREATE TABLE BOOK_LENDING(BOOK_ID NUMBER(5),
44BRANCH_ID NUMBER(5),
45P a g e 1 | 70DBMS LABORATORY WITH MINI PROJECT
4615CSL58
47CARD_NO NUMBER(3),
48DATE_OUT DATE,
49DUE_DATE DATE,
50CONSTRAINT CPK_BBC PRIMARY KEY (BOOK_ID, BRANCH_ID, CARD_NO),
51CONSTRAINT FK_A FOREIGN KEY (BOOK_ID) REFERENCES
52BOOK(BOOK_ID) ON DELETE CASCADE,
53CONSTRAINT FK_C FOREIGN KEY (BRANCH_ID) REFERENCES
54LIBRARY_BRANCH(BRANCH_ID) ON DELETE CASCADE
55);
56INSERTION COMMANDS
57SQL> INSERT INTO PUBLISHER VALUES('&NAME','&ADDRESS',&PHONE);
58Enter value for name: PHI
59Enter value for address: PUNE,INDIA
60Enter value for phone: 908070
61old 1: INSERT INTO PUBLISHER VALUES('&NAME','&ADDRESS',&PHONE)
62new 1: INSERT INTO PUBLISHER VALUES('PHI','PUNE,INDIA',908070)
631 row created.
64SQL> /
65Enter value for name: PEARSON
66Enter value for address: MUMBAI,INDIA
67Enter value for phone: 8080707060
68old 1: INSERT INTO PUBLISHER VALUES('&NAME','&ADDRESS',&PHONE)
69new 1: INSERT INTO PUBLISHER VALUES('PEARSON','MUMBAI,INDIA',8080707060)
701 row created.
71SQL> /
72Enter value for name: MCGRAWHILL
73Enter value for address: HOUSTIN,USA
74Enter value for phone: 120707070
75old 1: INSERT INTO PUBLISHER VALUES('&NAME','&ADDRESS',&PHONE)
76new 1: INSERT INTO PUBLISHER VALUES('MCGRAWHILL','HOUSTIN,USA',120707070)
771 row created.
78SQL> /
79Enter value for name: WILEY
80Enter value for address: CALIFORNIA,USA
81Enter value for phone: 1108080808
82old 1: INSERT INTO PUBLISHER VALUES('&NAME','&ADDRESS',&PHONE)
83new 1: INSERT INTO PUBLISHER VALUES('WILEY','CALIFORNIA,USA',1108080808)
841 row created.
85SQL> /
86Enter value for name: SSI
87Enter value for address: FLORIDA
88Enter value for phone: 1208080909
89old 1: INSERT INTO PUBLISHER VALUES('&NAME','&ADDRESS',&PHONE)
90new 1: INSERT INTO PUBLISHER VALUES('SSI','FLORIDA',1208080909)
911 row created.
92P a g e 2 | 70DBMS LABORATORY WITH MINI PROJECT
9315CSL58
94SQL> /
95Enter value for name: SP
96Enter value for address: BANGALORE,INDIA
97Enter value for phone: 9090909080
98old 1: INSERT INTO PUBLISHER VALUES('&NAME','&ADDRESS',&PHONE)
99new 1: INSERT INTO PUBLISHER VALUES('SP','BANGALORE,INDIA',9090909080)
1001 row created.
101SQL> SELECT * FROM PUBLISHER;
102NAME
103ADDRESS
104---------------
105------------------------- -
106PHI
107PUNE,INDIA
108PEARSON
109MUMBAI,INDIA
110MCGRAWHILL HOUSTIN,USA
111WILEY
112CALIFORNIA,USA
113SSI
114FLORIDA
115SP
116BANGALORE,INDIA
1176 rows selected.
118PHONE
119---------
120908070
1218080707060
122120707070
1231108080808
1241208080909
1259090909080
126SQL> INSERT INTO LIBRARY_BRANCH
127VALUES(&BRANCH_ID,'&BRANCH_NAME','&ADDRESS');
128Enter value for branch_id: 1000
129Enter value for branch_name: SMVIT
130Enter value for address: HUNASEMARANAHALLI
131old 1: INSERT INTO LIBRARY_BRANCH
132VALUES(&BRANCH_ID,'&BRANCH_NAME','&ADDRESS')
133new 1: INSERT INTO LIBRARY_BRANCH VALUES(1000,'SMVIT','HUNASEMARANAHALLI')
1341 row created.
135SQL> /
136Enter value for branch_id: 2000
137Enter value for branch_name: SVIT
138Enter value for address: DODDABALLAPUR
139old 1: INSERT INTO LIBRARY_BRANCH
140VALUES(&BRANCH_ID,'&BRANCH_NAME','&ADDRESS')
141new 1: INSERT INTO LIBRARY_BRANCH VALUES(2000,'SVIT','DODDABALLAPUR')
1421 row created.
143SQL> /
144Enter value for branch_id: 3000
145Enter value for branch_name: BMSIT
146Enter value for address: AVANAHALLI
147old 1: INSERT INTO LIBRARY_BRANCH
148VALUES(&BRANCH_ID,'&BRANCH_NAME','&ADDRESS')
149new 1: INSERT INTO LIBRARY_BRANCH VALUES(3000,'BMSIT','AVANAHALLI')
1501 row created.
151SQL> /
152P a g e 3 | 70DBMS LABORATORY WITH MINI PROJECT
15315CSL58
154Enter value for branch_id: 4000
155Enter value for branch_name: SVCE
156Enter value for address: VIDYANAGAR
157old 1: INSERT INTO LIBRARY_BRANCH
158VALUES(&BRANCH_ID,'&BRANCH_NAME','&ADDRESS')
159new 1: INSERT INTO LIBRARY_BRANCH VALUES(4000,'SVCE','VIDYANAGAR')
1601 row created.
161SQL> /
162Enter value for branch_id: 5000
163Enter value for branch_name: MSCE
164Enter value for address: CHIKKAJALA
165old 1: INSERT INTO LIBRARY_BRANCH
166VALUES(&BRANCH_ID,'&BRANCH_NAME','&ADDRESS')
167new 1: INSERT INTO LIBRARY_BRANCH VALUES(5000,'MSCE','CHIKKAJALA')
1681 row created.
169SQL> /
170Enter value for branch_id: 6000
171Enter value for branch_name: NMIT
172Enter value for address: YELAHANKA
173old 1: INSERT INTO LIBRARY_BRANCH
174VALUES(&BRANCH_ID,'&BRANCH_NAME','&ADDRESS')
175new 1: INSERT INTO LIBRARY_BRANCH VALUES(6000,'NMIT','YELAHANKA')
1761 row created
177SQL> SELECT * FROM LIBRARY_BRANCH;
178BRANCH_ID
179----------
1801000
1812000
1823000
1834000
1845000
1856000
186BRANCH_NAME
187-----
188SMVIT
189SVIT
190BMSIT
191SVCE
192MSCE
193NMIT
194ADDRESS
195--------------------
196HUNASEMARANAHALLI
197DODDABALLAPUR
198AVANAHALLI
199VIDYANAGAR
200CHIKKAJALA
201YELAHANKA
2026 rows selected.
203SQL> INSERT INTO BOOK
204VALUES(&BOOK_ID,'&TITLE','&PUBLISHER_NAME',&PUB_YEAR);
205Enter value for book_id: 1111
206Enter value for title: FUNNDAMENTALS OF DATABASE
207Enter value for publisher_name: PHI
208Enter value for pub_year: 2009
209old 1: INSERT INTO BOOK
210VALUES(&BOOK_ID,'&TITLE','&PUBLISHER_NAME',&PUB_YEAR)
211new 1: INSERT INTO BOOK VALUES(1111,'FUNNDAMENTALS OF DATABASE','PHI',2009)
2121 row created.
213SQL> /
214P a g e 4 | 70DBMS LABORATORY WITH MINI PROJECT
21515CSL58
216Enter value for book_id: 2222
217Enter value for title: BASICS OF LOGIC DESIGN
218Enter value for publisher_name: PEARSON
219Enter value for pub_year: 2009
220old 1: INSERT INTO BOOK
221VALUES(&BOOK_ID,'&TITLE','&PUBLISHER_NAME',&PUB_YEAR)
222new 1: INSERT INTO BOOK VALUES(2222,'BASICS OF LOGIC DESIGN','PEARSON',2009)
2231 row created.
224SQL> /
225Enter value for book_id: 3333
226Enter value for title: DATASTRUCTURES
227Enter value for publisher_name: MCGRAWHILL
228Enter value for pub_year: 2015
229old 1: INSERT INTO BOOK
230VALUES(&BOOK_ID,'&TITLE','&PUBLISHER_NAME',&PUB_YEAR)
231new 1: INSERT INTO BOOK VALUES(3333,'DATASTRUCTURES','MCGRAWHILL',2015)
2321 row created.
233SQL> /
234Enter value for book_id: 4444
235Enter value for title: ARTIFICIAL INTELLIGENCE
236Enter value for publisher_name: WILEY
237Enter value for pub_year: 2017
238old 1: INSERT INTO BOOK
239VALUES(&BOOK_ID,'&TITLE','&PUBLISHER_NAME',&PUB_YEAR)
240new 1: INSERT INTO BOOK VALUES(4444,'ARTIFICIAL INTELLIGENCE','WILEY',2017)
2411 row created.
242SQL> /
243Enter value for book_id: 5555
244Enter value for title: PROGRAMMING SKILLS
245Enter value for publisher_name: SSI
246Enter value for pub_year: 2014
247old 1: INSERT INTO BOOK
248VALUES(&BOOK_ID,'&TITLE','&PUBLISHER_NAME',&PUB_YEAR)
249new 1: INSERT INTO BOOK VALUES(5555,'PROGRAMMING SKILLS','SSI',2014)
2501 row created.
251SQL> /
252Enter value for book_id: 6666
253Enter value for title: DESIGN OF ALGORITHMS
254Enter value for publisher_name: SP
255Enter value for pub_year: 2013
256old 1: INSERT INTO BOOK
257VALUES(&BOOK_ID,'&TITLE','&PUBLISHER_NAME',&PUB_YEAR)
258new 1: INSERT INTO BOOK VALUES(6666,'DESIGN OF ALGORITHMS','SP',2013)
2591 row created.
260SQL> SELECT * FROM BOOK;
261P a g e 5 | 70DBMS LABORATORY WITH MINI PROJECT
262BOOK_ID TITLE
263----------
2641111
2652222
2663333
2674444
2685555
2696666
2706 rows selected.
271PUBLISHER_NAME
272-------------------------
273FUNNDAMENTALS OF DATABASE PHI
274BASICS OF LOGIC DESIGN PEARSON
275DATASTRUCTURES
276MCGRAWHILL
277ARTIFICIAL INTELLIGENCE WILEY
278PROGRAMMING SKILLS
279SSI
280DESIGN OF ALGORITHMS SP
28115CSL58
282PUB_YEAR
283-----------------
2842009
2852009
2862015
2872017
2882014
2892013
290SQL> INSERT INTO BOOK_AUTHORS VALUES(&BOOK_ID,'&AUTHOR_NAME');
291Enter value for book_id: 1111
292Enter value for author_name: NAVATHE
293old 1: INSERT INTO BOOK_AUTHORS VALUES(&BOOK_ID,'&AUTHOR_NAME')
294new 1: INSERT INTO BOOK_AUTHORS VALUES(1111,'NAVATHE')
2951 row created.
296SQL> /
297Enter value for book_id: 2222
298Enter value for author_name: GODSE
299old 1: INSERT INTO BOOK_AUTHORS VALUES(&BOOK_ID,'&AUTHOR_NAME')
300new 1: INSERT INTO BOOK_AUTHORS VALUES(2222,'GODSE')
3011 row created.
302SQL> /
303Enter value for book_id: 3333
304Enter value for author_name: SAHANI
305old 1: INSERT INTO BOOK_AUTHORS VALUES(&BOOK_ID,'&AUTHOR_NAME')
306new 1: INSERT INTO BOOK_AUTHORS VALUES(3333,'SAHANI')
3071 row created.
308SQL> /
309Enter value for book_id: 4444
310Enter value for author_name: RITCHIE KNIGHT
311old 1: INSERT INTO BOOK_AUTHORS VALUES(&BOOK_ID,'&AUTHOR_NAME')
312new 1: INSERT INTO BOOK_AUTHORS VALUES(4444,'RITCHIE KNIGHT')
3131 row created.
314SQL> /
315Enter value for book_id: 5555
316Enter value for author_name: BALAGURUSWAMY
317old 1: INSERT INTO BOOK_AUTHORS VALUES(&BOOK_ID,'&AUTHOR_NAME')
318new 1: INSERT INTO BOOK_AUTHORS VALUES(5555,'BALAGURUSWAMY')
3191 row created.
320SQL> /
321Enter value for book_id: 6666
322Enter value for author_name: COREMEN
323old 1: INSERT INTO BOOK_AUTHORS VALUES(&BOOK_ID,'&AUTHOR_NAME')
324new 1: INSERT INTO BOOK_AUTHORS VALUES(6666,'COREMEN')
3251 row created.
326P a g e 6 | 70DBMS LABORATORY WITH MINI PROJECT
32715CSL58
328SQL> SELECT * FROM BOOK_AUTHORS;
329BOOK_ID AUTHOR_NAME
330----------
331-------------------------
3321111
333NAVATHE
3342222
335GODSE
3363333
337SAHANI
3384444
339RITCHIE KNIGHT
3405555
341BALAGURUSWAMY
3426666
343COREMEN
3446 rows selected.
345SQL> INSERT INTO BOOK_COPIES VALUES(&BOOK_ID,&BRANCH_ID,&NO_OF_COPIES);
346Enter value for book_id: 1111
347Enter value for branch_id: 1000
348Enter value for no_of_copies: 10
349old 1: INSERT INTO BOOK_COPIES VALUES(&BOOK_ID,&BRANCH_ID,&NO_OF_COPIES)
350new 1: INSERT INTO BOOK_COPIES VALUES(1111,1000,10)
3511 row created.
352SQL> /
353Enter value for book_id: 2222
354Enter value for branch_id: 2000
355Enter value for no_of_copies: 5
356old 1: INSERT INTO BOOK_COPIES VALUES(&BOOK_ID,&BRANCH_ID,&NO_OF_COPIES)
357new 1: INSERT INTO BOOK_COPIES VALUES(2222,2000,5)
3581 row created.
359SQL> /
360Enter value for book_id: 3333
361Enter value for branch_id: 3000
362Enter value for no_of_copies: 7
363old 1: INSERT INTO BOOK_COPIES VALUES(&BOOK_ID,&BRANCH_ID,&NO_OF_COPIES)
364new 1: INSERT INTO BOOK_COPIES VALUES(3333,3000,7)
3651 row created.
366SQL> /
367Enter value for book_id: 4444
368Enter value for branch_id: 4000
369Enter value for no_of_copies: 9
370old 1: INSERT INTO BOOK_COPIES VALUES(&BOOK_ID,&BRANCH_ID,&NO_OF_COPIES)
371new 1: INSERT INTO BOOK_COPIES VALUES(4444,4000,9)
3721 row created.
373SQL> /
374Enter value for book_id: 5555
375Enter value for branch_id: 5000
376Enter value for no_of_copies: 6
377old 1: INSERT INTO BOOK_COPIES VALUES(&BOOK_ID,&BRANCH_ID,&NO_OF_COPIES)
378P a g e 7 | 70DBMS LABORATORY WITH MINI PROJECT
37915CSL58
380new 1: INSERT INTO BOOK_COPIES VALUES(5555,5000,6)
3811 row created.
382SQL> /
383Enter value for book_id: 6666
384Enter value for branch_id: 6000
385Enter value for no_of_copies: 12
386old 1: INSERT INTO BOOK_COPIES VALUES(&BOOK_ID,&BRANCH_ID,&NO_OF_COPIES)
387new 1: INSERT INTO BOOK_COPIES VALUES(6666,6000,12)
3881 row created.
389SQL> /
390Enter value for book_id: 2222
391Enter value for branch_id: 1000
392Enter value for no_of_copies: 15
393old 1: INSERT INTO BOOK_COPIES VALUES(&BOOK_ID,&BRANCH_ID,&NO_OF_COPIES)
394new 1: INSERT INTO BOOK_COPIES VALUES(2222,1000,15)
3951 row created.
396SQL> SELECT * FROM BOOK_COPIES;
397BOOK_ID
398----------
3991111
4002222
4013333
4024444
4035555
4046666
4052222
406BRANCH_ID
407----------
4081000
4092000
4103000
4114000
4125000
4136000
4141000
415NO_OF_COPIES
416------------
41710
4185
4197
4209
4216
42212
42315
4247 rows selected.
425SQL> INSERT INTO BOOK_LENDING
426VALUES(&BOOK_ID,&BRANCH_ID,&CARD_NO,'&DATE_OUT','&DUE_DATE');
427Enter value for book_id: 1111
428Enter value for branch_id: 1000
429Enter value for card_no: 10
430Enter value for date_out: 15-FEB-17
431Enter value for due_date: 15-JUN-17
432old 1: INSERT INTO BOOK_LENDING
433VALUES(&BOOK_ID,&BRANCH_ID,&CARD_NO,'&DATE_OUT','&DUE_DATE')
434new 1: INSERT INTO BOOK_LENDING VALUES(1111,1000,10,'15-FEB-17','15-JUN-17')
4351 row created.
436SQL> /
437Enter value for book_id: 2222
438Enter value for branch_id: 2000
439Enter value for card_no: 10
440Enter value for date_out: 10-MAR-17
441Enter value for due_date: 15-AUG-17
442P a g e 8 | 70DBMS LABORATORY WITH MINI PROJECT
44315CSL58
444old 1: INSERT INTO BOOK_LENDING
445VALUES(&BOOK_ID,&BRANCH_ID,&CARD_NO,'&DATE_OUT','&DUE_DATE')
446new 1: INSERT INTO BOOK_LENDING VALUES(2222,2000,10,'10-MAR-17','15-AUG-17')
4471 row created.
448SQL> /
449Enter value for book_id: 3333
450Enter value for branch_id: 3000
451Enter value for card_no: 10
452Enter value for date_out: 15-APR-17
453Enter value for due_date: 15-SEP-17
454old 1: INSERT INTO BOOK_LENDING
455VALUES(&BOOK_ID,&BRANCH_ID,&CARD_NO,'&DATE_OUT','&DUE_DATE')
456new 1: INSERT INTO BOOK_LENDING VALUES(3333,3000,10,'15-APR-17','15-SEP-17')
4571 row created.
458SQL> /
459Enter value for book_id: 4444
460Enter value for branch_id: 4000
461Enter value for card_no: 10
462Enter value for date_out: 10-JUN-17
463Enter value for due_date: 15-NOV-17
464old 1: INSERT INTO BOOK_LENDING
465VALUES(&BOOK_ID,&BRANCH_ID,&CARD_NO,'&DATE_OUT','&DUE_DATE')
466new 1: INSERT INTO BOOK_LENDING VALUES(4444,4000,10,'10-JUN-17','15-NOV-17')
4671 row created.
468SQL> /
469Enter value for book_id: 5555
470Enter value for branch_id: 5000
471Enter value for card_no: 20
472Enter value for date_out: 15-FEB-17
473Enter value for due_date: 15-JUN-17
474old 1: INSERT INTO BOOK_LENDING
475VALUES(&BOOK_ID,&BRANCH_ID,&CARD_NO,'&DATE_OUT','&DUE_DATE')
476new 1: INSERT INTO BOOK_LENDING VALUES(5555,5000,20,'15-FEB-17','15-JUN-17')
4771 row created.
478SQL> /
479Enter value for book_id: 6666
480Enter value for branch_id: 6000
481Enter value for card_no: 30
482Enter value for date_out: 10-MAR-17
483Enter value for due_date: 15-AUG-17
484old 1: INSERT INTO BOOK_LENDING
485VALUES(&BOOK_ID,&BRANCH_ID,&CARD_NO,'&DATE_OUT','&DUE_DATE')
486new 1: INSERT INTO BOOK_LENDING VALUES(6666,6000,30,'10-MAR-17','15-AUG-17')
4871 row created.
488SQL> /
489Enter value for book_id: 5555
490P a g e 9 | 70DBMS LABORATORY WITH MINI PROJECT
49115CSL58
492Enter value for branch_id: 5000
493Enter value for card_no: 10
494Enter value for date_out: 15-JAN-16
495Enter value for due_date: 15-JUN-16
496old 1: INSERT INTO BOOK_LENDING
497VALUES(&BOOK_ID,&BRANCH_ID,&CARD_NO,'&DATE_OUT','&DUE_DATE')
498new 1: INSERT INTO BOOK_LENDING VALUES(5555,5000,10,'15-JAN-16','15-JUN-16')
4991 row created.
500SQL> SELECT * FROM BOOK_LENDING;
501BOOK_ID BRANCH_ID CARD_NO DATE_OUT
502----------
503----------
504----------
505---------
5061111
5071000
50810
50915-FEB-17
5102222
5112000
51210
51310-MAR-17
5143333
5153000
51610
51715-APR-17
5184444
5194000
52010
52110-JUN-17
5225555
5235000
52420
52515-FEB-17
5266666
5276000
52830
52910-MAR-17
5305555
5315000
53210
53315-JAN-16
5347 rows selected.
535DUE_DATE
536---------
53715-JUN-17
53815-AUG-17
53915-SEP-17
54015-NOV-17
54115-JUN-17
54215-AUG-17
54315-JUN-16
544QUERIES
5451.Retrive details of all books in the library i.e ID,Tile,name of publisher,authors,no. of copies etc in
546each branch.
547SELECTC.BRANCH_ID,L.BRANCH_NAME,B.BOOK_ID,B.TITLE,B.PUBLISHER_NAME,B.PUB_
548YEAR,A.AUTHOR_NAME,C.NO_OF_COPIES
549FROM BOOK B,BOOK_AUTHORS A,LIBRARY_BRANCH L,BOOK_COPIES C
550WHERE B.BOOK_ID=A.BOOK_ID AND
551B.BOOK_ID=C.BOOK_ID AND
552L.BRANCH_ID=C.BRANCH_ID AND
553(C.BRANCH_ID,C.BOOK_ID) IN
554(SELECT BRANCH_ID,BOOK_ID
555FROM BOOK_COPIES
556GROUP BY BRANCH_ID,BOOK_ID);
557Output:
558BRANCH_ID BRANCH_NAME BOOK_ID
559TITLE
560PUBLISHER_NAME
561---------
562- -----
563----------
564-----------------
565-------------------------
5665000
567MSCE
5685555
569PROGRAMMING SKILLS
570SSI
5711000
572SMVIT
5731111
574FUNNDAMENTALS OF DATABASE
575PHI
5761000
577SMVIT
5782222
579BASICS OF LOGIC DESIGN
580PEARSON
5812000
582SVIT
5832222
584BASICS OF LOGIC DESIGN
585PEARSON
5863000
587BMSIT
5883333
589DATASTRUCTURES
590MCGRAWHILL
5914000
592SVCE
5934444
594ARTIFICIAL INTELLIGENCE
595WILEY
5966000
597NMIT
5986666
599DESIGN OF ALGORITHMS
600SP
601P a g e 10 | 70DBMS LABORATORY WITH MINI PROJECT
602PUB_YEAR
603----------
6042014
6052009
6062009
6072009
6082015
6092017
6102013
6117 rows selected.
612AUTHOR_NAME
613----------------------
614BALAGURUSWAMY
615NAVATHE
616GODSE
617GODSE
618SAHANI
619RITCHIE KNIGHT
620COREMEN
62115CSL58
622NO_OF_COPIES
623--- ------------
6246
62510
62615
6275
6287
6299
63012
6312.Get the particulars of borrowers who have borrowed more than 3 books but from Jan 2017 to
632Jun
6332017
634SQL> SELECT * FROM BOOK_LENDING
635WHERE DATE_OUT BETWEEN '01-JAN-17' AND '30-JUN-17' AND CARD_NO
636IN
637(SELECT CARD_NO
638FROM BOOK_LENDING
639GROUP BY CARD_NO
640HAVING COUNT(CARD_NO)>3);
641Output:
642BOOK_ID BRANCH_ID CARD_NO DATE_OUT
643----------
644----------
645----------
646---------
6474444
6484000
64910
65010-JUN-17
6513333
6523000
65310
65415-APR-17
6552222
6562000
65710
65810-MAR-17
6591111
6601000
66110
66215-FEB-17
663DUE_DATE
664---------
66515-NOV-17
66615-SEP-17
66715-AUG-17
66815-JUN-17
6693.Delete a book in book table.Update the contents of other tables to reflect this data manipulation
670operation.
671DELETE FROM BOOK WHERE BOOK_ID=&BOOK_ID;
672Enter value for book_id: 5555
673old 1: DELETE FROM BOOK WHERE BOOK_ID=&BOOK_ID
674new 1: DELETE FROM BOOK WHERE BOOK_ID=5555
6751 row deleted.
676SQL> SELECT * FROM BOOK;
677BOOK_ID
678---------
6791111
6802222
6813333
6824444
6836666
684TITLE
685PUBLISHER_NAME
686- -------------------------
687-------------------------
688FUNNDAMENTALS OF DATABASE
689PHI
690BASICS OF LOGIC DESIGN
691PEARSON
692DATASTRUCTURES
693MCGRAWHILL
694ARTIFICIAL INTELLIGENCE
695WILEY
696DESIGN OF ALGORITHMS
697SP
698PUB_YEAR
699----------
7002009
7012009
7022015
7032017
7042013
705P a g e 11 | 70DBMS LABORATORY WITH MINI PROJECT
70615CSL58
707SQL> SELECT * FROM BOOK_COPIES;
708BOOK_ID BRANCH_ID NO_OF_COPIES
709----------
710---------
711------------
7121111
7131000
71410
7152222
7162000
7175
7183333
7193000
7207
7214444
7224000
7239
7246666
7256000
72612
7272222
7281000
72915
7306 rows selected.
731SQL> SELECT * FROM BOOK_AUTHORS;
732BOOK_ID AUTHOR_NAME
733----------
734-------------------------
7351111
736NAVATHE
7372222
738GODSE
7393333
740SAHANI
7414444
742RITCHIE KNIGHT
7436666
744COREMEN
745SQL> SELECT * FROM BOOK_LENDING;
746BOOK_ID BRANCH_ID CARD_NO DATE_OUT DUE_DATE
747----------
748----------
749----------
750---------
751---------
7521111
7531000
75410
75515-FEB-17
75615-JUN-17
7572222
7582000
75910
76010-MAR-17 15-AUG-17
7613333
7623000
76310
76415-APR-17
76515-SEP-17
7664444
7674000
76810
76910-JUN-17
77015-NOV-17
7716666
7726000
77330
77410-MAR-17 15-AUG-17
7754.Partition the book table based on year of publication.Demostrate its working with a simple query.
776SQL> CONNECT SYSTEM/manjunath;
777Connected.
778SQL> GRANT CREATE VIEW TO B2;
779Grant succeeded.
780SQL> CONNECT B2/B2;
781Connected.
782SQL> CREATE VIEW YEAR AS SELECT PUB_YEAR FROM BOOK;
783View created.
784Output:
785SQL> SELECT * FROM YEAR;
786PUB_YEAR
787----------
7882009
7892009
7902015
7912017
7922013
793P a g e 12 | 70DBMS LABORATORY WITH MINI PROJECT
79415CSL58
7955.Create a view all books and its no. of copies that are currently available in the library.
796CREATE VIEW ALL_BOOK AS
797SELECT B.BOOK_ID,B.TITLE,C.NO_OF_COPIES,L.BRANCH_NAME
798FROM BOOK B,BOOK_COPIES C,LIBRARY_BRANCH L
799WHERE B.BOOK_ID=C.BOOK_ID
800AND L.BRANCH_ID=C.BRANCH_ID;
801View created.
802Output:
803SQL> SELECT * FROM ALL_BOOK;
804BOOK_ID
805TITLE
806NO_OF_COPIES
807----------
808-------------------------
809------------
8101111
811FUNNDAMENTALS OF DATABASE
81210
8132222
814BASICS OF LOGIC DESIGN
8155
8162222
817BASICS OF LOGIC DESIGN
81815
8193333
820DATASTRUCTURES
8217
8224444
823ARTIFICIAL INTELLIGENCE
8249
8256666
826DESIGN OF ALGORITHMS
82712
828BRANCH_NAME
829-----
830SMVIT
831SVIT
832SMVIT
833BMSIT
834SVCE
835NMIT
8366 rows selected.
837P a g e 13 | 70DBMS LABORATORY WITH MINI PROJECT
83815CSL58
839ORDERS DATABASE
840SALESMAN(SALESMAN_ID,NAME,CITY,COMISSION)
841CUSTOMER(CUSTOMER_ID,CUST_NAME,CITY,GRADE,SALESMAN_ID)
842ORDERS(ORD_NO,PURCHASE_AMT,ORD_DATE,CUSTOMER_ID,SALESMAN_ID)
8431. CREATE TABLE SALESMAN(SALESMAN_ID NUMBER(4),
844NAME VARCHAR(15),
845CITY VARCHAR(15),
846COMISSION NUMBER(7,2),
847CONSTRAINT PK_A PRIMARY KEY(SALESMAN_ID)
848);
849Table created.
850DESC SALESMAN;
851Name
852----------------
853SALESMAN_ID
854NAME
855CITY
856COMISSION
857Null?
858Type
859--------
860----------------------
861NOT NULL NUMBER(4)
862VARCHAR2(15)
863VARCHAR2(15)
864NUMBER(7,2)
8652. CREATE TABLE CUSTOMER(CUSTOMER_ID NUMBER(2),
866CUST_NAME VARCHAR(15),
867CITY VARCHAR(15),
868GRADE NUMBER(3),
869SALESMAN_ID NUMBER(4),
870CONSTRAINT PK_B PRIMARY KEY(CUSTOMER_ID),
871CONSTRAINT FK_D FOREIGN KEY(SALESMAN_ID) REFERENCES
872SALESMAN(SALESMAN_ID) ON DELETE SET NULL );
873Table created.
874DESC CUSTOMER;
875Name
876-------------------
877CUSTOMER_ID
878CUST_NAME
879CITY
880GRADE
881SALESMAN_ID
882Null?
883Type
884--------
885----------------------------
886NOT NULL NUMBER(2)
887VARCHAR2(15)
888VARCHAR2(15)
889NUMBER(3)
890NUMBER(4)
891P a g e 14 | 70DBMS LABORATORY WITH MINI PROJECT
89215CSL58
8933.CREATE TABLE ORDERS(ORD_NO NUMBER(4),
894PURCHASE_AMT NUMBER(10,2),
895ORD_DATE DATE,
896CUSTOMER_ID NUMBER(2),
897SALESMAN_ID NUMBER(4),
898CONSTRAINT PK_E PRIMARY KEY(ORD_NO),
899CONSTRAINT FK_G FOREIGN KEY(CUSTOMER_ID) REFERENCES
900CUSTOMER(CUSTOMER_ID) ON DELETE SET NULL,
901CONSTRAINT FK_H FOREIGN KEY(SALESMAN_ID) REFERENCES
902SALESMAN(SALESMAN_ID) ON DELETE SET NULL );
903Table created.
904SQL> DESC ORDERS;
905Name
906--------------------------
907ORD_NO
908PURCHASE_AMT
909ORD_DATE
910CUSTOMER_ID
911SALESMAN_ID
912Null?
913Type
914- -------- --------------------
915NOT NULL
916NUMBER(4)
917NUMBER(10,2)
918DATE
919NUMBER(2)
920NUMBER(4)
921Insert commands:
922INSERT INTO SALESMAN VALUES(&SALESMAN_ID,'&NAME','&CITY',&COMISSION);
923Enter value for salesman_id: 1000
924Enter value for name: RAMA
925Enter value for city: BANGALORE
926Enter value for comission: 10000.75
927old 1: INSERT INTO SALESMAN VALUES(&SALESMAN_ID,'&NAME','&CITY',&COMISSION)
928new 1: INSERT INTO SALESMAN VALUES(1000,'RAMA','BANGALORE',10000.75)
9291 row created.
930SQL> /
931Enter value for salesman_id: 2000
932Enter value for name: KRISHNA
933Enter value for city: MATHURA
934Enter value for comission: 20000.25
935old 1: INSERT INTO SALESMAN VALUES(&SALESMAN_ID,'&NAME','&CITY',&COMISSION)
936new 1: INSERT INTO SALESMAN VALUES(2000,'KRISHNA','MATHURA',20000.25)
9371 row created.
938SQL> /
939Enter value for salesman_id: 3000
940Enter value for name: SHIVA
941Enter value for city: AMARNATH
942Enter value for comission: 30000.30
943old 1: INSERT INTO SALESMAN VALUES(&SALESMAN_ID,'&NAME','&CITY',&COMISSION)
944new 1: INSERT INTO SALESMAN VALUES(3000,'SHIVA','AMARNATH',30000.30)
9451 row created.
946P a g e 15 | 70DBMS LABORATORY WITH MINI PROJECT
94715CSL58
948SQL> /
949Enter value for salesman_id: 4000
950Enter value for name: GOVINDA
951Enter value for city: TIRUPATHI
952Enter value for comission: 4000.40
953old 1: INSERT INTO SALESMAN VALUES(&SALESMAN_ID,'&NAME','&CITY',&COMISSION)
954new 1: INSERT INTO SALESMAN VALUES(4000,'GOVINDA','TIRUPATHI',4000.40)
9551 row created.
956SQL> /
957Enter value for salesman_id: 5000
958Enter value for name: NARAYANA
959Enter value for city: VELLORE
960Enter value for comission: 50000.50
961old 1: INSERT INTO SALESMAN VALUES(&SALESMAN_ID,'&NAME','&CITY',&COMISSION)
962new 1: INSERT INTO SALESMAN VALUES(5000,'NARAYANA','VELLORE',50000.50)
9631 row created.
964SELECT * FROM SALESMAN;
965SALESMAN_ID
966-----------
9671000
9682000
9693000
9704000
9715000
972NAME
973---------------
974RAMA
975KRISHNA
976SHIVA
977GOVINDA
978NARAYANA
979CITY
980-----------
981BANGALORE
982MATHURA
983AMARNATH
984TIRUPATHI
985VELLORE
986COMISSION
987----------
98810000.75
98920000.25
99030000.3
9914000.4
99250000.5
993INSERT INTO CUSTOMER
994VALUES(&CUSTOMER_ID,'&CUST_NAME','&CITY',&GRADE,&SALESMAN_ID);
995Enter value for customer_id: 10
996Enter value for cust_name: GANESH
997Enter value for city: BANGALORE
998Enter value for grade: 100
999Enter value for salesman_id: 1000
1000old 1: INSERT INTO CUSTOMER
1001VALUES(&CUSTOMER_ID,'&CUST_NAME','&CITY',&GRADE,&SALESMAN_ID)
1002new 1: INSERT INTO CUSTOMER VALUES(10,'GANESH','BANGALORE',100,1000)
10031 row created.
1004SQL> /
1005Enter value for customer_id: 20
1006Enter value for cust_name: SUDHEEP
1007Enter value for city: BANGALORE
1008Enter value for grade: 200
1009Enter value for salesman_id: 2000
1010old 1: INSERT INTO CUSTOMER
1011VALUES(&CUSTOMER_ID,'&CUST_NAME','&CITY',&GRADE,&SALESMAN_ID)
1012new 1: INSERT INTO CUSTOMER VALUES(20,'SUDHEEP','BANGALORE',200,2000)
1013P a g e 16 | 70DBMS LABORATORY WITH MINI PROJECT
101415CSL58
10151 row created.
1016SQL> /
1017Enter value for customer_id: 30
1018Enter value for cust_name: PRABHAS
1019Enter value for city: HYDERABAD
1020Enter value for grade: 300
1021Enter value for salesman_id: 3000
1022old 1: INSERT INTO CUSTOMER
1023VALUES(&CUSTOMER_ID,'&CUST_NAME','&CITY',&GRADE,&SALESMAN_ID)
1024new 1: INSERT INTO CUSTOMER VALUES(30,'PRABHAS','HYDERABAD',300,3000)
10251 row created.
1026SQL> /
1027Enter value for customer_id: 40
1028Enter value for cust_name: ARVIND
1029Enter value for city: CHENNAI
1030Enter value for grade: 400
1031Enter value for salesman_id: 4000
1032old 1: INSERT INTO CUSTOMER
1033VALUES(&CUSTOMER_ID,'&CUST_NAME','&CITY',&GRADE,&SALESMAN_ID)
1034new 1: INSERT INTO CUSTOMER VALUES(40,'ARVIND','CHENNAI',400,4000)
10351 row created.
1036SQL> /
1037Enter value for customer_id: 50
1038Enter value for cust_name: DARSHAN
1039Enter value for city: BANGALORE
1040Enter value for grade: 500
1041Enter value for salesman_id: 2000
1042old 1: INSERT INTO CUSTOMER
1043VALUES(&CUSTOMER_ID,'&CUST_NAME','&CITY',&GRADE,&SALESMAN_ID)
1044new 1: INSERT INTO CUSTOMER VALUES(50,'DARSHAN','BANGALORE',500,2000)
10451 row created.
1046SQL> /
1047Enter value for customer_id: 60
1048Enter value for cust_name: YASH
1049Enter value for city: BANGALORE
1050Enter value for grade: 600
1051Enter value for salesman_id: 1000
1052old 1: INSERT INTO CUSTOMER
1053VALUES(&CUSTOMER_ID,'&CUST_NAME','&CITY',&GRADE,&SALESMAN_ID)
1054new 1: INSERT INTO CUSTOMER VALUES(60,'YASH','BANGALORE',600,1000)
10551 row created.
1056P a g e 17 | 70DBMS LABORATORY WITH MINI PROJECT
105715CSL58
1058SELECT * FROM CUSTOMER;
1059CUSTOMER_ID
1060-----------
106110
106220
106330
106440
106550
106660
1067CUST_NAME
1068------------
1069GANESH
1070SUDHEEP
1071PRABHAS
1072ARVIND
1073DARSHAN
1074YASH
1075CITY
1076GRADE
1077-- ----------
1078---------------
1079BANGALORE
1080100
1081BANGALORE
1082200
1083HYDERABAD
1084300
1085CHENNAI
1086400
1087BANGALORE
1088500
1089BANGALORE
1090600
1091SALESMAN_ID
1092-----------
10931000
10942000
10953000
10964000
10972000
10981000
10996 rows selected.
1100INSERT INTO ORDERS
1101VALUES(&ORD_NO,&PURCHASE_AMT,'&ORD_DATE',&CUSTOMER_ID,&SALESMAN_ID);
1102Enter value for ord_no: 1111
1103Enter value for purchase_amt: 100000.00
1104Enter value for ord_date: 01-JAN-17
1105Enter value for customer_id: 10
1106Enter value for salesman_id: 2000
1107old 1: INSERT INTO ORDERS
1108VALUES(&ORD_NO,&PURCHASE_AMT,'&ORD_DATE',&CUSTOMER_ID,&SALESMAN_ID)
1109new 1: INSERT INTO ORDERS VALUES(1111,100000.00,'01-JAN-17',10,2000)
11101 row created.
1111SQL> /
1112Enter value for ord_no: 2222
1113Enter value for purchase_amt: 200000.00
1114Enter value for ord_date: 21-FEB-17
1115Enter value for customer_id: 20
1116Enter value for salesman_id: 3000
1117old 1: INSERT INTO ORDERS
1118VALUES(&ORD_NO,&PURCHASE_AMT,'&ORD_DATE',&CUSTOMER_ID,&SALESMAN_ID)
1119new 1: INSERT INTO ORDERS VALUES(2222,200000.00,'21-FEB-17',20,3000)
11201 row created.
1121SQL> /
1122Enter value for ord_no: 3333
1123Enter value for purchase_amt: 300000.00
1124Enter value for ord_date: 15-MAR-17
1125Enter value for customer_id: 30
1126Enter value for salesman_id: 4000
1127old 1: INSERT INTO ORDERS
1128VALUES(&ORD_NO,&PURCHASE_AMT,'&ORD_DATE',&CUSTOMER_ID,&SALESMAN_ID)
1129new 1: INSERT INTO ORDERS VALUES(3333,300000.00,'15-MAR-17',30,4000)
11301 row created.
1131SQL> /
1132Enter value for ord_no: 4444
1133Enter value for purchase_amt: 400000.00
1134P a g e 18 | 70DBMS LABORATORY WITH MINI PROJECT
113515CSL58
1136Enter value for ord_date: 18-APR-17
1137Enter value for customer_id: 40
1138Enter value for salesman_id: 5000
1139old 1: INSERT INTO ORDERS
1140VALUES(&ORD_NO,&PURCHASE_AMT,'&ORD_DATE',&CUSTOMER_ID,&SALESMAN_ID)
1141new 1: INSERT INTO ORDERS VALUES(4444,400000.00,'18-APR-17',40,5000)
11421 row created.
1143SQL> /
1144Enter value for ord_no: 5555
1145Enter value for purchase_amt: 500000.00
1146Enter value for ord_date: 12-MAY-17
1147Enter value for customer_id: 10
1148Enter value for salesman_id: 1000
1149old 1: INSERT INTO ORDERS
1150VALUES(&ORD_NO,&PURCHASE_AMT,'&ORD_DATE',&CUSTOMER_ID,&SALESMAN_ID)
1151new 1: INSERT INTO ORDERS VALUES(5555,500000.00,'12-MAY-17',10,1000)
11521 row created.
1153SQL> /
1154Enter value for ord_no: 6666
1155Enter value for purchase_amt: 600000.00
1156Enter value for ord_date: 12-MAY-17
1157Enter value for customer_id: 10
1158Enter value for salesman_id: 1000
1159old 1: INSERT INTO ORDERS
1160VALUES(&ORD_NO,&PURCHASE_AMT,'&ORD_DATE',&CUSTOMER_ID,&SALESMAN_ID)
1161new 1: INSERT INTO ORDERS VALUES(6666,600000.00,'12-MAY-17',10,1000)
11621 row created.
1163SELECT * FROM ORDERS;
1164ORD_NO PURCHASE_AMT
1165----------
1166------------
11671111
1168100000
11692222
1170200000
11713333
1172300000
11734444
1174400000
11755555
1176500000
11776666
1178600000
1179ORD_DATE CUSTOMER_ID SALESMAN_ID
1180---------
1181-----------
1182-----------
118301-JAN-17
118410
11852000
118621-FEB-17
118720
11883000
118915-MAR-17
119030
11914000
119218-APR-17
119340
11945000
119512-MAY-17
119610
11971000
119812-MAY-17
119910
12001000
1201QUERIES:
12021.Count the customers with grades above Banglore’s average
1203SELECT COUNT(CUSTOMER_ID)
1204FROM CUSTOMER
1205WHERE GRADE>( SELECT AVG(GRADE)
1206FROM CUSTOMER
1207WHERE CITY='BANGALORE' );
1208P a g e 19 | 70DBMS LABORATORY WITH MINI PROJECT
120915CSL58
1210Output:
1211COUNT(CUSTOMER_ID)
1212------------------
12133
12142.Find the names and numbers of all salesman who had more than one customer.
1215SELECT S.NAME,S.SALESMAN_ID
1216FROM SALESMAN S,CUSTOMER C
1217WHERE S.SALESMAN_ID=C.SALESMAN_ID
1218GROUP BY S.NAME,S.SALESMAN_ID
1219HAVING COUNT(C.CUSTOMER_ID)>1;
1220Output:
1221NAME
1222SALESMAN_ID
1223--------------- -----------
1224RAMA
12251000
1226KRISHNA
12272000
12283.List all salesman and indicate those who have and don’t have customers in their cities. Use union
1229operation.
1230(SELECT S.SALESMAN_ID,S.NAME,C.CUST_NAME
1231FROM SALESMAN S,CUSTOMER C
1232WHERE S.CITY=C.CITY AND S.SALESMAN_ID=C.SALESMAN_ID)
1233UNION
1234(SELECT S1.SALESMAN_ID,S1.NAME,'NO CUSTOMER'
1235FROM SALESMAN S1,CUSTOMER C1
1236WHERE S1.CITY!=C1.CITY AND S1.SALESMAN_ID=C1.SALESMAN_ID );
1237Output:
1238SALESMAN_ID
1239NAME
1240-----------
1241---------------
12421000
1243RAMA
12441000
1245RAMA
12462000
1247KRISHNA
12483000
1249SHIVA
12504000
1251GOVINDA
1252CUST_NAME
1253---------------
1254GANESH
1255YASH
1256NO CUSTOMER
1257NO CUSTOMER
1258NO CUSTOMER
1259P a g e 20 | 70DBMS LABORATORY WITH MINI PROJECT
126015CSL58
12614.Create a view that finds the salesman who have the customer with the highest order of a day.
1262CONNECT SYSTEM/MANJUNATH;
1263Connected.
1264GRANT CREATE VIEW TO B2;
1265Grant succeeded.
1266CONNECT B2;
1267Enter password: **
1268Connected.
1269CREATE VIEW HIGH_ORDER_DAY AS
1270SELECT O.ORD_DATE,S.SALESMAN_ID,S.NAME,C.CUST_NAME,O.PURCHASE_AMT
1271FROM ORDERS O,SALESMAN S,CUSTOMER C
1272WHERE O.SALESMAN_ID=S.SALESMAN_ID AND C.CUSTOMER_ID=O.CUSTOMER_ID;
1273View created.
1274SELECT *
1275FROM HIGH_ORDER_DAY H
1276WHERE H.PURCHASE_AMT=(SELECT MAX(H1.PURCHASE_AMT)
1277FROM HIGH_ORDER_DAY H1
1278WHERE H1.ORD_DATE=H.ORDER_DATE);
1279Output:
1280PURCHASE_AMT ORD_DATE CUST_NAME SALESMAN_ID NAME
1281------------
1282---------
1283-----------
1284-----------
1285----------------
1286100000
128701-JAN-17
1288GANESH
12892000
1290KRISHNA
1291200000
129221-FEB-17
1293SUDHEEP
12943000
1295SHIVA
1296300000
129715-MAR-17
1298PRABHAS
12994000
1300GOVINDA
1301400000
130218-APR-17
1303ARVIND
13045000
1305NARAYANA
1306600000
130712-MAY-17
1308GANESH
13091000
1310RAMA
13115.Demonstrate the delete operation by removing salesman with ID 1000, all their orders must also
1312be deleted.
1313DELETE FROM SALESMAN WHERE SALESMAN_ID=1000;
13141 row deleted.
1315Output:
1316SQL> SELECT * FROM CUSTOMER;
1317CUSTOMER_ID
1318CUST_NAME
1319CITY
1320-----------
1321---------------
1322---------------
132310
1324GANESH
1325BANGALORE
132620
1327SUDHEEP
1328BANGALORE
132930
1330PRABHAS
1331HYDERABAD
133240
1333ARVIND
1334CHENNAI
133550
1336DARSHAN
1337BANGALORE
133860
1339YASH
1340BANGALORE
1341GRADE
1342----------
1343100
1344200
1345300
1346400
1347500
1348600
1349SALESMAN_ID
1350-----------
13512000
13523000
13534000
13542000
13556 rows selected.
1356P a g e 21 | 70DBMS LABORATORY WITH MINI PROJECT
135715CSL58
1358SQL> SELECT * FROM ORDERS;
1359ORD_NO
1360----------
13611111
13622222
13633333
13644444
13655555
1366PURCHASE_AMT
1367------------
1368100000
1369200000
1370300000
1371400000
1372500000
1373ORD_DATE CUSTOMER_ID
1374---------
1375-----------
137601-JAN-17
137710
137821-FEB-17
137920
138015-MAR-17
138130
138218-APR-17
138340
138412-MAY-17
138510
1386SALESMAN_ID
1387-----------
13882000
13893000
13904000
13915000
1392P a g e 22 | 70DBMS LABORATORY WITH MINI PROJECT
139315CSL58
1394MOVIE DATABASE
1395ACTOR(ACT_ID,ACT_NAME,ACT_GENDER)
1396DIRECTOR(DIR_ID,DIR_NAME,DIR_PHONE)
1397MOVIES(MOV_ID,MOV_TITLE,MOV_YEAR,MOV_LANG,DIR_ID)
1398MOVIE_CAST(ACT_ID,MOV_ID,ROLE)
1399RATING(MOV_ID,REV_STARS)
14001. CREATE TABLE ACTOR(ACT_ID NUMBER(2),
1401ACT_NAME VARCHAR(15),
1402ACT_GENDER CHAR,
1403CONSTRAINT PK_AID PRIMARY KEY(ACT_ID)
1404);
1405Table created.
1406SQL> DESC ACTOR;
1407Name
1408Null?
1409Type
1410-------------------
1411-------------
1412--------------------
1413ACT_ID
1414NOT NULL NUMBER(2)
1415ACT_NAME
1416VARCHAR2(15)
1417ACT_GENDER
1418CHAR(1)
14192. CREATE TABLE DIRECTOR(DIR_ID NUMBER(2),
1420DIR_NAME VARCHAR(20),
1421DIR_PHONE NUMBER(10),
1422CONSTRAINT PK_DID PRIMARY KEY(DIR_ID)
1423);
1424Table created.
1425SQL> DESC DIRECTOR;
1426Name
1427Null?
1428Type
1429----------------------------------------- -------- ----------------------------
1430DIR_ID
1431NOT NULL NUMBER(2)
1432DIR_NAME
1433VARCHAR2(20)
1434DIR_PHONE
1435NUMBER(10)
14363. CREATE TABLE MOVIES(MOV_ID NUMBER(3),
1437MOV_TITLE VARCHAR(25),
1438MOV_YEAR NUMBER(4),
1439MOV_LANG VARCHAR(15),
1440DIR_ID NUMBER(2),
1441CONSTRAINT PK_MID PRIMARY KEY(MOV_ID),
1442CONSTRAINT FK_DIR FOREIGN KEY(DIR_ID) REFERENCES DIRECTOR(DIR_ID) ON
1443DELETE CASCADE
1444);
1445Table created.
1446P a g e 23 | 70DBMS LABORATORY WITH MINI PROJECT
144715CSL58
1448SQL> DESC MOVIES;
1449Name
1450Null?
1451Type
1452----------------------------------------- -------- ----------------------------
1453MOV_ID
1454NOT NULL NUMBER(3)
1455MOV_TITLE
1456VARCHAR2(25)
1457MOV_YEAR
1458NUMBER(4)
1459MOV_LANG
1460VARCHAR2(15)
1461DIR_ID
1462NUMBER(2)
14634. CREATE TABLE MOVIE_CAST(ACT_ID NUMBER(2),
1464MOV_ID NUMBER(3),
1465ROLE VARCHAR(20),
1466CONSTRAINT CPK_AM PRIMARY KEY(ACT_ID,MOV_ID),
1467CONSTRAINT FK_MA FOREIGN KEY(ACT_ID) REFERENCES ACTOR(ACT_ID) ON
1468DELETE CASCADE,
1469CONSTRAINT FK_MD FOREIGN KEY(MOV_ID) REFERENCES MOVIES(MOV_ID) ON
1470DELETE CASCADE
1471);
1472Table created
1473SQL> DESC MOVIE_CAST;
1474Name
1475Null?
1476Type
1477----------------------------------------- -------- ----------------------------
1478ACT_ID
1479NOT NULL
1480NUMBER(2)
1481MOV_ID
1482NOT NULL
1483NUMBER(3)
1484ROLE
1485VARCHAR2(20)
14865. CREATE TABLE RATING(MOV_ID NUMBER(3),
1487REV_STARS NUMBER(1),
1488CONSTRAINT CPK_MCT PRIMARY KEY(MOV_ID,REV_STARS),
1489CONSTRAINT FK_MCD FOREIGN KEY(MOV_ID) REFERENCES MOVIES(MOV_ID) ON
1490DELETE CASCADE
1491);
1492Table created.
1493SQL> DESC RATING;
1494Name
1495Null?
1496Type
1497----------------------------------------- -------- ----------------------------
1498MOV_ID
1499NOT NULL
1500NUMBER(3)
1501REV_STARS
1502NOT NULL
1503NUMBER(1)
1504INSERTION COMMANDS:
1505INSERT INTO ACTOR VALUES(&ACT_ID,'&ACT_NAME','&ACT_GENDER');
1506Enter value for act_id: 10
1507Enter value for act_name: AAYUSHMAN
1508Enter value for act_gender: M
1509old 1: INSERT INTO ACTOR VALUES(&ACT_ID,'&ACT_NAME','&ACT_GENDER')
1510new 1: INSERT INTO ACTOR VALUES(10,'AAYUSHMAN','M')
15111 row created.
1512P a g e 24 | 70DBMS LABORATORY WITH MINI PROJECT
151315CSL58
1514SQL> /
1515Enter value for act_id: 20
1516Enter value for act_name: VARUN DHAWAN
1517Enter value for act_gender: M
1518old 1: INSERT INTO ACTOR VALUES(&ACT_ID,'&ACT_NAME','&ACT_GENDER')
1519new 1: INSERT INTO ACTOR VALUES(20,'VARUN DHAWAN','M')
15201 row created.
1521SQL> /
1522Enter value for act_id: 30
1523Enter value for act_name: DEEPIKA
1524Enter value for act_gender: F
1525old 1: INSERT INTO ACTOR VALUES(&ACT_ID,'&ACT_NAME','&ACT_GENDER')
1526new 1: INSERT INTO ACTOR VALUES(30,'DEEPIKA','F')
15271 row created.
1528SQL> /
1529Enter value for act_id: 40
1530Enter value for act_name: CHRIS PRATT
1531Enter value for act_gender: M
1532old 1: INSERT INTO ACTOR VALUES(&ACT_ID,'&ACT_NAME','&ACT_GENDER')
1533new 1: INSERT INTO ACTOR VALUES(40,'CHRIS PRATT','M')
15341 row created.
1535SQL> /
1536Enter value for act_id: 50
1537Enter value for act_name: ANTHONY PERKINS
1538Enter value for act_gender: M
1539old 1: INSERT INTO ACTOR VALUES(&ACT_ID,'&ACT_NAME','&ACT_GENDER')
1540new 1: INSERT INTO ACTOR VALUES(50,'ANTHONY PERKINS','M')
15411 row created.
1542SQL> /
1543Enter value for act_id: 60
1544Enter value for act_name: SHRADDHA
1545Enter value for act_gender: F
1546old 1: INSERT INTO ACTOR VALUES(&ACT_ID,'&ACT_NAME','&ACT_GENDER')
1547new 1: INSERT INTO ACTOR VALUES(60,'SHRADDHA','F')
15481 row created.
1549SQL> SELECT * FROM ACTOR;
1550ACT_ID
1551----------
155210
155320
155430
155540
155650
155760
15586 rows selected.
1559ACT_NAME
1560ACT_GENDER
1561---------------
1562---------------
1563AAYUSHMAN
1564M
1565VARUN DHAWAN
1566M
1567DEEPIKA
1568F
1569CHRIS PRATT
1570M
1571ANTHONY PERKINS
1572M
1573SHRADDHA
1574F
1575P a g e 25 | 70DBMS LABORATORY WITH MINI PROJECT
157615CSL58
1577INSERT INTO DIRECTOR VALUES(&DIR_ID,'&ADIR_NAME',&DIR_PHONE);
1578Enter value for dir_id: 11
1579Enter value for adir_name: SOOJITH
1580Enter value for dir_phone: 1020304050
1581old 1: INSERT INTO DIRECTOR VALUES(&DIR_ID,'&ADIR_NAME',&DIR_PHONE)
1582new 1: INSERT INTO DIRECTOR VALUES(11,'SOOJITH',1020304050)
15831 row created.
1584SQL> /
1585Enter value for dir_id: 22
1586Enter value for adir_name: STEVEN SPIELBERG
1587Enter value for dir_phone: 1122334455
1588old 1: INSERT INTO DIRECTOR VALUES(&DIR_ID,'&ADIR_NAME',&DIR_PHONE)
1589new 1: INSERT INTO DIRECTOR VALUES(22,'STEVEN SPIELBERG',1122334455)
15901 row created.
1591SQL> /
1592Enter value for dir_id: 33
1593Enter value for adir_name: DAVID
1594Enter value for dir_phone: 9966443322
1595old 1: INSERT INTO DIRECTOR VALUES(&DIR_ID,'&ADIR_NAME',&DIR_PHONE)
1596new 1: INSERT INTO DIRECTOR VALUES(33,'DAVID',9966443322)
15971 row created.
1598SQL> /
1599Enter value for dir_id: 44
1600Enter value for adir_name: HITCH COCK
1601Enter value for dir_phone: 1002003000
1602old 1: INSERT INTO DIRECTOR VALUES(&DIR_ID,'&ADIR_NAME',&DIR_PHONE)
1603new 1: INSERT INTO DIRECTOR VALUES(44,'HITCH COCK',1002003000)
16041 row created.
1605SQL> /
1606Enter value for dir_id: 55
1607Enter value for adir_name: BANSALI
1608Enter value for dir_phone: 9080706050
1609old 1: INSERT INTO DIRECTOR VALUES(&DIR_ID,'&ADIR_NAME',&DIR_PHONE)
1610new 1: INSERT INTO DIRECTOR VALUES(55,'BANSALI',9080706050)
16111 row created.
1612SQL> /
1613Enter value for dir_id: 66
1614Enter value for adir_name: REMO
1615Enter value for dir_phone: 9988776655
1616old 1: INSERT INTO DIRECTOR VALUES(&DIR_ID,'&ADIR_NAME',&DIR_PHONE)
1617new 1: INSERT INTO DIRECTOR VALUES(66,'REMO',9988776655)
16181 row created.
1619P a g e 26 | 70DBMS LABORATORY WITH MINI PROJECT
162015CSL58
1621SQL> SELECT * FROM DIRECTOR;
1622DIR_ID
1623----------
162411
162522
162633
162744
162855
162966
1630DIR_NAME
1631------------------- -
1632SOOJITH
1633STEVEN SPIELBERG
1634DAVID
1635HITCH COCK
1636BANSALI
1637REMO
1638DIR_PHONE
1639----------
16401020304050
16411122334455
16429966443322
16431002003000
16449080706050
16459988776655
16466 rows selected.
1647INSERT INTO MOVIES
1648VALUES(&MOV_ID,'&MOV_TITLE',&MOV_YEAR,'&MOV_LANG',&DIR_ID);
1649Enter value for mov_id: 111
1650Enter value for mov_title: ABCD2
1651Enter value for mov_year: 1999
1652Enter value for mov_lang: HINDI
1653Enter value for dir_id: 66
1654old 1: INSERT INTO MOVIES
1655VALUES(&MOV_ID,'&MOV_TITLE',&MOV_YEAR,'&MOV_LANG',&DIR_ID)
1656new 1: INSERT INTO MOVIES VALUES(111,'ABCD2',1999,'HINDI',66)
16571 row created.
1658SQL> \
1659Enter value for mov_id: 222
1660Enter value for mov_title: PSYCHO
1661Enter value for mov_year: 1995
1662Enter value for mov_lang: ENGLISH
1663Enter value for dir_id: 44
1664old 1: INSERT INTO MOVIES
1665VALUES(&MOV_ID,'&MOV_TITLE',&MOV_YEAR,'&MOV_LANG',&DIR_ID)
1666new 1: INSERT INTO MOVIES VALUES(222,'PSYCHO',1995,'ENGLISH',44)
16671 row created.
1668SQL> /
1669Enter value for mov_id: 333
1670Enter value for mov_title: BAREILLI KI BURFI
1671Enter value for mov_year: 2017
1672Enter value for mov_lang: HINDI
1673Enter value for dir_id: 11
1674old 1: INSERT INTO MOVIES
1675VALUES(&MOV_ID,'&MOV_TITLE',&MOV_YEAR,'&MOV_LANG',&DIR_ID)
1676new 1: INSERT INTO MOVIES VALUES(333,'BAREILLI KI BURFI',2017,'HINDI',11)
16771 row created.
1678SQL> /
1679Enter value for mov_id: 444
1680Enter value for mov_title: RAMLEELA
1681Enter value for mov_year: 2015
1682P a g e 27 | 70DBMS LABORATORY WITH MINI PROJECT
168315CSL58
1684Enter value for mov_lang: HINDI
1685Enter value for dir_id: 55
1686old 1: INSERT INTO MOVIES
1687VALUES(&MOV_ID,'&MOV_TITLE',&MOV_YEAR,'&MOV_LANG',&DIR_ID)
1688new 1: INSERT INTO MOVIES VALUES(444,'RAMLEELA',2015,'HINDI',55)
16891 row created.
1690SQL> /
1691Enter value for mov_id: 555
1692Enter value for mov_title: MEIN TERA HERO
1693Enter value for mov_year: 2014
1694Enter value for mov_lang: HINDI
1695Enter value for dir_id: 33
1696old 1: INSERT INTO MOVIES
1697VALUES(&MOV_ID,'&MOV_TITLE',&MOV_YEAR,'&MOV_LANG',&DIR_ID)
1698new 1: INSERT INTO MOVIES VALUES(555,'MEIN TERA HERO',2014,'HINDI',33)
16991 row created.
1700SQL> /
1701Enter value for mov_id: 666
1702Enter value for mov_title: JURASSIC PARK
1703Enter value for mov_year: 2000
1704Enter value for mov_lang: ENGLISH
1705Enter value for dir_id: 22
1706old 1: INSERT INTO MOVIES
1707VALUES(&MOV_ID,'&MOV_TITLE',&MOV_YEAR,'&MOV_LANG',&DIR_ID)
1708new 1: INSERT INTO MOVIES VALUES(666,'JURASSIC PARK',2000,'ENGLISH',22)
17091 row created.
1710SQL> /
1711Enter value for mov_id: 777
1712Enter value for mov_title: VICKY DONOR
1713Enter value for mov_year: 2011
1714Enter value for mov_lang: HINDI
1715Enter value for dir_id: 11
1716old 1: INSERT INTO MOVIES
1717VALUES(&MOV_ID,'&MOV_TITLE',&MOV_YEAR,'&MOV_LANG',&DIR_ID)
1718new 1: INSERT INTO MOVIES VALUES(777,'VICKY DONOR',2011,'HINDI',11)
17191 row created.
1720SQL> SELECT * FROM MOVIES;
1721MOV_ID
1722MOV_TITLE
1723MOV_YEAR
1724---------- -------------------------
1725----------
1726111
1727ABCD2
17281999
1729222
1730PSYCHO
17311995
1732333
1733BAREILLI KI BURFI 2017
1734444
1735RAMLEELA
17362015
1737555
1738MEIN TERA HERO
17392014
1740666
1741JURASSIC PARK
17422000
1743777
1744VICKY DONOR
17452011
1746MOV_LANG
1747---------------
1748HINDI
1749ENGLISH
1750HINDI
1751HINDI
1752HINDI
1753ENGLISH
1754HINDI
1755DIR_ID
1756----------
175766
175844
175911
176055
176133
176222
176311
1764P a g e 28 | 70DBMS LABORATORY WITH MINI PROJECT
176515CSL58
17667 rows selected.
1767INSERT INTO MOVIE_CAST VALUES(&ACT_ID,&MOV_ID,'&ROLE');
1768Enter value for act_id: 10
1769Enter value for mov_id: 333
1770Enter value for role: HERO
1771old 1: INSERT INTO MOVIE_CAST VALUES(&ACT_ID,&MOV_ID,'&ROLE')
1772new 1: INSERT INTO MOVIE_CAST VALUES(10,333,'HERO')
17731 row created.
1774SQL> /
1775Enter value for act_id: 20
1776Enter value for mov_id: 555
1777Enter value for role: HERO
1778old 1: INSERT INTO MOVIE_CAST VALUES(&ACT_ID,&MOV_ID,'&ROLE')
1779new 1: INSERT INTO MOVIE_CAST VALUES(20,555,'HERO')
17801 row created.
1781SQL> /
1782Enter value for act_id: 30
1783Enter value for mov_id: 444
1784Enter value for role: HEROINE
1785old 1: INSERT INTO MOVIE_CAST VALUES(&ACT_ID,&MOV_ID,'&ROLE')
1786new 1: INSERT INTO MOVIE_CAST VALUES(30,444,'HEROINE')
17871 row created.
1788SQL> /
1789Enter value for act_id: 40
1790Enter value for mov_id: 666
1791Enter value for role: HERO
1792old 1: INSERT INTO MOVIE_CAST VALUES(&ACT_ID,&MOV_ID,'&ROLE')
1793new 1: INSERT INTO MOVIE_CAST VALUES(40,666,'HERO')
17941 row created.
1795SQL> /
1796Enter value for act_id: 50
1797Enter value for mov_id: 222
1798Enter value for role: VILLAIN
1799old 1: INSERT INTO MOVIE_CAST VALUES(&ACT_ID,&MOV_ID,'&ROLE')
1800new 1: INSERT INTO MOVIE_CAST VALUES(50,222,'VILLAIN')
18011 row created.
1802SQL> /
1803Enter value for act_id: 60
1804Enter value for mov_id: 111
1805Enter value for role: HEROINE
1806old 1: INSERT INTO MOVIE_CAST VALUES(&ACT_ID,&MOV_ID,'&ROLE')
1807new 1: INSERT INTO MOVIE_CAST VALUES(60,111,'HEROINE')
18081 row created.
1809P a g e 29 | 70DBMS LABORATORY WITH MINI PROJECT
181015CSL58
1811SQL> /
1812Enter value for act_id: 20
1813Enter value for mov_id: 111
1814Enter value for role: HERO
1815old 1: INSERT INTO MOVIE_CAST VALUES(&ACT_ID,&MOV_ID,'&ROLE')
1816new 1: INSERT INTO MOVIE_CAST VALUES(20,111,'HERO')
18171 row created.
1818SQL> /
1819Enter value for act_id: 10
1820Enter value for mov_id: 777
1821Enter value for role: HERO
1822old 1: INSERT INTO MOVIE_CAST VALUES(&ACT_ID,&MOV_ID,'&ROLE')
1823new 1: INSERT INTO MOVIE_CAST VALUES(10,777,'HERO')
18241 row created.
1825SQL> /
1826Enter value for act_id: 60
1827Enter value for mov_id: 333
1828Enter value for role: HEROINE
1829old 1: INSERT INTO MOVIE_CAST VALUES(&ACT_ID,&MOV_ID,'&ROLE')
1830new 1: INSERT INTO MOVIE_CAST VALUES(60,333,'HEROINE')
18311 row created.
1832SQL> SELECT * FROM MOVIE_CAST;
1833ACT_ID MOV_ID ROLE
1834---------- ---------- --------------------
183510
1836333
1837HERO
183820
1839555
1840HERO
184130
1842444
1843HEROINE
184440
1845666
1846HERO
184750
1848222
1849VILLAIN
185060
1851111
1852HEROINE
185320
1854111
1855HERO
185610
1857777
1858HERO
185960
1860333
1861HEROINE
1862SQL> INSERT INTO RATING VALUES(&MOV_ID,&REV_STARS);
1863Enter value for mov_id: 111
1864Enter value for rev_stars: 3
1865old 1: INSERT INTO RATING VALUES(&MOV_ID,&REV_STARS)
1866new 1: INSERT INTO RATING VALUES(111,3)
18671 row created.
1868SQL> /
1869Enter value for mov_id: 222
1870Enter value for rev_stars: 2
1871P a g e 30 | 70DBMS LABORATORY WITH MINI PROJECT
187215CSL58
1873old 1: INSERT INTO RATING VALUES(&MOV_ID,&REV_STARS)
1874new 1: INSERT INTO RATING VALUES(222,2)
18751 row created.
1876SQL> /
1877Enter value for mov_id: 333
1878Enter value for rev_stars: 0
1879old 1: INSERT INTO RATING VALUES(&MOV_ID,&REV_STARS)
1880new 1: INSERT INTO RATING VALUES(333,0)
18811 row created.
1882SQL> /
1883Enter value for mov_id: 444
1884Enter value for rev_stars: 4
1885old 1: INSERT INTO RATING VALUES(&MOV_ID,&REV_STARS)
1886new 1: INSERT INTO RATING VALUES(444,4)
18871 row created.
1888SQL> /
1889Enter value for mov_id: 555
1890Enter value for rev_stars: 3
1891old 1: INSERT INTO RATING VALUES(&MOV_ID,&REV_STARS)
1892new 1: INSERT INTO RATING VALUES(555,3)
18931 row created.
1894SQL> /
1895Enter value for mov_id: 666
1896Enter value for rev_stars: 2
1897old 1: INSERT INTO RATING VALUES(&MOV_ID,&REV_STARS)
1898new 1: INSERT INTO RATING VALUES(666,2)
18991 row created.
1900SQL> /
1901Enter value for mov_id: 222
1902Enter value for rev_stars: 3
1903old 1: INSERT INTO RATING VALUES(&MOV_ID,&REV_STARS)
1904new 1: INSERT INTO RATING VALUES(222,3)
19051 row created.
1906SQL> /
1907Enter value for mov_id: 111
1908Enter value for rev_stars: 5
1909old 1: INSERT INTO RATING VALUES(&MOV_ID,&REV_STARS)
1910new 1: INSERT INTO RATING VALUES(111,5)
19111 row created.
1912SQL> /
1913Enter value for mov_id: 444
1914Enter value for rev_stars: 5
1915old 1: INSERT INTO RATING VALUES(&MOV_ID,&REV_STARS)
1916new 1: INSERT INTO RATING VALUES(444,5)
1917P a g e 31 | 70DBMS LABORATORY WITH MINI PROJECT
191815CSL58
19191 row created.
1920SQL> /
1921Enter value for mov_id: 777
1922Enter value for rev_stars: 4
1923old 1: INSERT INTO RATING VALUES(&MOV_ID,&REV_STARS)
1924new 1: INSERT INTO RATING VALUES(777,4)
19251 row created.
1926SQL> SELECT * FROM RATING;
1927MOV_ID
1928----------
1929111
1930111
1931222
1932222
1933333
1934444
1935444
1936555
1937666
1938777
1939REV_STARS
1940----------
19413
19425
19432
19443
19450
19464
19475
19483
19492
19504
195110 rows selected.
1952QUERIES:
19531.List the titles of all movies directed by hitch cock.
1954SELECT M.MOV_TITLE
1955FROM MOVIES M,DIRECTOR D
1956WHERE D.DIR_ID=M.DIR_ID AND D.DIR_NAME='HITCH COCK';
1957Output:
1958MOV_TITLE
1959-------------------------
1960PSYCHO
19612.Find the movie names where one or more actors acted in two ormore movies.
1962SELECT M.MOV_TITLE,A.ACT_NAME
1963FROM MOVIES M,ACTOR A,MOVIE_CAST M1
1964WHERE M.MOV_ID=M1.MOV_ID AND
1965M1.ACT_ID=A.ACT_ID AND
1966M1.ACT_ID IN( SELECT ACT_ID
1967FROM MOVIE_CAST
1968GROUP BY ACT_ID
1969HAVING COUNT(MOV_ID)>1);
1970P a g e 32 | 70DBMS LABORATORY WITH MINI PROJECT
197115CSL58
1972Output:
1973MOV_TITLE
1974ACT_NAME
1975------------------------- ---------------
1976MEIN TERA HERO
1977VARUN DHAWAN
1978ABCD2
1979VARUN DHAWAN
1980VICKY DONOR
1981AAYUSHMAN
1982BAREILLI KI BURFI
1983AAYUSHMAN
1984BAREILLI KI BURFI
1985SHRADDHA
1986ABCD2
1987SHRADDHA
19886 rows selected.
19893.List all actors who acted in a movie before 2000 and also in a movie after 2015.(Use JOIN
1990operator).
1991(SELECT A.ACT_NAME
1992FROM ACTOR A JOIN MOVIE_CAST M ON A.ACT_ID=M.ACT_ID
1993JOIN MOVIES M1 ON M.MOV_ID=M1.MOV_ID
1994WHERE M1.MOV_YEAR<2000 )
1995INTERSECT
1996(SELECT A.ACT_NAME
1997FROM ACTOR A JOIN MOVIE_CAST M ON A.ACT_ID=M.ACT_ID
1998JOIN MOVIES M1 ON M.MOV_ID=M1.MOV_ID
1999WHERE M1.MOV_YEAR>2015 );
2000Output:
2001ACT_NAME
2002---------------
2003SHRADDHA
20044.Find the title of movies and no. of stars for each movie that has atleast one rating and find the
2005highest no. of stars that movie received. Sort the result by movie titkle.
2006SELECT M.MOV_TITLE,MAX(R.REV_STARS)
2007FROM MOVIES M,RATING R
2008WHERE M.MOV_ID=R.MOV_ID AND
2009M.MOV_ID IN(SELECT MOV_ID
2010FROM RATING
2011GROUP BY MOV_ID,REV_STARS
2012HAVING REV_STARS>0 )
2013GROUP BY M.MOV_TITLE
2014ORDER BY M.MOV_TITLE;
2015Output:
2016MOV_TITLE
2017------------------
2018ABCD2
2019JURASSIC PARK
2020MEIN TERA HERO
2021PSYCHO
2022RAMLEELA
2023VICKY DONOR
2024MAX(R.REV_STARS)
2025----------------
20265
20272
20283
20293
20305
20314
2032P a g e 33 | 70DBMS LABORATORY WITH MINI PROJECT
203315CSL58
20346 rows selected.
20355.Update ratings of all movies directed by STEVEN SPIELBERG.
2036UPDATE RATING SET REV_STARS=5
2037WHERE MOV_ID IN ( SELECT MOV_ID
2038FROM MOVIES
2039WHERE DIR_ID=(SELECT DIR_ID
2040FROM DIRECTOR
2041WHERE DIR_NAME='STEVEN SPIELBERG' )
2042);
20431 row updated.
2044Output:
2045SQL> SELECT * FROM RATING;
2046MOV_ID REV_STARS
2047---------- ----------
2048111
20493
2050111
20515
2052222
20532
2054222
20553
2056333
20570
2058444
20594
2060444
20615
2062555
20633
2064666
20655
2066777
20674
206810 rows selected.
2069P a g e 34 | 70DBMS LABORATORY WITH MINI PROJECT
207015CSL58
2071COLLEGE DATABASE
2072STUDENT (USN,SNAME,ADDRESS,PHONE,GENDER)
2073SEMSEC (SSID,SEM,SEC)
2074CLASS (USN,SSID)
2075SUBJECT (SUBCODE,TITLE,SEM,CREDITS)
2076IAMARKS (USN,SUBCODE,SSID,TEST1,TEST2,TEST3,FINALIA)
2077CREATE TABLE COMMANDS:
20781. CREATE TABLE STUDENT(USN CHAR(10),
2079SNAME VARCHAR(20),
2080ADDRESS VARCHAR(25),
2081PHONE NUMBER(10),
2082GENDER CHAR,
2083CONSTRAINT A PRIMARY KEY(USN)
2084);
2085Table created.
2086SQL> DESC STUDENT;
2087Name
2088Null?
2089Type
2090----------------------------------------- -------- ----------------------------
2091USN
2092NOT NULL
2093CHAR(10)
2094SNAME
2095VARCHAR2(20)
2096ADDRESS
2097VARCHAR2(25)
2098PHONE
2099NUMBER(10)
2100GENDER
2101CHAR(1)
21022. CREATE TABLE SEMSEC(SSID CHAR(2),
2103SEM NUMBER(1),
2104SEC CHAR,
2105CONSTRAINT B PRIMARY KEY(SSID),
2106CONSTRAINT C CHECK(SEM BETWEEN 1 AND 8)
2107);
2108Table created.
2109SQL> DESC SEMSEC;
2110Name
2111Null?
2112Type
2113----------------------------------------- -------- ----------------------------
2114SSID
2115NOT NULL
2116CHAR(2)
2117SEM
2118NUMBER(1)
2119SEC
2120CHAR(1)
21213. CREATE TABLE CLASS(USN CHAR(10),
2122SSID CHAR(2),
2123CONSTRAINT D PRIMARY KEY(USN,SSID),
2124CONSTRAINT E FOREIGN KEY(USN) REFERENCES STUDENT(USN) ON DELETE
2125CASCADE,
2126CONSTRAINT F FOREIGN KEY(SSID) REFERENCES SEMSEC(SSID) ON DELETE CASCADE
2127);
2128Table created.
2129P a g e 35 | 70DBMS LABORATORY WITH MINI PROJECT
213015CSL58
2131SQL> DESC CLASS;
2132Name
2133Null?
2134Type
2135----------------------------------------- -------- ----------------------------
2136USN
2137NOT NULL
2138CHAR(10)
2139SSID
2140NOT NULL
2141CHAR(2)
21424. CREATE TABLE SUBJECT(SUBCODE VARCHAR(7),
2143TITLE VARCHAR(20),
2144SEM NUMBER(1),
2145CREDITS NUMBER(1),
2146CONSTRAINT G PRIMARY KEY(SUBCODE)
2147);
2148Table created.
2149SQL> DESC SUBJECT;
2150Name
2151Null?
2152Type
2153----------------------------------------- -------- ----------------------------
2154SUBCODE
2155NOT NULL
2156VARCHAR2(7)
2157TITLE
2158VARCHAR2(20)
2159SEM
2160NUMBER(1)
2161CREDITS
2162NUMBER(1)
21635. CREATE TABLE IAMARKS(USN CHAR(10),
2164SUBCODE VARCHAR(7),
2165SSID CHAR(2),
2166TEST1 NUMBER(2),
2167TEST2 NUMBER(2),
2168TEST3 NUMBER(2),
2169FINALIA NUMBER(2),
2170CONSTRAINT H PRIMARY KEY(USN,SUBCODE,SSID),
2171CONSTRAINT I FOREIGN KEY(USN) REFERENCES STUDENT(USN) ON DELETE CASCADE,
2172CONSTRAINT J FOREIGN KEY(SSID) REFERENCES SEMSEC(SSID) ON DELETE CASCADE,
2173CONSTRAINT K FOREIGN KEY(SUBCODE) REFERENCES SUBJECT(SUBCODE) ON DELETE
2174CASCADE
2175);
2176Table created.
2177SQL> DESC IAMARKS;
2178Name
2179Null?
2180Type
2181----------------------------------------- -------- ----------------------------
2182USN
2183NOT NULL
2184CHAR(10)
2185SUBCODE
2186NOT NULL
2187VARCHAR2(7)
2188SSID
2189NOT NULL
2190CHAR(2)
2191TEST1
2192NUMBER(2)
2193TEST2
2194NUMBER(2)
2195TEST3
2196NUMBER(2)
2197FINALIA
2198NUMBER(2)
2199P a g e 36 | 70DBMS LABORATORY WITH MINI PROJECT
220015CSL58
2201INSERTION COMMANDS:
2202SQL> INSERT INTO STUDENT
2203VALUES('&USN','&SNAME','&ADDRESS',&PHONE,'&GENDER');
2204Enter value for usn: 1MV17CS001
2205Enter value for sname: AASHISH
2206Enter value for address: BANGALORE
2207Enter value for phone: 1020304050
2208Enter value for gender: M
2209old 1: INSERT INTO STUDENT
2210VALUES('&USN','&SNAME','&ADDRESS',&PHONE,'&GENDER')
2211new 1: INSERT INTO STUDENT
2212VALUES('1MV17CS001','AASHISH','BANGALORE',1020304050,'M')
22131 row created.
2214SQL> /
2215Enter value for usn: 1MV17CS060
2216Enter value for sname: NAELA
2217Enter value for address: MYSORE
2218Enter value for phone: 1122334455
2219Enter value for gender: F
2220old 1: INSERT INTO STUDENT
2221VALUES('&USN','&SNAME','&ADDRESS',&PHONE,'&GENDER')
2222new 1: INSERT INTO STUDENT VALUES('1MV17CS060','NAELA','MYSORE',1122334455,'F')
22231 row created.
2224SQL> /
2225Enter value for usn: 1MV17CS130
2226Enter value for sname: MILIND
2227Enter value for address: JAMMU
2228Enter value for phone: 5060708090
2229Enter value for gender: M
2230old 1: INSERT INTO STUDENT
2231VALUES('&USN','&SNAME','&ADDRESS',&PHONE,'&GENDER')
2232new 1: INSERT INTO STUDENT VALUES('1MV17CS130','MILIND','JAMMU',5060708090,'M')
22331 row created.
2234SQL> /
2235Enter value for usn: 1MV16CS001
2236Enter value for sname: ABHIJITH
2237Enter value for address: PUNE
2238Enter value for phone: 9988776655
2239Enter value for gender: M
2240old 1: INSERT INTO STUDENT
2241VALUES('&USN','&SNAME','&ADDRESS',&PHONE,'&GENDER')
2242new 1: INSERT INTO STUDENT VALUES('1MV16CS001','ABHIJITH','PUNE',9988776655,'M')
22431 row created.
2244SQL> /
2245P a g e 37 | 70DBMS LABORATORY WITH MINI PROJECT
224615CSL58
2247Enter value for usn: 1MV16CS060
2248Enter value for sname: NIKITHA
2249Enter value for address: HYDERABAD
2250Enter value for phone: 9080706050
2251Enter value for gender: F
2252old 1: INSERT INTO STUDENT
2253VALUES('&USN','&SNAME','&ADDRESS',&PHONE,'&GENDER')
2254new 1: INSERT INTO STUDENT
2255VALUES('1MV16CS060','NIKITHA','HYDERABAD',9080706050,'F')
22561 row created.
2257SQL> /
2258Enter value for usn: 1MV16CS130
2259Enter value for sname: SANJANA
2260Enter value for address: GUWAHATTI
2261Enter value for phone: 1234567890
2262Enter value for gender: F
2263old 1: INSERT INTO STUDENT
2264VALUES('&USN','&SNAME','&ADDRESS',&PHONE,'&GENDER')
2265new 1: INSERT INTO STUDENT
2266VALUES('1MV16CS130','SANJANA','GUWAHATTI',1234567890,'F')
22671 row created.
2268SQL> /
2269Enter value for usn: 1MV15CS001
2270Enter value for sname: ANSHUMAN
2271Enter value for address: PANAJI
2272Enter value for phone: 1112223334
2273Enter value for gender: M
2274old 1: INSERT INTO STUDENT
2275VALUES('&USN','&SNAME','&ADDRESS',&PHONE,'&GENDER')
2276new 1: INSERT INTO STUDENT
2277VALUES('1MV15CS001','ANSHUMAN','PANAJI',1112223334,'M')
22781 row created.
2279SQL> /
2280Enter value for usn: 1MV15CS060
2281Enter value for sname: AMRUTHA
2282Enter value for address: BANGALORE
2283Enter value for phone: 1002003004
2284Enter value for gender: F
2285old 1: INSERT INTO STUDENT
2286VALUES('&USN','&SNAME','&ADDRESS',&PHONE,'&GENDER')
2287new 1: INSERT INTO STUDENT
2288VALUES('1MV15CS060','AMRUTHA','BANGALORE',1002003004,'F')
22891 row created.
2290SQL> /
2291Enter value for usn: 1MV15CS130
2292Enter value for sname: BHUVANESH
2293Enter value for address: JAIPUR
2294Enter value for phone: 9008007006
2295P a g e 38 | 70DBMS LABORATORY WITH MINI PROJECT
229615CSL58
2297Enter value for gender: M
2298old 1: INSERT INTO STUDENT
2299VALUES('&USN','&SNAME','&ADDRESS',&PHONE,'&GENDER')
2300new 1: INSERT INTO STUDENT
2301VALUES('1MV15CS130','BHUVANESH','JAIPUR',9008007006,'M')
23021 row created.
2303SQL> /
2304Enter value for usn: 1MV14CS001
2305Enter value for sname: DEVAYANI
2306Enter value for address: BANGALORE
2307Enter value for phone: 10020030
2308Enter value for gender: F
2309old 1: INSERT INTO STUDENT
2310VALUES('&USN','&SNAME','&ADDRESS',&PHONE,'&GENDER')
2311new 1: INSERT INTO STUDENT
2312VALUES('1MV14CS001','DEVAYANI','BANGALORE',10020030,'F')
23131 row created.
2314SQL> /
2315Enter value for usn: 1MV14CS060
2316Enter value for sname: DAVID
2317Enter value for address: KOCHI
2318Enter value for phone: 90080070
2319Enter value for gender: M
2320old 1: INSERT INTO STUDENT
2321VALUES('&USN','&SNAME','&ADDRESS',&PHONE,'&GENDER')
2322new 1: INSERT INTO STUDENT VALUES('1MV14CS060','DAVID','KOCHI',90080070,'M')
23231 row created.
2324SQL> /
2325Enter value for usn: 1MV14CS130
2326Enter value for sname: AISHWARYA
2327Enter value for address: MUMBAI
2328Enter value for phone: 1000020000
2329Enter value for gender: F
2330old 1: INSERT INTO STUDENT
2331VALUES('&USN','&SNAME','&ADDRESS',&PHONE,'&GENDER')
2332new 1: INSERT INTO STUDENT
2333VALUES('1MV14CS130','AISHWARYA','MUMBAI',1000020000,'F')
23341 row created.
2335P a g e 39 | 70DBMS LABORATORY WITH MINI PROJECT
233615CSL58
2337SQL> SELECT * FROM STUDENT;
2338USN
2339SNAME
2340----------
2341----------------
23421MV17CS001 AASHISH
23431MV17CS060 NAELA
23441MV17CS130 MILIND
23451MV16CS001 ABHIJITH
23461MV16CS060 NIKITHA
23471MV16CS130 SANJANA
23481MV15CS001 ANSHUMAN
23491MV15CS060 AMRUTHA
23501MV15CS130 BHUVANESH
23511MV14CS001 DEVAYANI
23521MV14CS060 DAVID
23531MV14CS130 AISHWARYA
2354ADDRESS
2355------------
2356BANGALORE
2357MYSORE
2358JAMMU
2359PUNE
2360HYDERABAD
2361GUWAHATTI
2362PANAJI
2363BANGALORE
2364JAIPUR
2365BANGALORE
2366KOCHI
2367MUMBAI
2368PHONE
2369-------------
23701020304050
23711122334455
23725060708090
23739988776655
23749080706050
23751234567890
23761112223334
23771002003004
23789008007006
237910020030
238090080070
23811000020000
2382GENDER
2383---------- -
2384M
2385F
2386M
2387M
2388F
2389F
2390M
2391F
2392M
2393F
2394M
2395F
239612 rows selected.
2397SQL> INSERT INTO SEMSEC VALUES('&SSID',&SEM,'&SEC');
2398Enter value for ssid: 2A
2399Enter value for sem: 2
2400Enter value for sec: A
2401old 1: INSERT INTO SEMSEC VALUES('&SSID',&SEM,'&SEC')
2402new 1: INSERT INTO SEMSEC VALUES('2A',2,'A')
24031 row created.
2404SQL> /
2405Enter value for ssid: 2B
2406Enter value for sem: 2
2407Enter value for sec: B
2408old 1: INSERT INTO SEMSEC VALUES('&SSID',&SEM,'&SEC')
2409new 1: INSERT INTO SEMSEC VALUES('2B',2,'B')
24101 row created.
2411SQL> /
2412Enter value for ssid: 2C
2413Enter value for sem: 2
2414Enter value for sec: C
2415old 1: INSERT INTO SEMSEC VALUES('&SSID',&SEM,'&SEC')
2416new 1: INSERT INTO SEMSEC VALUES('2C',2,'C')
24171 row created.
2418SQL> /
2419Enter value for ssid: 4A
2420Enter value for sem: 4
2421Enter value for sec: A
2422old 1: INSERT INTO SEMSEC VALUES('&SSID',&SEM,'&SEC')
2423new 1: INSERT INTO SEMSEC VALUES('4A',4,'A')
24241 row created.
2425SQL> /
2426P a g e 40 | 70DBMS LABORATORY WITH MINI PROJECT
242715CSL58
2428Enter value for ssid: 4B
2429Enter value for sem: 4
2430Enter value for sec: B
2431old 1: INSERT INTO SEMSEC VALUES('&SSID',&SEM,'&SEC')
2432new 1: INSERT INTO SEMSEC VALUES('4B',4,'B')
24331 row created.
2434SQL> /
2435Enter value for ssid: 4C
2436Enter value for sem: 4
2437Enter value for sec: C
2438old 1: INSERT INTO SEMSEC VALUES('&SSID',&SEM,'&SEC')
2439new 1: INSERT INTO SEMSEC VALUES('4C',4,'C')
24401 row created.
2441SQL> /
2442Enter value for ssid: 6A
2443Enter value for sem: 6
2444Enter value for sec: A
2445old 1: INSERT INTO SEMSEC VALUES('&SSID',&SEM,'&SEC')
2446new 1: INSERT INTO SEMSEC VALUES('6A',6,'A')
24471 row created.
2448SQL> /
2449Enter value for ssid: 6B
2450Enter value for sem: 6
2451Enter value for sec: B
2452old 1: INSERT INTO SEMSEC VALUES('&SSID',&SEM,'&SEC')
2453new 1: INSERT INTO SEMSEC VALUES('6B',6,'B')
24541 row created.
2455SQL> /
2456Enter value for ssid: 6C
2457Enter value for sem: 6
2458Enter value for sec: C
2459old 1: INSERT INTO SEMSEC VALUES('&SSID',&SEM,'&SEC')
2460new 1: INSERT INTO SEMSEC VALUES('6C',6,'C')
24611 row created.
2462SQL> /
2463Enter value for ssid: 8A
2464Enter value for sem: 8
2465Enter value for sec: A
2466old 1: INSERT INTO SEMSEC VALUES('&SSID',&SEM,'&SEC')
2467new 1: INSERT INTO SEMSEC VALUES('8A',8,'A')
24681 row created.
2469SQL> /
2470Enter value for ssid: 8B
2471Enter value for sem: 8
2472P a g e 41 | 70DBMS LABORATORY WITH MINI PROJECT
247315CSL58
2474Enter value for sec: B
2475old 1: INSERT INTO SEMSEC VALUES('&SSID',&SEM,'&SEC')
2476new 1: INSERT INTO SEMSEC VALUES('8B',8,'B')
24771 row created.
2478SQL> /
2479Enter value for ssid: 8C
2480Enter value for sem: 8
2481Enter value for sec: C
2482old 1: INSERT INTO SEMSEC VALUES('&SSID',&SEM,'&SEC')
2483new 1: INSERT INTO SEMSEC VALUES('8C',8,'C')
24841 row created.
2485SQL> SELECT * FROM SEMSEC;
2486SSID
2487SEM
2488--
2489----
24902A
24912
24922B
24932
24942C
24952
24964A
24974
24984B
24994
25004C
25014
25026A
25036
25046B
25056
25066C
25076
25088A
25098
25108B
25118
25128C
25138
251412 rows selected.
2515SEC
2516----- -
2517A
2518B
2519C
2520A
2521B
2522C
2523A
2524B
2525C
2526A
2527B
2528C
2529SQL> INSERT INTO CLASS VALUES('&USN','&SSID');
2530Enter value for usn: 1MV17CS001
2531Enter value for ssid: 2A
2532old 1: INSERT INTO CLASS VALUES('&USN','&SSID')
2533new 1: INSERT INTO CLASS VALUES('1MV17CS001','2A')
25341 row created.
2535SQL> /
2536Enter value for usn: 1MV17CS060
2537Enter value for ssid: 2B
2538old 1: INSERT INTO CLASS VALUES('&USN','&SSID')
2539new 1: INSERT INTO CLASS VALUES('1MV17CS060','2B')
25401 row created.
2541SQL> /
2542Enter value for usn: 1MV17CS130
2543Enter value for ssid: 2C
2544old 1: INSERT INTO CLASS VALUES('&USN','&SSID')
2545new 1: INSERT INTO CLASS VALUES('1MV17CS130','2C')
25461 row created.
2547SQL> /
2548P a g e 42 | 70DBMS LABORATORY WITH MINI PROJECT
254915CSL58
2550Enter value for usn: 1MV16CS001
2551Enter value for ssid: 4A
2552old 1: INSERT INTO CLASS VALUES('&USN','&SSID')
2553new 1: INSERT INTO CLASS VALUES('1MV16CS001','4A')
25541 row created.
2555SQL> /
2556Enter value for usn: 1MV16CS060
2557Enter value for ssid: 4B
2558old 1: INSERT INTO CLASS VALUES('&USN','&SSID')
2559new 1: INSERT INTO CLASS VALUES('1MV16CS060','4B')
25601 row created.
2561SQL> /
2562Enter value for usn: 1MV16CS130
2563Enter value for ssid: 4C
2564old 1: INSERT INTO CLASS VALUES('&USN','&SSID')
2565new 1: INSERT INTO CLASS VALUES('1MV16CS130','4C')
25661 row created.
2567SQL> /
2568Enter value for usn: 1MV15CS001
2569Enter value for ssid: 6A
2570old 1: INSERT INTO CLASS VALUES('&USN','&SSID')
2571new 1: INSERT INTO CLASS VALUES('1MV15CS001','6A')
25721 row created.
2573SQL> /
2574Enter value for usn: 1MV15CS060
2575Enter value for ssid: 6B
2576old 1: INSERT INTO CLASS VALUES('&USN','&SSID')
2577new 1: INSERT INTO CLASS VALUES('1MV15CS060','6B')
25781 row created.
2579SQL> /
2580Enter value for usn: 1MV15CS130
2581Enter value for ssid: 6C
2582old 1: INSERT INTO CLASS VALUES('&USN','&SSID')
2583new 1: INSERT INTO CLASS VALUES('1MV15CS130','6C')
25841 row created.
2585SQL> /
2586Enter value for usn: 1MV14CS001
2587Enter value for ssid: 8A
2588old 1: INSERT INTO CLASS VALUES('&USN','&SSID')
2589new 1: INSERT INTO CLASS VALUES('1MV14CS001','8A')
25901 row created.
2591SQL> /
2592Enter value for usn: 1MV14CS060
2593Enter value for ssid: 8B
2594P a g e 43 | 70DBMS LABORATORY WITH MINI PROJECT
259515CSL58
2596old 1: INSERT INTO CLASS VALUES('&USN','&SSID')
2597new 1: INSERT INTO CLASS VALUES('1MV14CS060','8B')
25981 row created.
2599SQL> /
2600Enter value for usn: 1MV14CS130
2601Enter value for ssid: 8C
2602old 1: INSERT INTO CLASS VALUES('&USN','&SSID')
2603new 1: INSERT INTO CLASS VALUES('1MV14CS130','8C')
26041 row created.
2605SQL> SELECT * FROM CLASS;
2606USN
2607SSID
2608-------
2609-----
26101MV14CS001 8A
26111MV14CS060 8B
26121MV14CS130 8C
26131MV15CS001 6A
26141MV15CS060 6B
26151MV15CS130 6C
26161MV16CS001 4A
26171MV16CS060 4B
26181MV16CS130 4C
26191MV17CS001 2A
26201MV17CS060 2B
26211MV17CS130 2C
262212 rows selected.
2623SQL> INSERT INTO SUBJECT VALUES('&SUBCODE','&TITLE',&SEM,&CREDITS);
2624Enter value for subcode: 15CS21
2625Enter value for title: M2
2626Enter value for sem: 2
2627Enter value for credits: 4
2628old 1: INSERT INTO SUBJECT VALUES('&SUBCODE','&TITLE',&SEM,&CREDITS)
2629new 1: INSERT INTO SUBJECT VALUES('15CS21','M2',2,4)
26301 row created.
2631SQL> /
2632Enter value for subcode: 15PCD23
2633Enter value for title: PCD
2634Enter value for sem: 2
2635Enter value for credits: 4
2636old 1: INSERT INTO SUBJECT VALUES('&SUBCODE','&TITLE',&SEM,&CREDITS)
2637new 1: INSERT INTO SUBJECT VALUES('15PCD23','PCD',2,4)
26381 row created.
2639SQL> /
2640Enter value for subcode: 15CS42
2641Enter value for title: SE
2642Enter value for sem: 4
2643P a g e 44 | 70DBMS LABORATORY WITH MINI PROJECT
264415CSL58
2645Enter value for credits: 4
2646old 1: INSERT INTO SUBJECT VALUES('&SUBCODE','&TITLE',&SEM,&CREDITS)
2647new 1: INSERT INTO SUBJECT VALUES('15CS42','SE',4,4)
26481 row created.
2649SQL> /
2650Enter value for subcode: 15CS44
2651Enter value for title: MP
2652Enter value for sem: 4
2653Enter value for credits: 4
2654old 1: INSERT INTO SUBJECT VALUES('&SUBCODE','&TITLE',&SEM,&CREDITS)
2655new 1: INSERT INTO SUBJECT VALUES('15CS44','MP',4,4)
26561 row created.
2657SQL> /
2658Enter value for subcode: 15CS64
2659Enter value for title: CG
2660Enter value for sem: 6
2661Enter value for credits: 4
2662old 1: INSERT INTO SUBJECT VALUES('&SUBCODE','&TITLE',&SEM,&CREDITS)
2663new 1: INSERT INTO SUBJECT VALUES('15CS64','CG',6,4)
26641 row created.
2665SQL> /
2666Enter value for subcode: 15CS62
2667Enter value for title: USP
2668Enter value for sem: 6
2669Enter value for credits: 4
2670old 1: INSERT INTO SUBJECT VALUES('&SUBCODE','&TITLE',&SEM,&CREDITS)
2671new 1: INSERT INTO SUBJECT VALUES('15CS62','USP',6,4)
26721 row created.
2673SQL> /
2674Enter value for subcode: 10CS81
2675Enter value for title: SA
2676Enter value for sem: 8
2677Enter value for credits: 4
2678old 1: INSERT INTO SUBJECT VALUES('&SUBCODE','&TITLE',&SEM,&CREDITS)
2679new 1: INSERT INTO SUBJECT VALUES('10CS81','SA',8,4)
26801 row created.
2681SQL> /
2682Enter value for subcode: 10CS842
2683Enter value for title: ST
2684Enter value for sem: 8
2685Enter value for credits: 3
2686old 1: INSERT INTO SUBJECT VALUES('&SUBCODE','&TITLE',&SEM,&CREDITS)
2687new 1: INSERT INTO SUBJECT VALUES('10CS842','ST',8,3)
26881 row created.
2689P a g e 45 | 70DBMS LABORATORY WITH MINI PROJECT
269015CSL58
2691SQL> SELECT * FROM SUBJECT;
2692SUBCODE TITLE
2693SEM CREDITS
2694------- -------------------- ---------- ----------
269515CS21
2696M2
26972
26984
269915PCD23
2700PCD
27012
27024
270315CS42
2704SE
27054
27064
270715CS44
2708MP
27094
27104
271115CS64
2712CG
27136
27144
271515CS62
2716USP
27176
27184
271910CS81
2720SA
27218
27224
272310CS842
2724ST
27258
27263
27278 rows selected.
2728SQL> INSERT INTO
2729IAMARKS(USN,SUBCODE,SSID,TEST1,TEST2,TEST3)VALUES('&USN','&SUBCODE','&SSID',&
2730TEST1,
2731&TEST2,&TEST3);
2732Enter value for usn: 1MV17CS001
2733Enter value for subcode: 15CS21
2734Enter value for ssid: 2A
2735Enter value for test1: 15
2736Enter value for test2: 14
2737Enter value for test3: 13
2738old 1: INSERT INTO
2739IAMARKS(USN,SUBCODE,SSID,TEST1,TEST2,TEST3)VALUES('&USN','&SUBCODE','&SSID',&
2740TE
2741new 1: INSERT INTO
2742IAMARKS(USN,SUBCODE,SSID,TEST1,TEST2,TEST3)VALUES('1MV17CS001','15CS21','2A',15
27431 row created.
2744SQL> /
2745Enter value for usn: 1MV17CS060
2746Enter value for subcode: 15PCD23
2747Enter value for ssid: 2B
2748Enter value for test1: 15
2749Enter value for test2: 15
2750Enter value for test3: 14
2751old 1: INSERT INTO
2752IAMARKS(USN,SUBCODE,SSID,TEST1,TEST2,TEST3)VALUES('&USN','&SUBCODE','&SSID',&
2753TE
2754new 1: INSERT INTO
2755IAMARKS(USN,SUBCODE,SSID,TEST1,TEST2,TEST3)VALUES('1MV17CS060','15PCD23','2B',1
27561 row created.
2757SQL> /
2758Enter value for usn: 1MV17CS130
2759Enter value for subcode: 15CS21
2760Enter value for ssid: 2C
2761Enter value for test1: 11
2762P a g e 46 | 70DBMS LABORATORY WITH MINI PROJECT
276315CSL58
2764Enter value for test2: 12
2765Enter value for test3: 13
2766old 1: INSERT INTO
2767IAMARKS(USN,SUBCODE,SSID,TEST1,TEST2,TEST3)VALUES('&USN','&SUBCODE','&SSID',&
2768TE
2769new 1: INSERT INTO
2770IAMARKS(USN,SUBCODE,SSID,TEST1,TEST2,TEST3)VALUES('1MV17CS130','15CS21','2C',11
27711 row created.
2772SQL> /
2773Enter value for usn: 1MV16CS001
2774Enter value for subcode: 15CS42
2775Enter value for ssid: 4A
2776Enter value for test1: 19
2777Enter value for test2: 19
2778Enter value for test3: 18
2779old 1: INSERT INTO
2780IAMARKS(USN,SUBCODE,SSID,TEST1,TEST2,TEST3)VALUES('&USN','&SUBCODE','&SSID',&
2781TE
2782new 1: INSERT INTO
2783IAMARKS(USN,SUBCODE,SSID,TEST1,TEST2,TEST3)VALUES('1MV16CS001','15CS42','4A',19
27841 row created.
2785SQL> /
2786Enter value for usn: 1MV16CS060
2787Enter value for subcode: 15CS44
2788Enter value for ssid: 4B
2789Enter value for test1: 5
2790Enter value for test2: 8
2791Enter value for test3: 5
2792old 1: INSERT INTO
2793IAMARKS(USN,SUBCODE,SSID,TEST1,TEST2,TEST3)VALUES('&USN','&SUBCODE','&SSID',&
2794TE
2795new 1: INSERT INTO
2796IAMARKS(USN,SUBCODE,SSID,TEST1,TEST2,TEST3)VALUES('1MV16CS060','15CS44','4B',5,
27971 row created.
2798SQL> /
2799Enter value for usn: 1MV16CS130
2800Enter value for subcode: 15CS42
2801Enter value for ssid: 4C
2802Enter value for test1: 20
2803Enter value for test2: 20
2804Enter value for test3: 20
2805old 1: INSERT INTO
2806IAMARKS(USN,SUBCODE,SSID,TEST1,TEST2,TEST3)VALUES('&USN','&SUBCODE','&SSID',&
2807TE
2808new 1: INSERT INTO
2809IAMARKS(USN,SUBCODE,SSID,TEST1,TEST2,TEST3)VALUES('1MV16CS130','15CS42','4C',20
28101 row created.
2811SQL> /
2812P a g e 47 | 70DBMS LABORATORY WITH MINI PROJECT
281315CSL58
2814Enter value for usn: 1MV15CS001
2815Enter value for subcode: 15CS64
2816Enter value for ssid: 6A
2817Enter value for test1: 12
2818Enter value for test2: 12
2819Enter value for test3: 12
2820old 1: INSERT INTO
2821IAMARKS(USN,SUBCODE,SSID,TEST1,TEST2,TEST3)VALUES('&USN','&SUBCODE','&SSID',&
2822TE
2823new 1: INSERT INTO
2824IAMARKS(USN,SUBCODE,SSID,TEST1,TEST2,TEST3)VALUES('1MV15CS001','15CS64','6A',12
28251 row created.
2826SQL> /
2827Enter value for usn: 1MV15CS060
2828Enter value for subcode: 15CS62
2829Enter value for ssid: 6B
2830Enter value for test1: 18
2831Enter value for test2: 19
2832Enter value for test3: 20
2833old 1: INSERT INTO
2834IAMARKS(USN,SUBCODE,SSID,TEST1,TEST2,TEST3)VALUES('&USN','&SUBCODE','&SSID',&
2835TE
2836new 1: INSERT INTO
2837IAMARKS(USN,SUBCODE,SSID,TEST1,TEST2,TEST3)VALUES('1MV15CS060','15CS62','6B',18
28381 row created.
2839SQL> /
2840Enter value for usn: 1MV15CS130
2841Enter value for subcode: 15CS64
2842Enter value for ssid: 6C
2843Enter value for test1: 8
2844Enter value for test2: 12
2845Enter value for test3: 11
2846old 1: INSERT INTO
2847IAMARKS(USN,SUBCODE,SSID,TEST1,TEST2,TEST3)VALUES('&USN','&SUBCODE','&SSID',&
2848TE
2849new 1: INSERT INTO
2850IAMARKS(USN,SUBCODE,SSID,TEST1,TEST2,TEST3)VALUES('1MV15CS130','15CS64','6C',8,
28511 row created.
2852SQL> /
2853Enter value for usn: 1MV14CS001
2854Enter value for subcode: 10CS81
2855Enter value for ssid: 8A
2856Enter value for test1: 3
2857Enter value for test2: 11
2858Enter value for test3: 12
2859P a g e 48 | 70DBMS LABORATORY WITH MINI PROJECT
286015CSL58
2861old 1: INSERT INTO
2862IAMARKS(USN,SUBCODE,SSID,TEST1,TEST2,TEST3)VALUES('&USN','&SUBCODE','&SSID',&
2863TE
2864new 1: INSERT INTO
2865IAMARKS(USN,SUBCODE,SSID,TEST1,TEST2,TEST3)VALUES('1MV14CS001','10CS81','8A',3,
28661 row created.
2867SQL> /
2868Enter value for usn: 1MV14CS060
2869Enter value for subcode: 10CS842
2870Enter value for ssid: 8B
2871Enter value for test1: 0
2872Enter value for test2: 0
2873Enter value for test3: 7
2874old 1: INSERT INTO
2875IAMARKS(USN,SUBCODE,SSID,TEST1,TEST2,TEST3)VALUES('&USN','&SUBCODE','&SSID',&
2876TE
2877new 1: INSERT INTO
2878IAMARKS(USN,SUBCODE,SSID,TEST1,TEST2,TEST3)VALUES('1MV14CS060','10CS842','8B',0
28791 row created.
2880SQL> /
2881Enter value for usn: 1MV14CS130
2882Enter value for subcode: 10CS81
2883Enter value for ssid: 8C
2884Enter value for test1: 0
2885Enter value for test2: 0
2886Enter value for test3: 20
2887old 1: INSERT INTO
2888IAMARKS(USN,SUBCODE,SSID,TEST1,TEST2,TEST3)VALUES('&USN','&SUBCODE','&SSID',&
2889TE
2890new 1: INSERT INTO
2891IAMARKS(USN,SUBCODE,SSID,TEST1,TEST2,TEST3)VALUES('1MV14CS130','10CS81','8C',0,
28921 row created.
2893SQL> SELECT * FROM IAMARKS;
2894USN
2895SUBCODE SSID TEST1 TEST2
2896----------
2897-------
2898--
2899---------- -----------------
29001MV17CS001 15CS21 2A
290115
290214
29031MV17CS060 15PCD23 2B
290415
290515
29061MV17CS130 15CS21 2C
290711
290812
29091MV16CS001 15CS42 4A
291019
291119
29121MV16CS060 15CS44 4B
29135
29148
29151MV16CS130 15CS42 4C
291620
291720
29181MV15CS001 15CS64 6A
291912
292012
29211MV15CS060 15CS62 6B
292218
292319
29241MV15CS130 15CS64 6C
29258
292612
29271MV14CS001 10CS81 8A
29283
292911
29301MV14CS060 10CS842 8B
29310
29320
29331MV14CS130 10CS81 8C
29340
29350
293612 rows selected.
2937TEST3 FINALIA
2938----------
293913
294014
294113
294218
29435
294420
294512
294620
294711
294812
29497
295020
2951P a g e 49 | 70DBMS LABORATORY WITH MINI PROJECT
295215CSL58
2953QUERIES:
29541.Make a list of all students details studying in 4 th sem c-sec.
2955SELECT S.USN,S.SNAME,S.ADDRESS,S.PHONE,S.GENDER
2956FROM STUDENT S,CLASS C,SEM_SEC SS
2957WHERE S.USN=C.USN AND
2958SS.SSID=C.SSID AND
2959SS.SEM=4 AND
2960SS.SEC='C';
2961Output:
2962USN
2963SNAME
2964----------
2965-----------
29661MV16CS130 SANJANA
2967ADDRESS
2968PHONE
2969GENDER
2970--------- ---- ------------------ ---------- -
2971GUWATHI
29721234567890
2973F
29742.Compute the total no.of male and female students in each semester and in each sec.
2975SELECT SS.SEM,SS.SEC,S.GENDER,COUNT(S.GENDER)
2976FROM STUDENT S,SEMSEC SS,CLASS C
2977WHERE S.USN=C.USN AND SS.SSID=C.SSID
2978GROUP BY SS.SEM,SS.SEC,S.GENDER;
2979Output:
2980SEM SEC GENDER COUNT(S.GENDER)
2981------ ----
2982-- ----
2983-----------
29844
2985B
2986F
29871
29882
2989C
2990M
29911
29926
2993A
2994M
29951
29968
2997B
2998M
29991
30002
3001A
3002M
30031
30048
3005A
3006F
30071
30082
3009B
3010F
30111
30124
3013A
3014M
30151
30164
3017C
3018F
30191
30206
3021B
3022F
30231
30246
3025C
3026M
30271
30288
3029C
3030F
30311
303212 rows selected.
30333.Create view of test1 marks of student 1MV15CS060 in all subjects.
3034CREATE VIEW TEST1_MARKS AS
3035SELECT USN,SUBCODE,TEST1
3036FROM IAMARKS
3037WHERE USN='1MV15CS060';
3038View created.
3039Output:
3040SQL> SELECT * FROM TEST1_MARKS;
3041USN
3042SUBCODE TEST1
3043----------
3044-------
3045----------
30461MV15CS060 15CS62
304718
3048P a g e 50 | 70DBMS LABORATORY WITH MINI PROJECT
304915CSL58
3050SQL> UPDATE IAMARKS SET TEST1=19,TEST2=18,TEST3=17 WHERE USN='1MV14CS001';
30511 row updated.
3052SQL> UPDATE IAMARKS SET TEST1=11,TEST2=0,TEST3=14 WHERE USN='1MV14CS060';
30531 row updated.
3054SQL> UPDATE IAMARKS SET TEST1=10,TEST2=0,TEST3=7 WHERE USN='1MV14CS130';
30551 row updated.
3056SQL> SELECT * FROM IAMARKS;
3057USN
3058SUBCODE
3059----------
3060-------
30611MV17CS001 15CS21
30621MV17CS060 15PCD23
30631MV17CS130 15CS21
30641MV16CS001 15CS42
30651MV16CS060 15CS44
30661MV16CS130 15CS42
30671MV15CS001 15CS64
30681MV15CS060 15CS62
30691MV15CS130 15CS64
30701MV14CS001 10CS81
30711MV14CS060 10CS842
30721MV14CS130 10CS81
3073SSID
3074--
30752A
30762B
30772C
30784A
30794B
30804C
30816A
30826B
30836C
30848A
30858B
30868C
3087TEST1
3088----------
308915
309015
309111
309219
30935
309420
309512
309618
30978
309819
309911
310010
3101TEST2 TEST3 FINALIA
3102-----------------
3103----------
310414
310513
310615
310714
310812
310913
311019
311118
31128
31135
311420
311520
311612
311712
311819
311920
312012
312111
312218
312317
31240
312514
31260
31277
312812 rows selected.
3129P a g e 51 | 70DBMS LABORATORY WITH MINI PROJECT
313015CSL58
31314.Calculate the final IA amrks and update the corresponding table for all students.
3132CREATE OR REPLACE PROCEDURE AVGMARKS
3133IS
3134CURSOR C_IAMARKS IS
3135SELECT GREATEST(TEST1,TEST2) AS A,GREATEST(TEST1,TEST3) AS
3136B,GREATEST(TEST2,TEST3) AS C
3137FROM IAMARKS
3138WHERE FINALIA IS NULL
3139FOR UPDATE;
3140C_A NUMBER;
3141C_B NUMBER;
3142C_C NUMBER;
3143C_SUM NUMBER;
3144C_AVG NUMBER;
3145BEGIN
3146OPEN C_IAMARKS;
3147LOOP
3148FETCH C_IAMARKS INTO C_A,C_B,C_C;
3149EXIT WHEN C_IAMARKS%NOTFOUND;
3150DBMS_OUTPUT.PUT_LINE(C_A||''||C_B||''||C_C);
3151IF(C_A!=C_B) THEN
3152C_SUM:=C_A+C_B;
3153ELSE
3154C_SUM:=C_A+C_C;
3155END IF;
3156C_AVG:=C_SUM/2;
3157DBMS_OUTPUT.PUT_LINE('SUM='||C_SUM);
3158DBMS_OUTPUT.PUT_LINE('AVERAGE='||C_AVG);
3159UPDATE IAMARKS SET FINALIA=C_AVG
3160WHERE CURRENT OF C_IAMARKS;
3161END LOOP;
3162CLOSE C_IAMARKS;
3163END;
3164/
3165Procedure created.
3166SQL> BEGIN AVGMARKS;
3167END;
3168/
3169PL/SQL procedure successfully completed.
3170Output:
3171SQL> SELECT * FROM IAMARKS;
3172USN
3173SUBCODE
3174----------
3175-------
31761MV17CS001 15CS21
31771MV17CS060 15PCD23
31781MV17CS130 15CS21
31791MV16CS001 15CS42
3180SSID
3181--
31822A
31832B
31842C
31854A
3186TEST1 TEST2 TEST3 FINALIA
3187--------- ------ -----
3188----------
318915
319014
319113
319215
319315
319415
319514
319615
319711
319812
319913
320013
320119
320219
320318
320419
3205P a g e 52 | 70DBMS LABORATORY WITH MINI PROJECT
32061MV16CS060
32071MV16CS130
32081MV15CS001
32091MV15CS060
32101MV15CS130
32111MV14CS001
32121MV14CS060
32131MV14CS130
321415CS44
321515CS42
321615CS64
321715CS62
321815CS64
321910CS81
322010CS842
322110CS81
32224B
32234C
32246A
32256B
32266C
32278A
32288B
32298C
32305
323120
323212
323318
32348
323519
323611
323710
32388
323920
324012
324119
324212
324318
32440
32450
324615CSL58
32475
324820
324912
325020
325111
325217
325314
32547
32557
325620
325712
325820
325912
326019
326113
32629
326312 rows selected.
3264SQL> UPDATE IAMARKS SET FINALIA=NULL;
326512 rows updated.
3266SQL> SELECT * FROM IAMARKS;
3267USN
3268SUBCODE SSID TEST1 TEST2 TEST3 FINALIA
3269----------
3270-------
3271--
3272---------- -----------------
3273----------
32741MV17CS001 15CS21 2A
327515
327614
327713
32781MV17CS060 15PCD23 2B
327915
328015
328114
32821MV17CS130 15CS21 2C
328311
328412
328513
32861MV16CS001 15CS42 4A
328719
328819
328918
32901MV16CS060 15CS44 4B
32915
32928
32935
32941MV16CS130 15CS42 4C
329520
329620
329720
32981MV15CS001 15CS64 6A
329912
330012
330112
33021MV15CS060 15CS62 6B
330318
330419
330520
33061MV15CS130 15CS64 6C
33078
330812
330911
33101MV14CS001 10CS81 8A
331119
331218
331317
33141MV14CS060 10CS842 8B
331511
33160
331714
33181MV14CS130 10CS81 8C
331910
33200
33217
332212 rows selected.
3323Alternate:
3324SQL> DECLARE
3325CURSOR C_IA_MARKS
3326IS
3327SELECT TEST1,TEST2,TEST3 FROM IA_MARKS
3328WHERE FINAL_IA IS NULL
3329FOR UPDATE;
3330C_T1 NUMBER;
3331C_T2 NUMBER;
3332C_T3 NUMBER;
3333C_SUM NUMBER;
3334C_AVG NUMBER;
3335C_MIN NUMBER;
3336BEGIN
3337OPEN C_IA_MARKS;
3338LOOP
3339FETCH C_IA_MARKS INTO C_T1,C_T2,C_T3;
3340P a g e 53 | 70DBMS LABORATORY WITH MINI PROJECT
334115CSL58
3342EXIT WHEN C_IA_MARKS%NOTFOUND;
3343C_SUM:=C_T1+C_T2+C_T3;
3344DBMS_OUTPUT.PUT_LINE('SUM='||C_SUM);
3345IF((C_T1<=C_T2)AND(C_T1<=C_T3))
3346THEN
3347C_MIN:=C_T1;
3348ELSE IF((C_T2<=C_T1)
3349AND(C_T2<=C_T3))THEN
3350C_MIN:=C_T2;
3351ELSE
3352C_MIN:=C_T3;
3353END IF;
3354END IF;
3355DBMS_OUTPUT.PUT_LINE('MIN='||C_MIN);
3356C_AVG:=(C_SUM-C_MIN)/2;
3357DBMS_OUTPUT.PUT_LINE('AVERAGE='||C_AVG);
3358UPDATE IA_MARKS SET FINAL_IA=C_AVG WHERE
3359CURRENT OF C_IA_MARKS;
3360END LOOP;
3361CLOSE C_IA_MARKS;
3362END;
3363/
3364PL/SQL procedure successfully completed.
3365Output:
3366SQL> SELECT * FROM IAMARKS;
3367USN
3368SUBCODE
3369----------
3370-------
33711MV17CS001 15CS21
33721MV17CS060 15PCD23
33731MV17CS130 15CS21
33741MV16CS001 15CS42
33751MV16CS060 15CS44
33761MV16CS130 15CS42
33771MV15CS001 15CS64
33781MV15CS060 15CS62
33791MV15CS130 15CS64
33801MV14CS001 10CS81
33811MV14CS060 10CS842
33821MV14CS130 10CS81
3383SSID
3384--
33852A
33862B
33872C
33884A
33894B
33904C
33916A
33926B
33936C
33948A
33958B
33968C
3397TEST1 TEST2 TEST3 FINALIA
3398--------- ------ -----
3399----------
340015
340114
340213
340315
340415
340515
340614
340715
340811
340912
341013
341113
341219
341319
341418
341519
34165
34178
34185
34197
342020
342120
342220
342320
342412
342512
342612
342712
342818
342919
343020
343120
34328
343312
343411
343512
343619
343718
343817
343919
344011
34410
344214
344313
344410
34450
34467
34479
344812 rows selected.
3449P a g e 54 | 70DBMS LABORATORY WITH MINI PROJECT
345015CSL58
34515.Categorise students based on the following criteria
3452If FINALIA=17 to 20 then CAT=’OUTSTANDING’
3453If FINALIA=12 to 16 then CAT=’AVERAGE’
3454If FINALIA=00 to 11 then CAT=’WEAK’
3455Give these details only for 8 th semester A, B and C section students.
3456SELECT S.USN,S.SNAME,S.ADDRESS,S.PHONE,S.GENDER,
3457( CASE
3458WHEN IA.FINALIA BETWEEN 17 AND 20
3459THEN 'OUTSTANDING'
3460WHEN IA.FINALIA BETWEEN 12 AND 16
3461THEN 'AVERAGE'
3462ELSE 'WEAK'
3463END
3464) AS CAT
3465FROM STUDENT S,SEMSEC SS,IAMARKS IA
3466WHERE S.USN=IA.USN AND
3467SS.SSID=IA.SSID AND
3468SS.SEM=8;
3469USN
3470SNAME
3471ADDRESS
3472----------
3473-----------
3474--- ------------
34751MV14CS001 DEVAYANI
3476BANGALORE
34771MV14CS060 DAVID
3478KOCHI
34791MV14CS130 AISHWARYA MUMBAI
3480PHONE
3481------------
348210020030
348390080070
34841000020000
3485GENDER CAT
3486----------
3487-----------
3488F
3489OUTSTANDING
3490M
3491AVERAGE
3492F
3493WEAK
3494P a g e 55 | 70DBMS LABORATORY WITH MINI PROJECT
349515CSL58
3496COMPANY DATABASE
3497EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY,SUPERSSN,DNO)
3498DEPARTMENT(DNO,DNAME,MGRSSN,MGRSTARTDATE)
3499DLOCATION(DNO,DLOC)
3500PROJECT(PNO,PNAME,PLOCATION,DNO)
3501WORKS_ON(SSN,PNO,HOURS)
35021. CREATE TABLE EMPLOYEE (SSN NUMBER(9),
3503NAME VARCHAR(20),
3504ADDRESS VARCHAR(25),
3505SEX CHAR,
3506SALARY NUMBER(10,2),
3507SUPERSSN NUMBER(9),
3508DNO NUMBER(2),
3509CONSTRAINT PA PRIMARY KEY(SSN),
3510CONSTRAINT PB FOREIGN KEY(SUPERSSN) REFERENCES EMPLOYEE(SSN) ON DELETE
3511CASCADE);
3512Table created.
3513SQL> DESC EMPLOYEE;
3514Name
3515Null?
3516Type
3517----------------------------------------- -------- ----------------------------
3518SSN
3519NOT NULL NUMBER(9)
3520NAME
3521VARCHAR2(20)
3522ADDRESS
3523VARCHAR2(25)
3524SEX
3525CHAR(1)
3526SALARY
3527NUMBER(10,2)
3528SUPERSSN
3529NUMBER(9)
3530DNO
3531NUMBER(2)
35322. CREATE TABLE DEPARTMENT (DNO NUMBER(2),
3533DNAME VARCHAR(15),
3534MGRSSN NUMBER(9),
3535MGRSTARTDATE DATE,
3536CONSTRAINT PC PRIMARY KEY(DNO),
3537CONSTRAINT PKD FOREIGN KEY(MGRSSN) REFERENCES EMPLOYEE(SSN) ON DELETE
3538CASCADE);
3539Table created.
3540SQL> DESC DEPARTMENT;
3541Name
3542Null?
3543Type
3544----------------------------------------- -------- ----------------------------
3545DNO
3546NOT NULL NUMBER(2)
3547DNAME
3548VARCHAR2(15)
3549MGRSSN
3550NUMBER(9)
3551MGRSTARTDATE
3552DATE
3553SQL> ALTER TABLE EMPLOYEE ADD CONSTRAINT PE FOREIGN KEY (DNO) REFERENCES
3554DEPARTMENT (DNO) ON DELETE CASCADE;
3555P a g e 56 | 70DBMS LABORATORY WITH MINI PROJECT
355615CSL58
3557Table altered.
35583. CREATE TABLE DLOCATION (DNO NUMBER(2),
3559DLOC VARCHAR(15),
3560CONSTRAINT CPK PRIMARY KEY (DNO,DLOC),
3561CONSTRAINT FK_D1 FOREIGN KEY (DNO) REFERENCES DEPARTMENT (DNO) ON
3562DELETE CASCADE);
3563Table created.
3564SQL> DESC DLOCATION;
3565Name
3566Null?
3567Type
3568----------------------------------------- -------- ----------------------------
3569DNO
3570NOT NULL NUMBER(2)
3571DLOC
3572NOT NULL VARCHAR2(15)
35734. CREATE TABLE PROJECT (PNO NUMBER(2),
3574PNAME VARCHAR(15),
3575PLOCATION VARCHAR(15),
3576DNO NUMBER(2),
3577CONSTRAINT PKP PRIMARY KEY(PNO),
3578CONSTRAINT FKD FOREIGN KEY (DNO) REFERENCES DEPARTMENT (DNO) ON DELETE
3579CASCADE);
3580Table created.
3581SQL> DESC PROJECT;
3582Name
3583Null?
3584Type
3585----------------------------------------- -------- ----------------------------
3586PNO
3587NOT NULL
3588NUMBER(2)
3589PNAME
3590VARCHAR2(15)
3591PLOCATION
3592VARCHAR2(15)
3593DNO
3594NUMBER(2)
35955. CREATE TABLE WORKS_ON (SSN NUMBER(9),
3596PNO NUMBER(2),
3597HOURS NUMBER(3),
3598CONSTRAINT SP PRIMARY KEY (SSN,PNO),
3599CONSTRAINT FKS FOREIGN KEY (SSN) REFERENCES EMPLOYEE (SSN) ON DELETE
3600CASCADE,
3601CONSTRAINT FKP FOREIGN KEY (PNO) REFERENCES PROJECT (PNO) ON DELETE
3602CASCADE);
3603Table created
3604P a g e 57 | 70DBMS LABORATORY WITH MINI PROJECT
360515CSL58
3606SQL> DESC WORKS_ON;
3607Name
3608Null?
3609Type
3610----------------------------------------- -------- ----------------------------
3611SSN
3612NOT NULL
3613NUMBER(9)
3614PNO
3615NOT NULL
3616NUMBER(2)
3617HOURS
3618NUMBER(3)
3619INSERTION:
3620SQL> INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY)
3621VALUES(&SSN,'&NAME','&ADDRESS','&SEX',&SALARY
3622);
3623Enter value for ssn: 123456789
3624Enter value for name: ASHA
3625Enter value for address: YELAHANKA
3626Enter value for sex: F
3627Enter value for salary: 500000
3628old 1: INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY)
3629VALUES(&SSN,'&NAME','&ADDRESS','&SEX',&SA
3630new 1: INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY)
3631VALUES(123456789,'ASHA','YELAHANKA','F',5
36321 row created.
3633SQL> /
3634Enter value for ssn: 234567891
3635Enter value for name: SHEELA
3636Enter value for address: JAKPUR
3637Enter value for sex: F
3638Enter value for salary: 700000
3639old 1: INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY)
3640VALUES(&SSN,'&NAME','&ADDRESS','&SEX',&SA
3641new 1: INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY)
3642VALUES(234567891,'SHEELA','JAKPUR','F',70
36431 row created.
3644SQL> /
3645Enter value for ssn: 345678912
3646Enter value for name: PALLAVI
3647Enter value for address: NEWTOWN
3648Enter value for sex: F
3649Enter value for salary: 700000
3650old 1: INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY)
3651VALUES(&SSN,'&NAME','&ADDRESS','&SEX',&SA
3652new 1: INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY)
3653VALUES(345678912,'PALLAVI','NEWTOWN','F',
36541 row created.
3655SQL> /
3656Enter value for ssn: 456789123
3657Enter value for name: SHREYAS
3658Enter value for address: BASAWESWAR NAGAR
3659Enter value for sex: M
3660P a g e 58 | 70DBMS LABORATORY WITH MINI PROJECT
366115CSL58
3662Enter value for salary: 750000
3663old 1: INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY)
3664VALUES(&SSN,'&NAME','&ADDRESS','&SEX',&SA
3665new 1: INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY)
3666VALUES(456789123,'SHREYAS','BASAWESWAR NA
36671 row created.
3668SQL> /
3669Enter value for ssn: 567891234
3670Enter value for name: MOHAN
3671Enter value for address: TUMKUR
3672Enter value for sex: M
3673Enter value for salary: 350000
3674old 1: INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY)
3675VALUES(&SSN,'&NAME','&ADDRESS','&SEX',&SA
3676new 1: INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY)
3677VALUES(567891234,'MOHAN','TUMKUR','M',350
36781 row created.
3679SQL> /
3680Enter value for ssn: 678912345
3681Enter value for name: SCOTT
3682Enter value for address: NEWYORK
3683Enter value for sex: M
3684Enter value for salary: 1000000
3685old 1: INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY)
3686VALUES(&SSN,'&NAME','&ADDRESS','&SEX',&SA
3687new 1: INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY)
3688VALUES(678912345,'SCOTT','NEWYORK','M',10
36891 row created.
3690SQL> /
3691Enter value for ssn: 789123456
3692Enter value for name: DIVYA
3693Enter value for address: HUNSMARENHALLI
3694Enter value for sex: F
3695Enter value for salary: 350000
3696old 1: INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY)
3697VALUES(&SSN,'&NAME','&ADDRESS','&SEX',&SA
3698new 1: INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY)
3699VALUES(789123456,'DIVYA','HUNSMARENHALLI'
37001 row created.
3701SQL> /
3702Enter value for ssn: 891234567
3703Enter value for name: SAPNA
3704Enter value for address: VIDYARANYAPURA
3705Enter value for sex: F
3706Enter value for salary: 350000
3707old 1: INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY)
3708VALUES(&SSN,'&NAME','&ADDRESS','&SEX',&SA
3709P a g e 59 | 70DBMS LABORATORY WITH MINI PROJECT
371015CSL58
3711new 1: INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY)
3712VALUES(891234567,'SAPNA','VIDYARANYAPURA'
37131 row created.
3714SQL> /
3715Enter value for ssn: 912345678
3716Enter value for name: REVAN
3717Enter value for address: MADIWALA
3718Enter value for sex: M
3719Enter value for salary: 450000
3720old 1: INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY)
3721VALUES(&SSN,'&NAME','&ADDRESS','&SEX',&SA
3722new 1: INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY)
3723VALUES(912345678,'REVAN','MADIWALA','M',4
37241 row created.
3725SQL> /
3726Enter value for ssn: 112233445
3727Enter value for name: SAVITHA
3728Enter value for address: DBSANDRA
3729Enter value for sex: F
3730Enter value for salary: 700000
3731old 1: INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY)
3732VALUES(&SSN,'&NAME','&ADDRESS','&SEX',&SA
3733new 1: INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY)
3734VALUES(112233445,'SAVITHA','DBSANDRA','F'
37351 row created.
3736SQL> /
3737Enter value for ssn: 223344556
3738Enter value for name: VIJAY
3739Enter value for address: VIJAYPURA
3740Enter value for sex: M
3741Enter value for salary: 600000
3742old 1: INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY)
3743VALUES(&SSN,'&NAME','&ADDRESS','&SEX',&SA
3744new 1: INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY)
3745VALUES(223344556,'VIJAY','VIJAYPURA','M',
37461 row created.
3747SQL> /
3748Enter value for ssn: 334455667
3749Enter value for name: MANDHAR
3750Enter value for address: JAYMAHAL
3751Enter value for sex: M
3752Enter value for salary: 500000
3753old 1: INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY)
3754VALUES(&SSN,'&NAME','&ADDRESS','&SEX',&SA
3755new 1: INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY)
3756VALUES(334455667,'MANDHAR','JAYMAHAL','M'
37571 row created.
3758P a g e 60 | 70DBMS LABORATORY WITH MINI PROJECT
375915CSL58
3760SQL> /
3761Enter value for ssn: 445566778
3762Enter value for name: RAGHAV
3763Enter value for address: YELAHANKA
3764Enter value for sex: M
3765Enter value for salary: 600000
3766old 1: INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY)
3767VALUES(&SSN,'&NAME','&ADDRESS','&SEX',&SA
3768new 1: INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY)
3769VALUES(445566778,'RAGHAV','YELAHANKA','M'
37701 row created.
3771SQL> INSERT INTO DEPARTMENT
3772VALUES(&DNO,'&DNAME',&MGRSSN,'&MGRSTARTDATE');
3773Enter value for dno: 1
3774Enter value for dname: CSE
3775Enter value for mgrssn: 234567891
3776Enter value for mgrstartdate: 01-JAN-10
3777old 1: INSERT INTO DEPARTMENT
3778VALUES(&DNO,'&DNAME',&MGRSSN,'&MGRSTARTDATE')
3779new 1: INSERT INTO DEPARTMENT VALUES(1,'CSE',234567891,'01-JAN-10')
37801 row created.
3781SQL> /
3782Enter value for dno: 2
3783Enter value for dname: ISE
3784Enter value for mgrssn: 345678912
3785Enter value for mgrstartdate: 15-FEB-11
3786old 1: INSERT INTO DEPARTMENT
3787VALUES(&DNO,'&DNAME',&MGRSSN,'&MGRSTARTDATE')
3788new 1: INSERT INTO DEPARTMENT VALUES(2,'ISE',345678912,'15-FEB-11')
37891 row created.
3790SQL> /
3791Enter value for dno: 3
3792Enter value for dname: ECE
3793Enter value for mgrssn: 456789123
3794Enter value for mgrstartdate: 01-MAR-12
3795old 1: INSERT INTO DEPARTMENT
3796VALUES(&DNO,'&DNAME',&MGRSSN,'&MGRSTARTDATE')
3797new 1: INSERT INTO DEPARTMENT VALUES(3,'ECE',456789123,'01-MAR-12')
37981 row created.
3799SQL> /
3800Enter value for dno: 4
3801Enter value for dname: ACCOUNTS
3802Enter value for mgrssn: 678912345
3803Enter value for mgrstartdate: 15-APR-13
3804P a g e 61 | 70DBMS LABORATORY WITH MINI PROJECT
380515CSL58
3806old 1: INSERT INTO DEPARTMENT
3807VALUES(&DNO,'&DNAME',&MGRSSN,'&MGRSTARTDATE')
3808new 1: INSERT INTO DEPARTMENT VALUES(4,'ACCOUNTS',678912345,'15-APR-13')
38091 row created.
3810SQL> /
3811Enter value for dno: 5
3812Enter value for dname: TCE
3813Enter value for mgrssn: 123456789
3814Enter value for mgrstartdate: 02-MAY-14
3815old 1: INSERT INTO DEPARTMENT
3816VALUES(&DNO,'&DNAME',&MGRSSN,'&MGRSTARTDATE')
3817new 1: INSERT INTO DEPARTMENT VALUES(5,'TCE',123456789,'02-MAY-14')
38181 row created.
3819SQL> /
3820Enter value for dno: 6
3821Enter value for dname: ECE
3822Enter value for mgrssn: 567891234
3823Enter value for mgrstartdate: 15-JUN-15
3824old 1: INSERT INTO DEPARTMENT
3825VALUES(&DNO,'&DNAME',&MGRSSN,'&MGRSTARTDATE')
3826new 1: INSERT INTO DEPARTMENT VALUES(6,'ECE',567891234,'15-JUN-15')
38271 row created.
3828SQL> SELECT * FROM DEPARTMENT;
3829DNO
3830----------
38311
38322
38333
38344
38355
38366
3837DNAME
3838---------------
3839CSE
3840ISE
3841ECE
3842ACCOUNTS
3843TCE
3844ECE
3845MGRSSN
3846----------
3847234567891
3848345678912
3849456789123
3850678912345
3851123456789
3852567891234
3853MGRSTARTDATE
3854---------
385501-JAN-10
385615-FEB-11
385701-MAR-12
385815-APR-13
385902-MAY-14
386015-JUN-15
38616 rows selected.
3862SQL> UPDATE EMPLOYEE SET SUPERSSN=&SUPERSSN,DNO=&DNO WHERE SSN=&SSN;
3863Enter value for superssn: 234567891
3864Enter value for dno: 1
3865Enter value for ssn: 123456789
3866old 1: UPDATE EMPLOYEE SET SUPERSSN=&SUPERSSN,DNO=&DNO WHERE SSN=&SSN
3867new 1: UPDATE EMPLOYEE SET SUPERSSN=234567891,DNO=1 WHERE SSN=123456789
38681 row updated.
3869SQL> /
3870Enter value for superssn: 678912345
3871Enter value for dno: 2
3872Enter value for ssn: 234567891
3873old 1: UPDATE EMPLOYEE SET SUPERSSN=&SUPERSSN,DNO=&DNO WHERE SSN=&SSN
3874P a g e 62 | 70DBMS LABORATORY WITH MINI PROJECT
387515CSL58
3876new 1: UPDATE EMPLOYEE SET SUPERSSN=678912345,DNO=2 WHERE SSN=234567891
3877SQL> /
3878Enter value for superssn: 234567891
3879Enter value for dno: 3
3880Enter value for ssn: 345678912
3881old 1: UPDATE EMPLOYEE SET SUPERSSN=&SUPERSSN,DNO=&DNO WHERE SSN=&SSN
3882new 1: UPDATE EMPLOYEE SET SUPERSSN=234567891,DNO=3 WHERE SSN=345678912
38831 row updated.
3884SQL> /
3885Enter value for superssn: 234567891
3886Enter value for dno: 4
3887Enter value for ssn: 456789123
3888old 1: UPDATE EMPLOYEE SET SUPERSSN=&SUPERSSN,DNO=&DNO WHERE SSN=&SSN
3889new 1: UPDATE EMPLOYEE SET SUPERSSN=234567891,DNO=4 WHERE SSN=456789123
38901 row updated.
3891SQL> /
3892Enter value for superssn: 678912345
3893Enter value for dno: 5
3894Enter value for ssn: 567891234
3895old 1: UPDATE EMPLOYEE SET SUPERSSN=&SUPERSSN,DNO=&DNO WHERE SSN=&SSN
3896new 1: UPDATE EMPLOYEE SET SUPERSSN=678912345,DNO=5 WHERE SSN=567891234
38971 row updated.
3898SQL> /
3899Enter value for superssn: NULL
3900Enter value for dno: 6
3901Enter value for ssn: 678912345
3902old 1: UPDATE EMPLOYEE SET SUPERSSN=&SUPERSSN,DNO=&DNO WHERE SSN=&SSN
3903new 1: UPDATE EMPLOYEE SET SUPERSSN=NULL,DNO=6 WHERE SSN=678912345
39041 row updated.
3905SQL> /
3906Enter value for superssn: 223344556
3907Enter value for dno: 2
3908Enter value for ssn: 789123456
3909old 1: UPDATE EMPLOYEE SET SUPERSSN=&SUPERSSN,DNO=&DNO WHERE SSN=&SSN
3910new 1: UPDATE EMPLOYEE SET SUPERSSN=223344556,DNO=2 WHERE SSN=789123456
39111 row updated.
3912SQL> /
3913Enter value for superssn: 112233445
3914Enter value for dno: 2
3915Enter value for ssn: 891234567
3916old 1: UPDATE EMPLOYEE SET SUPERSSN=&SUPERSSN,DNO=&DNO WHERE SSN=&SSN
3917new 1: UPDATE EMPLOYEE SET SUPERSSN=112233445,DNO=2 WHERE SSN=891234567
39181 row updated.
3919SQL> /
3920P a g e 63 | 70DBMS LABORATORY WITH MINI PROJECT
392115CSL58
3922Enter value for superssn: 123456789
3923Enter value for dno: 2
3924Enter value for ssn: 912345678
3925old 1: UPDATE EMPLOYEE SET SUPERSSN=&SUPERSSN,DNO=&DNO WHERE SSN=&SSN
3926new 1: UPDATE EMPLOYEE SET SUPERSSN=123456789,DNO=2 WHERE SSN=912345678
39271 row updated.
3928SQL> /
3929Enter value for superssn: 234567891
3930Enter value for dno: 2
3931Enter value for ssn: 112233445
3932old 1: UPDATE EMPLOYEE SET SUPERSSN=&SUPERSSN,DNO=&DNO WHERE SSN=&SSN
3933new 1: UPDATE EMPLOYEE SET SUPERSSN=234567891,DNO=2 WHERE SSN=112233445
39341 row updated.
3935SQL> /
3936Enter value for superssn: 345678912
3937Enter value for dno: 2
3938Enter value for ssn: 223344556
3939old 1: UPDATE EMPLOYEE SET SUPERSSN=&SUPERSSN,DNO=&DNO WHERE SSN=&SSN
3940new 1: UPDATE EMPLOYEE SET SUPERSSN=345678912,DNO=2 WHERE SSN=223344556
39411 row updated.
3942SQL> /
3943Enter value for superssn: 456789123
3944Enter value for dno: 4
3945Enter value for ssn: 334455667
3946old 1: UPDATE EMPLOYEE SET SUPERSSN=&SUPERSSN,DNO=&DNO WHERE SSN=&SSN
3947new 1: UPDATE EMPLOYEE SET SUPERSSN=456789123,DNO=4 WHERE SSN=334455667
39481 row updated.
3949SQL> /
3950Enter value for superssn: 345678912
3951Enter value for dno: 4
3952Enter value for ssn: 445566778
3953old 1: UPDATE EMPLOYEE SET SUPERSSN=&SUPERSSN,DNO=&DNO WHERE SSN=&SSN
3954new 1: UPDATE EMPLOYEE SET SUPERSSN=345678912,DNO=4 WHERE SSN=445566778
39551 row updated.
3956SQL> SELECT *FROM EMPLOYEE;
3957SSN
3958NAME
3959---------
3960-------------
3961123456789 ASHA
3962234567891 SHEELA
3963345678912 PALLAVI
3964456789123 SHREYAS
3965ADDRESS
3966SEX SALARY
3967-------
3968--------- ------------
3969YELAHANKA
3970F
3971500000
3972JAKPUR
3973F
3974700000
3975NEWTOWN
3976F
3977700000
3978BASAWESWAR M
3979750000
3980NAGAR
3981567891234 MOHAN
3982TUMKUR
3983M
3984350000
3985678912345 SCOTT
3986NEWYORK
3987M 1000000
3988789123456 DIVYA
3989HUNSMARENHALLI F
3990350000
3991SUPERSSN
3992DNO
3993--- ----
3994------
3995234567891
39961
3997678912345
39982
3999234567891 3
4000234567891 4
4001678912345
4002223344556
40035
40046
40052
4006P a g e 64 | 70DBMS LABORATORY WITH MINI PROJECT
4007891234567 SAPNA
4008VIDYARANYAPURA
4009912345678 REVAN
4010MADIWALA
4011112233445 SAVITHA
4012DBSANDRA
4013223344556 VIJAY
4014VIJAYPURA
4015334455667 MANDHAR
4016JAYMAHAL
4017445566778 RAGHAV
4018YELAHANKA
401915CSL58
4020F
4021M
4022F
4023M
4024M
4025M
4026350000
4027450000
4028700000
4029600000
4030500000
4031600000
4032112233445
4033123456789
4034234567891
4035345678912
4036456789123
4037345678912
40382
40392
40402
40412
40424
40434
404413 rows selected
4045SQL> INSERT INTO DLOCATION VALUES(&DNO,'&DLOC');
4046Enter value for dno: 1
4047Enter value for dloc: NB1
4048old 1: INSERT INTO DLOCATION VALUES(&DNO,'&DLOC')
4049new 1: INSERT INTO DLOCATION VALUES(1,'NB1')
40501 row created.
4051SQL> /
4052Enter value for dno: 2
4053Enter value for dloc: NB0
4054old 1: INSERT INTO DLOCATION VALUES(&DNO,'&DLOC')
4055new 1: INSERT INTO DLOCATION VALUES(2,'NB0')
40561 row created.
4057SQL> /
4058Enter value for dno: 3
4059Enter value for dloc: NB2
4060old 1: INSERT INTO DLOCATION VALUES(&DNO,'&DLOC')
4061new 1: INSERT INTO DLOCATION VALUES(3,'NB2')
40621 row created.
4063SQL> /
4064Enter value for dno: 4
4065Enter value for dloc: NB1
4066old 1: INSERT INTO DLOCATION VALUES(&DNO,'&DLOC')
4067new 1: INSERT INTO DLOCATION VALUES(4,'NB1')
40681 row created.
4069SQL> /
4070Enter value for dno: 5
4071Enter value for dloc: NB3
4072old 1: INSERT INTO DLOCATION VALUES(&DNO,'&DLOC')
4073new 1: INSERT INTO DLOCATION VALUES(5,'NB3')
40741 row created.
4075SQL> /
4076Enter value for dno: 6
4077Enter value for dloc: EEE2
4078old 1: INSERT INTO DLOCATION VALUES(&DNO,'&DLOC')
4079new 1: INSERT INTO DLOCATION VALUES(6,'EEE2')
40801 row created.
4081P a g e 65 | 70DBMS LABORATORY WITH MINI PROJECT
408215CSL58
4083SQL> SELECT * FROM DLOCATION;
4084DNO DLOC
4085--------- ---------------
40861
4087NB1
40882
4089NB0
40903
4091NB2
40924
4093NB1
40945
4095NB3
40966
4097EEE2
40986 rows selected.
4099SQL> INSERT INTO PROJECT VALUES(&PNO,'&PNAME','&PLOCATION',&DNO);
4100Enter value for pno: 11
4101Enter value for pname: JAVA
4102Enter value for plocation: MARATHAHALLI
4103Enter value for dno: 1
4104old 1: INSERT INTO PROJECT VALUES(&PNO,'&PNAME','&PLOCATION',&DNO)
4105new 1: INSERT INTO PROJECT VALUES(11,'JAVA','MARATHAHALLI',1)
41061 row created.
4107SQL> /
4108Enter value for pno: 22
4109Enter value for pname: DOTNET
4110Enter value for plocation: HEBBAL
4111Enter value for dno: 2
4112old 1: INSERT INTO PROJECT VALUES(&PNO,'&PNAME','&PLOCATION',&DNO)
4113new 1: INSERT INTO PROJECT VALUES(22,'DOTNET','HEBBAL',2)
41141 row created.
4115SQL> /
4116Enter value for pno: 33
4117Enter value for pname: IOT
4118Enter value for plocation: MANYATA
4119Enter value for dno: 3
4120old 1: INSERT INTO PROJECT VALUES(&PNO,'&PNAME','&PLOCATION',&DNO)
4121new 1: INSERT INTO PROJECT VALUES(33,'IOT','MANYATA',3)
41221 row created.
4123SQL> /
4124Enter value for pno: 44
4125Enter value for pname: ANDROID
4126Enter value for plocation: YELAHANKA
4127Enter value for dno: 4
4128old 1: INSERT INTO PROJECT VALUES(&PNO,'&PNAME','&PLOCATION',&DNO)
4129new 1: INSERT INTO PROJECT VALUES(44,'ANDROID','YELAHANKA',4)
41301 row created.
4131SQL> /
4132P a g e 66 | 70DBMS LABORATORY WITH MINI PROJECT
413315CSL58
4134Enter value for pno: 55
4135Enter value for pname: BIGDATA
4136Enter value for plocation: KR PURAM
4137Enter value for dno: 5
4138old 1: INSERT INTO PROJECT VALUES(&PNO,'&PNAME','&PLOCATION',&DNO)
4139new 1: INSERT INTO PROJECT VALUES(55,'BIGDATA','KR PURAM',5)
41401 row created.
4141SQL> /
4142Enter value for pno: 66
4143Enter value for pname: WEB
4144Enter value for plocation: ELECTRONIC CITY
4145Enter value for dno: 6
4146old 1: INSERT INTO PROJECT VALUES(&PNO,'&PNAME','&PLOCATION',&DNO)
4147new 1: INSERT INTO PROJECT VALUES(66,'WEB','ELECTRONIC CITY',6)
41481 row created.
4149SQL> SELECT * FROM PROJECT;
4150PNO PNAME
4151PLOCATION
4152DNO
4153----------
4154--------------- ---------------
4155----------
415611
4157JAVA
4158MARATHAHALLI
41591
416022
4161DOTNET
4162HEBBAL
41632
416433
4165IOT
4166MANYATA
41673
416844
4169ANDROID
4170YELAHANKA
41714
417255
4173BIGDATA
4174KR PURAM
41755
417666
4177WEB
4178ELECTRONIC CITY
41796
41806 rows selected.
4181SQL> INSERT INTO WORKS_ON VALUES(&SSN,&PNO,&HOURS);
4182Enter value for ssn: 678912345
4183Enter value for pno: 11
4184Enter value for hours: 25
4185old 1: INSERT INTO WORKS_ON VALUES(&SSN,&PNO,&HOURS)
4186new 1: INSERT INTO WORKS_ON VALUES(678912345,11,25)
41871 row created.
4188SQL> /
4189Enter value for ssn: 123456789
4190Enter value for pno: 22
4191Enter value for hours: 30
4192old 1: INSERT INTO WORKS_ON VALUES(&SSN,&PNO,&HOURS)
4193new 1: INSERT INTO WORKS_ON VALUES(123456789,22,30)
41941 row created.
4195SQL> /
4196Enter value for ssn: 234567891
4197Enter value for pno: 33
4198Enter value for hours: 40
4199old 1: INSERT INTO WORKS_ON VALUES(&SSN,&PNO,&HOURS)
4200P a g e 67 | 70DBMS LABORATORY WITH MINI PROJECT
420115CSL58
4202new 1: INSERT INTO WORKS_ON VALUES(234567891,33,40)
42031 row created.
4204SQL> /
4205Enter value for ssn: 678912345
4206Enter value for pno: 44
4207Enter value for hours: 20
4208old 1: INSERT INTO WORKS_ON VALUES(&SSN,&PNO,&HOURS)
4209new 1: INSERT INTO WORKS_ON VALUES(678912345,44,20)
42101 row created.
4211SQL> /
4212Enter value for ssn: 345678912
4213Enter value for pno: 55
4214Enter value for hours: 50
4215old 1: INSERT INTO WORKS_ON VALUES(&SSN,&PNO,&HOURS)
4216new 1: INSERT INTO WORKS_ON VALUES(345678912,55,50)
42171 row created.
4218SQL> /
4219Enter value for ssn: 456789123
4220Enter value for pno: 66
4221Enter value for hours: 60
4222old 1: INSERT INTO WORKS_ON VALUES(&SSN,&PNO,&HOURS)
4223new 1: INSERT INTO WORKS_ON VALUES(456789123,66,60)
42241 row created.
4225SQL> SELECT * FROM WORKS_ON;
4226SSN
4227PNO HOURS
4228---------- ---------- ----------
4229678912345
423011
423125
4232123456789
423322
423430
4235234567891
423633
423740
4238678912345
423944
424020
4241345678912
424255
424350
4244456789123
424566
424660
42476 rows selected.
4248P a g e 68 | 70DBMS LABORATORY WITH MINI PROJECT
424915CSL58
4250QUERIES:
42511.Make a list of all project members for projects that involve an employee whose name is SCOTT
4252either as a worker or as a manager of the department that controls the project.
4253(SELECT DISTINCT P.PNO
4254FROM PROJECT P,DEPARTMENT D,EMPLOYEE E
4255WHERE P.DNO=D.DNO AND D.MGRSSN=E.SSN AND E.NAME='SCOTT')
4256UNION
4257(SELECT DISTINCT P.PNO
4258FROM PROJECT P,WORKS_ON W,EMPLOYEE E
4259WHERE P.PNO=W.PNO AND W.SSN=E.SSN AND E.NAME='SCOTT');
4260Output:
4261PNO
4262----------
426311
426444
42652.Show the resulting salry for employee working on IOT project is given a 10% raise.
4266SELECT E.NAME,1.1*E.SALARY AS HIKE_SALARY
4267FROM EMPLOYEE E,WORKS_ON W,PROJECT P
4268WHERE E.SSN=W.SSN AND P.PNO=W.PNO AND P.PNAME='IOT';
4269Output:
4270NAME
4271HIKE_SALARY
4272-------------------- -----------
4273SHEELA
4274770000
42753.Find the sum of salaries of all employees of ‘ACCOUNTS’ department as well as the
4276MAX(SAL),MIN(SAL),AVG(SAL) in this department.
4277SELECT SUM(E.SALARY) AS SUM_SAL,MAX(E.SALARY) AS MAX_SAL,MIN(E.SALARY) AS
4278MIN_SAL,AVG(E.SALARY) AS AVG_SAL
4279FROM EMPLOYEE E,DEPARTMENT D
4280WHERE E.DNO=D.DNO AND D.DNAME='ACCOUNTS';
4281SUM_SAL MAX_SAL MIN_SAL AVG_SAL
4282----------
4283----------
4284---------- ----------
42851850000
4286750000
4287500000
4288616666.667
42894.Retrive the name of each employee who works on all the projects controlled by department
4290no. 5.(use NOT EXISTS ) operator.
4291SQL>SELECT E.NAME FROM EMPLOYEE E
4292WHERE NOT EXISTS((SELECT P.PNO FROM PROJECT P WHERE P.DNO=5)
4293MINUS (SELECT W.PNO FROM WORKS_ON W WHERE E.SSN=W.SSN));
4294NAME
4295--------------------
4296PALLAVI
4297P a g e 69 | 70DBMS LABORATORY WITH MINI PROJECT
429815CSL58
4299Alternate:
4300SELECT E.NAME FROM EMPLOYEE E
4301WHERE NOT EXISTS(SELECT *
4302FROM WORKS_ON W1
4303WHERE (W1.PNO IN(SELECT P.PNO
4304FROM PROJECT P
4305WHERE P.DNO=5)
43062 AND
43073 NOT EXISTS(SELECT *
4308FROM WORKS_ON W2
4309WHERE W2.SSN=E.SSN AND W2.PNO=W1.PNO)));
4310Output:
4311NAME
4312--------------------
4313PALLAVI
43145.For each department that has more than 5 employees retrieve the dno and no. of its employees
4315who are making more than 6,00,000.
4316SELECT DNO,COUNT(*) AS NO_OF_EMP
4317FROM EMPLOYEE
4318WHERE SALARY>600000 AND DNO IN(SELECT DNO
4319FROM EMPLOYEE
4320GROUP BY(DNO)
4321HAVING COUNT(*)>5)
4322GROUP BY DNO;
4323Output:
4324DNO NO_OF_EMP
4325---------- ----------
43262
43272