
CREATE TABLE Classes (
    ClassID INT IDENTITY NOT NULL ,
    ClassName VARCHAR (20) NOT NULL,
    SchoolID INT NULL,
    PRIMARY KEY(ClassID)
);

CREATE TABLE ClassProperties (
    ClassPropertiesID INT  NOT NULL IDENTITY(1,1),
    LecturerDescription VARCHAR (20) NULL,
    SubjectID INT  NULL,
    ClassID INT  NULL,
    TeacherID INT  NULL,
    PRIMARY KEY(ClassPropertiesID)
);

CREATE TABLE Exam (
    ExamID INT NOT NULL IDENTITY,
    ExamName VARCHAR NULL,
    MaximumMarks INT NULL,
    PassingMarks INT NULL,
    ParentExamID INT NULL,
    ParentMarksCarryForward INT NULL,
    PRIMARY KEY(ExamID)
);

CREATE TABLE MarksRecord (
    Marks INT NULL,
    StudentID INT NULL,
    SubjectID INT NULL,
    ExamID INT NULL,
);

CREATE TABLE Post (
    PostID INT  NOT NULL,
    PostName VARCHAR (20) NOT NULL,
    PostRank VARCHAR (20) NULL,
    PRIMARY KEY(PostID)
);

CREATE TABLE School (
    SchoolID INT  NOT NULL IDENTITY,
    SchoolName VARCHAR (20) NULL,
    SchoolAddress VARCHAR (80) NULL,
    PhoneNumber VARCHAR (20) NULL,
    EMailAddress VARCHAR (80) NULL,
    PRIMARY KEY(SchoolID)
);

CREATE TABLE Student (
    StudentID INT NOT NULL IDENTITY,
    StudentName VARCHAR (20) NULL,
    RollNumber INT NULL,
    Gender CHAR (1) NULL,
    StudentAddress VARCHAR (80) NULL,
    PhoneNumber VARCHAR (20) NULL,
    ClassID INT NULL,
    PRIMARY KEY(StudentID)
);

CREATE TABLE Subject (
    SubjectID INT NOT NULL IDENTITY,
    SubjectName VARCHAR (20) NOT NULL,
    PRIMARY KEY(SubjectID)
);

CREATE TABLE Teacher (
    EmployeeID INT NOT NULL IDENTITY,
    TeacherName VARCHAR (20) NULL,
    DateOfJoining DATETIME NULL,
    DateOfBirth DATETIME NULL,
    Salary INT NULL,
    DepartName VARCHAR (20) NULL,
    PostID INT NULL,
    SchoolID INT NULL,
    PRIMARY KEY(EmployeeID)
);

SET IDENTITY_INSERT Classes ON
INSERT INTO Classes(ClassId,ClassName,SchoolId) VALUES ('1', '6TH', '1');
INSERT INTO Classes(ClassId,ClassName,SchoolId) VALUES ('2', '7TH', '1');
INSERT INTO Classes(ClassId,ClassName,SchoolId) VALUES ('3', '8TH', '1');
SET IDENTITY_INSERT Classes OFF

