Database Documentation

-- Courses Table
CREATE TABLE courses (
    course_id INT PRIMARY KEY AUTO_INCREMENT,
    course_code VARCHAR(10) NOT NULL UNIQUE,
    course_name VARCHAR(100) NOT NULL,
    description TEXT
);

-- Materials Table
CREATE TABLE materials (
    material_id INT PRIMARY KEY AUTO_INCREMENT,
    course_id INT NOT NULL,
    title VARCHAR(150) NOT NULL,
    file_type VARCHAR(10) NOT NULL,
    upload_date DATE NOT NULL,
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

-- Study Groups Table
CREATE TABLE study_groups (
    group_id INT PRIMARY KEY AUTO_INCREMENT,
    course_id INT NOT NULL,
    group_name VARCHAR(100) NOT NULL,
    meeting_time VARCHAR(100) NOT NULL,
    member_count INT DEFAULT 0,
    description TEXT,
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

1. SELECT with JOIN - Get all materials with course names

SELECT 
    m.material_id,
    m.title,
    m.file_type,
    c.course_code,
    c.course_name,
    m.upload_date
FROM materials m
INNER JOIN courses c ON m.course_id = c.course_id
ORDER BY m.upload_date DESC;

2. GROUP BY - Count materials per course

SELECT 
    c.course_code,
    c.course_name,
    COUNT(m.material_id) AS total_materials
FROM courses c
LEFT JOIN materials m ON c.course_id = m.course_id
GROUP BY c.course_id, c.course_code, c.course_name
ORDER BY total_materials DESC;

3. HAVING - Courses with more than 2 study groups

SELECT 
    c.course_code,
    c.course_name,
    COUNT(sg.group_id) AS group_count,
    SUM(sg.member_count) AS total_members
FROM courses c
INNER JOIN study_groups sg ON c.course_id = sg.course_id
GROUP BY c.course_id, c.course_code, c.course_name
HAVING COUNT(sg.group_id) > 2
ORDER BY group_count DESC;

4. Complex JOIN - Get complete course statistics

SELECT 
    c.course_code,
    c.course_name,
    COUNT(DISTINCT m.material_id) AS material_count,
    COUNT(DISTINCT sg.group_id) AS group_count,
    SUM(sg.member_count) AS total_members
FROM courses c
LEFT JOIN materials m ON c.course_id = m.course_id
LEFT JOIN study_groups sg ON c.course_id = sg.course_id
GROUP BY c.course_id, c.course_code, c.course_name
ORDER BY c.course_code;