Practice2.sql

CREATE DATABASE practice2;
USE practice2;

CREATE TABLE students_performance (
    id CHAR(36) PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    gender ENUM('Male','Female'),
    math_score INT,
    reading_score INT,
    writing_score INT
);

CREATE TABLE courses (
    id CHAR(36) PRIMARY KEY,
    course_name VARCHAR(100),
    instructor VARCHAR(100),
    difficulty ENUM('Beginner','Intermediate','Advanced')
);

CREATE TABLE enrollments (
    id CHAR(36) PRIMARY KEY,
    student_id CHAR(36),
    course_id CHAR(36),
    grade INT,
    FOREIGN KEY (student_id) REFERENCES students_performance(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);


EXPLAIN ANALYZE
SELECT 
    s.name,
    c.course_name,
    AVG(e.grade) AS avg_grade
FROM 
    enrollments e
JOIN 
    students_performance s ON s.id = e.student_id
JOIN 
    courses c ON c.id = e.course_id
WHERE 
    YEAR(CURDATE()) = YEAR(CURDATE())  
    AND c.difficulty IN (
        SELECT difficulty 
        FROM courses 
        WHERE difficulty LIKE '%Beginner%'
    )
GROUP BY 
    s.name, c.course_name
ORDER BY 
    avg_grade DESC
LIMIT 10;


CREATE INDEX idx_enrollments_student ON enrollments(student_id);
CREATE INDEX idx_enrollments_course ON enrollments(course_id);
CREATE INDEX idx_courses_difficulty ON courses(difficulty);
CREATE INDEX idx_students_name ON students_performance(name);
CREATE INDEX idx_enrollments_grade ON enrollments(grade);



EXPLAIN ANALYZE
WITH /*+ MATERIALIZATION */ beginner_courses AS (
    SELECT id, course_name
    FROM courses USE INDEX (idx_courses_difficulty)
    WHERE difficulty = 'Beginner'
)
SELECT /*+ NO_MERGE(beginner_courses) */
    s.name,
    bc.course_name,
    AVG(e.grade) AS avg_grade
FROM enrollments e
    USE INDEX (idx_enrollments_course, idx_enrollments_student, idx_enrollments_grade)
JOIN students_performance s USE INDEX (PRIMARY) ON e.student_id = s.id
JOIN beginner_courses bc ON e.course_id = bc.id
WHERE e.grade > 80
GROUP BY s.name, bc.course_name
ORDER BY avg_grade DESC
LIMIT 10;