CREATE TABLE departments (

dept_id INT PRIMARY KEY,

dept_name VARCHAR(50),
building VARCHAR(50)
);

INSERT INTO departments VALUES

(101,'CSE','Block A'),
(102,'ECE','Block B'),
(103,'ME','Block C'),
(104,'CE','Block D'),
(105,'IT','Block A'),
(106,'AI','Block E'),
(107,'DS','Block E'),
(108,'EE','Block B'),
(109,'Civil','Block D'),
(110,'MBA','Block F'),
(111,'Law','Block G'),
(112,'Architecture','Block H'),
(113,'Pharmacy','Block I'),
(114,'Biotech','Block J'),
(115,'Robotics','Block K'),
(116,'Physics','Block L'),
(117,'Chemistry','Block M'),
(118,'Maths','Block N'),
(119,'English','Block O'),
(120,'Economics','Block P'),
(121,'History','Block Q'),
(122,'Psychology','Block R'),
(123,'Statistics','Block S'),
(124,'Design','Block T'),
(125,'Animation','Block U'),
(126,'Music','Block V'),
(127,'Fine Arts','Block W'),
(128,'Sports','Block X'),
(129,'Astronomy','Block Y'),
(130,'Geology','Block Z');

CREATE TABLE students (

student_id INT PRIMARY KEY,
student_name VARCHAR(50),
dept_id INT,
gender VARCHAR(10),
city VARCHAR(30),
year_of_study INT,
marks INT,
email VARCHAR(50)
);

INSERT INTO students VALUES

(1,'Amit Sharma',101,'Male','Delhi',1,78,'amit@[Link]'),
(2,'Neha Verma',102,'Female','Mumbai',2,85,'neha@[Link]'),
(3,'Rahul Singh',101,'Male','Bhopal',3,67,NULL),
(4,'Pooja Patel',103,'Female','Ahmedabad',4,72,'pooja@[Link]'),
(5,'Karan Mehta',NULL,'Male','Mumbai',1,60,NULL),
(6,'Anjali Jain',105,'Female','Delhi',2,90,'anjali@[Link]'),
(7,'Rohit Gupta',106,'Male','Indore',3,55,NULL),
(8,'Sneha Kapoor',107,'Female','Pune',4,88,'sneha@[Link]'),
(9,'Vikas Yadav',108,'Male','Jaipur',2,64,NULL),
(10,'Nisha Malhotra',109,'Female','Chandigarh',1,75,'nisha@[Link]'),
(11,'Arjun Rana',110,'Male','Delhi',3,82,NULL),
(12,'Kriti Saxena',NULL,'Female','Noida',4,91,'kriti@[Link]'),
(13,'Suresh Kumar',111,'Male','Patna',2,58,NULL),
(14,'Riya Gupta',112,'Female','Lucknow',1,69,NULL),
(15,'Manish Joshi',113,'Male','Dehradun',3,77,'manish@[Link]'),
(16,'Shalini Roy',114,'Female','Kolkata',2,85,NULL),
(17,'Aakash Verma',115,'Male','Kanpur',4,73,NULL),
(18,'Pallavi Mishra',116,'Female','Varanasi',1,81,'pallavi@[Link]'),
(19,'Nitin Agarwal',117,'Male','Agra',2,66,NULL),
(20,'Priya Soni',118,'Female','Udaipur',3,92,'priya@[Link]'),
(21,'Mohit Jain',119,'Male','Ajmer',4,54,NULL),
(22,'Divya Arora',120,'Female','Delhi',2,89,NULL),
(23,'Kunal Sharma',121,'Male','Gurgaon',1,70,'kunal@[Link]'),
(24,'Isha Bansal',122,'Female','Faridabad',3,95,NULL),
(25,'Deepak Yadav',123,'Male','Mathura',4,63,NULL),
(26,'Sakshi Gupta',124,'Female','Meerut',2,88,'sakshi@[Link]'),
(27,'Abhishek Rai',125,'Male','Prayagraj',1,59,NULL),
(28,'Tanvi Kulkarni',126,'Female','Nagpur',3,84,NULL),
(29,'Ramesh Pawar',127,'Male','Nashik',2,71,'ramesh@[Link]'),
(30,'Kavita Desai',NULL,'Female','Surat',4,90,NULL);

