DROP TABLE IF EXISTS MarksGrades;
DROP TABLE IF EXISTS Students;
DROP TABLE IF EXISTS Branches;
DROP TABLE IF EXISTS Teachers;
DROP TABLE IF EXISTS Subjects;
CREATE TABLE Branches (
BranchID INT PRIMARY KEY,
BranchName VARCHAR(15) NOT NULL UNIQUE
);
-- 2. References Branches(BranchName)
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
StudentName VARCHAR(15) NOT NULL,
StudentBranch VARCHAR(15),
CONSTRAINT UQ_Student_Branch
UNIQUE (StudentID, StudentBranch),
CONSTRAINT FK_Student_Branch
FOREIGN KEY (StudentBranch)
REFERENCES Branches(BranchName)
);
-- 3. Independent table: no FKs needed
CREATE TABLE Subjects (
SubjectID VARCHAR(5) PRIMARY KEY,
SubjectName VARCHAR(10),
Semester INT
);
-- 4. References Subjects(SubjectID)
CREATE TABLE Teachers (
Teacher_id VARCHAR(10) PRIMARY KEY,
Teacher_Name VARCHAR(20),
SubjectID VARCHAR(5),
CONSTRAINT FK_Teacher_Subject
FOREIGN KEY (SubjectID)
REFERENCES Subjects(SubjectID)
);
-- 5. References Students AND Subjects
CREATE TABLE MarksGrades (
StudentID INT,
StudentBranch VARCHAR(15),
SubjectID VARCHAR(5),
MARKS FLOAT,
GRADE FLOAT,
PRIMARY KEY (StudentID, SubjectID),
CONSTRAINT FK_Marks_Student
FOREIGN KEY (StudentID, StudentBranch)
REFERENCES Students(StudentID, StudentBranch),
CONSTRAINT FK_Marks_Subject
FOREIGN KEY (SubjectID)
REFERENCES Subjects(SubjectID)
);
DELETE FROM MarksGrades ;
DELETE FROM Teachers ;
DELETE FROM Subjects ;
DELETE FROM Students ;
DELETE FROM Branches ;
INSERT INTO Branches (BranchID, BranchName) VALUES
(1, 'CSE'),
(2, 'ECE'),
(3, 'ME');
INSERT INTO Subjects (SubjectID, SubjectName, Semester) VALUES
('CS101', 'Java', 5),
('CS102', 'DSA', 5),
('CS103', 'DBMS', 6),
('CS104', 'OS', 6),
('CS105', 'CN', 6);
DELETE FROM Teachers ;
INSERT INTO Teachers (Teacher_id, Teacher_Name, SubjectID) VALUES
('T001', 'Dr Sharma', 'CS101'),
('T002', 'Prof Rao', 'CS101'),
('T003', 'Dr Mehta', 'CS101'),
('T004', 'Prof Gupta', 'CS102'),
('T005', 'Dr Iyer', 'CS102'),
('T006', 'Prof Kumar', 'CS102'),
('T007', 'Dr Reddy', 'CS103'),
('T008', 'Prof Singh', 'CS103'),
('T009', 'Dr Patel', 'CS103'),
('T010', 'Prof Joshi', 'CS104'),
('T011', 'Dr Verma', 'CS104'),
('T012', 'Prof Nair', 'CS104'),
('T013', 'Dr Kapoor', 'CS105'),
('T014', 'Prof Das', 'CS105'),
('T015', 'Dr Mishra', 'CS105');
DELETE FROM Students ;
INSERT INTO Students (StudentID, StudentName, StudentBranch) VALUES
(1001, 'Aarav Sharma', 'CSE'),
(1002, 'Vivaan Rao', 'CSE'),
(1003, 'Aditya Kumar', 'CSE'),
(1004, 'Arjun Mehta', 'CSE'),
(1005, 'Rohan Gupta', 'CSE'),
(1006, 'Krishna Iyer', 'CSE'),
(1007, 'Rahul Verma', 'CSE'),
(1008, 'Karan Singh', 'CSE'),
(1009, 'Ankit Patel', 'CSE'),
(1010, 'Siddharth Joshi', 'CSE'),
(1011, 'Manish Nair', 'CSE'),
(1012, 'Varun Kapoor', 'CSE'),
(1013, 'Ritesh Das', 'CSE'),
(1014, 'Nikhil Mishra', 'CSE'),
(1015, 'Yash Agarwal', 'CSE'),
(1016, 'Harsh Bansal', 'CSE'),
(1017, 'Mohit Chandra', 'CSE'),
(1018, 'Saurabh Kulkarni', 'CSE'),
(1019, 'Deepak Reddy', 'CSE'),
(1020, 'Akshay Malhotra', 'CSE'),
(1021, 'Pooja Sharma', 'CSE'),
(1022, 'Sneha Rao', 'CSE'),
(1023, 'Ananya Kumar', 'CSE'),
(1024, 'Neha Mehta', 'CSE'),
(1025, 'Priya Gupta', 'CSE'),
(1026, 'Kavya Iyer', 'CSE'),
(1027, 'Divya Verma', 'CSE'),
(1028, 'Meera Singh', 'CSE'),
(1029, 'Isha Patel', 'CSE'),
(1030, 'Aditi Joshi', 'CSE'),
(1031, 'Riya Nair', 'CSE'),
(1032, 'Tanvi Kapoor', 'CSE'),
(1033, 'Shreya Das', 'CSE'),
(1034, 'Nandini Mishra', 'CSE'),
(1035, 'Aman Sharma', 'ECE'),
(1036, 'Rohit Rao', 'ECE'),
(1037, 'Kunal Kumar', 'ECE'),
(1038, 'Vikas Mehta', 'ECE'),
(1039, 'Sandeep Gupta', 'ECE'),
(1040, 'Ajay Iyer', 'ECE'),
(1041, 'Tarun Verma', 'ECE'),
(1042, 'Sameer Singh', 'ECE'),
(1043, 'Pranav Patel', 'ECE'),
(1044, 'Abhishek Joshi', 'ECE'),
(1045, 'Naveen Nair', 'ECE'),
(1046, 'Rahul Kapoor', 'ECE'),
(1047, 'Vivek Das', 'ECE'),
(1048, 'Rakesh Mishra', 'ECE'),
(1049, 'Yuvraj Agarwal', 'ECE'),
(1050, 'Gaurav Bansal', 'ECE'),
(1051, 'Sahil Chandra', 'ECE'),
(1052, 'Manav Kulkarni', 'ECE'),
(1053, 'Raj Reddy', 'ECE'),
(1054, 'Amit Malhotra', 'ECE'),
(1055, 'Kiran Sharma', 'ECE'),
(1056, 'Monika Rao', 'ECE'),
(1057, 'Swati Kumar', 'ECE'),
(1058, 'Pallavi Mehta', 'ECE'),
(1059, 'Komal Gupta', 'ECE'),
(1060, 'Simran Iyer', 'ECE'),
(1061, 'Preeti Verma', 'ECE'),
(1062, 'Lakshmi Singh', 'ECE'),
(1063, 'Bhavna Patel', 'ECE'),
(1064, 'Rashmi Joshi', 'ECE'),
(1065, 'Shalini Nair', 'ECE'),
(1066, 'Divya Kapoor', 'ECE'),
(1067, 'Aryan Sharma', 'ME'),
(1068, 'Dev Rao', 'ME'),
(1069, 'Ishaan Kumar', 'ME'),
(1070, 'Dhruv Mehta', 'ME'),
(1071, 'Yash Gupta', 'ME'),
(1072, 'Ravi Iyer', 'ME'),
(1073, 'Hemant Verma', 'ME'),
(1074, 'Nitin Singh', 'ME'),
(1075, 'Suraj Patel', 'ME'),
(1076, 'Karthik Joshi', 'ME'),
(1077, 'Lokesh Nair', 'ME'),
(1078, 'Harish Kapoor', 'ME'),
(1079, 'Vimal Das', 'ME'),
(1080, 'Sachin Mishra', 'ME'),
(1081, 'Rajesh Agarwal', 'ME'),
(1082, 'Vishal Bansal', 'ME'),
(1083, 'Pradeep Chandra', 'ME'),
(1084, 'Mohan Kulkarni', 'ME'),
(1085, 'Ramesh Reddy', 'ME'),
(1086, 'Dinesh Malhotra', 'ME'),
(1087, 'Kavitha Sharma', 'ME'),
(1088, 'Deepa Rao', 'ME'),
(1089, 'Sonia Kumar', 'ME'),
(1090, 'Geeta Mehta', 'ME'),
(1091, 'Rekha Gupta', 'ME'),
(1092, 'Anjali Iyer', 'ME'),
(1093, 'Swetha Verma', 'ME'),
(1094, 'Pooja Singh', 'ME'),
(1095, 'Namrata Patel', 'ME'),
(1096, 'Archana Joshi', 'ME'),
(1097, 'Sakshi Nair', 'ME'),
(1098, 'Prema Kapoor', 'ME'),
(1099, 'Jyoti Das', 'ME'),
(1100, 'Madhuri Mishra', 'ME');
DELETE FROM MarksGrades ;
INSERT INTO MarksGrades
(StudentID, StudentBranch, SubjectID, MARKS, GRADE)
VALUES
-- Student 1001 CSE
(1001,'CSE','CS101',92,10.0),
(1001,'CSE','CS102',86,9.0),
(1001,'CSE','CS103',78,8.0),
-- Student 1002 CSE
(1002,'CSE','CS101',88,9.0),
(1002,'CSE','CS102',74,8.0),
(1002,'CSE','CS103',91,10.0),
-- Student 1003 CSE
(1003,'CSE','CS101',81,9.0),
(1003,'CSE','CS102',69,7.0),
(1003,'CSE','CS103',76,8.0),
-- Student 1004 CSE
(1004,'CSE','CS101',95,10.0),
(1004,'CSE','CS102',89,9.0),
(1004,'CSE','CS103',84,9.0),
-- Student 1005 CSE
(1005,'CSE','CS101',73,8.0),
(1005,'CSE','CS102',82,9.0),
(1005,'CSE','CS103',67,7.0),
-- Student 1006 CSE
(1006,'CSE','CS101',90,10.0),
(1006,'CSE','CS102',77,8.0),
(1006,'CSE','CS103',85,9.0),
-- Student 1007 CSE
(1007,'CSE','CS101',79,8.0),
(1007,'CSE','CS102',93,10.0),
(1007,'CSE','CS103',71,8.0),
-- Student 1008 CSE
(1008,'CSE','CS101',87,9.0),
(1008,'CSE','CS102',66,7.0),
(1008,'CSE','CS103',80,9.0),
-- Student 1009 CSE
(1009,'CSE','CS101',94,10.0),
(1009,'CSE','CS102',83,9.0),
(1009,'CSE','CS103',75,8.0),
-- Student 1010 CSE
(1010,'CSE','CS101',68,7.0),
(1010,'CSE','CS102',91,10.0),
(1010,'CSE','CS103',86,9.0),
-- Student 1011 CSE
(1011,'CSE','CS101',82,9.0),
(1011,'CSE','CS102',78,8.0),
(1011,'CSE','CS103',90,10.0),
-- Student 1012 CSE
(1012,'CSE','CS101',96,10.0),
(1012,'CSE','CS102',85,9.0),
(1012,'CSE','CS103',72,8.0),
-- Student 1013 CSE
(1013,'CSE','CS101',76,8.0),
(1013,'CSE','CS102',88,9.0),
(1013,'CSE','CS103',81,9.0),
-- Student 1014 CSE
(1014,'CSE','CS101',84,9.0),
(1014,'CSE','CS102',70,8.0),
(1014,'CSE','CS103',92,10.0),
-- Student 1015 CSE
(1015,'CSE','CS101',91,10.0),
(1015,'CSE','CS102',79,8.0),
(1015,'CSE','CS103',87,9.0),
-- Student 1016 CSE
(1016,'CSE','CS101',65,7.0),
(1016,'CSE','CS102',90,10.0),
(1016,'CSE','CS103',83,9.0),
-- Student 1017 CSE
(1017,'CSE','CS101',89,9.0),
(1017,'CSE','CS102',77,8.0),
(1017,'CSE','CS103',69,7.0),
-- Student 1018 CSE
(1018,'CSE','CS101',93,10.0),
(1018,'CSE','CS102',81,9.0),
(1018,'CSE','CS103',74,8.0),
-- Student 1019 CSE
(1019,'CSE','CS101',80,9.0),
(1019,'CSE','CS102',86,9.0),
(1019,'CSE','CS103',95,10.0),
-- Student 1020 CSE
(1020,'CSE','CS101',71,8.0),
(1020,'CSE','CS102',92,10.0),
(1020,'CSE','CS103',88,9.0),
-- Student 1021 CSE
(1021,'CSE','CS101',85,9.0),
(1021,'CSE','CS102',73,8.0),
(1021,'CSE','CS103',90,10.0),
-- Student 1022 CSE
(1022,'CSE','CS101',78,8.0),
(1022,'CSE','CS102',84,9.0),
(1022,'CSE','CS103',67,7.0),
-- Student 1023 CSE
(1023,'CSE','CS101',97,10.0),
(1023,'CSE','CS102',88,9.0),
(1023,'CSE','CS103',76,8.0),
-- Student 1024 CSE
(1024,'CSE','CS101',83,9.0),
(1024,'CSE','CS102',91,10.0),
(1024,'CSE','CS103',79,8.0),
-- Student 1025 CSE
(1025,'CSE','CS101',69,7.0),
(1025,'CSE','CS102',82,9.0),
(1025,'CSE','CS103',94,10.0),
-- Student 1026 CSE
(1026,'CSE','CS101',86,9.0),
(1026,'CSE','CS102',75,8.0),
(1026,'CSE','CS103',89,9.0),
-- Student 1027 CSE
(1027,'CSE','CS101',92,10.0),
(1027,'CSE','CS102',80,9.0),
(1027,'CSE','CS103',73,8.0),
-- Student 1028 CSE
(1028,'CSE','CS101',77,8.0),
(1028,'CSE','CS102',96,10.0),
(1028,'CSE','CS103',85,9.0),
-- Student 1029 CSE
(1029,'CSE','CS101',90,10.0),
(1029,'CSE','CS102',68,7.0),
(1029,'CSE','CS103',82,9.0),
-- Student 1030 CSE
(1030,'CSE','CS101',81,9.0),
(1030,'CSE','CS102',87,9.0),
(1030,'CSE','CS103',70,8.0),
-- Student 1031 CSE
(1031,'CSE','CS101',95,10.0),
(1031,'CSE','CS102',76,8.0),
(1031,'CSE','CS103',88,9.0),
-- Student 1032 CSE
(1032,'CSE','CS101',74,8.0),
(1032,'CSE','CS102',90,10.0),
(1032,'CSE','CS103',83,9.0),
-- Student 1033 CSE
(1033,'CSE','CS101',88,9.0),
(1033,'CSE','CS102',79,8.0),
(1033,'CSE','CS103',91,10.0),
-- Student 1034 CSE
(1034,'CSE','CS101',93,10.0),
(1034,'CSE','CS102',85,9.0),
(1034,'CSE','CS103',72,8.0) ;
-- select StudentName,StudentID,StudentBranch
-- from Students
-- where (StudentName like 'A%' or StudentName like 'S%') and (StudentBranch = 'CSE' or StudentBranch = null)
-- order by StudentName;
-- select * from Students;
select tech.Teacher_Name , sub.SubjectName , sub.Semester
from Teachers as tech , Subjects as sub
where tech.SubjectID = sub.SubjectID
order by sub.Semester , tech.Teacher_Name;
To embed this project on your website, copy the following code and paste it into your website's HTML: