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', '[Link]', '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 [Link]
FROM SOCIETIES S
JOIN ENROLLMENTS E ON [Link] = [Link]
GROUP BY [Link]
HAVING COUNT([Link]) > 5;
9. Find the name of the youngest student enrolled in society 'NSS'
SELECT [Link]
FROM STUDENTS ST
JOIN ENROLLMENTS E ON [Link] = [Link]
JOIN SOCIETIES S ON [Link] = [Link]
WHERE [Link] = 'NSS'
ORDER BY [Link] DESC
LIMIT 1;
10. Find the name of the most popular society (on the basis of enrolled students)
SELECT [Link]
FROM SOCIETIES
JOIN ENROLLMENTS ON [Link] = [Link]
GROUP BY [Link]
ORDER BY COUNT([Link]) DESC
LIMIT 1;
11. Find the name of two least popular societies (on the basis of enrolled students
SELECT [Link]
FROM SOCIETIES
JOIN ENROLLMENTS ON [Link] = [Link]
GROUP BY [Link]
ORDER BY COUNT([Link]) ASC
LIMIT 2;
12. Find the students names who are not enrolled in any society.
SELECT STUDENTNAME
FROM STUDENTS
LEFT JOIN ENROLLMENTS ON [Link] = [Link]
WHERE [Link] IS NULL;
13. Find the students names enrolled in at least two societies
SELECT STUDENTNAME
FROM (
SELECT [Link], COUNT(*) AS SOCIETY_COUNT
FROM STUDENTS S
JOIN ENROLLMENTS E ON [Link] = [Link]
GROUP BY [Link]
HAVING COUNT(*) >= 2
) AS subquery;
14. Find society names in which maximum students are enrolled
SELECT [Link]
FROM SOCIETIES
JOIN ENROLLMENTS ON [Link] = [Link]
GROUP BY [Link]
ORDER BY COUNT([Link]) 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 [Link], [Link]
FROM STUDENTS ST
JOIN ENROLLMENTS E ON [Link] = [Link]
JOIN SOCIETIES SOC ON [Link] = [Link];
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 [Link] = [Link]
JOIN SOCIETIES ON [Link] = [Link]
WHERE [Link] IN ( 'DEBATING', 'DANCING' , 'SASHAKT');
17. Find society names such that its mentor has a name with 'Gupta' in it.
SELECT [Link],MENTORNAME
FROM SOCIETIES
WHERE [Link] LIKE '%GUPTA%';
18. Find the society names in which the number of enrolled students is only 10% of its
capacity.
SELECT [Link]
FROM SOCIETIES
JOIN ENROLLMENTS ON [Link] = [Link]
GROUP BY [Link],[Link]
HAVING COUNT(DISTINCT [Link]) <= 0.1 * [Link];
19. Display the vacant seats for each society.
SELECT [Link], [Link] - COUNT([Link]) AS
VACANT_SEATS
FROM SOCIETIES
LEFT JOIN ENROLLMENTS ON [Link] = [Link]
GROUP BY [Link];
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';
[Link] 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 [Link], COUNT([Link]) AS TOTALSTUDENTS
FROM SOCIETIES
JOIN ENROLLMENTS ON [Link] = [Link]
GROUP BY [Link];
24. Find student names enrolled in all the societies.
SELECT [Link]
FROM STUDENTS
JOIN ENROLLMENTS ON [Link] = [Link]
GROUP BY [Link]
HAVING COUNT(DISTINCT [Link]) = (SELECT COUNT(DISTINCT SOCID) FROM SOCIETIES);
25. Count the number of societies with more than 4 students enrolled in it
SELECT COUNT(DISTINCT [Link]) AS SOCIETY_COUNT
FROM ENROLLMENTS E
GROUP BY [Link]
HAVING COUNT([Link]) > 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 [Link]
FROM STUDENTS
JOIN ENROLLMENTS ON [Link] = [Link]
WHERE YEAR([Link]) = 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 [Link]) AS SOCIETY_COUNT, SOCNAME
FROM SOCIETIES SOC
JOIN ENROLLMENTS E ON [Link] = [Link]
WHERE [Link] LIKE 'S%T'
GROUP BY [Link]
HAVING COUNT([Link]) >= 5;
30. Display the following information:
Society name Mentor name Total Capacity Total Enrolled Unfilled Seats
SELECT
[Link] AS "SOCIETY NAME",
[Link] AS "MENTOR NAME",
[Link] AS "TOTAL CAPACITY",
COUNT([Link]) AS "TOTAL ENROLLED",
[Link] - COUNT([Link]) AS "UNFILLED SEATS"
FROM SOCIETIES SOC
LEFT JOIN ENROLLMENTS E ON [Link] = [Link]
GROUP BY [Link], [Link], [Link];
