for kaushal singh

create database gourav;
use gourav;

CREATE TABLE department(dno int primary key,dname varchar(30));
insert into department(dno,dname)
values(1,"Mathematics"),(2,"Physics"),(3,"Computer Science"),(4,"MCA"),(5,"Management");

create table branch( bcode int primary key, bname varchar(30), dno int);
alter table branch add foreign key (DNO) references department(DNO);

insert into branch (bcode,bname,dno)
values(101,"Data Science",4),(102,"Calculus",1),(103,"Data Structure",3),(104,"Artificial intelligence",4),(105,"Supply Chain",5),(106,"Algebra",1),(107,"Quantum Theory",2),(108,"Human Resources",5),(109,"Thermodynamics",2),(110,"Algorithms",3);

create table branch_course( bcode int, Ccode int, semester varchar(50));
ALTER TABLE branch_course Modify COLUMN semester INT;
desc branch_course;
alter table branch_course add primary key (bcode,Ccode);
alter table branch_course add foreign key (bcode) references branch (bcode);
alter table branch_course add foreign key (ccode) references course (Ccode);

create table course( Ccode int primary key, Cname varchar(30), Credits int , dno int);
alter table course add foreign key (dno) references department (dno);

alter table branch_course add foreign key (ccode) references course (Ccode);

INSERT INTO course (Ccode, Cname, Credits, dno) VALUES
(1011, 'Programming in Python', 3, 2),
(1012, 'Database Management Systems', 4, 3),
(1013, 'Introduction to Statistics', 3, 2),
(1014, 'Software Engineering', 4, 4),
(1015, 'Digital Marketing', 3, 5),
(1016, 'Microeconomics', 3, 1),
(1017, 'Computer Graphics', 4, 3),
(1018, 'Leadership and Management', 3, 5),
(1019, 'Web Development', 4, 2),
(1020, 'Organizational Behavior', 3, 5),
(1021, 'Machine Learning', 5, 4),
(1022, 'Operating Systems', 4, 3),
(1023, 'Corporate Finance', 4, 5),
(1024, 'Network Security', 4, 2),
(1025, 'Introduction to Psychology', 3, 1),
(1026, 'Ethical Hacking', 4, 3),
(1027, 'Human-Computer Interaction', 4, 4),
(1028, 'Strategic Management', 4, 5),
(1029, 'Mobile Application Development', 4, 4),
(1030, 'Artificial Intelligence Ethics', 3, 4),
(1031, 'Data Mining', 4, 3),
(1032, 'Macroeconomics', 3, 1),
(1033, 'Database Design', 4, 3),
(1034, 'Project Management', 4, 4);


INSERT INTO branch_course (bcode, Ccode, semester) VALUES
(101, 1011, 1),
(102, 1012, 2),
(103, 1013, 3),
(104, 1014, 4),
(105, 1015, 5),
(106, 1016, 6),
(107, 1017, 1),
(108, 1018, 2),
(109, 1019, 3),
(110, 1020, 4),
(101, 1021, 5),
(102, 1022, 6),
(103, 1023, 1),
(104, 1024, 2),
(105, 1025, 3),
(106, 1026, 4),
(107, 1027, 5),
(108, 1028, 6),
(109, 1029, 1),
(110, 1030, 2),
(101, 1031, 3),
(102, 1032, 4),
(103, 1033, 5),
(104, 1034, 6);

CREATE TABLE STUDENT (ROLLNO int(5),NAME VARCHAR(20),DOB DATE,GENDER CHAR(2),DOA DATE,BCODE int);
alter table student add primary key (rollno);
alter table student add foreign key (bcode) references branch (bcode);
ALTER TABLE STUDENT ADD CONSTRAINT CHECK (GENDER IN ('M','F'));
ALTER TABLE STUDENT ADD CONSTRAINT CHECK ( DOA between DATE '2023-01-01' and '2025-11-06');



