Create and use the following student-society database schema for a college to
answer the given (sample) queries using the standalone SQL editor.
STUDENT Roll No StudentName Course DOB
Char(6) Varchar(20) Varchar(10) Date
SOCIETY SocID SocName MentorName TotalSeats
Char(6) Varchar(20) Varchar(15 Unsigned int
ENROLLMENT Roll No SID DateOfEnrollment
Char(6) Char(6) Date
Here Rollno (ENROLLMENT) and SID (ENROLLMENT) are foreign keys
CREATE TABLE STUDENTS( ROLLNO CHAR(6) PRIMARY KEY, STUDENTNAME VARCHAR(25), COURSE
VARCHAR(15), DOB DATE );
INSERT INTO STUDENTS VALUES
('X111', 'ABHAY', 'MATHS', '2004-01-01'),
('Y106', 'ZUBIN', 'ENGLISH', '2003-03-03'),
('Z103', 'KARAN', 'CHEMISTRY', '2002-02-19'),
('W104', 'TARUN', 'GEOGRAPHY', '1998-01-01'),
('X105', 'RABINA', 'ENGLISH', '1998-01-12'),
('Y102', 'AAKASH', 'COMPUTERSCIENCE', '1998-01-25'),
('W109', 'RAJAT', 'CHEMISTRY', '2005-01-28'),
('W110', 'NEHA GUPTA', 'GEOGRAPHY', '2003-09-11'),
('X101', 'ANAND KUMAR', 'COMMERCE', '1995-01-12'),
('W112', 'ARIZ', 'MATHS', '2003-12-05'),
('W113', 'KAJAL', 'CHEMISTRY', '2004-02-15'),
('X114', 'ASHIS', 'ARTS', '1997-12-02'),
('Y115', 'INDRA', 'MUSIC', '2005-10-07'),
('Y116', 'BITTU GUPTA', 'PHYSICAL.EDU', '2001-03-22'),
('X117', 'SHASWAT', 'COMMERCE', '2002-04-27'),
('X119', 'SURA', 'HISTORY', '2001-09-11'),
('Z199', 'MONU GUPTA', 'PHYSICS', '2003-03-17'),
('W189', 'RAGHU KUMAR', 'DANCE', '1997-11-05'),
('Y179', 'MEERA', 'NURSING', '2002-11-30'),
('2120', 'RANJEET', 'ECONOMICS', '2005-02-04'),
('X301', 'DEEPAK KUMAR', 'ECONOMICS', '2007-02-04'),
('W201', 'RAJU', 'COMMERCE', '2004-03-12'),
('Y123', 'ABHIJEET', 'BIOLOGY', '2000-08-18'),
('Z127', 'ABHINAV', 'PHYSIOTHERAPY', '2001-02-11'),
('Z129', 'PRIYA', 'POLITICS', '2003-06-15');
SELECT * FROM students;
CREATE TABLE SOCIETIES ( SOCID CHAR(6) PRIMARY KEY, SOCNAME VARCHAR(20), MENTORNAME
VARCHAR(15), TOTALSEATS INT );
INSERT INTO SOCIETIES VALUES
(111, 'RAGA', 'AYUSH', 20),
(112, 'DANCE', 'KABITA GUPTA', 39),
(113, 'NSS', 'ASHISH', 33),
(114, 'SASHAKT', 'KHUSHI GUPTA', 28),
(115, 'DEBATING', 'ALOK', 26),
(116, 'DRAMA', 'CHETAN', 27),
(117, 'POLARIED', 'PRIYA', 30);
SELECT * FROM SOCIETIES;
CREATE TABLE ENROLLMENTS (
ROLLNO CHAR(6),
SID CHAR(6),
DATEOFENROLLMENT DATE,
FOREIGN KEY (ROLLNO) REFERENCES STUDENTS(ROLLNO),
FOREIGN KEY (SID) REFERENCES SOCIETIES(SOCID)
);
INSERT INTO ENROLLMENTS (ROLLNO, SID, DATEOFENROLLMENT)
VALUES
('W113', 111, '2005-03-28'),
('W113', 114, '2005-03-28'),
('Y102', 112, '2021-10-12'),
('Z103', 113, '2002-01-19'),
('W104', 114, '1998-03-01'),
('X105', 115, '1998-06-12'),
('Y106', 116, '2006-03-23'),
('W109', 117, '2004-03-01'),
('X111', 115, '2024-07-19'),
('W112', 114, '2003-01-03'),
('X101', 117, '2022-06-18'),
('X114', 112, '2022-01-10'),
('Y115', 113, '2024-02-11'),
('Y116', 114, '2021-11-17'),
('X117', 117, '2023-10-15'),
('X119', 115, '2024-03-20'),
('Z199', 116, '2023-08-18'),
('W189', 111, '2022-01-24'),
('Y179', 112, '2021-02-14'),
('Z129', 114, '2023-05-28');
SELECT * FROM ENROLLMENTS;
QUERIES:
1. Retrieve names of students enrolled in any society.
SELECT STUDENTNAME
FROM STUDENTS
WHERE ROLLNO IN (SELECT ROLLNO FROM ENROLLMENTS);
2. Retrieve all society names.
SELECT SOCNAME FROM SOCIETIES ;
3. Retrieve students' names starting with the letter 'A'.
SELECT STUDENTNAME FROM STUDENTS WHERE STUDENTNAME LIKE 'A%';
4. Retrieve students' details studying in courses 'computer science' or 'chemistry'.
SELECT * FROM STUDENTS WHERE COURSE IN ('COMPUTERSCIENCE', 'CHEMISTRY')
5. Retrieve students' names whose roll no either starts with 'X' or 'Z' and ends with '9'
SELECT STUDENTNAME
FROM STUDENTS
WHERE ROLLNO LIKE 'X%' OR ROLLNO LIKE 'Z%'
AND ROLLNO LIKE '%9';
6. Find society details with more than N TotalSeats where N is to be input by the user
SELECT *
FROM SOCIETIES
WHERE TOTALSEATS > 20;
7. Update society table for the Mentor name of a specific society
UPDATE SOCIETIES
SET MENTORNAME = 'PAYAL GUPTA'
WHERE SOCNAME = 'DRAMA';
8. Find society names in which more than five students have enrolled
SELECT S.SOCNAME
FROM SOCIETIES S
JOIN ENROLLMENTS E ON S.SOCID = E.SID
GROUP BY S.SOCNAME
HAVING COUNT(E.SID) > 5;
9. Find the name of the youngest student enrolled in society 'NSS'
SELECT ST.STUDENTNAME
FROM STUDENTS ST
JOIN ENROLLMENTS E ON ST.ROLLNO = E.ROLLNO
JOIN SOCIETIES S ON E.SID = S.SOCID
WHERE S.SOCNAME = 'NSS'
ORDER BY ST.DOB DESC
LIMIT 1;
10. Find the name of the most popular society (on the basis of enrolled students)
SELECT SOCIETIES.SOCNAME
FROM SOCIETIES
JOIN ENROLLMENTS ON SOCIETIES.SOCID = ENROLLMENTS.SID
GROUP BY SOCIETIES.SOCID
ORDER BY COUNT(ENROLLMENTS.ROLLNO) DESC
LIMIT 1;
11. Find the name of two least popular societies (on the basis of enrolled students
SELECT SOCIETIES.SOCNAME
FROM SOCIETIES
JOIN ENROLLMENTS ON SOCIETIES.SOCID = ENROLLMENTS.SID
GROUP BY SOCIETIES.SOCID
ORDER BY COUNT(ENROLLMENTS.ROLLNO) ASC
LIMIT 2;
12. Find the students names who are not enrolled in any society.
SELECT STUDENTNAME
FROM STUDENTS
LEFT JOIN ENROLLMENTS ON STUDENTS.ROLLNO = ENROLLMENTS.ROLLNO
WHERE ENROLLMENTS.ROLLNO IS NULL;
13. Find the students names enrolled in at least two societies
SELECT STUDENTNAME
FROM (
SELECT S.STUDENTNAME, COUNT(*) AS SOCIETY_COUNT
FROM STUDENTS S
JOIN ENROLLMENTS E ON S.ROLLNO = E.ROLLNO
GROUP BY S.STUDENTNAME
HAVING COUNT(*) >= 2
) AS subquery;
14. Find society names in which maximum students are enrolled
SELECT SOCIETIES.SOCNAME
FROM SOCIETIES
JOIN ENROLLMENTS ON SOCIETIES.SOCID = ENROLLMENTS.SID
GROUP BY SOCIETIES.SOCID
ORDER BY COUNT(ENROLLMENTS.ROLLNO) DESC
LIMIT 1;
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 ST.STUDENTNAME, SOC.SOCNAME
FROM STUDENTS ST
JOIN ENROLLMENTS E ON ST.ROLLNO = E.ROLLNO
JOIN SOCIETIES SOC ON E.SID = SOC.SOCID;
16. Find names of students who are enrolled in any of the three societies 'Debating',
'Dancing' and 'Sashakt'.
SELECT STUDENTNAME
FROM STUDENTS
JOIN ENROLLMENTS ON STUDENTS.ROLLNO = ENROLLMENTS.ROLLNO
JOIN SOCIETIES ON ENROLLMENTS.SID = SOCIETIES.SOCID
WHERE SOCIETIES.SOCNAME IN ( 'DEBATING', 'DANCING' , 'SASHAKT');
17. Find society names such that its mentor has a name with 'Gupta' in it.
SELECT SOCIETIES.SOCNAME,MENTORNAME
FROM SOCIETIES
WHERE SOCIETIES.MENTORNAME LIKE '%GUPTA%';
18. Find the society names in which the number of enrolled students is only 10% of its
capacity.
SELECT SOCIETIES.SOCNAME
FROM SOCIETIES
JOIN ENROLLMENTS ON SOCIETIES.SOCID = ENROLLMENTS.SID
GROUP BY SOCIETIES.SOCID,SOCIETIES.TOTALSEATS
HAVING COUNT(DISTINCT ENROLLMENTS.ROLLNO) <= 0.1 * SOCIETIES.TOTALSEATS;
19. Display the vacant seats for each society.
SELECT SOCIETIES.SOCNAME, SOCIETIES.TOTALSEATS - COUNT(ENROLLMENTS.ROLLNO) AS
VACANT_SEATS
FROM SOCIETIES
LEFT JOIN ENROLLMENTS ON SOCIETIES.SOCID = ENROLLMENTS.SID
GROUP BY SOCIETIES.SOCID;
20. Increment Total Seats of each society by 10%
UPDATE SOCIETIES
SET TOTALSEATS = TOTALSEATS * 1.1;
21. Add the enrollment fees paid ('yes'/'No') field in the enrollment table.
ALTER TABLE ENROLLMENTS
ADD COLUMN FEESPAID VARCHAR(5) NOT NULL DEFAULT 'NO';
22.Update date of enrollment of society id '111' to '2018-01-15', '112' to the current date and '113
to 2018-01-02.
UPDATE ENROLLMENTS
SET DATEOFENROLLMENT = '2018-01-15'
WHERE SID = 111;
UPDATE ENROLLMENTS
SET DATEOFENROLLMENT = '2024-11-02'
WHERE SID = 112;
UPDATE ENROLLMENTS
SET DATEOFENROLLMENT = '2018-01-02'
WHERE SID = 113;
SELECT * FROM ENROLLMENTS;
23. Create a view to keep track of society names with the total number of students enrolled in it.
CREATE VIEW SOCIETYENROLLMENTCOUNT AS
SELECT SOCIETIES.SOCNAME, COUNT(ENROLLMENTS.ROLLNO) AS TOTALSTUDENTS
FROM SOCIETIES
JOIN ENROLLMENTS ON SOCIETIES.SOCID = ENROLLMENTS.SID
GROUP BY SOCIETIES.SOCNAME;
24. Find student names enrolled in all the societies.
SELECT STUDENTS.STUDENTNAME
FROM STUDENTS
JOIN ENROLLMENTS ON STUDENTS.ROLLNO = ENROLLMENTS.ROLLNO
GROUP BY STUDENTS.ROLLNO
HAVING COUNT(DISTINCT ENROLLMENTS.SID) = (SELECT COUNT(DISTINCT SOCID) FROM SOCIETIES);
25. Count the number of societies with more than 4 students enrolled in it
SELECT COUNT(DISTINCT E.SID) AS SOCIETY_COUNT
FROM ENROLLMENTS E
GROUP BY E.SID
HAVING COUNT(E.ROLLNO) > 4;
26. Add column Mobile number in student table with default value '9999999999’
ALTER TABLE STUDENTS
ADD COLUMN MOBILENUMBER CHAR(10) DEFAULT '9999999999';
SELECT * FROM STUDENTS;
27. Find the total number of students whose age is > 20 years.
SELECT COUNT(*) AS TOTAL_STUDENTS
FROM STUDENTS
WHERE TIMESTAMPDIFF(YEAR, DOB, CURDATE()) > 20;
28. Find names of students who were born in 2001 and are enrolled in at least one society.
SELECT DISTINCT STUDENTS.STUDENTNAME
FROM STUDENTS
JOIN ENROLLMENTS ON STUDENTS.ROLLNO = ENROLLMENTS.ROLLNO
WHERE YEAR(STUDENTS.DOB) = 2001;
29. Count all societies whose name starts with 'S' and ends with 't' and at least 5 students are
enrolled in the society.
SELECT COUNT(DISTINCT SOC.SOCNAME) AS SOCIETY_COUNT, SOCNAME
FROM SOCIETIES SOC
JOIN ENROLLMENTS E ON SOC.SOCID = E.SID
WHERE SOC.SOCNAME LIKE 'S%T'
GROUP BY SOC.SOCNAME
HAVING COUNT(E.ROLLNO) >= 5;
30. Display the following information:
Society name Mentor name Total Capacity Total Enrolled Unfilled Seats
SELECT
SOC.SOCNAME AS "SOCIETY NAME",
SOC.MENTORNAME AS "MENTOR NAME",
SOC.TOTALSEATS AS "TOTAL CAPACITY",
COUNT(E.ROLLNO) AS "TOTAL ENROLLED",
SOC.TOTALSEATS - COUNT(E.ROLLNO) AS "UNFILLED SEATS"
FROM SOCIETIES SOC
LEFT JOIN ENROLLMENTS E ON SOC.SOCID = E.SID
GROUP BY SOC.SOCNAME, SOC.MENTORNAME, SOC.TOTALSEATS;
