· 4 years ago · Jul 11, 2021, 02:02 PM
1drop database if exists dbw_project;
2CREATE DATABASE dbw_project;
3USE dbw_project;
4-- Student(Enroll INT , DOB DATE , Name VARCHAR(20) , Mob_Num BIGINT, Email VARCHAR(30) );
5
6CREATE TABLE Student (
7 enroll INT PRIMARY KEY,
8 name VARCHAR(50),
9 dob DATE,
10 email VARCHAR(50),
11 mob_num BIGINT
12);
13
14-- INSERT
15INSERT INTO Student VALUES
16(19803021,"PRANAV GUPTA","2001-11-14","guptapranav@gmail.com",7878787878),
17(19803003,"KHUSHBOO KUMARI","2001-11-14","kumarikhushboo@gmail.com",9834567878),
18(19803008,"KANISTHA","2001-11-14","kanistha@gmail.com",7823456578),
19(19803011,"KANISHKA KHULLAR","2001-11-14","khullarkanishka@gmail.com",7878651298),
20(19103223,"ABHINAV VERMA","2001-04-06","avverma@gmail.com",9898989898),
21(19103029,"MONICA JAIN","2001-10-19","monica@gmail.com",1212121212),
22(19103024,"NIDHI JAIN","2000-03-10","nidhijn@gmail.com",9899647123),
23(19103015,"PAWAN SONI","2000-10-06","pawansoni@gmail.com",9632786512),
24(19103020,"ANIL KUMAR","2000-07-19","anil_k@gmail.com",9627651010),
25(19103005,"VAIBHAV TIWARI","2001-03-12","vaibhavt@gmail.com",9455237465),
26(19103018,"RISHI BANGAR","2002-12-24","rishbangar@gmail.com",9566905400),
27(19803013,"ATUL TIWARI","2000-01-01","atultiw@gmail.com",9080342675),
28(19803017,"RAVI GUPTA","2001-11-05","ravi.gupta@gmail.com",9780512676),
29(19803007,"YOGESH RAJ","2000-08-01","yog.raj@gmail.com",8880382675),
30(19803016,"PANKAJ SHARMA","2002-05-12","pankaj__@gmail.com",9780032161),
31(19803019,"EKANSH SHARMA","2000-11-03","1kansh@gmail.com",9980352775),
32(19103221,"STUTI BHARDWAJ","2000-03-11","stutiii@gmail.com",8798449658),
33(19103214,"RAGHAV BANSAL","2000-10-13","r.raghav@gmail.com",9899107648),
34(19103227,"DIYA SINGH","2001-02-28","diya_sing@gmail.com",9568912893),
35(19103211,"TANISHQ VATS","2000-02-16","tanishq07@gmail.com",9899449308),
36(19103228,"VIHAAN MEHRA","2000-03-11","vihaanmeh@gmail.com",8798669898);
37
38-- Batch( BID : VARCHAR(4) , Strength : INT, CR_Enroll INT);
39
40CREATE TABLE Batch (
41 bid VARCHAR(4) PRIMARY KEY,
42 strength INT,
43 cr_enroll INT,
44 FOREIGN KEY (cr_enroll)
45 REFERENCES Student (enroll)
46);
47
48INSERT INTO Batch VALUES
49("B1",0,19103029),
50("B13",0,19803021),
51("B7",0,19103223);
52
53 -- ENROLLED(ENROLL INT, BID VARCHAR(4));
54
55CREATE TABLE Enrolled (
56 enroll INT,
57 bid VARCHAR(4),
58 PRIMARY KEY (enroll , bid),
59 FOREIGN KEY (enroll)
60 REFERENCES Student (enroll),
61 FOREIGN KEY (bid)
62 REFERENCES Batch (bid)
63);
64
65-- 1. Update strength of the batch after enrollment of a student in a batch
66
67drop procedure if exists insert_trigger_details;
68delimiter //
69create procedure insert_trigger_details(in sid int,in nbid varchar(4))
70begin
71update batch set strength=strength+1 where bid=nbid;
72end //
73
74drop trigger if exists insert_batch ;
75DELIMITER //
76CREATE TRIGGER insert_batch after insert ON enrolled FOR EACH ROW
77BEGIN
78CALL insert_TRIGGER_DETAILS(new.enroll,new.bid);
79END //
80DELIMITER ;
81
82INSERT INTO Enrolled VALUES
83(19803003,'B13'),(19803008,'B13'),(19803011,'B13'),(19103228,"B7"),
84(19103029,"B1"),(19803021,"B13"),(19103223,"B7"),(19103024,"B1"),
85(19103015,"B1"),(19103020,"B1"),(19103005,"B1"),(19103018,"B1"),
86(19803013,"B13"),(19803017,"B13"),(19803007,"B13"),(19803016,"B13"),
87(19803019,"B13"),(19103221,"B7"),(19103214,"B7"),(19103227,"B7"),
88(19103211,"B7");
89
90-- SUBJECT( SCODE VARCHAR(15) , SNAME VARCHAR(30))
91
92CREATE TABLE Subject (
93 scode VARCHAR(15) PRIMARY KEY,
94 sname VARCHAR(30)
95);
96
97INSERT INTO SUBJECT VALUES
98("15B11CI313","COA"),
99("15B11CI212","DBMS"),
100("15B17CI411","SDF-2");
101
102-- DEPARTMENT(DID INT , DNAME VARCHAR(30))
103
104CREATE TABLE Department (
105 did INT PRIMARY KEY,
106 dname VARCHAR(30)
107);
108
109INSERT INTO DEPARTMENT VALUES
110(11,"CSE"),
111(12,"IT");
112
113
114-- FACULTY (FID INT ,FNAME VARCHAR(20) , EMAIL VARCHAR(30), SALARY REAL,DID INT);
115
116CREATE TABLE Faculty (
117 fid INT PRIMARY KEY,
118 fname VARCHAR(50),
119 email VARCHAR(50),
120 salary REAL,
121 did INT,
122 FOREIGN KEY (did)
123 REFERENCES Department (did)
124);
125
126INSERT INTO FACULTY VALUES
127(1923,"ANKITA WADHWA","ankita.wadhwa@mail.jiit.ac.in",70000,11),
128(1823,"PARUL AGGARWAL","parul.aggarwal@mail.jiit.ac.in",68000,11),
129(2212,"EKANT JOSHI","ekant.joshi@mail.jiit.ac.in",69000,11),
130(2213,"TANYA NANDA","tanya.nanda@mail.jiit.ac.in",67500,11),
131(2215,"YASH SEHGAL","yash.sehgal@mail.jiit.ac.in",68500,11),
132(2217,"N K SINGH","nk.singh@mail.jiit.ac.in",72000,11),
133(2218,"HITESH JAIN","hitesh,jain@mail.jiit.ac.in",68000,12),
134(1922,"JUGAL MITTAL","jugal.mittal@mail.jiit.ac.in",67500,12),
135(1819,"RAMA GUPTA","rama.gupta@mail.jiit.ac.in",71000,12),
136(1822,"PRAGYA MISHRA","pragya.mishra@mail.jiit.ac.in",66000,12);
137
138-- HOD(DID INT , FID INT);
139
140CREATE TABLE HOD (
141 did INT,
142 fid INT,
143 PRIMARY KEY (did , fid),
144 FOREIGN KEY (did)
145 REFERENCES Department (did),
146 FOREIGN KEY (fid)
147 REFERENCES Faculty (fid)
148);
149
150INSERT INTO HOD VALUES
151(11,1923),
152(12,1819);
153
154-- FAC_SUBJ( FID INT, SCODE VARCHAR(15));
155
156CREATE TABLE Fac_Subj (
157 fid INT,
158 scode VARCHAR(15),
159 PRIMARY KEY (fid , scode),
160 FOREIGN KEY (fid)
161 REFERENCES Faculty (fid),
162 FOREIGN KEY (scode)
163 REFERENCES Subject (scode)
164);
165
166INSERT INTO Fac_Subj VALUES
167(1923,"15B11CI212"),(1923,"15B11CI313"),(1823,"15B17CI411"),(2212,"15B17CI411"),
168(2213,"15B11CI212"),(2215,"15B17CI411"),(2215,"15B11CI313"),(2215,"15B11CI212"),
169(2217,"15B11CI313"),(2217,"15B17CI411"),(2218,"15B11CI212"),(1922,"15B17CI411"),
170(1922,"15B11CI212"),(1819,"15B17CI411"),(1822,"15B11CI212"),(1822,"15B17CI411");
171
172-- TIME_TABLE ( DAY VARCHAR(11), START_T TIME , DURATION INT , BID VARCHAR(4), SCODE VARCHAR(15), FID INT)
173
174CREATE TABLE Time_Table (
175 day VARCHAR(11),
176 start_time TIME,
177 duration INT,
178 bid VARCHAR(4),
179 scode VARCHAR(15),
180 fid INT,
181 PRIMARY KEY (day , start_time , bid),
182 FOREIGN KEY (bid)
183 REFERENCES Batch (bid),
184 FOREIGN KEY (scode)
185 REFERENCES Subject (scode),
186 FOREIGN KEY (fid)
187 REFERENCES Faculty (fid)
188);
189
190INSERT INTO TIME_TABLE VALUES
191("MONDAY","09:00",1,"B13","15B11CI212","1923"),("MONDAY","13:00",1,"B13","15B11CI313","2215"),("MONDAY","13:00",1,"B1","15B11CI313","2215"),
192("MONDAY","15:00",2,"B7","15B17CI411","1823"),("TUESDAY","10:00",1,"B1","15B11CI212","2213"),("TUESDAY","11:00",1,"B13","15B11CI212","1922"),
193("TUESDAY","12:00",1,"B7","15B11CI313","2217"),("TUESDAY","14:00",2,"B1","15B11CI313","1923"),("TUESDAY","16:00",1,"B1","15B17CI411","2212"),
194("WEDNESDAY","9:00",1,"B7","15B11CI313","2217"),("WEDNESDAY","12:00",1,"B1","15B11CI212","2213"),("WEDNESDAY","15:00",1,"B13","15B11CI313","2217"),
195("THURSDAY","11:00",1,"B1","15B17CI411","1819"),("THURSDAY","14:00",1,"B13","15B17CI411","1823"),("THURSDAY","15:00",1,"B7","15B11CI212","1922"),
196("FRIDAY","9:00",1,"B1","15B11CI212","2213"),("FRIDAY","11:00",1,"B7","15B17CI411","1823"),("FRIDAY","12:00",1,"B7","15B11CI313","1923"),
197("FRIDAY","15:00",2,"B13","15B11CI313","2215"),("FRIDAY","16:00",2,"B1","15B11CI212","2218"),("FRIDAY","16:00",2,"B13","15B11CI212","2218"),
198("FRIDAY","16:00",1,"B7","15B11CI313","2215"),("SATURDAY","10:00",2,"B13","15B11CI313","2217"),("SATURDAY","13:00",1,"B1","15B11CI313","2215"),
199("SATURDAY","14:00",1,"B1","15B17CI411","1819"),("SATURDAY","14:00",2,"B7","15B11CI212","1922");
200
201
202-- DATA AT A GLANCE
203SELECT
204 *
205FROM
206 STUDENT;
207
208SELECT
209 *
210FROM
211 BATCH;
212
213SELECT
214 *
215FROM
216 ENROLLED;
217
218SELECT
219 *
220FROM
221 SUBJECT;
222
223SELECT
224 *
225FROM
226 DEPARTMENT;
227
228SELECT
229 *
230FROM
231 FACULTY;
232
233SELECT
234 *
235FROM
236 HOD;
237
238SELECT
239 *
240FROM
241 FAC_SUBJ;
242
243SELECT
244 *
245FROM
246 TIME_TABLE;
247
248-- 2. Find all the faculties who have taught a given student.
249DELIMITER //
250CREATE PROCEDURE Find_All_Fac(IN SNAME VARCHAR(50))
251BEGIN
252SELECT * FROM FACULTY F WHERE F.FID IN
253(SELECT DISTINCT T.FID FROM TIME_TABLE T WHERE T.BID IN
254(SELECT E.BID FROM ENROLLED E,student s1 WHERE E.ENROLL=s1.enroll and
255s1.name=SNAME));
256END //
257DELIMITER ;
258CALL Find_All_Fac('HITESH JAIN');
259
260-- 3 Find all the faculties in a dept with corresponding hod and dept id and faculties inside a dept must be concatenated with a comma;
261DELIMITER //
262CREATE PROCEDURE QUERY8 ()
263BEGIN
264SELECT f1.did,d1.dname,f2.fname as HOD,GROUP_CONCAT(f1.fname) as 'faculties'
265FROM faculty f1 ,faculty f2, hod h1 ,department d1 where f1.did = d1.did
266and h1.did = f1.did and h1.fid = f2.fid
267 group by f1.did ;
268
269END //
270DELIMITER ;
271CALL QUERY8();
272
273-- 4 Find the number of students who study subject S on day D at time T
274
275DELIMITER //
276CREATE PROCEDURE Query9 (IN scode VARCHAR(15), IN day VARCHAR(11), IN stime TIME)
277BEGIN
278SELECT SUM(B.strength) AS No_Of_Students FROM Batch B WHERE B.bid IN (SELECT DISTINCT (T.bid) FROM Time_Table T, Batch B, Subject S WHERE T.scode = scode AND T.day = day AND T.start_time = stime);
279END //
280DELIMITER ;
281CALL QUERY9('15B11CI212', 'Friday', '16:00');
282
283-- 5 List the batches who study the same subject classes in the same slot.
284
285SELECT
286 T.day,
287 T.start_time,
288 T.scode,
289 GROUP_CONCAT(DISTINCT T.bid) AS Batches
290FROM
291 Time_Table T,
292 Batch B,
293 Subject S
294GROUP BY T.day , T.start_time , T.scode;
295
296-- 6 Find all the subjects in which a student is enrolled
297
298DELIMITER //
299CREATE PROCEDURE Query10 (IN stuName VARCHAR(50))
300BEGIN
301(SELECT DISTINCT T1.scode, Sub.sname FROM Enrolled E, Student S1, Subject Sub, Time_Table T1 WHERE S1.enroll = E.enroll AND S1.name = stuName AND T1.bid = E.bid AND T1.scode = Sub.scode);
302END //
303DELIMITER ;
304CALL QUERY10('HITESH JAIN');
305
306-- 7. Find the name of CR for each batch.
307
308SELECT DISTINCT
309 B.bid, S.name
310FROM
311 Student S,
312 Batch B
313WHERE
314 S.enroll = B.cr_enroll;
315
316
317
318-- 8. Deduce the time table of all the faculties (PLSQL)
319DROP PROCEDURE IF EXISTS get_schedule ;
320DROP PROCEDURE IF EXISTS get_fac_schedule;
321
322delimiter //
323CREATE PROCEDURE get_schedule(IN f INT )
324BEGIN
325DECLARE fac_id INT ;
326DECLARE s_time TIME;
327DECLARE dur INT;
328DECLARE dy VARCHAR(11);
329DECLARE batch VARCHAR(4);
330DECLARE sid VARCHAR(15);
331DECLARE fac_schedule_list VARCHAR(1000) DEFAULT '';
332DECLARE FINISHED1 BOOLEAN DEFAULT FALSE;
333DECLARE CUR2 CURSOR FOR SELECT * FROM time_table;
334DECLARE CONTINUE HANDLER FOR NOT FOUND SET FINISHED1 = TRUE;
335DROP TABLE IF EXISTS fac_time_table;
336CREATE TABLE fac_time_table (
337 faculty_id INT,
338 day VARCHAR(11),
339 start_time TIME,
340 duration INT,
341 batch VARCHAR(4),
342 sub_id VARCHAR(15),
343 PRIMARY KEY (start_time , batch , day)
344);
345OPEN CUR2;
346LOOP2 : LOOP
347FETCH CUR2 INTO dy, s_time , dur, batch ,sid , fac_id;
348IF FINISHED1 THEN
349LEAVE LOOP2;
350END IF;
351IF fac_id=f THEN
352insert into fac_time_table values(f,dy,s_time,dur,batch,sid);
353END IF;
354END LOOP LOOP2;
355CLOSE CUR2;
356select * from fac_time_table;
357end //
358
359delimiter //
360CREATE PROCEDURE get_fac_schedule()
361BEGIN
362DECLARE fac_id INT ;
363DECLARE FINISHED1 BOOLEAN DEFAULT FALSE;
364DECLARE CUR1 CURSOR FOR SELECT fid FROM faculty;
365DECLARE CONTINUE HANDLER FOR NOT FOUND SET FINISHED1 = TRUE;
366OPEN CUR1;
367LOOP1 : LOOP
368FETCH CUR1 INTO fac_id;
369IF FINISHED1 THEN
370LEAVE LOOP1;
371END IF;
372CALL get_schedule(fac_id);
373END LOOP LOOP1;
374CLOSE CUR1;
375END //
376
377CALL get_fac_schedule();
378
379
380
381-- 9 Suppose, a student left the college, then remove his info from all the tables so that there is no referential integrity left. (PLSQL)
382
383drop procedure if exists trigger_details;
384delimiter //
385create procedure trigger_details(in sid int)
386begin
387delete from enrolled e where e.enroll=sid;
388update batch set cr_enroll=null where cr_enroll=sid;
389end //
390
391drop trigger if exists solve_constraint_error;
392DELIMITER //
393CREATE TRIGGER solve_constraint_error BEFORE delete ON student FOR EACH ROW
394BEGIN
395CALL TRIGGER_DETAILS(old.enroll);
396END //
397DELIMITER ;
398
399SELECT
400 *
401FROM
402 student;
403DELETE FROM student
404WHERE
405 enroll = 19103024;
406SELECT
407 *
408FROM
409 student;
410SELECT
411 *
412FROM
413 batch;
414DELETE FROM student
415WHERE
416 enroll = 19803021;
417SELECT
418 *
419FROM
420 student;
421SELECT
422 *
423FROM
424 batch;
425
426-- Student(Enroll INT , DOB DATE , Name VARCHAR(20) , Mob_Num BIGINT, Email VARCHAR(30) );
427-- Batch( BID : VARCHAR(4) , Strength : INT, CR_Enroll INT);
428-- ENROLLED(ENROLL INT, BID VARCHAR(4));
429-- SUBJECT( SCODE VARCHAR(15) , SNAME VARCHAR(30))
430-- DEPARTMENT(DID INT , DNAME VARCHAR(30))
431-- FACULTY (FID INT ,FNAME VARCHAR(20) , EMAIL VARCHAR(30), SALARY REAL,DID INT);
432-- HOD(DID INT , FID INT);
433-- FAC_SUBJ( FID INT, SCODE VARCHAR(15));
434-- TIME_TABLE ( DAY VARCHAR(11), START_Time TIME , DURATION INT , BID VARCHAR(4), SCODE VARCHAR(15), FID INT)
435
436-- 10 Update strength of the batch after batch updation of students
437
438drop procedure if exists update_trigger_details;
439delimiter //
440create procedure update_trigger_details(in sid int,in obid varchar(4),in nbid varchar(4))
441begin
442update batch set strength=strength-1 where bid=obid;
443update batch set strength=strength+1 where bid=nbid;
444end //
445
446drop trigger if exists update_batch ;
447DELIMITER //
448CREATE TRIGGER update_batch after update ON enrolled FOR EACH ROW
449BEGIN
450CALL update_TRIGGER_DETAILS(old.enroll,old.bid,new.bid);
451END //
452DELIMITER ;
453
454SELECT
455 *
456FROM
457 batch;
458UPDATE enrolled
459SET
460 bid = 'B7'
461WHERE
462 enroll = 19803007;
463SELECT
464 *
465FROM
466 batch;