CREATE TABLE STUDENT (
    RollNo CHAR(6),
    StudentName VARCHAR(20),
    Course VARCHAR(20),
    DOB VARCHAR(10)
);

CREATE TABLE SOCIETY (
    SocID CHAR(6),
    SocName VARCHAR(20),
    MentorName VARCHAR(20),
    TotalSeats INT UNSIGNED
);

CREATE TABLE ENROLLMENT (
    RollNo CHAR(6),
    SID CHAR(6),
    DateOfEnrollment VARCHAR(10)
);


-- Sample data entries for the STUDENT table
INSERT INTO STUDENT (RollNo, StudentName, Course, DOB) VALUES
('S00001', 'John Doe', 'Computer Science', '1998-05-15'),
('S00002', 'Jane Smith', 'Chemistry', '1999-02-28'),
('S00003', 'Alice Johnson', 'English Literature', '2000-09-10'),
('S00004', 'Bob Williams', 'History', '1997-12-03'),
('S00005', 'Emily Brown', 'Mathematics', '1998-07-20'),
('S00006', 'Michael Lee', 'Physics', '1999-11-25'),
('S00007', 'Sarah Miller', 'Economics', '2000-03-12'),
('S00008', 'David Gupta', 'Computer Science', '1997-08-10'),
('S00009', 'Sophia Johnson', 'Chemistry', '1998-06-05'),
('S00010', 'Emma Gupta', 'English Literature', '2001-02-15'),
('S00011', 'William Anderson', 'Biology', '1999-01-01'),
('S00012', 'Olivia Jones', 'Psychology', '2000-07-07');

-- Sample data entries (unchanged) for the SOCIETY table
INSERT INTO SOCIETY (SocID, SocName, MentorName, TotalSeats) VALUES
('SC001', 'Debating Club', 'David Gupta', 50),
('SC002', 'Dancing Society', 'Sophia Johnson', 60),
('SC003', 'NSS', 'Emily Brown', 100),
('SC004', 'Music Club', 'Michael Smith', 40),
('SC005', 'Sashakt', 'Unknown', 40);

-- Sample data entries (corrected) for the ENROLLMENT table
INSERT INTO ENROLLMENT (RollNo, SID, DateOfEnrollment) VALUES
('S00001', 'SC001', '2022-01-15'),  
('S00001', 'SC004', '2022-02-20'),  
('S00002', 'SC001', '2022-02-20'),  
('S00002', 'SC003', '2022-03-25'),  
('S00003', 'SC002', '2022-03-25'),
('S00004', 'SC002', '2022-04-30'),
('S00005', 'SC003', '2022-05-05'),
('S00006', 'SC003', '2022-06-10'),
('S00007', 'SC003', '2022-07-15'),
('S00008', 'SC004', '2022-08-20'),
('S00009', 'SC004', '2022-09-25'),
('S00010', 'SC004', '2022-10-30'),
('S00011', 'SC003', '2022-11-15'),
('S00005', 'SC005', '2023-01-10'),  
('S00009', 'SC005', '2023-02-15'); 

/*8. Find society names in which more than five students have enrolled*/
SELECT s.SocName
FROM SOCIETY s
INNER JOIN ENROLLMENT e ON s.SocID = e.SID
GROUP BY s.SocName
HAVING COUNT(*) > 5;

/*9. Find the name of the youngest student enrolled in society 'NSS'.*/
SELECT MIN(s.StudentName) AS YoungestStudent
FROM STUDENT s
INNER JOIN ENROLLMENT e ON s.RollNo = e.RollNo
WHERE e.SID = 'NSS'
ORDER BY s.DOB ASC
LIMIT 1;

/*10. Find the name of the most popular society (on the basis of enrolled students)*/
SELECT s.SocName
FROM SOCIETY s
INNER JOIN ENROLLMENT e ON s.SocID = e.SID
GROUP BY s.SocName
ORDER BY COUNT(*) DESC
LIMIT 1;

/*11.Find the name of two least popular societies (on the basis of enrolled students).*/
SELECT s.SocName
FROM SOCIETY s
LEFT JOIN ENROLLMENT e ON s.SocID = e.SID
GROUP BY s.SocName
ORDER BY COUNT(*) ASC
LIMIT 2;

/*12.Find the student names who are not enrolled in any society.*/
SELECT StudentName
FROM STUDENT
WHERE RollNo NOT IN (SELECT RollNo FROM ENROLLMENT);


/*13.Find the student names enrolled in at least two societies*/
SELECT s.StudentName
FROM STUDENT s
INNER JOIN ENROLLMENT e ON s.RollNo = e.RollNo
GROUP BY s.StudentName
HAVING COUNT(DISTINCT e.SID) >= 2;

/*14. Find society names in which the maximum students are enrolled*/
SELECT s.SocName
FROM SOCIETY s
INNER JOIN (
    SELECT SID, COUNT(*) AS EnrolledCount
    FROM ENROLLMENT
    GROUP BY SID
) e ON s.SocID = e.SID
WHERE EnrolledCount = (
    SELECT MAX(EnrolledCount)
    FROM (
        SELECT COUNT(*) AS EnrolledCount
        FROM ENROLLMENT
        GROUP BY SID
    ) temp
);

/*15. Find names of all students who have enrolled in any society and society names in which at least one student has enrolled.*/
SELECT DISTINCT s.StudentName
FROM STUDENT s
INNER JOIN ENROLLMENT e ON s.RollNo = e.RollNo;

SELECT DISTINCT s.SocName
FROM SOCIETY s
INNER JOIN ENROLLMENT e ON s.SocID = e.SID;


/*16. Find names of students who are enrolled in any of the three societies 'Debating', 'Dancing', and 'Sashakt'.*/
SELECT DISTINCT s.StudentName
FROM STUDENT s
INNER JOIN ENROLLMENT e ON s.RollNo = e.RollNo
INNER JOIN SOCIETY soc ON e.SID = soc.SocID
WHERE soc.SocName IN ('Debating', 'Dancing', 'Sashakt');


/*17. Find society names such that its mentor has a name with 'Gupta' in it.*/
SELECT SocName
FROM SOCIETY
WHERE MentorName LIKE '%Gupta%';


/*18. Find the society names in which the number of enrolled students is only 10% of its capacity.*/
SELECT s.SocName
FROM SOCIETY s
INNER JOIN (
    SELECT SID, COUNT(*) AS EnrolledCount
    FROM ENROLLMENT
    GROUP BY SID
) e ON s.SocID = e.SID
WHERE EnrolledCount <= 0.1 * s.TotalSeats;