Notes

-- Table 1: Student
CREATE TABLE Student (
sid INT PRIMARY KEY,
sname VARCHAR(50) NOT NULL,
dob DATE NOT NULL,
marks INT,
CONSTRAINT chk_age CHECK (TIMESTAMPDIFF(YEAR, dob, CURDATE()) < 24)
);

-- Table 2: Course
CREATE TABLE Course (
cid INT PRIMARY KEY,
cname VARCHAR(50) NOT NULL,
credits INT NOT NULL,
CONSTRAINT chk_credits CHECK (credits BETWEEN 2 AND 4)
);

-- Table 3: Enroll
CREATE TABLE Enroll (
sid INT,
cid INT,
enroll_date DATE NOT NULL,
PRIMARY KEY (sid, cid),
FOREIGN KEY (sid) REFERENCES Student(sid),
FOREIGN KEY (cid) REFERENCES Course(cid)
);


SELECT sid, cid, COUNT(*) AS enroll_count
FROM Enroll
GROUP BY sid, cid
HAVING COUNT(*) > 1;


SELECT cname
FROM Course c
WHERE cid NOT IN (SELECT DISTINCT cid FROM Enroll);


SELECT c.cname
FROM Course c
WHERE NOT EXISTS (
SELECT s.sid
FROM Student s
WHERE s.sid NOT IN (
SELECT e.sid
FROM Enroll e
WHERE e.cid = c.cid
)
);