· 4 years ago · Feb 03, 2021, 02:22 PM
11------
2Table Creation
3
4CREATE TABLE PUBLISHER
5(
6NAME VARCHAR(20) PRIMARY KEY,
7PHONE BIGINT,
8ADDRESS VARCHAR(20)
9);
10
11
12
13
14
15CREATE TABLE BOOK
16(
17BOOK_ID INTEGER PRIMARY KEY,
18TITLE VARCHAR(20),
19PUB_YEAR VARCHAR(20),
20PUBLISHER_NAME VARCHAR(20),
21FOREIGN KEY (PUBLISHER_NAME) REFERENCES PUBLISHER(NAME) ON DELETE CASCADE
22);
23
24
25
26
27CREATE TABLE BOOK_AUTHORS
28(
29AUTHOR_NAME VARCHAR(20),
30BOOK_ID INTEGER,
31FOREIGN KEY(BOOK_ID) REFERENCES BOOK (BOOK_ID) ON DELETE CASCADE,
32PRIMARY KEY(BOOK_ID, AUTHOR_NAME)
33);
34
35
36
37
38
39CREATE TABLE LIBRARY_PROGRAMME
40(
41PROGRAMME_ID INTEGER PRIMARY KEY,
42PROGRAMME_NAME VARCHAR(50),
43ADDRESS VARCHAR(50)
44);
45
46
47CREATE TABLE BOOK_COPIES
48(
49NO_OF_COPIES INTEGER,
50BOOK_ID INTEGER,
51PROGRAMME_ID INTEGER,
52FOREIGN KEY(BOOK_ID) REFERENCES BOOK(BOOK_ID)ON DELETE CASCADE,
53FOREIGN KEY(PROGRAMME_ID) REFERENCES LIBRARY_PROGRAMME(PROGRAMME_ID)ON DELETE CASCADE,
54PRIMARY KEY(BOOK_ID, PROGRAMME_ID)
55);
56
57
58
59CREATE TABLE CARD
60(
61CARD_NO INTEGER PRIMARY KEY
62);
63
64
65
66
67CREATE TABLE BOOK_LENDING
68(
69DATE_OUT DATE,
70DUE_DATE DATE,
71BOOK_ID INTEGER,
72PROGRAMME_ID INTEGER,
73FOREIGN KEY (BOOK_ID) REFERENCES BOOK(BOOK_ID) ON DELETE CASCADE,
74FOREIGN KEY (PROGRAMME_ID) REFERENCES LIBRARY_PROGRAMME(PROGRAMME_ID) ON DELETE CASCADE,
75CARD_NO INTEGER,
76FOREIGN KEY (CARD_NO) REFERENCES CARD(CARD_NO) ON DELETE CASCADE,
77PRIMARY KEY(BOOK_ID, PROGRAMME_ID, CARD_NO)
78);
79
80
81
82
83
84
85INSERT INTO PUBLISHER VALUES ('MCGRAW-HILL', 9944403999, 'BANGALORE');
86INSERT INTO PUBLISHER VALUES ('PEARSON', 8050662235, 'NEWDELHI');
87INSERT INTO PUBLISHER VALUES ('SUDHA', 9964161212, 'HYDRABAD');
88INSERT INTO PUBLISHER VALUES ('DREAMLAND', 9739011243, 'CHENAI');
89INSERT INTO PUBLISHER VALUES ('TULIKA', 9945123145, 'BANGALORE');
90
91INSERT INTO BOOK VALUES (1,'DBMS','JAN-2017', 'MCGRAW-HILL');
92INSERT INTO BOOK VALUES (2,'ADBMS','JUN-2016', 'MCGRAW-HILL');
93INSERT INTO BOOK VALUES (3,'CN','SEP-2016', 'PEARSON');
94INSERT INTO BOOK VALUES (4,'OS','MAY-2016', 'PEARSON');
95INSERT INTO BOOK VALUES (5,'CG','SEP-2015', 'TULIKA');
96
97
98INSERT INTO BOOK_AUTHORS VALUES ('NAVATHE', 1);
99INSERT INTO BOOK_AUTHORS VALUES ('NAVATHE', 2);
100INSERT INTO BOOK_AUTHORS VALUES ('TANENBAUM', 3);
101INSERT INTO BOOK_AUTHORS VALUES ('EDWARD ANGEL', 4);
102INSERT INTO BOOK_AUTHORS VALUES ('GALVIN', 5);
103
104
105INSERT INTO LIBRARY_PROGRAMME VALUES (11,'ISE','BANGALORE');
106INSERT INTO LIBRARY_PROGRAMME VALUES (12,'CSE','BANGALORE');
107INSERT INTO LIBRARY_PROGRAMME VALUES (13,'ME', 'TUMKUR');
108INSERT INTO LIBRARY_PROGRAMME VALUES (14,'ECE','MYSORE');
109INSERT INTO LIBRARY_PROGRAMME VALUES (10,'EEE','BANGALORE');
110
111INSERT INTO BOOK_COPIES VALUES (9, 1, 10);
112INSERT INTO BOOK_COPIES VALUES (4, 1, 11);
113INSERT INTO BOOK_COPIES VALUES (2, 2, 12);
114INSERT INTO BOOK_COPIES VALUES (5, 2, 13);
115INSERT INTO BOOK_COPIES VALUES (6, 3, 14);
116INSERT INTO BOOK_COPIES VALUES (1, 5, 10);
117INSERT INTO BOOK_COPIES VALUES (3, 4, 11);
118
119INSERT INTO CARD VALUES (100);
120INSERT INTO CARD VALUES (101);
121INSERT INTO CARD VALUES (102);
122INSERT INTO CARD VALUES (103);
123INSERT INTO CARD VALUES (104);
124
125
126INSERT INTO BOOK_LENDING VALUES ('2017-01-01','2017-06-01', 1, 10, 101);
127INSERT INTO BOOK_LENDING VALUES ('2017-01-11','2017-03-11', 3, 14, 101);
128INSERT INTO BOOK_LENDING VALUES ('2017-02-21','2017-04-21', 2, 13, 101);
129INSERT INTO BOOK_LENDING VALUES ('2017-03-15','2017-07-15', 4, 11, 101);
130INSERT INTO BOOK_LENDING VALUES ('2017-04-12','2017-05-12', 1, 11, 104);
131
1321.Retrieve details of all books in the library – id, title, name of publisher, authors, number of copies in each branch, etc.
133
134SELECT B.BOOK_ID, B.TITLE, B.PUBLISHER_NAME, A.AUTHOR_NAME, C.NO_OF_COPIES, L.PROGRAMME_ID
135FROM BOOK B, BOOK_AUTHORS A, BOOK_COPIES C, LIBRARY_PROGRAMME L
136WHERE B.BOOK_ID=A.BOOK_ID
137AND B.BOOK_ID=C.BOOK_ID
138AND L.PROGRAMME_ID=C.PROGRAMME_ID;
139
1402.Get the particulars of borrowers who have borrowed more than 3 books, but from Jan 2017 to Jun 2017.
141
142SELECT CARD_NO
143FROM BOOK_LENDING
144WHERE DATE_OUT BETWEEN '2017-01-01' AND '2017-07-15'
145GROUP BY (CARD_NO)
146HAVING COUNT(*)>3;
147
148
1493.Delete a book in BOOK table. Update the contents of other tables to reflect this data manipulation operation.
150
151DELETE FROM BOOK
152WHERE BOOK_ID=3;
153
154
155
1564.Partition the BOOK table based on year of publication. Demonstrate its working with a simple query.
157
158CREATE VIEW VPUBLICATION AS
159(
160SELECT BOOK_ID,TITLE,PUB_YEAR FROM BOOK
161 GROUP BY PUB_YEAR,BOOK_ID, TITLE
162);
163
164View created.
165
166
1675.Create a view of all books and its number of copies that are currently available in the Library.
168
169CREATE VIEW VBOOKS AS
170SELECT B.BOOK_ID,B.TITLE,C.NO_OF_COPIES
171FROM BOOK B,BOOK_COPIES C,LIBRARY_PROGRAMME L
172WHERE B.BOOK_ID=C.BOOK_ID
173AND C.PROGRAMME_ID=L.PROGRAMME_ID;
174
175View created.
1762--------------------
177CREATE TABLE SALESMAN
178(
179SALESMAN_ID INT(4)PRIMARY KEY,
180NAME VARCHAR(20),
181CITY VARCHAR(20),
182COMMISSION VARCHAR(20)
183);
184
185CREATE TABLE CUSTOMER
186(
187CUSTOMER_ID INT(4),
188CUST_NAME VARCHAR(20),
189CITY VARCHAR(20),
190GRADE INT(3),
191SALESMANID INT(10),
192PRIMARY KEY (CUSTOMER_ID),
193FOREIGN KEY(SALESMANID) REFERENCES SALESMAN(SALESMAN_ID) ON DELETE SET NULL
194);
195
196CREATE TABLE ORDERS
197(
198ORD_NO INT(5),
199PURCHASE_AMT DECIMAL(10, 2),
200ORD_DATE DATE,
201CUSTOMER_ID INT(4),
202SALESMAN_ID INT(4),
203PRIMARY KEY(ORD_NO),
204FOREIGN KEY(CUSTOMER_ID) REFERENCES CUSTOMER(CUSTOMER_ID) ON DELETE CASCADE,
205FOREIGN KEY(SALESMAN_ID) REFERENCES SALESMAN (SALESMAN_ID) ON DELETE CASCADE
206);
207
208INSERT INTO SALESMAN VALUES (1000, 'KIRAN','BANGALORE','25 %');
209INSERT INTO SALESMAN VALUES (1001, 'CHETHAN','BANGALORE','25 %');
210INSERT INTO SALESMAN VALUES (2001, 'SUHAS','BANGALORE','20 %');
211INSERT INTO SALESMAN VALUES (3001, 'KUMAR','MYSORE','15 %');
212INSERT INTO SALESMAN VALUES (4001, 'SMITHA','DELHI','30 %');
213INSERT INTO SALESMAN VALUES (5001, 'HARSHA','HYDRABAD','15 %');
214
215
216
217INSERT INTO CUSTOMER VALUES (100, 'ARUN','BANGALORE', 100, 1001);
218INSERT INTO CUSTOMER VALUES (101, 'AJAY','MANGALORE', 300, 1001);
219INSERT INTO CUSTOMER VALUES (102, 'GIRISH','CHENNAI', 400, 2001);
220INSERT INTO CUSTOMER VALUES (103, 'RANJITH','BANGALORE', 200, 2001);
221INSERT INTO CUSTOMER VALUES (104, 'LATHA','BANGALORE', 400, 3001);
222
223INSERT INTO ORDERS VALUES (50, 5000, '2017-05-04', 100, 1001);
224INSERT INTO ORDERS VALUES (51, 450, '2017-01-20', 100, 2001);
225INSERT INTO ORDERS VALUES (52, 1000, '2017-02-24', 103, 2001);
226INSERT INTO ORDERS VALUES (53, 3500, '2017-04-13', 104, 3001);
227INSERT INTO ORDERS VALUES (54, 550, '2017-03-09', 102, 2001);
228
2291. Count the customers with grades above Bangalore’s average.
230
231SELECT GRADE,COUNT(DISTINCT CUSTOMER_ID)
232FROM CUSTOMER
233GROUP BY GRADE
234HAVING GRADE>
235(
236SELECT AVG(GRADE)
237FROM CUSTOMER
238WHERE CITY='BANGALORE'
239);
240
241
242
2432. Find the name and numbers of all salesmen who had more than one customer.
244
245SELECT SALESMAN_ID,NAME
246 FROM SALESMAN A
247 WHERE 1<(SELECT COUNT(*)
248 FROM CUSTOMER
249 WHERE SALESMAN_ID=A.SALESMAN_ID);
250
251
2523. List all salesmen and indicate those who have and don’t have customers in their cities (Use UNION operation.)
253
254SELECT SALESMAN.SALESMAN_ID, NAME,CUST_NAME,COMMISSION
255FROM SALESMAN,CUSTOMER
256WHERE SALESMAN.CITY = CUSTOMER.CITY
257UNION
258SELECT SALESMAN_ID,NAME,'NO MATCH',COMMISSION
259FROM SALESMAN
260WHERE NOT CITY=ANY
261 (
262SELECT CITY
263 FROM CUSTOMER
264);
265
266
2674. Create a view that finds the salesman who has the customer with the highest order of a day.
268
269CREATE VIEW ELITSALESMAN AS
270SELECT B.ORD_DATE,A.SALESMAN_ID,A.NAME
271FROM SALESMAN A,ORDERS B
272WHERE A.SALESMAN_ID = B.SALESMAN_ID
273AND B.PURCHASE_AMT=(SELECT MAX(PURCHASE_AMT)
274 FROM ORDERS C
275 WHERE C.ORD_DATE = B.ORD_DATE);
276
277
2785. Demonstrate the DELETE operation by removing salesman with id 1000. All his orders must also be deleted.
279
280Use ON DELETE CASCADE at the end of foreign key definitions while creating child table orders and then execute the following:
281
282Use ON DELETE SET NULL at the end of foreign key definitions while creating child table customers and then executes the following:
283
284 SELECT * FROM SALESMAN;
285
286
287DELETE FROM SALESMAN
288WHERE SALESMAN_ID=1000;
2893------
290CREATE TABLE ACTOR
291(
292ACT_ID INT(3),
293ACT_NAME VARCHAR(20),
294ACT_GENDER CHAR(1),
295PRIMARY KEY(ACT_ID)
296);
297
298CREATE TABLE DIRECTOR
299(
300DIR_ID INT(3),
301DIR_NAME VARCHAR(20),
302DIR_PHONE BIGINT(10),
303PRIMARY KEY(DIR_ID)
304);
305
306CREATE TABLE MOVIES
307(
308MOV_ID INT(4),
309MOV_TITLE VARCHAR(25),
310MOV_YEAR INT(4),
311MOV_LANG VARCHAR(12),
312DIR_ID INT(3),
313PRIMARY KEY (MOV_ID),
314FOREIGN KEY (DIR_ID) REFERENCES DIRECTOR(DIR_ID)
315);
316
317CREATE TABLE MOVIE_CAST
318(
319ACT_ID INT(3),
320MOV_ID INT(4),
321ROLE VARCHAR(10),
322PRIMARY KEY(ACT_ID, MOV_ID),
323FOREIGN KEY(ACT_ID) REFERENCES ACTOR(ACT_ID),
324FOREIGN KEY(MOV_ID) REFERENCES MOVIES(MOV_ID)
325);
326
327CREATE TABLE RATING
328(
329MOV_ID INT(4),
330REV_STARS VARCHAR(25),
331PRIMARY KEY (MOV_ID),
332FOREIGN KEY (MOV_ID) REFERENCES MOVIES(MOV_ID)
333);
334
335
336INSERT INTO ACTOR VALUES (401,'ANUSHKA','F');
337INSERT INTO ACTOR VALUES (402,'PRABHAS','M');
338INSERT INTO ACTOR VALUES (403,'PUNITH','M');
339INSERT INTO ACTOR VALUES (404,'JERMY','M');
340
341INSERT INTO DIRECTOR VALUES (100,'RAJAMOULI', 9844403999);
342INSERT INTO DIRECTOR VALUES (101,'HITCHCOCK', 9964161212);
343INSERT INTO DIRECTOR VALUES (102,'FARAN', 9731317703);
344INSERT INTO DIRECTOR VALUES (103,'STEVEN SPIELBERG', 9739011243);
345
346INSERT INTO MOVIES VALUES(1001,'BAHUBALI-2',2017,'TELAGU',100);
347INSERT INTO MOVIES VALUES(1002,'BAHUBALI-1',2015,'TELAGU',100);
348INSERT INTO MOVIES VALUES(1003,'AKASH', 2008,'KANNADA', 101);
349INSERT INTO MOVIES VALUES(1004,'WAR HORSE', 2011, 'ENGLISH', 103);
350
351INSERT INTO MOVIE_CAST VALUES(401,1002,'HEROINE');
352INSERT INTO MOVIE_CAST VALUES(401,1001,'HEROINE');
353INSERT INTO MOVIE_CAST VALUES(403,1003,'HERO');
354INSERT INTO MOVIE_CAST VALUES(403,1002,'GUEST');
355INSERT INTO MOVIE_CAST VALUES(404,1004,'HERO');
356
357INSERT INTO RATING VALUES(1001,4);
358INSERT INTO RATING VALUES(1002,2);
359INSERT INTO RATING VALUES(1003,5);
360INSERT INTO RATING VALUES(1004,4);
361
3621.List the titles of all movies directed by ‘Hitchcock’.
363
364SELECT MOV_TITLE
365FROM MOVIES
366WHERE DIR_ID IN
367(
368SELECT DIR_ID
369FROM DIRECTOR
370WHERE DIR_NAME='HITCHCOCK');
371
372
373
3742.Find the movie names where one or more actors acted in two or more movies.
375
376SELECT MOV_TITLE
377FROM MOVIES M,MOVIE_CAST MV
378WHERE M.MOV_ID=MV.MOV_ID AND ACT_ID IN(SELECT ACT_ID
379FROM MOVIE_CAST GROUP BY ACT_ID
380HAVING COUNT(ACT_ID)>1)
381GROUP BY MOV_TITLE
382HAVING COUNT(*)>1;
383
384
385
3863. List all actors who acted in a movie before 2000 and also in a movie after 2015 (use JOIN operation).
387
388SELECT ACT_NAME,MOV_TITLE,MOV_YEAR
389FROM ACTOR A
390JOIN MOVIE_CAST C
391ON A.ACT_ID=C.ACT_ID
392JOIN MOVIES M
393ON C.MOV_ID=M.MOV_ID
394WHERE M.MOV_YEAR NOT BETWEEN 2000 AND 2015;
395
396OR
397
398SELECT A.ACT_NAME,A.ACT_NAME,C.MOV_TITLE,C.MOV_YEAR
399FROM ACTOR A,MOVIE_CAST B,MOVIES C
400WHERE A.ACT_ID=B.ACT_ID
401AND B.MOV_ID=C.MOV_ID
402AND C.MOV_YEAR NOT BETWEEN 2000 AND 2015;
403
404
405
4064. Find the title of movies and number of stars for each movie that has at least one rating and find the highest number of stars that movie received. Sort the result by movie title.
407
408SELECT MOV_TITLE,MAX(REV_STARS)
409FROM MOVIES
410INNER JOIN RATING USING(MOV_ID)
411GROUP BY MOV_TITLE
412HAVING MAX(REV_STARS)>0
413ORDER BY MOV_TITLE;
414
4155. Update rating of all movies directed by ‘Steven Spielberg’ to 5
416KL
417
418UPDATE RATING
419SET REV_STARS=5
420WHERE MOV_ID IN(SELECT MOV_ID FROM MOVIES
421 WHERE DIR_ID IN(SELECT DIR_ID
422 FROM DIRECTOR
423WHERE DIR_NAME='STEVEN SPIELBERG'));
424
4254-------------------------
426CREATE TABLE STUDENT(
427USN VARCHAR(10) PRIMARY KEY,
428SNAME VARCHAR(25),
429ADDRESS VARCHAR(25),
430PHONE BIGINT,
431GENDER CHAR(1));
432
433CREATE TABLE SEMSEC(
434SSID VARCHAR(5) PRIMARY KEY,
435SEM INT,
436SEC CHAR(1));
437
438CREATE TABLE CLASS(
439USN VARCHAR(10),
440SSID VARCHAR(5),
441PRIMARY KEY(USN,SSID),
442FOREIGN KEY(USN) REFERENCES STUDENT(USN),
443FOREIGN KEY(SSID) REFERENCES SEMSEC(SSID));
444
445CREATE TABLE COURSE(
446SUBCODE VARCHAR(10) PRIMARY KEY,
447TITLE VARCHAR(20),
448SEM INT,
449CREDITS INT);
450
451CREATE TABLE IAMARKS(
452USN VARCHAR(10),
453SUBCODE VARCHAR(8),
454SSID VARCHAR(5),
455TEST1 INT,
456TEST2 INT,
457TEST3 INT,
458FINALIA INT,
459PRIMARY KEY(USN, SUBCODE, SSID),
460FOREIGN KEY(USN) REFERENCES STUDENT (USN),
461FOREIGN KEY(SUBCODE) REFERENCES COURSE(SUBCODE),
462FOREIGN KEY(SSID) REFERENCES SEMSEC(SSID));
463
464
465INSERT INTO STUDENT VALUES ('1AY13IS020','SANJAY','BELAGAVI', 9742507651,'M');
466INSERT INTO STUDENT VALUES ('1AY13IS062','VINDHYA','BENGALURU', 9908533455,'F');
467INSERT INTO STUDENT VALUES ('1BI15CS101','LATHA','BENGALURU', 9008636692,'F');
468INSERT INTO STUDENT VALUES ('1AY13IS066','SINDHU','MANGALURU', 9844625522,'F');
469INSERT INTO STUDENT VALUES ('1AY14IS010','SHILPA','BENGALURU', 7406380851,'M');
470INSERT INTO STUDENT VALUES ('1AY14IS032','JAGADEESH','BENGALURU', 9739011243,'M');
471INSERT INTO STUDENT VALUES ('1AY14IS025','SUHAS','BENGALURU', 9066186963,'F');
472INSERT INTO STUDENT VALUES ('1AY15CS101','CHETHAN','TUMKUR', 9844403999,'M');
473INSERT INTO STUDENT VALUES ('1AY15IS029','CHITRA','DAVANGERE', 9108716169,'F');
474INSERT INTO STUDENT VALUES ('1AY15IS045','JEEVA','BELLARY', 9845964241,'M');
475INSERT INTO STUDENT VALUES ('1AY15IS091','RANJITH','MANGALURU', 7204166613,'M');
476INSERT INTO STUDENT VALUES ('1AY16IS045','SHOBHA','KALBURGI', 8867052667,'M');
477INSERT INTO STUDENT VALUES ('1AY16IS088','SANGEETHA','SHIMOGA', 9591128981,'F');
478INSERT INTO STUDENT VALUES ('1AY16IS122','PRIYA','CHIKAMAGALUR', 9945123145,'M');
479INSERT INTO STUDENT VALUES ('1BI13CS091','PURVIKA','MYSORE', 9008636692,'F');
480
481INSERT INTO SEMSEC VALUES ('ISE8A', 8,'A');
482INSERT INTO SEMSEC VALUES ('ISE8B', 8,'B');
483INSERT INTO SEMSEC VALUES ('ISE8C', 8,'C');
484
485INSERT INTO SEMSEC VALUES ('ISE7A', 7,'A');
486INSERT INTO SEMSEC VALUES ('ISE7B', 7,'B');
487INSERT INTO SEMSEC VALUES ('ISE7C', 7,'C');
488
489INSERT INTO SEMSEC VALUES ('ISE6A', 6,'A');
490INSERT INTO SEMSEC VALUES ('ISE6B', 6,'B');
491INSERT INTO SEMSEC VALUES ('ISE6C', 6,'C');
492
493INSERT INTO SEMSEC VALUES ('ISE5A', 5,'A');
494INSERT INTO SEMSEC VALUES ('ISE5B', 5,'B');
495INSERT INTO SEMSEC VALUES ('ISE5C', 5,'C');
496
497INSERT INTO SEMSEC VALUES ('ISE4A', 4,'A');
498INSERT INTO SEMSEC VALUES ('ISE4B', 4,'B');
499INSERT INTO SEMSEC VALUES ('ISE4C', 4,'C');
500
501INSERT INTO SEMSEC VALUES ('ISE3A', 3,'A');
502INSERT INTO SEMSEC VALUES ('ISE3B', 3,'B');
503INSERT INTO SEMSEC VALUES ('ISE3C', 3,'C');
504
505INSERT INTO SEMSEC VALUES ('ISE2A', 2,'A');
506INSERT INTO SEMSEC VALUES ('ISE2B', 2,'B');
507INSERT INTO SEMSEC VALUES ('ISE2C', 2,'C');
508
509INSERT INTO SEMSEC VALUES ('ISE1A', 1,'A');
510INSERT INTO SEMSEC VALUES ('ISE1B', 1,'B');
511INSERT INTO SEMSEC VALUES ('ISE1C', 1,'C');
512
513INSERT INTO CLASS VALUES ('1AY13IS020', 'ISE8A');
514INSERT INTO CLASS VALUES ('1AY13IS062', 'ISE8A');
515INSERT INTO CLASS VALUES ('1AY13IS066', 'ISE8B');
516INSERT INTO CLASS VALUES ('1AY14IS010', 'ISE7A');
517INSERT INTO CLASS VALUES ('1AY14IS025', 'ISE7A');
518INSERT INTO CLASS VALUES ('1AY14IS032', 'ISE7A');
519INSERT INTO CLASS VALUES ('1AY15IS029', 'ISE4A');
520INSERT INTO CLASS VALUES ('1AY15IS045', 'ISE4B');
521INSERT INTO CLASS VALUES ('1AY15IS091', 'ISE4C');
522INSERT INTO CLASS VALUES ('1AY16IS045', 'ISE3A');
523INSERT INTO CLASS VALUES ('1AY16IS088', 'ISE3B');
524INSERT INTO CLASS VALUES ('1AY16IS122', 'ISE3C');
525INSERT INTO CLASS VALUES ('1BI13CS091', 'ISE8C');
526
527INSERT INTO COURSE VALUES ('10CS81','ACA', 8, 4);
528INSERT INTO COURSE VALUES ('10CS82','SSM', 8, 4);
529INSERT INTO COURSE VALUES ('10CS83','NM', 8, 4);
530INSERT INTO COURSE VALUES ('10CS84','CC', 8, 4);
531INSERT INTO COURSE VALUES ('10CS85','PW', 8, 4);
532
533INSERT INTO COURSE VALUES ('10CS71','OOAD', 7, 4);
534INSERT INTO COURSE VALUES ('10CS72','ECS', 7, 4);
535INSERT INTO COURSE VALUES ('10CS73','PTW', 7, 4);
536INSERT INTO COURSE VALUES ('10CS74','DWDM', 7, 4);
537INSERT INTO COURSE VALUES ('10CS75','JAVA', 7, 4);
538INSERT INTO COURSE VALUES ('10CS76','SAN', 7, 4);
539
540INSERT INTO COURSE VALUES ('15CS51', 'ME', 5, 4);
541INSERT INTO COURSE VALUES ('15CS52','CN', 5, 4);
542INSERT INTO COURSE VALUES ('15CS53','DBMS', 5, 4);
543INSERT INTO COURSE VALUES ('15CS54','ATC', 5, 4);
544INSERT INTO COURSE VALUES ('15CS55','JAVA', 5, 3);
545INSERT INTO COURSE VALUES ('15CS56','AI', 5, 3);
546INSERT INTO COURSE VALUES ('15CS41','M4', 4, 4);
547INSERT INTO COURSE VALUES ('15CS42','SE', 4, 4);
548INSERT INTO COURSE VALUES ('15CS43','DAA', 4, 4);
549INSERT INTO COURSE VALUES ('15CS44','MPMC', 4, 4);
550INSERT INTO COURSE VALUES ('15CS45','OOC', 4, 3);
551INSERT INTO COURSE VALUES ('15CS46','DC', 4, 3);
552INSERT INTO COURSE VALUES ('15CS31','M3', 3, 4);
553INSERT INTO COURSE VALUES ('15CS32','ADE', 3, 4);
554INSERT INTO COURSE VALUES ('15CS33','DSA', 3, 4);
555INSERT INTO COURSE VALUES ('15CS34','CO', 3, 4);
556INSERT INTO COURSE VALUES ('15CS35','USP', 3, 3);
557INSERT INTO COURSE VALUES ('15CS36','DMS', 3, 3);
558INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3) VALUES ('1BI13CS091','10CS81','ISE8C', 15, 16, 18);
559INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3) VALUES ('1BI13CS091','10CS82','ISE8C', 12, 19, 14);
560INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3) VALUES ('1BI13CS091','10CS83','ISE8C', 19, 15, 20);
561INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3) VALUES ('1BI13CS091','10CS84','ISE8C', 20, 16, 19);
562INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3) VALUES ('1BI13CS091','10CS85','ISE8C', 15, 15, 12);
563
564
565
566
567
5681.List all the student details studying in fourth semester ‘C’ section.
569
570SELECT S.*, SS.SEM, SS.SEC
571FROM STUDENT S, SEMSEC SS, CLASS C
572WHERE S.USN = C.USN AND
573SS.SSID = C.SSID AND
574SS.SEM = 4 AND
575SS.SEc='C';
576
5772.Compute the total number of male and female students in each semester and in each section.
578
579SELECT SS.SEM, SS.SEC, S.GENDER, COUNT(S.GENDER) AS COUNT
580FROM STUDENT S, SEMSEC SS, CLASS C
581WHERE S.USN = C.USN AND
582SS.SSID = C.SSID
583GROUP BY SS.SEM, SS.SEC, S.GENDER
584ORDER BY SEM;
585
586
5873. Create a view of Test1 marks of student USN ‘1BI15CS101’ in all courses.
588
589CREATE VIEW STU_TEST1_MARKS_VIEW1
590AS
591SELECT TEST1, SUBCODE
592FROM IAMARKS
593WHERE USN='1BI13CS091';
594
595
5964. Calculate the FinalIA (average of best two test marks) and update the corresponding table for all courses.
597
598
599UPDATE IAMARKS
600SET FINALIA=GREATEST(TEST1+TEST2,TEST2+TEST3,TEST1+TEST3)/2;
601
602UPDATE IAMARKS
603SET FINALIA=((TEST1+TEST2+TEST3)-LEAST(TEST1,TEST2,TEST3))/2;
604
605
6065. Categorize students based on the following criterion:
607If FinalIA = 17 to 20 then CAT = ‘Outstanding’
608If FinalIA = 12 to 16 then CAT = ‘Average’
609If FinalIA< 12 then CAT = ‘Weak’
610Give these details only for 8th semester A, B, and C section students.
611
612SELECT S.USN,S.SNAME,S.ADDRESS,S.PHONE,S.GENDER,
613 (CASE
614 WHEN IA.FINALIA BETWEEN 17 AND 20 THEN 'OUTSTANDING'
615 WHEN IA.FINALIA BETWEEN 12 AND 16 THEN 'AVERAGE'
616 ELSE 'WEAK'
617 END) AS CAT
618FROM STUDENT S, SEMSEC SS, IAMARKS IA, COURSE C
619WHERE S.USN = IA.USN AND
620SS.SSID = IA.SSID AND
621C.SUBCODE = IA.SUBCODE AND
622C.SEM = 8;
623
6245-----------------------------
625CREATE TABLE DEPARTMENT
626(
627DNO VARCHAR(20) PRIMARY KEY,
628DNAME VARCHAR(20),
629MGRSTARTDATE DATE
630);
631
632CREATE TABLE EMPLOYEE
633(
634SSN VARCHAR(20) PRIMARY KEY,
635FNAME VARCHAR(20),
636LNAME VARCHAR(20),
637ADDRESS VARCHAR(20),
638SEX VARCHAR(1),
639SALARY INTEGER,
640SUPERSSN VARCHAR(20),
641DNO VARCHAR(20),
642FOREIGN kEY(SUPERSSN) REFERENCES EMPLOYEE(SSN),
643FOREIGN kEY(DNO) REFERENCES DEPARTMENT(DNO)
644);
645
646ALTER TABLE DEPARTMENT
647ADD MGRSSN VARCHAR(20);
648
649
650
651ALTER TABLE DEPARTMENT
652ADD FOREIGN KEY (MGRSSN)REFERENCES EMPLOYEE(SSN);
653
654CREATE TABLE DLOCATION
655(
656DLOC VARCHAR(20),
657DNO VARCHAR(20),
658FOREIGN kEY(DNO) REFERENCES DEPARTMENT(DNO),
659PRIMARY KEY(DNO,DLOC)
660);
661
662CREATE TABLE PROJECT
663(
664PNO INTEGER PRIMARY KEY,
665PNAME VARCHAR(20),
666PLOCATION VARCHAR(20),
667DNO VARCHAR(20),
668FOREIGN kEY(DNO) REFERENCES DEPARTMENT(DNO)
669);
670
671CREATE TABLE WORKS_ON
672(
673HOURS INTEGER(2),
674SSN VARCHAR(20),
675PNO INTEGER,
676FOREIGN KEY(SSN) REFERENCES EMPLOYEE(SSN),
677FOREIGN KEY(PNO) REFERENCES PROJECT(PNO),
678PRIMARY KEY(SSN,PNO)
679);
680
681
682INSERT INTO EMPLOYEE(SSN,FNAME,LNAME,ADDRESS,SEX,SALARY)VALUES ('AYECE01','JOHN','SCOTT','BANGALORE','M',450000);
683
684INSERT INTO EMPLOYEE (SSN,FNAME,LNAME,ADDRESS,SEX,SALARY)VALUES ('AYISE01','JAMES','SMITH','BANGALORE','M',500000);
685
686INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX,SALARY)VALUES ('AYISE02','HEARN','BAKER','BANGALORE','M', 700000);
687INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES ('AYISE03','EDWARD','SCOTT','MYSORE','M', 500000);
688INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES ('AYISE04','PAVAN','HEGDE','MANGALORE','M', 650000);
689INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES ('AYISE05','GIRISH','MALYA','MYSORE','M', 450000);
690INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES ('AYISE06','NEHA','SN','BANGALORE','F', 800000);
691INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES ('AYACC01','AHANA','K','MANGALORE','F', 350000);
692INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES ('AYACC02','SANTHOSH','KUMAR','MANGALORE','M', 300000);
693INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES ('AYISE01','VEENA','M','MYSORE','M', 600000);
694INSERT INTO EMPLOYEE(SSN,FNAME,LNAME,ADDRESS,SEX,SALARY)VALUES ('AYIT01','NAGESH','HR','BANGALORE','M',500000);
695
696INSERT INTO DEPARTMENT VALUES ('1','ACCOUNTS','01-01-01','AYACC02');
697INSERT INTO DEPARTMENT VALUES ('2','IT','16-07-01','AYIT01');
698INSERT INTO DEPARTMENT VALUES ('3','ECE','08-06-01','AYECE01');
699INSERT INTO DEPARTMENT VALUES ('4','ISE','15-08-01','AYISE01');
700INSERT INTO DEPARTMENT VALUES ('5','ISE','02-06-01','AYISE05');
701
702Note: update entries of employee table to fill missing fields SUPERSSN and DNO
703UPDATE EMPLOYEE SET
704SUPERSSN=NULL, DNO='3'
705WHERE SSN='AYECE01';
706
707UPDATE EMPLOYEE SET
708SUPERSSN='AYISE02', DNO='5'
709WHERE SSN='AYISE01';
710
711UPDATE EMPLOYEE SET
712SUPERSSN='AYISE03', DNO='5'
713WHERE SSN='AYISE02';
714
715UPDATE EMPLOYEE SET
716SUPERSSN='AYISE04', DNO='5'
717WHERE SSN='AYISE03';
718
719UPDATE EMPLOYEE SET
720 DNO='5', SUPERSSN='AYISE05'
721WHERE SSN='AYISE04';
722
723UPDATE EMPLOYEE SET
724 DNO='5', SUPERSSN='AYISE06'
725WHERE SSN='AYISE05';
726
727UPDATE EMPLOYEE SET
728 DNO='5', SUPERSSN=NULL
729WHERE SSN='AYISE06';
730
731UPDATE EMPLOYEE SET
732 DNO='1', SUPERSSN='AYACC02'
733WHERE SSN='AYACC01';
734UPDATE EMPLOYEE SET
735 DNO='1', SUPERSSN=NULL
736WHERE SSN='AYACC02';
737
738UPDATE EMPLOYEE SET
739 DNO='4', SUPERSSN=NULL
740WHERE SSN='AYISE01';
741
742UPDATE EMPLOYEE SET
743 DNO='2', SUPERSSN=NULL
744WHERE SSN='AYIT01';
745
746INSERT INTO DLOCATION VALUES ('BANGALORE', '1');
747INSERT INTO DLOCATION VALUES ('BANGALORE', '2');
748INSERT INTO DLOCATION VALUES ('BANGALORE', '3');
749INSERT INTO DLOCATION VALUES ('MANGALORE', '4');
750INSERT INTO DLOCATION VALUES ('MANGALORE', '5');
751
752INSERT INTO PROJECT VALUES (100,'IOT','BANGALORE','5');
753INSERT INTO PROJECT VALUES (101,'CLOUD','BANGALORE','5');
754INSERT INTO PROJECT VALUES (102,'BIGDATA','BANGALORE','5');
755INSERT INTO PROJECT VALUES (103,'SENSORS','BANGALORE','3');
756INSERT INTO PROJECT VALUES (104,'BANK MANAGEMENT','BANGALORE','1');
757INSERT INTO PROJECT VALUES (105,'SALARY MANAGEMENT','BANGALORE','1');
758INSERT INTO PROJECT VALUES (106,'OPENSTACK','BANGALORE','4');
759INSERT INTO PROJECT VALUES (107,'SMART CITY','BANGALORE','2');
760
761INSERT INTO WORKS_ON VALUES (4, 'AYISE01', 100);
762INSERT INTO WORKS_ON VALUES (6, 'AYISE01', 101);
763INSERT INTO WORKS_ON VALUES (8, 'AYISE01', 102);
764INSERT INTO WORKS_ON VALUES (10, 'AYISE02', 100);
765INSERT INTO WORKS_ON VALUES (3, 'AYISE04', 100);
766INSERT INTO WORKS_ON VALUES (4, 'AYISE05', 101);
767INSERT INTO WORKS_ON VALUES (5, 'AYISE06', 102);
768INSERT INTO WORKS_ON VALUES (6, 'AYISE03', 102);
769INSERT INTO WORKS_ON VALUES (7, 'AYECE01', 103);
770INSERT INTO WORKS_ON VALUES (5, 'AYACC01', 104);
771INSERT INTO WORKS_ON VALUES (6, 'AYACC02', 105);
772INSERT INTO WORKS_ON VALUES (4, 'AYISE01', 106);
773INSERT INTO WORKS_ON VALUES (10, 'AYIT01', 107);
774
775
7761.Make a list of all project numbers for projects that involve an employee whose last name is ‘Scott’, either as a worker or as a manager of the department that controls the project.
777
778(
779SELECT DISTINCT P.PNO
780FROM PROJECT P, DEPARTMENT D, EMPLOYEE E
781WHERE E.DNO=D.DNO
782AND D.MGRSSN=E.SSN
783AND E.LNAME='SCOTT'
784)
785UNION
786(
787SELECT DISTINCT P1.PNO
788FROM PROJECT P1, WORKS_ON W, EMPLOYEE E1
789WHERE P1.PNO=W.PNO
790AND E1.SSN=W.SSN
791AND E1.LNAME='SCOTT'
792);
793
794
7952.Show the resulting salaries if every employee working on the ‘IoT’ project is given a 10 percent raise.
796
797SELECT E.FNAME, E.LNAME, 1.1*E.SALARY AS INCR_SAL
798FROM EMPLOYEE E, WORKS_ON W, PROJECT P
799WHERE E.SSN=W.SSN
800AND W.PNO=P.PNO
801AND P.PNAME='IOT';
802
803
8043.Find the sum of the salaries of all employees of the ‘Accounts’ department, as well as the maximum salary, the minimum salary, and the average salary in this department
805
806
807SELECT SUM(E.SALARY),MAX(E.SALARY),MIN(E.SALARY),AVG(E.SALARY)
808FROM EMPLOYEE E,DEPARTMENT D
809WHERE E.DNO=D.DNO
810AND D.DNAME='ACCOUNTS';
811
8124.Retrieve the name of each employee who works on all the projects Controlled by department number 5 (use NOT EXISTS operator).
813
814SELECT E.FNAME,E.LNAME
815FROM EMPLOYEE E
816WHERE NOT EXISTS(SELECT PNO
817FROM PROJECT
818WHERE DNO = '5' AND PNO NOT IN (SELECT PNO
819FROM WORKS_ON
820WHERE E.SSN=SSN));
821
822 or
823
824SELECT E.FNAME,E.LNAME
825FROM EMPLOYEE E
826WHERE NOT EXISTS((SELECT PNO
827FROM PROJECT
828WHERE DNO='5')
829 MINUS (SELECT PNO
830FROM WORKS_ON
831WHERE E.SSN=SSN));
832
8335.For each department that has more than five employees, retrieve the department number and the number of its employees who are making more than Rs. 6, 00,000.
834
835SELECT D.DNO,COUNT(*)
836FROM DEPARTMENT D,EMPLOYEE E
837WHERE D.DNO=E.DNO
838AND E.SALARY>600000
839AND D.DNO IN(SELECT E1.DNO
840 FROM EMPLOYEE E1
841 GROUP BY E1.DNO
842 HAVING COUNT(*)>=5)
843 GROUP BY D.DNO;
844
845
846
847