CREATE TABLE Institutions (
    institution_id INT PRIMARY KEY,
    institution_name VARCHAR(255),
    location VARCHAR(255),
    institution_contact VARCHAR(255),
    website VARCHAR(255)
);
CREATE TABLE Courses (
    course_id INT PRIMARY KEY,
    institution_id INT,
    course_name VARCHAR(255),
    course_code VARCHAR(20),
    department VARCHAR(100),
    course_description TEXT,
    credit_hours INT,
    FOREIGN KEY (institution_id) REFERENCES Institutions(institution_id)    
);
CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    student_firts_name VARCHAR(50),
    student_last_name VARCHAR(50),
    student_date_of_birth DATE,
    student_contact VARCHAR(255),
    enrollment_date DATE,
    institution_id INT,
    FOREIGN KEY (institution_id) REFERENCES Institutions(institution_id)
);
CREATE TABLE Faculty (
    faculty_id INT PRIMARY KEY,
    lecturer_first_name VARCHAR(50),
    lecturer_last_name VARCHAR(50),
    lecturer_date_of_birth DATE,
    lecturer_contact VARCHAR(255),
    hire_date DATE,
    institution_id INT,
    department VARCHAR(100),
    FOREIGN KEY (institution_id) REFERENCES Institutions(institution_id)
);
CREATE TABLE Enrollments (
    enrollment_id INT PRIMARY KEY,
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    FOREIGN KEY (student_id) REFERENCES Students(student_id),
    FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);
CREATE TABLE Assignments (
    assignment_id INT PRIMARY KEY,
    course_id INT,
    title VARCHAR(255),
    description TEXT,
    due_date DATE,
    FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);
CREATE TABLE Grades (
    grade_id INT PRIMARY KEY,
    student_id INT,
    assignment_id INT,
    score DECIMAL(5,2),
    FOREIGN KEY (student_id) REFERENCES Students(student_id),
    FOREIGN KEY (assignment_id) REFERENCES Assignments(assignment_id)
);
CREATE TABLE Events (
    event_id INT PRIMARY KEY,
    institution_id INT,
    event_name VARCHAR(255),
    event_date DATE,
    event_description TEXT,
    FOREIGN KEY (institution_id) REFERENCES Institutions(institution_id)
);
CREATE TABLE Faculty_Courses (
    faculty_courses_id INT PRIMARY KEY,
    faculty_id INT,
    course_id INT,
    FOREIGN KEy (faculty_id) REFERENCES Faculty(faculty_id),
    FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);
INSERT INTO Institutions (institution_id, institution_name, location, institution_contact, website) VALUES
    (1, 'University of Science', 'New York', 'contact@science.edu', 'www.science.edu'),
    (2, 'University of Taxes', 'Washington', 'contact@taxes.edu', 'www.taxes.edu'),
    (3, 'Technical Institute', 'Tect Town', 'info@tectinst.edu', 'www.tectinst.edu');
INSERT INTO Courses (course_id, institution_id, course_name, course_code, department,course_description, credit_hours) VALUES
    (1, 1, 'Coding', 'CS101', 'Computer Science', 'Introduction to Programming', 3),
    (2, 1, 'History', 'HIST101', 'History', 'World history overview', 3),
    (3, 2, 'Maths', 'MATH101', 'Mathematics', 'Advance calculus', 4);
INSERT INTO Students (student_id, student_firts_name, student_last_name, student_date_of_birth, student_contact, enrollment_date, institution_id) VALUES
    (1, 'John', 'Doe', '1995-05-15', 'jhon@example.com', '2021-09-01', 1),
    (2, 'Jane', 'Smith', '1998-02-20', 'jane@example.com', '2022-01-15', 1),
    (3, 'Bob', 'Johnson', '1997-11-10', 'bob@example.com', '2022-03-10', 2),
    (4, 'Alice', 'Brown', '1999-07-30', 'alice@example.com', '2022-09-05', 3),
    (5, 'Eva', 'Miller', '1996-04-25', 'eva@example.com', '2021-08-20', 2);
INSERT INTO Faculty (faculty_id, lecturer_first_name,lecturer_last_name,lecturer_date_of_birth, lecturer_contact, hire_date, institution_id, department) VALUES
    (1, 'Prof', 'Smith', '1970-09-05', 'prof.smith@example.com', '2015-02-15', 1, 'Computer Science'),
    (2, 'Dr', 'Johnson', '1985-03-20', 'dr.johnson@example.com', '2010-05-10', 2, 'History'),
    (3, 'Prof', 'White', '1978-06-15', 'prof.white@example.com', '2018-01-20', 1, 'Mathematics'),
    (4, 'Dr', 'Brown', '1980-11-30', 'dr.brown@example.com', '2017-09-03', 1, 'Computer Science'),
    (5, 'Prof', 'Clark', '1975-08-10', 'prof.clark@example.com', '2014-03-01', 1, 'History');
