DBMS Practical – All Questions and Answers (Checked & Corrected)
1. Retrieve names of students enrolled in any society.
SELECT DISTINCT S.StudentName
FROM STUDENT S
JOIN ENROLLMENT E ON S.RollNo = E.RollNo;
2. Retrieve all society names.
SELECT SocName
FROM SOCIETY;
3. Retrieve students’ names starting with A.
SELECT StudentName
FROM STUDENT
WHERE StudentName LIKE 'A%';
4. Students studying in computer science or chemistry.
SELECT *
FROM STUDENT
WHERE LOWER(Course) IN ('computer science','chemistry');
5. Students whose RollNo starts with X or Z and ends with 4.
SELECT StudentName
FROM STUDENT
WHERE (RollNo LIKE 'X%4' OR RollNo LIKE 'Z%4');
6. Society details with more than N seats.
SELECT *
FROM SOCIETY
WHERE TotalSeats > N;
7. Update mentor name of a specific society.
UPDATE SOCIETY
SET MentorName = 'New Mentor'
WHERE SocID = 'S001';
8. Society names in which more than five students have enrolled.
SELECT S.SocName
FROM SOCIETY S
LEFT JOIN ENROLLMENT E ON S.SocID = E.SID
GROUP BY S.SocID, S.SocName
HAVING COUNT(E.RollNo) > 5;
9. Youngest student enrolled in society 'SSS'.
SELECT S.StudentName
FROM STUDENT S
WHERE EXISTS (
SELECT 1 FROM ENROLLMENT E WHERE E.RollNo = S.RollNo AND E.SID = 'SSS'
)
ORDER BY S.DOB DESC
LIMIT 1;
10. Most popular society (by 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.RollNo) DESC
LIMIT 1;
11. Two least popular societies (by 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.RollNo) ASC
LIMIT 2;
12. Students not enrolled in any society.
SELECT S.StudentName
FROM STUDENT S
WHERE NOT EXISTS (
SELECT 1 FROM ENROLLMENT E WHERE E.RollNo = S.RollNo
);
13. Students enrolled in at least two societies.
SELECT S.StudentName
FROM STUDENT S
JOIN ENROLLMENT E ON S.RollNo = E.RollNo
GROUP BY S.RollNo, S.StudentName
HAVING COUNT(DISTINCT E.SID) >= 2;
14. Societies with maximum enrollments (ties allowed).
SELECT S.SocName
FROM SOCIETY S
LEFT JOIN ENROLLMENT E ON S.SocID = E.SID
GROUP BY S.SocID, S.SocName
HAVING COUNT(E.RollNo) = (
SELECT MAX(cnt) FROM (
SELECT COUNT(*) AS cnt FROM ENROLLMENT GROUP BY SID
) AS sub
);
15. Students + society names for enrolled students.
SELECT ST.StudentName, SO.SocName
FROM STUDENT ST
JOIN ENROLLMENT EN ON ST.RollNo = EN.RollNo
JOIN SOCIETY SO ON EN.SID = SO.SocID;
16. Students enrolled in Debating, Dancing, or Sashakt.
SELECT DISTINCT S.StudentName
FROM STUDENT S
JOIN ENROLLMENT E ON S.RollNo = E.RollNo
JOIN SOCIETY SO ON E.SID = SO.SocID
WHERE SO.SocName IN ('Debating','Dancing','Sashakt');
17. Societies whose mentor or name contains 'Gupta'.
SELECT SocName
FROM SOCIETY
WHERE MentorName LIKE '%Gupta%' OR SocName LIKE '%Gupta%';
18. Societies where enrolled students = 10% of capacity.
SELECT S.SocName
FROM SOCIETY S
LEFT JOIN ENROLLMENT E ON S.SocID = E.SID
GROUP BY S.SocID, S.SocName
HAVING COUNT(E.RollNo) * 10 = S.TotalSeats;
19. Vacant seats for each society.
SELECT S.SocName,
S.TotalSeats - COUNT(E.RollNo) AS VacantSeats
FROM SOCIETY S
LEFT JOIN ENROLLMENT E ON S.SocID = E.SID
GROUP BY S.SocID, S.SocName;
20. Increment TotalSeats of each society by 10%.
UPDATE SOCIETY
SET TotalSeats = CEIL(TotalSeats * 1.10);
21. Add EnrollmentFees (Yes/No) column in ENROLLMENT.
ALTER TABLE ENROLLMENT
ADD COLUMN EnrollmentFees ENUM('Yes','No') DEFAULT 'No';
22. Update DateOfEnrollment examples.
UPDATE ENROLLMENT SET DateOfEnrollment = '2024-01-28' WHERE SID = 'S1';
UPDATE ENROLLMENT SET DateOfEnrollment = CURRENT_DATE WHERE SID = 'S2';
UPDATE ENROLLMENT SET DateOfEnrollment = '2024-01-15' WHERE SID = 'S3';
23. Create view for society names with total enrolled.
CREATE VIEW SocietyEnrollment AS
SELECT S.SocName, COUNT(E.RollNo) AS TotalEnrolled
FROM SOCIETY S
LEFT JOIN ENROLLMENT E ON S.SocID = E.SID
GROUP BY S.SocID, S.SocName;
24. Students enrolled in all societies (MySQL-compatible).
SELECT S.StudentName
FROM STUDENT S
WHERE NOT EXISTS (
SELECT 1 FROM SOCIETY C
WHERE NOT EXISTS (
SELECT 1 FROM ENROLLMENT E WHERE E.RollNo = S.RollNo AND E.SID =
C.SocID
)
);
25. Count societies with more than 5 students enrolled.
SELECT COUNT(*)
FROM (
SELECT SID FROM ENROLLMENT GROUP BY SID HAVING COUNT(*) > 5
) AS temp;
26. Add Mobile number to STUDENT with default value.
ALTER TABLE STUDENT
ADD COLUMN Mobile CHAR(10) DEFAULT '9999999999';
27. Total number of students whose age is > 20 years.
SELECT COUNT(*)
FROM STUDENT
WHERE TIMESTAMPDIFF(YEAR, DOB, CURDATE()) > 20;
28. Names of students born in 2001 and enrolled in at least one society.
SELECT DISTINCT S.StudentName
FROM STUDENT S
JOIN ENROLLMENT E ON S.RollNo = E.RollNo
WHERE YEAR(S.DOB) = 2001;
29. Count societies starting with 'S' and ending with 't' with >= 8 enrollments.
SELECT COUNT(*)
FROM (
SELECT S.SocID FROM SOCIETY S
JOIN ENROLLMENT E ON S.SocID = E.SID
WHERE S.SocName LIKE 'S%t'
GROUP BY S.SocID
HAVING COUNT(E.RollNo) >= 8
) AS result;
30. Display Society Name, Mentor Name, Total Capacity, Total Enrolled, Unfilled Seats.
SELECT S.SocName, S.MentorName, S.TotalSeats,
COUNT(E.RollNo) AS TotalEnrolled,
S.TotalSeats - COUNT(E.RollNo) AS UnfilledSeats
FROM SOCIETY S
LEFT JOIN ENROLLMENT E ON S.SocID = E.SID
GROUP BY S.SocID, S.SocName;
