-- 1. Create Tables
CREATE TABLE Students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT NOT NULL CHECK (age >= 5),
    gender CHAR(1) NOT NULL
);

CREATE TABLE Course (
    id2 INT AUTO_INCREMENT PRIMARY KEY,
    cname VARCHAR(100) NOT NULL,
    cdesc VARCHAR(100) NOT NULL,
    cyr CHAR(4) NOT NULL
);

CREATE TABLE Enroll (
    id3 INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    edate VARCHAR(12) NOT NULL,
    FOREIGN KEY (student_id) REFERENCES Students(id),
    FOREIGN KEY (course_id) REFERENCES Course(id2)
);

-- 2. Insert 5 Tuples into Each Table
INSERT INTO Students (name, age, gender) VALUES
('Joanna Regnerita', 67, 'F'),
('Librong James', 24, 'M'),
('Charlie Regner', 19, 'M'),
('Louise Prince', 29, 'F'),
('Peter Parking', 22, 'M');

INSERT INTO Course (cname, cdesc, cyr) VALUES
('CS101', 'Intro to CS', '2026'),
('MATH201', 'Calculus II', '2026'),
('ENG102', 'Academic Writing', '2025'),
('PHYS101', 'General Physics', '2026'),
('HIST202', 'World History', '2025');

INSERT INTO Enroll (student_id, course_id, edate) VALUES
(1, 1, '2026-01-10'),
(1, 2, '2026-01-11'),
(2, 1, '2026-01-12'),
(3, 4, '2026-01-15'),
(4, 3, '2025-09-01');

-- 3. Perform 5 Queries Combining All Three Tables

-- Query 1: Display student name, course details, and enrollment date
SELECT s.name AS student_name, c.cname AS course_code, c.cdesc AS course_title, e.edate 
FROM Enroll e
JOIN Students s ON e.student_id = s.id
JOIN Course c ON e.course_id = c.id2;

-- Query 2: Find all male students taking courses in 2026
SELECT s.name, s.gender, c.cname, c.cyr 
FROM Enroll e
JOIN Students s ON e.student_id = s.id
JOIN Course c ON e.course_id = c.id2
WHERE s.gender = 'M' AND c.cyr = '2026';

-- Query 3: Count total enrolled courses per student
SELECT s.name, COUNT(e.course_id) AS total_courses 
FROM Students s
JOIN Enroll e ON s.id = e.student_id
JOIN Course c ON e.course_id = c.id2
GROUP BY s.id, s.name;

-- Query 4: List students enrolled specifically in 'CS101'
SELECT s.name, s.age, c.cname, c.cdesc 
FROM Enroll e
JOIN Students s ON e.student_id = s.id
JOIN Course c ON e.course_id = c.id2
WHERE c.cname = 'CS101';

-- Query 5: Find students older than 20 enrolled in 2026 courses
SELECT DISTINCT s.name, s.age, c.cname, c.cyr 
FROM Enroll e
JOIN Students s ON e.student_id = s.id
JOIN Course c ON e.course_id = c.id2
WHERE s.age > 20 AND c.cyr = '2026';