SET IDENTITY_INSERT ClassProperties ON
INSERT INTO ClassProperties(ClassPropertiesID,LecturerDescription,SubjectId,ClassID,TeacherID) VALUES ('1', 'Prose', '2', '3', '1');
INSERT INTO ClassProperties(ClassPropertiesID,LecturerDescription,SubjectId,ClassID,TeacherID) VALUES ('2', 'Prose', '2', '2', '6');
INSERT INTO ClassProperties(ClassPropertiesID,LecturerDescription,SubjectId,ClassID,TeacherID) VALUES ('3', 'Prose', '2', '2', '6');
INSERT INTO ClassProperties(ClassPropertiesID,LecturerDescription,SubjectId,ClassID,TeacherID) VALUES ('4', 'Botany', '1', '1', '8');
INSERT INTO ClassProperties(ClassPropertiesID,LecturerDescription,SubjectId,ClassID,TeacherID) VALUES ('5', 'Zoology', '1', '2', '8');
INSERT INTO ClassProperties(ClassPropertiesID,LecturerDescription,SubjectId,ClassID,TeacherID) VALUES ('6', 'Zoology', '1', '3', '8');
INSERT INTO ClassProperties(ClassPropertiesID,LecturerDescription,SubjectId,ClassID,TeacherID) VALUES ('7', 'Factors', '3', '1', '4');
INSERT INTO ClassProperties(ClassPropertiesID,LecturerDescription,SubjectId,ClassID,TeacherID) VALUES ('8', 'Geomectry', '3', '2', '4');
INSERT INTO ClassProperties(ClassPropertiesID,LecturerDescription,SubjectId,ClassID,TeacherID) VALUES ('9', 'Cubes', '3', '3', '4');
INSERT INTO ClassProperties(ClassPropertiesID,LecturerDescription,SubjectId,ClassID,TeacherID) VALUES ('10', 'Physics', '4', '3', '3');
INSERT INTO ClassProperties(ClassPropertiesID,LecturerDescription,SubjectId,ClassID,TeacherID) VALUES ('11', 'Chemistry', '4', '2', '3');
INSERT INTO ClassProperties(ClassPropertiesID,LecturerDescription,SubjectId,ClassID,TeacherID) VALUES ('12', 'Physics', '4', '3', '3');
INSERT INTO ClassProperties(ClassPropertiesID,LecturerDescription,SubjectId,ClassID,TeacherID) VALUES ('13', 'Physics', '4', '1', '3');
INSERT INTO ClassProperties(ClassPropertiesID,LecturerDescription,SubjectId,ClassID,TeacherID) VALUES ('14', 'Geography', '5', '1', '7');
INSERT INTO ClassProperties(ClassPropertiesID,LecturerDescription,SubjectId,ClassID,TeacherID) VALUES ('15', 'Geography', '5', '2', '7');
INSERT INTO ClassProperties(ClassPropertiesID,LecturerDescription,SubjectId,ClassID,TeacherID) VALUES ('16', 'Geography', '5', '3', '7');
INSERT INTO ClassProperties(ClassPropertiesID,LecturerDescription,SubjectId,ClassID,TeacherID) VALUES ('17', 'History', '5', '3', '7');
INSERT INTO ClassProperties(ClassPropertiesID,LecturerDescription,SubjectId,ClassID,TeacherID) VALUES ('18', 'History', '5', '2', '7');
INSERT INTO ClassProperties(ClassPropertiesID,LecturerDescription,SubjectId,ClassID,TeacherID) VALUES ('19', 'History', '5', '1', '7');
INSERT INTO ClassProperties(ClassPropertiesID,LecturerDescription,SubjectId,ClassID,TeacherID) VALUES ('20', 'fundatmentals', '6', '3', '5');
INSERT INTO ClassProperties(ClassPropertiesID,LecturerDescription,SubjectId,ClassID,TeacherID) VALUES ('21', 'memory', '6', '2', '5');
INSERT INTO ClassProperties(ClassPropertiesID,LecturerDescription,SubjectId,ClassID,TeacherID) VALUES ('22', 'BASIC', '6', '1', '5');
SET IDENTITY_INSERT ClassProperties OFF


SET IDENTITY_INSERT Exam ON
INSERT INTO Exam(ExamID,ExamName,MaximumMarks,PassingMarks,ParentExamId,ParentMarksCarryForward) VALUES ('1', 'final year', '600', '250', '0', '0');
INSERT INTO Exam(ExamID,ExamName,MaximumMarks,PassingMarks,ParentExamId,ParentMarksCarryForward) VALUES ('2', 'half yearly', '600', '250', '1', '25');
INSERT INTO Exam(ExamID,ExamName,MaximumMarks,PassingMarks,ParentExamId,ParentMarksCarryForward) VALUES ('3', 'session1', '600', '250', '2', '20');
INSERT INTO Exam(ExamID,ExamName,MaximumMarks,PassingMarks,ParentExamId,ParentMarksCarryForward) VALUES ('4', 'session2', '600', '250', '2', '20');
SET IDENTITY_INSERT Exam OFF


INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('98', '1', '1', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('87', '1', '2', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('99', '1', '3', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('78', '1', '4', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('52', '1', '5', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('77', '1', '6', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('88', '2', '1', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('91', '2', '2', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('80', '2', '3', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('89', '2', '4', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('70', '2', '5', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('75', '2', '6', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('79', '3', '1', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('71', '3', '2', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('79', '3', '3', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('78', '3', '4', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('73', '3', '5', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('89', '3', '6', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('99', '4', '1', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('81', '4', '2', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('59', '4', '3', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('89', '4', '4', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('71', '4', '5', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('79', '4', '6', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('99', '5', '1', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('81', '5', '2', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('59', '5', '3', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('89', '5', '4', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('71', '5', '5', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('79', '5', '6', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('98', '6', '1', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('87', '6', '2', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('89', '6', '3', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('78', '6', '4', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('72', '6', '5', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('67', '6', '6', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('79', '7', '1', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('71', '7', '2', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('69', '7', '3', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('78', '7', '4', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('73', '7', '5', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('89', '7', '6', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('78', '8', '1', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('80', '8', '2', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('88', '8', '3', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('89', '8', '4', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('70', '8', '5', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('75', '8', '6', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('99', '9', '1', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('81', '9', '2', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('59', '9', '3', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('89', '9', '4', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('71', '9', '5', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('79', '9', '6', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('99', '10', '1', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('81', '10', '2', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('59', '10', '3', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('89', '10', '4', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('71', '10', '5', '1');
INSERT INTO MarksRecord(Marks,StudentID,SubjectID,ExamID) VALUES ('79', '10', '6', '1');


INSERT INTO Post(PostID,PostName,PostRank) VALUES ('1', 'Principal', '1');
INSERT INTO Post(PostID,PostName,PostRank) VALUES ('2', 'Vice Principal', '2');
INSERT INTO Post(PostID,PostName,PostRank) VALUES ('3', 'Teacher', '3');

SET IDENTITY_INSERT School ON
INSERT INTO School(SchoolID,SchoolName,SchoolAddress,PhoneNumber,EMailAddress) VALUES ('1', 'Arthur Morgan School', '1901,Hannah Branch RoadBurnsville,NC', '1-828-675-4262', 'info@arthurmorganschool.org');
SET IDENTITY_INSERT School OFF

SET IDENTITY_INSERT Student ON
INSERT INTO Student(StudentID,StudentName,RollNumber,Gender,StudentAddress,PhoneNumber,ClassID) VALUES ('1', 'Catherine Guzman', '1001', 'F', '1500 Warburton Avenue, Santa Clara, CA 95050', '(408)615-2250', '1');
INSERT INTO Student(StudentID,StudentName,RollNumber,Gender,StudentAddress,PhoneNumber,ClassID) VALUES ('2', 'John K.', '1002', 'M', 'Oroville City Palace 1735 Montgomery Street Oroville, California 95965', '(408) 615 - 7297', '1');
INSERT INTO Student(StudentID,StudentName,RollNumber,Gender,StudentAddress,PhoneNumber,ClassID) VALUES ('3', 'Cathe', '1003', 'F', 'Raananah Park, Highland Mills, NY 10930-0000', '(845) 928 - 6395', '1');
INSERT INTO Student(StudentID,StudentName,RollNumber,Gender,StudentAddress,PhoneNumber,ClassID) VALUES ('4', 'John QQQ', '1004', 'M', '3000 N Sheridan Rd, Chicago, IL 60657-5553', '(773) 477 - 6034', '1');
INSERT INTO Student(StudentID,StudentName,RollNumber,Gender,StudentAddress,PhoneNumber,ClassID) VALUES ('5', 'Woll B.', '1005', 'F', '11200 Arbor Creek Dr, Richmond, VA 23233-020', '(804) 934 - 9178', '1');
INSERT INTO Student(StudentID,StudentName,RollNumber,Gender,StudentAddress,PhoneNumber,ClassID) VALUES ('6', 'Valine', '1006', 'F', '1419 Madison Pl, Pompton Lakes, NJ 07442-1517', '(973) 835 - 9107', '2');
INSERT INTO Student(StudentID,StudentName,RollNumber,Gender,StudentAddress,PhoneNumber,ClassID) VALUES ('7', 'Liebig', '1007', 'F', '1590 N Airport Rd, Columbia City, IN 46725-8679', '(219) 248 - 8261', '2');
INSERT INTO Student(StudentID,StudentName,RollNumber,Gender,StudentAddress,PhoneNumber,ClassID) VALUES ('8', 'Williams', '1008', 'M', '2700 Park Ave, Minneapolis, MN 55407-1017', '(612) 872 - 1014', '2');
INSERT INTO Student(StudentID,StudentName,RollNumber,Gender,StudentAddress,PhoneNumber,ClassID) VALUES ('9', 'Tovera', '1009', 'M', '3048 Brighton 13th St, Brooklyn, NY 11235-5638', '(718) 646 - 3603', '2');
INSERT INTO Student(StudentID,StudentName,RollNumber,Gender,StudentAddress,PhoneNumber,ClassID) VALUES ('10', 'WinkField', '1010', 'F', '19325 Royalton Rd, Strongsville, OH 44149-4946', '(440) 238 - 7297', '2');
SET IDENTITY_INSERT Student OFF

SET IDENTITY_INSERT Subject ON
INSERT INTO Subject(SubjectID,SubjectName) VALUES ('1', 'Biology');
INSERT INTO Subject(SubjectID,SubjectName) VALUES ('2', 'English');
INSERT INTO Subject(SubjectID,SubjectName) VALUES ('3', 'Mathematics');
INSERT INTO Subject(SubjectID,SubjectName) VALUES ('4', 'Science');
INSERT INTO Subject(SubjectID,SubjectName) VALUES ('5', 'Social Studies');
INSERT INTO Subject(SubjectID,SubjectName) VALUES ('6', 'Computers');
SET IDENTITY_INSERT Subject OFF

SET IDENTITY_INSERT Teacher ON
INSERT INTO Teacher(EmployeeID,TeacherName,DateOfJoining,DateOfBirth,Salary,DepartName,PostID,SchoolID) VALUES ('1', 'Mr. Agregado', '1996-04-16', '1965-04-09', '10000', 'English', '1', '1');
INSERT INTO Teacher(EmployeeID,TeacherName,DateOfJoining,DateOfBirth,Salary,DepartName,PostID,SchoolID) VALUES ('2', 'Mr. Brumfield', '1997-06-30', '1966-11-26', '8520', 'Science', '2', '1');
INSERT INTO Teacher(EmployeeID,TeacherName,DateOfJoining,DateOfBirth,Salary,DepartName,PostID,SchoolID) VALUES ('3', 'Ms. McKelvey', '1998-09-24', '1968-01-06', '6000', 'Science', '3', '1');
INSERT INTO Teacher(EmployeeID,TeacherName,DateOfJoining,DateOfBirth,Salary,DepartName,PostID,SchoolID) VALUES ('4', 'Mr. Everett', '1998-10-24', '1968-01-16', '7200', 'Math', '3', '1');
INSERT INTO Teacher(EmployeeID,TeacherName,DateOfJoining,DateOfBirth,Salary,DepartName,PostID,SchoolID) VALUES ('5', 'Mr. Verstrepen', '1998-08-28', '1969-05-17', '6000', 'Computer', '3', '1');
INSERT INTO Teacher(EmployeeID,TeacherName,DateOfJoining,DateOfBirth,Salary,DepartName,PostID,SchoolID) VALUES ('6', 'Mr. Haight', '1998-09-14', '1968-02-21', '6001', 'English', '3', '1');
INSERT INTO Teacher(EmployeeID,TeacherName,DateOfJoining,DateOfBirth,Salary,DepartName,PostID,SchoolID) VALUES ('7', 'Ms. Hartenfeld', '1998-09-14', '1970-01-05', '6002', 'Social Studies', '3', '1');
INSERT INTO Teacher(EmployeeID,TeacherName,DateOfJoining,DateOfBirth,Salary,DepartName,PostID,SchoolID) VALUES ('8', 'Mr. Henry', '1998-10-24', '1969-03-22', '6065', 'Biology', '3', '1');
SET IDENTITY_INSERT Teacher OFF