INSERT INTO student (rollno, name, dob, gender, doa, bcode) VALUES
(12001, 'Gourav Upadhyay', '2002-10-07', 'M', '2023-10-10', 101),
(12002, 'Priya Sharma', '2003-05-15', 'F', '2024-02-21', 102),
(12003, 'Rahul Patel', '2002-07-25', 'M', '2023-12-05', 103),
(12004, 'Sneha Gupta', '2003-01-12', 'F', '2024-01-08', 104),
(12005, 'Amit Singh', '2002-11-30', 'M', '2023-11-25', 105),
(12006, 'Ananya Reddy', '2003-09-18', 'F', '2024-05-30', 106),
(12007, 'Rajesh Kumar', '2002-04-05', 'M', '2023-09-15', 107),
(12008, 'Pooja Mishra', '2003-02-20', 'F', '2024-03-12', 108),
(12009, 'Nitin Sharma', '2002-08-10', 'M', '2023-12-20', 109),
(12010, 'Shreya Das', '2003-06-28', 'F', '2024-04-03', 110),
(12011, 'Vikram Tiwari', '2002-03-14', 'M', '2023-08-05', 101),
(12012, 'Divya Singh', '2003-11-09', 'F', '2024-06-15', 102),
(12013, 'Rohan Gupta', '2002-01-25', 'M', '2023-11-30', 103),
(12014, 'Aishwarya Patel', '2003-04-18', 'F', '2024-02-28', 104),
(12015, 'Ankur Dubey', '2002-10-02', 'M', '2023-10-18', 105),
(12016, 'Sneha Rao', '2003-08-16', 'F', '2024-05-25', 106),
(12017, 'Rajat Sharma', '2002-05-30', 'M', '2023-09-05', 107),
(12018, 'Anjali Gupta', '2003-03-25', 'F', '2024-03-20', 108),
(12019, 'Aryan Kumar', '2002-09-10', 'M', '2023-12-25', 109),
(12020, 'Sakshi Singh', '2003-07-28', 'F', '2024-04-10', 110),
(12021, 'Varun Mishra', '2002-04-14', 'M', '2023-08-15', 101),
(12022, 'Aditi Reddy', '2003-12-09', 'F', '2024-06-25', 102),
(12023, 'Kunal Gupta', '2002-02-25', 'M', '2023-12-02', 103),
(12024, 'Meera Patel', '2003-05-18', 'F', '2024-03-02', 104),
(12025, 'Shivam Dubey', '2002-11-02', 'M', '2023-11-20', 105),
(12026, 'Preeti Rao', '2003-09-16', 'F', '2024-05-28', 106),
(12027, 'Vivek Sharma', '2002-06-30', 'M', '2023-09-10', 107),
(12028, 'Neha Gupta', '2003-04-25', 'F', '2024-03-18', 108),
(12029, 'Rahul Kumar', '2002-08-12', 'M', '2023-12-22', 109),
(12030, 'Simran Das', '2003-07-02', 'F', '2024-04-05', 110),
(12031, 'Aditya Tiwari', '2002-03-18', 'M', '2023-08-08', 101),
(12032, 'Ananya Singh', '2003-11-14', 'F', '2024-06-18', 102),
(12033, 'Akash Gupta', '2002-01-28', 'M', '2023-12-05', 103),
(12034, 'Riya Patel', '2003-04-22', 'F', '2024-02-25', 104);



create table enrolls( rollno INT, Ccode INT, sess varchar(15) , grade char(2));
alter table enrolls add primary key (rollno,Ccode,sess);
alter table enrolls add foreign key (rollno) references student (rollno);
alter table enrolls add foreign key (Ccode) references course (Ccode);
ALTER TABLE enrolls ADD CONSTRAINT CHECK (grade IN ('A','B','C','D','E','F'));

INSERT INTO enrolls (rollno, Ccode, sess, grade) VALUES
(12001, 1011, 'dec 2023', 'A'),
(12002, 1012, 'jan 2024', 'B'),
(12003, 1013, 'feb 2024', 'C'),
(12004, 1014, 'mar 2024', 'D'),
(12005, 1015, 'apr 2024', 'E'),
(12006, 1016, 'may 2024', 'F'),
(12007, 1017, 'jun 2024', 'A'),
(12008, 1018, 'jul 2024', 'B'),
(12009, 1019, 'aug 2024', 'C'),
(12010, 1020, 'sep 2024', 'D'),
(12011, 1021, 'oct 2024', 'E'),
(12012, 1022, 'nov 2024', 'F'),
(12013, 1023, 'dec 2024', 'A'),
(12014, 1024, 'jan 2025', 'B'),
(12015, 1025, 'feb 2025', 'C'),
(12016, 1026, 'mar 2025', 'D'),
(12017, 1027, 'apr 2025', 'E'),
(12018, 1028, 'may 2025', 'F'),
(12019, 1029, 'jun 2025', 'A'),
(12020, 1030, 'jul 2025', 'B'),
(12021, 1031, 'aug 2025', 'C'),
(12022, 1032, 'sep 2025', 'D'),
(12023, 1033, 'oct 2025', 'E'),
(12024, 1034, 'nov 2025', 'F'),
(12001, 1012, 'dec 2023', 'A'),
(12002, 1013, 'jan 2024', 'B'),
(12003, 1014, 'feb 2024', 'C'),
(12004, 1015, 'mar 2024', 'D'),
(12005, 1016, 'apr 2024', 'E'),
(12006, 1017, 'may 2024', 'F'),
(12007, 1018, 'jun 2024', 'A'),
(12008, 1019, 'jul 2024', 'B'),
(12009, 1020, 'aug 2024', 'C'),
(12010, 1021, 'sep 2024', 'D'),
(12011, 1022, 'oct 2024', 'E'),
(12012, 1023, 'nov 2024', 'F'),
(12013, 1024, 'dec 2024', 'A'),
(12014, 1025, 'jan 2025', 'B'),
(12015, 1026, 'feb 2025', 'C'),
(12016, 1027, 'mar 2025', 'D'),
(12017, 1028, 'apr 2025', 'E'),
(12018, 1029, 'may 2025', 'F'),
(12019, 1030, 'jun 2025', 'A'),
(12020, 1031, 'jul 2025', 'B'),
(12021, 1032, 'aug 2025', 'C'),
(12022, 1033, 'sep 2025', 'D'),
(12023, 1034, 'oct 2025', 'E'),
(12002, 1011, 'nov 2023', 'A'),
(12003, 1012, 'dec 2023', 'B'),
(12004, 1013, 'jan 2024', 'C'),
(12005, 1014, 'feb 2024', 'D'),
(12006, 1015, 'mar 2024', 'E'),
(12007, 1016, 'apr 2024', 'F'),
(12008, 1017, 'may 2024', 'A'),
(12009, 1018, 'jun 2024', 'B'),
(12010, 1019, 'jul 2024', 'C'),
(12011, 1020, 'aug 2024', 'D'),
(12012, 1021, 'sep 2024', 'E'),
(12013, 1022, 'oct 2024', 'F');