CREATE TABLE Student (
Roll_no CHAR(6) PRIMARY KEY,
Studentname VARCHAR(20),
Course VARCHAR(20),
Dob DATE
);
INSERT into Student(Roll_no, Studentname, Course, Dob)
VALUES('1','Akash','Computer science','2004-01-25');
INSERT into Student(Roll_no, Studentname, Course, Dob)
VALUES('2','Sumit','Bsc','2002-01-15');
INSERT into Student(Roll_no, Studentname, Course, Dob)
VALUES('3','Ayush','Chemistry','2002-12-18');
INSERT into Student(Roll_no, Studentname, Course, Dob)
VALUES('4','Ujjwal','Economics','2002-05-10');
INSERT into Student(Roll_no, Studentname, Course, Dob)
VALUES('5','Deewakar','M.phil','2000-11-23');
INSERT into Student(Roll_no, Studentname, Course, Dob)
VALUES('6','Deepak','B.pharma','2005-10-03');
INSERT into Student(Roll_no, Studentname, Course, Dob)
VALUES('7','Prabhakar','Msc','2000-06-08');
INSERT into Student(Roll_no, Studentname, Course, Dob)
VALUES('8','Vikas','Bca','2002-01-28');
INSERT into Student(Roll_no, Studentname, Course, Dob)
VALUES('X9','Krish','Mca','2001-07-13');
INSERT into Student(Roll_no, Studentname, Course, Dob)
VALUES('Z9','Prince','History','2005-01-19');
SELECT * FROM Student;
CREATE TABLE Society (
Socid CHAR(6) PRIMARY KEY,
Socname VARCHAR(20),
Mentorname VARCHAR(15),
Totalseats INT
);
INSERT into Society(Socid, Socname, Mentorname, Totalseats)
VALUES('A1','Akrosh','rajan shah',45);
INSERT into Society(Socid, Socname, Mentorname, Totalseats)
VALUES('A2','sashakt','munna bhai',12);
INSERT into Society(Socid, Socname, Mentorname, Totalseats)
VALUES('A3','Ncc','super man',23);
INSERT into Society(Socid, Socname, Mentorname, Totalseats)
VALUES('B1','Aaroh','avenger',30);
INSERT into Society(Socid, Socname, Mentorname, Totalseats)
VALUES('B2','Dancing','manali',35);
INSERT into Society(Socid, Socname, Mentorname, Totalseats)
VALUES('B3','Aarambh','chatti',55);
INSERT into Society(Socid, Socname, Mentorname, Totalseats)
VALUES('C1','Econot','manju',15);
INSERT into Society(Socid, Socname, Mentorname, Totalseats)
VALUES('C2','Debating','manjulika',05);
INSERT into Society(Socid, Socname, Mentorname, Totalseats)
VALUES('C3','Nazaakat','anbhati',52);
INSERT into Society(Socid, Socname, Mentorname, Totalseats)
VALUES('D1','Voices','charan',32);
SELECT * FROM Society;
CREATE TABLE Enrollment (
Roll_no CHAR(6),
Sid CHAR(6),
Date_of_enrollment DATE,
PRIMARY KEY (Roll_no, Sid),
FOREIGN KEY (Roll_no) REFERENCES Student(Roll_no),
FOREIGN KEY (sid) REFERENCES Society(socid)
);
INSERT into Enrollment( Roll_no, Sid, Date_of_enrollment)
value('1', 'B1', '2024-01-01');
INSERT into Enrollment( Roll_no, Sid, Date_of_enrollment)
VALUES('2', 'B1', '2023-03-01');
INSERT into Enrollment( Roll_no, Sid, Date_of_enrollment)
VALUES('3', 'B1', '2022-10-01');
INSERT into Enrollment( Roll_no, Sid, Date_of_enrollment)
VALUES('4', 'B1', '2024-04-01');
INSERT into Enrollment( Roll_no, Sid, Date_of_enrollment)
VALUES('5', 'B1', '2023-12-31');
INSERT into Enrollment( Roll_no, Sid, Date_of_enrollment)
VALUES('6', 'B1', '2023-01-31');
INSERT into Enrollment( Roll_no, Sid, Date_of_enrollment)
VALUES('7', 'B2', '2022-11-03');
INSERT into Enrollment( Roll_no, Sid, Date_of_enrollment)
VALUES('7', 'C2', '2022-11-03');
INSERT into Enrollment( Roll_no, Sid, Date_of_enrollment)
VALUES('X9', 'A2', '2021-05-13');
INSERT into Enrollment( Roll_no, Sid, Date_of_enrollment)
VALUES('Z9', 'A2', '2022-10-01');
SELECT * FROM Enrollment;
QUESTIONS
QUESTION 1 : Retrieve names of students enrolled in any society
SELECT DISTINCT S.Studentname
FROM Student S
JOIN Enrollment E ON S.Roll_no = E.Roll_no;
QUESTION 2: Retrieve all society names.
SELECT Socname FROM Society;
QUESTION 3 : Retrieve students' names starting with letter 'A'.
SELECT Studentname FROM Student
WHERE Studentname like 'A%';
QUESTION 4: Retrieve students' details studying in courses 'computer science' or 'chemistry'.
SELECT * FROM Student
WHERE COURSE ='Computer science' OR Course = 'Chemistry';
QUESTION 5 : Retrieve students' names whose roll no either starts with 'X' or 'Z' and ends
with '9'.
SELECT Studentname FROM Student
WHERE (Roll_no like 'X%9' OR Roll_no like 'Z%9');
QUESTION 6: Find society details with more than N TotalSeats where N is entered by user.
SELECT * FROM Society
WHERE Totalseats > 50;
QUESTION 7 : Update society table for mentor name of a specific society.
UPDATE Society
SET Mentorname = 'Akshay'
WHERE SOCID = 'B1';
SELECT * FROM SOCIETY;
QUESTION 8 : Find society names in which more than five students have enrolled.
SELECT S.Socname
FROM Society S
JOIN Enrollment E ON S.Socid = E.Sid
GROUP BY S.Socid, S.Socname
HAVING COUNT(E.Roll_no) > 5;
QUESTION 9 : Find the name of youngest student enrolled in society NSS.
SELECT S.Studentname
FROM Student S
JOIN Enrollment E ON S.Roll_no = E.Roll_no
WHERE E.Sid = 'A2'
ORDER BY S.Dob DESC
LIMIT 1;
QUESTION 10 : *Find the most two popular society names (one the basis of enrolled
students).
SELECT S.Socname
FROM Society S
JOIN Enrollment E ON S.Socid = E.Sid
GROUP BY S.Socid, S.Socname
ORDER BY COUNT(E.Roll_no) DESC
LIMIT 1;
QUESTION 11 : Find the two least popular society names (one the basis of enrolled students).
SELECT S.Socname
FROM Society S
LEFT JOIN Enrollment E ON S.Socid = E.Sid
GROUP BY S.Socid, S.Socname
ORDER BY COUNT(E.Roll_no) ASC
LIMIT 2;
QUESTION 12 : Find the student names who are not enrolled in any society.
SELECT S.Studentname
FROM Student S
LEFT JOIN Enrollment E ON S.Roll_no = E.Roll_no
WHERE E.Sid IS NULL;
QUESTION 13 : Find the student names enrolled in at least two societies.
SELECT S.Studentname
FROM Student S
JOIN Enrollment E ON S.Roll_no = E.Roll_no
GROUP BY S.Roll_no, S.Studentname
HAVING COUNT(E.Sid) >= 2;
QUESTION 14 Find society names in which maximum students are enrolled
SELECT S.Socname
FROM Society S
JOIN Enrollment E on S.Socid= E.Sid
GROUP BY S.Socid ORDER BY
COUNT(*) DESC LIMIT 1;
QUESTION 15 : */Find names of all students who have enrolled in any society and society names
in which at least one student has enrolled.
SELECT S.Socname,COUNT(Roll_no)
FROM Society S
JOIN Enrollment E ON S.Socid = E.Sid
GROUP BY S.Socname;
QUESTION 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
JOIN Enrollment E ON S.Roll_no = E.Roll_no
JOIN Society Soc ON E.Sid = Soc.Socid
WHERE Soc.Socname IN ('Debating', 'Dancing', 'Sashakt');
QUESTION 17 : Find the society names such that its mentor has a name with “Gupta” in it.
SELECT Socname FROM Society
where Mentorname like '%shah';
QUESTION 18 : Find the society names in which the number of enrolled students is only 10%
of its capacity.
SELECT Socname
FROM Society S
WHERE (SELECT COUNT(*) FROM Enrollment E WHERE E.sid = S.Socid) <= 0.1 * (SELECT COUNT(*)
FROM Enrollment);
QUESTION 19 : Display the vacant seats for each society.
SELECT Soc.Socname, Soc.Totalseats - COUNT(E.Roll_no) AS Vacant_seats
FROM Society Soc
LEFT JOIN Enrollment E ON Soc.Socid = E.Sid
GROUP BY Soc.Socname, Soc.Totalseats;
QUESTION 20 : Increment total seats of each society by 10%.
UPDATE Society
SET Totalseats = Totalseats + 0.1* Totalseats;
SELECT * FROM Society;