CREATE TABLE courses (

course_id INT PRIMARY KEY,
course_name VARCHAR(50),
dept_id INT,
credits INT
);

INSERT INTO courses VALUES

(201,'DSA',101,4),
(202,'DBMS',101,4),
(203,'OS',101,3),
(204,'Networks',102,4),
(205,'VLSI',102,3),
(206,'Thermodynamics',103,4),
(207,'Machine Design',103,3),
(208,'AI Basics',106,4),
(209,'ML',106,4),
(210,'Data Science',107,4),
(211,'Python',105,3),
(212,'Java',105,3),
(213,'Statistics',118,4),
(214,'Linear Algebra',118,3),
(215,'Economics 101',120,3),
(216,'Business Law',110,4),
(217,'Constitution',111,4),
(218,'Architecture Design',112,5),
(219,'Pharmacology',113,4),
(220,'Biotech Lab',114,3),
(221,'Robotics Intro',115,4),
(222,'Quantum Physics',116,4),
(223,'Organic Chem',117,3),
(224,'English Lit',119,3),
(225,'Psychology Basics',122,4),
(226,'Statistics II',123,4),
(227,'Design Thinking',124,3),
(228,'Animation Basics',125,4),
(229,'Music Theory',126,3),
(230,'Fine Arts',127,3);

CREATE TABLE enrollments (

enroll_id INT PRIMARY KEY,
student_id INT,
course_id INT,
semester INT,
grade CHAR(2)
);

INSERT INTO enrollments VALUES

(1,1,201,1,'A'),
(2,1,202,1,'B'),
(3,2,204,2,'A'),
(4,3,201,3,'C'),
(5,4,206,4,'B'),
(6,5,211,1,NULL),
(7,6,212,2,'A'),
(8,7,208,3,'C'),
(9,8,210,4,'A'),
(10,9,205,2,'B'),
(11,10,224,1,'A'),
(12,11,216,3,'B'),
(13,12,215,4,'A'),
(14,13,217,2,'C'),
(15,14,218,1,'B'),
(16,15,219,3,'A'),
(17,16,220,2,NULL),
(18,17,221,4,'B'),
(19,18,222,1,'A'),
(20,19,223,2,'C'),
(21,20,213,3,'A'),
(22,21,224,4,'B'),
(23,22,215,2,'A'),
(24,23,224,1,'B'),
(25,24,225,3,'A'),
(26,25,226,4,'C'),
(27,26,227,2,'A'),
(28,27,228,1,'B'),
(29,28,229,3,NULL),
(30,29,230,2,'A');

CREATE TABLE faculty (

faculty_id INT PRIMARY KEY,
faculty_name VARCHAR(50),
dept_id INT,
manager_id INT,
salary INT
);

INSERT INTO faculty VALUES

(1,'Dr. Sharma',101,NULL,90000),
(2,'Dr. Mehta',101,1,75000),
(3,'Dr. Verma',102,NULL,88000),
(4,'Dr. Jain',102,3,72000),
(5,'Dr. Patel',103,NULL,85000),
(6,'Dr. Roy',105,NULL,82000),
(7,'Dr. Gupta',106,6,70000),
(8,'Dr. Singh',107,NULL,87000),
(9,'Dr. Yadav',108,NULL,78000),
(10,'Dr. Khan',109,NULL,76000),
(11,'Dr. Arora',110,NULL,90000),
(12,'Dr. Mishra',111,11,68000),
(13,'Dr. Saxena',112,NULL,92000),
(14,'Dr. Kapoor',113,NULL,84000),
(15,'Dr. Das',114,NULL,81000),
(16,'Dr. Iyer',115,15,73000),
(17,'Dr. Bose',116,NULL,88000),
(18,'Dr. Sen',117,NULL,79000),
(19,'Dr. Gill',118,NULL,86000),
(20,'Dr. Chatterjee',119,19,69000),
(21,'Dr. Malhotra',120,NULL,83000),
(22,'Dr. Bansal',121,NULL,77000),
(23,'Dr. Tiwari',122,22,71000),
(24,'Dr. Mathur',123,NULL,80000),
(25,'Dr. Joshi',124,NULL,82000),
(26,'Dr. Kulkarni',125,NULL,85000),
(27,'Dr. Pawar',126,NULL,78000),
(28,'Dr. Desai',127,27,70000),
(29,'Dr. Nair',128,NULL,76000),
(30,'Dr. Rao',129,NULL,90000);

