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

INSERT INTO Students (name, age, gender) VALUES
('Abdul', 20, 'M'),
('John', 21, 'M'),
('Maria', 19, 'F'),
('Sarah', 22, 'F'),
('David', 20, 'M');

INSERT INTO Course (cname, cdesc, cyr) VALUES
('Database Systems', 'Introduction to databases', '2026'),
('Programming 1', 'Basic programming concepts', '2026'),
('Web Development', 'HTML CSS and JavaScript', '2026'),
('Computer Networks', 'Networking fundamentals', '2026'),
('Software Engineering', 'Software development process', '2026');

INSERT INTO Enroll (ename, ecname, edate) VALUES
('Abdul', 'Database Systems', '2026-09-01'),
('John', 'Programming 1', '2026-09-02'),
('Maria', 'Web Development', '2026-09-03'),
('Sarah', 'Computer Networks', '2026-09-04'),
('David', 'Software Engineering', '2026-09-05');

SELECT Students.name, Course.cname, Enroll.edate
FROM Students
JOIN Enroll ON Students.name = Enroll.ename
JOIN Course ON Enroll.ecname = Course.cname;

SELECT Students.name, Students.age, Course.cname
FROM Students
JOIN Enroll ON Students.name = Enroll.ename
JOIN Course ON Enroll.ecname = Course.cname;

SELECT Students.name, Students.gender, Course.cname
FROM Students
JOIN Enroll ON Students.name = Enroll.ename
JOIN Course ON Enroll.ecname = Course.cname
WHERE Students.gender = 'M';

SELECT Students.name, Students.gender, Course.cname
FROM Students
JOIN Enroll ON Students.name = Enroll.ename
JOIN Course ON Enroll.ecname = Course.cname
WHERE Students.gender = 'F';

SELECT Students.name,
       Students.age,
       Course.cname,
       Course.cdesc,
       Enroll.edate
FROM Students
JOIN Enroll ON Students.name = Enroll.ename
JOIN Course ON Enroll.ecname = Course.cname
WHERE Students.age >= 20;