Performance Task 1-3

-- CREATE a database
CREATE DATABASE school_management_sys;

USE school_management_sys;


-- Table Creation
CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    date_of_birth DATE
);

CREATE TABLE teachers (
    teacher_id INT AUTO_INCREMENT PRIMARY KEY,
    teacher_name VARCHAR(50),
    teacher_department VARCHAR(10),
    address VARCHAR(100)
);

CREATE TABLE courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(50),
    credits INT
);

CREATE TABLE enrollments (
    enrollment_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    CONSTRAINT fk_students FOREIGN KEY (student_id) REFERENCES students(student_id) ON DELETE CASCADE,
    CONSTRAINT fk_courses FOREIGN KEY (course_id) REFERENCES courses(course_id) ON DELETE CASCADE
);


-- ALTER/MODIFY THE TABLES
ALTER TABLE students ADD email VARCHAR(100);

ALTER TABLE courses MODIFY course_name VARCHAR(200);
-- modify course name for longer course title

ALTER TABLE teachers DROP COLUMN address;


-- INSERT ROWS OF DATA
INSERT INTO students (first_name, last_name, date_of_birth, email)
VALUES ('khier', 'lapurga', '2007-09-11', 'khier.pogi123@gmail.com'),
       ('john', 'credo', '2006-07-11', 'john.onel@gmail.com'),
       ('fred', 'santiago', '2004-07-11', 'running.fred@gmial.com');

INSERT INTO courses (course_name, credits)
VALUES ('Introduction to Computer Science', 1),
       ('Data Structures and Algorithms', 4),
       ('Object-Oriented Programming', 2);

INSERT INTO enrollments (student_id, course_id, enrollment_date)
VALUES  (1,3,'2024-11-09'),
        (2,2,'2024-10-01'),
        (3,1,'2023-01-05');

-- RETRIEVE DATA
SELECT
    students.student_id,
    students.first_name,
    students.last_name,
    students.email,
    courses.course_name,
    enrollments.enrollment_date
FROM enrollments
JOIN students ON enrollments.student_id = students.student_id
JOIN courses ON enrollments.course_id = courses.course_id;


-- DROPPING/Turncating Tables

-- DEPENDE: Identify the foreign key constraints
-- SHOW CREATE TABLE enrollments;

-- Drop foreign key constraints
ALTER TABLE enrollments
DROP FOREIGN KEY 'fk_courses'; -- Adjust this name based on your SHOW CREATE TABLE output

-- Drop the courses table
DROP TABLE courses;


-- Truncate the enrollments table
TRUNCATE TABLE enrollments;

-- TRUNCATE the students
-- OR WAG NA?

-- INSERT new data for the new semester
-- MAKE A NEW COURSES STRUCTURE
CREATE TABLE courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(200) NOT NULL,
    credits FLOAT NOT NULL,
    course_code VARCHAR(20) UNIQUE NOT NULL,
    description TEXT,
    teacher_id INT,
    semester VARCHAR(50),
    max_students INT DEFAULT 30,
    CONSTRAINT fk_teachers FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id) ON DELETE SET NULL
);



-- Insert new enrollments for the new semester
INSERT INTO enrollments (student_id, course_id, enrollment_date)
VALUES
    (1, 1, '2024-09-01'),  --
    (2, 2, '2024-09-02'),  --
    (3, 3, '2024-09-03');  --

SELECT students.first_name,
               students.last_name,
               students.email,
               courses.course_name,
               enrollment.enrollment_date
FROM enrollments
JOIN students ON enrollments.students_id = students.student_id
JOIN courses ON enrollment.course_id = courses.course_id;


https://drawsql.app/teams/aquabluejay/diagrams/reporting-diagram