CREATE TABLE members ( member_id INT PRIMARY KEY,

member_name VARCHAR(50), gender
VARCHAR(10), city VARCHAR(30), membership_type VARCHAR(20) );

INSERT INTO members

VALUES (1,'Amit Sharma','Male','Delhi','Gold'), (2,'Neha
Verma','Female','Mumbai','Silver'), (3,'Rahul Singh','Male','Bhopal','Gold'),
(4,'Pooja Patel','Female','Ahmedabad','Platinum'), (5,'Karan
Mehta','Male','Mumbai','Silver'), (6,'Anjali Jain','Female','Delhi','Gold'),
(7,'Rohit Gupta','Male','Indore','Gold'), (8,'Sneha
Kapoor','Female','Pune','Silver'), (9,'Vikas Yadav','Male','Jaipur','Gold'),
(10,'Nisha Malhotra','Female','Chandigarh','Silver'), (11,'Arjun
Rana','Male','Delhi','Gold'), (12,'Kriti Saxena','Female','Noida','Platinum'),
(13,'Suresh Kumar','Male','Patna','Silver'), (14,'Riya
Gupta','Female','Lucknow','Gold'), (15,'Manish
Joshi','Male','Dehradun','Gold'),
(16,'Shalini Roy','Female','Kolkata','Silver'), (17,'Aakash
Verma','Male','Kanpur','Gold'), (18,'Pallavi
Mishra','Female','Varanasi','Platinum'), (19,'Nitin
Agarwal','Male','Agra','Silver'), (20,'Priya Soni','Female','Udaipur','Gold'),
(21,'Mohit Jain','Male','Ajmer','Silver'), (22,'Divya
Arora','Female','Delhi','Gold'), (23,'Kunal Sharma','Male','Gurgaon','Silver'),
(24,'Isha Bansal','Female','Faridabad','Gold'), (25,'Deepak
Yadav','Male','Mathura','Silver'), (26,'Sakshi
Gupta','Female','Meerut','Gold'),
(27,'Abhishek Rai','Male','Prayagraj','Silver'), (28,'Tanvi
Kulkarni','Female','Nagpur','Gold'), (29,'Ramesh
Pawar','Male','Nashik','Silver'),
(30,'Kavita Desai','Female','Surat','Gold');

CREATE TABLE books ( book_id INT

PRIMARY KEY, title VARCHAR(100), category VARCHAR(30), price INT );

INSERT INTO
books VALUES (101,'Data Structures','Computer',550), (102,'Operating
Systems','Computer',600), (103,'DBMS','Computer',500), (104,'Modern
Physics','Science',450), (105,'Organic Chemistry','Science',480),
(106,'Indian
Polity','Arts',400), (107,'World History','Arts',420), (108,'Linear
Algebra','Maths',520), (109,'Statistics','Maths',510), (110,'Micro
Economics','Commerce',490), (111,'Macro Economics','Commerce',530),
(112,'Psychology','Arts',460), (113,'Sociology','Arts',440),
(114,'Genetics','Science',560), (115,'Biology','Science',470), (116,'Machine
Learning','Computer',650), (117,'AI Basics','Computer',630),
(118,'Environmental
Sci','Science',410), (119,'Ethics','Arts',390), (120,'Business
Studies','Commerce',580), (121,'Accounting','Commerce',600), (122,'Data
Science','Computer',670), (123,'Cloud Computing','Computer',620),
(124,'Physics
II','Science',490), (125,'Chemistry II','Science',510), (126,'Political
Theory','Arts',430), (127,'Mathematics II','Maths',540),
(128,'Probability','Maths',560), (129,'Entrepreneurship','Commerce',570),
(130,'Digital Marketing','Commerce',590);

CREATE TABLE issue_records ( issue_id INT

PRIMARY KEY, member_id INT, book_id INT, issue_date DATE,
return_date DATE, fine INT
);

INSERT INTO issue_records VALUES (1,1,101,'2024-01-01','2024-01-

10',0),
(2,2,102,'2024-01-02','2024-01-15',20), (3,3,103,'2024-01-03','2024-01-
18',0),
(4,4,104,'2024-01-04','2024-01-20',30), (5,5,105,'2024-01-05','2024-01-
14',10),
(6,6,106,'2024-01-06','2024-01-22',25), (7,7,107,'2024-01-07','2024-01-
17',0),
(8,8,108,'2024-01-08','2024-01-25',40), (9,9,109,'2024-01-09','2024-01-
19',15),
(10,10,110,'2024-01-10','2024-01-28',0), (11,11,111,'2024-01-11','2024-01-
21',10),
(12,12,112,'2024-01-12','2024-01-30',35), (13,13,113,'2024-01-13','2024-
01-27',0),
(14,14,114,'2024-01-14','2024-02-01',20), (15,15,115,'2024-01-15','2024-
02-05',25),
(16,16,116,'2024-01-16','2024-02-02',0), (17,17,117,'2024-01-17','2024-02-
10',45),
(18,18,118,'2024-01-18','2024-02-12',0), (19,19,119,'2024-01-19','2024-02-
03',15),
(20,20,120,'2024-01-20','2024-02-08',0), (21,21,121,'2024-01-21','2024-02-
15',30),
(22,22,122,'2024-01-22','2024-02-18',0), (23,23,123,'2024-01-23','2024-02-
20',20),
(24,24,124,'2024-01-24','2024-02-22',0), (25,25,125,'2024-01-25','2024-02-
25',35),
(26,26,126,'2024-01-26','2024-02-26',0), (27,27,127,'2024-01-27','2024-02-
28',15),
(28,28,128,'2024-01-28','2024-03-01',0), (29,29,129,'2024-01-29','2024-03-
02',25),
(30,30,130,'2024-01-30','2024-03-05',0);

CREATE TABLE librarians ( librarian_id INT

PRIMARY KEY, librarian_name VARCHAR(50), city VARCHAR(30), shift
VARCHAR(20), salary
INT );

INSERT INTO librarians VALUES (1,'Rakesh','Delhi','Morning',30000),

(2,'Sunita','Mumbai','Evening',32000),
(3,'Mahesh','Bhopal','Morning',28000),
(4,'Kavita','Pune','Evening',31000), (5,'Anoop','Jaipur','Morning',29000),
(6,'Neelam','Delhi','Evening',33000), (7,'Sanjay','Indore','Morning',27000),
(8,'Poonam','Nagpur','Evening',30000), (9,'Ravi','Surat','Morning',31000),
(10,'Meena','Udaipur','Evening',32000), (11,'Ajay','Agra','Morning',28000),
(12,'Rekha','Noida','Evening',34000), (13,'Vijay','Gurgaon','Morning',35000),
(14,'Seema','Faridabad','Evening',30000),
(15,'Nitin','Meerut','Morning',29000),
(16,'Alka','Kanpur','Evening',31000), (17,'Rohit','Lucknow','Morning',32000),
(18,'Shweta','Patna','Evening',28000), (19,'Amit','Kolkata','Morning',33000),
(20,'Priti','Varanasi','Evening',30000), (21,'Manoj','Ajmer','Morning',27000),
(22,'Kiran','Mathura','Evening',29000),
(23,'Deepa','Nashik','Morning',31000),
(24,'Sachin','Pune','Evening',32000), (25,'Komal','Delhi','Morning',34000),
(26,'Harish','Mumbai','Evening',36000),
(27,'Anita','Bhopal','Morning',30000),
(28,'Prakash','Indore','Evening',31000),
(29,'Sonal','Jaipur','Morning',32000),
(30,'Dinesh','Surat','Evening',33000);