app/schemas/college.sql

-- Students table
CREATE TABLE students (
    student_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    department TEXT,
    year INTEGER,
    email TEXT
);

-- Courses table
CREATE TABLE courses (
    course_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    department TEXT,
    credits INTEGER,
    instructor TEXT
);

-- Enrollments table
CREATE TABLE enrollments (
    enrollment_id INTEGER PRIMARY KEY,
    student_id INTEGER,
    course_id INTEGER,
    semester TEXT,
    grade TEXT,
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);


INSERT INTO students (student_id, name, department, year, email) VALUES
(1, 'Rahul Sharma', 'CSE', 2, 'rahul.s@univ.edu'),
(2, 'Anjali Mehta', 'ECE', 3, 'anjali.m@univ.edu'),
(3, 'Soham Verma', 'ME', 2, 'soham.v@univ.edu'),
(4, 'Pooja Singh', 'CSE', 1, 'pooja.s@univ.edu'),
(5, 'Karan Patel', 'EEE', 4, 'karan.p@univ.edu');

INSERT INTO courses (course_id, name, department, credits, instructor) VALUES
(101, 'Data Structures', 'CSE', 4, 'Dr. Anil Kapoor'),
(102, 'Digital Electronics', 'ECE', 3, 'Dr. Priya Nair'),
(103, 'Thermodynamics', 'ME', 4, 'Dr. Ramesh Rao'),
(104, 'Operating Systems', 'CSE', 4, 'Dr. Neha Malhotra'),
(105, 'Power Systems', 'EEE', 3, 'Dr. Amit Kumar');

INSERT INTO enrollments (enrollment_id, student_id, course_id, semester, grade) VALUES
(1, 1, 101, 'Spring 2023', 'A'),
(2, 1, 104, 'Spring 2023', 'B+'),
(3, 2, 102, 'Spring 2023', 'A-'),
(4, 3, 103, 'Spring 2023', 'B'),
(5, 4, 101, 'Spring 2023', 'A'),
(6, 4, 104, 'Spring 2023', 'A-'),
(7, 5, 105, 'Spring 2023', 'B+');