INSERT INTO Enrollments (enrollment_id, student_id, course_id, enrollment_date) VALUES
    (1, 1, 1, '2021-09-01'),
    (2, 2, 1, '2022-01-15'),
    (3, 3, 2, '2022-03-10'),
    (4, 4, 3, '2022-09-05'),
    (5, 5, 3, '2021-08-20');
INSERT INTO Assignments (assignment_id, course_id, title, description, due_date) VALUES
    (1, 1, 'Assignment 1', 'Assignment on Programming', '2023-10-30'),
    (2, 1, 'Assignment 2', 'Data structures assignment', '2023-11-15'),
    (3, 2, 'History Essay', 'Research paper on history', '2023-11-10'),
    (4, 3, 'Assignment 3', 'Engineering project on topic', '2023-12-05'),
    (5, 1, 'Assignment 4', 'Machine learning project', '2023-11-25');
INSERT INTO Grades (grade_id, student_id, assignment_id, score) VALUES
    (1, 1, 1, 90.5),
    (2, 2, 1, 85.0),
    (3, 3, 3, 92.5),
    (4, 4, 4, 88.0),
    (5, 5, 4, 95.5);
INSERT INTO Events (event_id, institution_id, event_name, event_date, event_description) VALUES
    (1, 1, 'Computer Symposium', '2023-11-15', 'Annual symposium on computer science topics.'),
    (2, 2, 'History Conference', '2023-12-05', 'Conference on historical research and topics.'),
    (3, 3, 'Engineering Expo', '2023-11-20', 'Exhibition of engineering project and innovations.');
INSERT INTO Faculty_Courses (faculty_courses_id, faculty_id, course_id) VALUES
    (1, 1, 1),
    (2, 2, 2),
    (3, 3, 3),
    (4, 4, 2),
    (5, 5, 2);
-- SELECT institution_id, institution_name, location, institution_contact, website
-- FROM Institutions
-- SELECT course_name, department, course_description
-- FROM Institutions
-- JOIN Courses ON Institutions.institution_id=Courses.institution_id
-- WHERE Institutions.institution_name='University of Science';
-- SELECT student_id, student_firts_name, student_last_name, student_date_of_birth, student_contact, enrollment_date, institution_id
-- FROM Students
-- WHERE Students.student_firts_name='John' AND Students.student_last_name='Doe';
-- SELECT faculty_id, lecturer_first_name, lecturer_last_name, lecturer_date_of_birth, lecturer_contact, hire_date, institution_id, department
-- FROM Faculty
-- WHERE Faculty.department='Computer Science';
-- SELECT Students.student_firts_name, Students.student_last_name
-- FROM Students
-- JOIN Enrollments ON Enrollments.student_id=Students.student_id
-- JOIN Courses ON Courses.course_id=Enrollments.course_id
-- WHERE Courses.course_name='Coding';
-- SELECT title, description, due_date
-- FROM Assignments
-- JOIN Courses ON Assignments.course_id=Courses.course_id
-- WHERE Courses.course_name='Coding';
-- SELECT AVG (Grades.score) AS average_grade
-- FROM Grades
-- JOIN Students ON Grades.student_id=Students.student_id
-- WHERE Students.student_firts_name='John' AND Students.student_last_name='Doe';
-- SELECT event_name, event_date, event_description
-- FROM Events
-- JOIN Institutions ON Events.institution_id=Institutions.institution_id
-- WHERE Institutions.institution_name='University of Science';
-- SELECT lecturer_first_name, lecturer_last_name
-- FROM Faculty
-- JOIN Faculty_Courses ON Faculty.faculty_id=Faculty_Courses.faculty_id
-- JOIN Courses ON Courses.course_id=Faculty_Courses.course_id
-- WHERE Courses.course_name='Coding';
-- SELECT Students.student_firts_name, Students.student_last_name, Grades.score
-- FROM Students
-- JOIN Grades ON Students.student_id=Grades.student_id
-- JOIN Assignments ON Assignments.assignment_id=Grades.assignment_id
-- JOIN Courses ON Assignments.course_id=Courses.course_id
-- WHERE Courses.course_name='Maths' ORDER BY Grades.score DESC LIMIT 1;