-- ==========================================
-- RESET TABLES
-- ==========================================

DROP TABLE IF EXISTS enroll;
DROP TABLE IF EXISTS course;
DROP TABLE IF EXISTS students;


-- ==========================================
-- 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,
    ename VARCHAR(100) NOT NULL,
    ecname VARCHAR(100) NOT NULL,
    edate VARCHAR(12) NOT NULL
);


-- ==========================================
-- 2. INSERT 5 STUDENTS
-- ==========================================

INSERT INTO students (name, age, gender) VALUES
('Lebron James', 41, 'M'),
('Manny Pacman', 47, 'M'),
('Stephen Curry', 38, 'M'),
('Cristiano Ronaldo', 41, 'M'),
('Spiderman', 18, 'M');


-- ==========================================
-- 3. INSERT 5 COURSES
-- ==========================================

INSERT INTO course (cname, cdesc, cyr) VALUES
('Basketball Fundamentals', 'Basic basketball skills and training', '2026'),
('Boxing and Fitness', 'Boxing techniques and physical fitness', '2026'),
('Sports Management', 'Management of sports activities', '2026'),
('Football Training', 'Football skills and conditioning', '2026'),
('Superhero Studies', 'Study of superheroes and abilities', '2026');


-- ==========================================
-- 4. INSERT 5 ENROLLMENTS
-- ==========================================

INSERT INTO enroll (ename, ecname, edate) VALUES
('Lebron James', 'Basketball Fundamentals', '2026-09-01'),
('Manny Pacman', 'Boxing and Fitness', '2026-09-02'),
('Stephen Curry', 'Sports Management', '2026-09-03'),
('Cristiano Ronaldo', 'Football Training', '2026-09-04'),
('Spiderman', 'Superhero Studies', '2026-09-05');


-- ==========================================
-- QUERY 1
-- Show all students, courses, and enrollment dates
-- ==========================================

SELECT '========== QUERY 1 ==========' AS Result;

SELECT
    students.id AS Student_ID,
    students.name AS Student,
    course.id2 AS Course_ID,
    course.cname AS Course,
    enroll.id3 AS Enrollment_ID,
    enroll.edate AS Enrollment_Date
FROM students
JOIN enroll
    ON students.name = enroll.ename
JOIN course
    ON enroll.ecname = course.cname;

SELECT '' AS ' ';


-- ==========================================
-- QUERY 2
-- Show student age and course description
-- ==========================================

SELECT '========== QUERY 2 ==========' AS Result;

SELECT
    students.id AS Student_ID,
    students.name AS Student,
    students.age AS Age,
    course.cname AS Course,
    course.cdesc AS Description
FROM students
JOIN enroll
    ON students.name = enroll.ename
JOIN course
    ON enroll.ecname = course.cname;

SELECT '' AS ' ';


-- ==========================================
-- QUERY 3
-- Show students who are 40 years old or older
-- ==========================================

SELECT '========== QUERY 3 ==========' AS Result;

SELECT
    students.id AS Student_ID,
    students.name AS Student,
    students.age AS Age,
    course.cname AS Course,
    enroll.edate AS Enrollment_Date
FROM students
JOIN enroll
    ON students.name = enroll.ename
JOIN course
    ON enroll.ecname = course.cname
WHERE students.age >= 40;

SELECT '' AS ' ';


-- ==========================================
-- QUERY 4
-- Show students enrolled in a training course
-- ==========================================

SELECT '========== QUERY 4 ==========' AS Result;

SELECT
    students.id AS Student_ID,
    students.name AS Student,
    course.id2 AS Course_ID,
    course.cname AS Course,
    course.cdesc AS Description,
    enroll.edate AS Enrollment_Date
FROM students
JOIN enroll
    ON students.name = enroll.ename
JOIN course
    ON enroll.ecname = course.cname
WHERE course.cname LIKE '%Training%';

SELECT '' AS ' ';


-- ==========================================
-- QUERY 5
-- Show all students ordered by age
-- ==========================================

SELECT '========== QUERY 5 ==========' AS Result;

SELECT
    students.id AS Student_ID,
    students.name AS Student,
    students.age AS Age,
    course.cname AS Course,
    enroll.edate AS Enrollment_Date
FROM students
JOIN enroll
    ON students.name = enroll.ename
JOIN course
    ON enroll.ecname = course.cname
ORDER BY students.age ASC;