DBMS Practice Questions
Scenario-Based Questions
1. 1. A university wants to list the names of students enrolled in the course 'Database
Systems' along with their grades.
Answer:
SELECT s.Name, e.Grade
FROM Students s
JOIN Enrollments e ON s.StudentID = e.StudentID
JOIN Courses c ON e.CourseID = c.CourseID
WHERE c.CourseName = 'Database Systems';
2. 2. The administration wants to find students who are enrolled in more than one course.
Answer:
SELECT StudentID
FROM Enrollments
GROUP BY StudentID
HAVING COUNT(CourseID) > 1;
3. 3. Generate a report of students with an 'A' grade in any course, showing their name,
course name, and department.
Answer:
SELECT s.Name, c.CourseName, s.Department
FROM Students s
JOIN Enrollments e ON s.StudentID = e.StudentID
JOIN Courses c ON e.CourseID = c.CourseID
WHERE e.Grade = 'A';
4. 4. A student wants a complete list of their enrolled courses with credit hours and
grades. Write a query for StudentID = 1.
Answer:
SELECT c.CourseName, c.CreditHours, e.Grade
FROM Courses c
JOIN Enrollments e ON c.CourseID = e.CourseID
WHERE e.StudentID = 1;
5. 5. The university wants to find out which students in 'Computer Science' have scored
grade 'A'.
Answer:
SELECT s.Name
FROM Students s
JOIN Enrollments e ON s.StudentID = e.StudentID
WHERE s.Department = 'Computer Science' AND e.Grade = 'A';
Table-Based Questions
6. 1. Create a table named Students with columns: StudentID (Primary Key), Name, Age,
Department.
Answer:
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name VARCHAR(100),
Age INT,
Department VARCHAR(50)
);
7. 2. Insert a student record: StudentID = 1, Name = 'Ali', Age = 20, Department =
'Computer Science'.
Answer:
INSERT INTO Students VALUES (1, 'Ali', 20, 'Computer Science');
8. 3. Add a column Email to the Students table.
Answer:
ALTER TABLE Students ADD Email VARCHAR(100);
9. 4. Delete the course with CourseID = 103.
Answer:
DELETE FROM Courses WHERE CourseID = 103;
10. 5. Update the department of student with ID = 2 to 'Data Science'.
Answer:
UPDATE Students SET Department = 'Data Science' WHERE StudentID = 2;
20 Scenario-Based SQL Questions
(SELECT & JOIN)
11. 1. Scenario: List all students enrolled in the course 'Database Systems'.
Answer:
SELECT s.Name
FROM Students s
JOIN Enrollments e ON s.StudentID = e.StudentID
JOIN Courses c ON e.CourseID = c.CourseID
WHERE c.CourseName = 'Database Systems';
12. 2. Scenario: Show student names and course names they are enrolled in.
Answer:
SELECT s.Name, c.CourseName
FROM Students s
JOIN Enrollments e ON s.StudentID = e.StudentID
JOIN Courses c ON e.CourseID = c.CourseID;
13. 3. Scenario: List the names of students who received grade 'A'.
Answer:
SELECT s.Name
FROM Students s
JOIN Enrollments e ON s.StudentID = e.StudentID
WHERE e.Grade = 'A';
14. 4. Scenario: Get courses that student 'Sara' is enrolled in.
Answer:
SELECT c.CourseName
FROM Students s
JOIN Enrollments e ON s.StudentID = e.StudentID
JOIN Courses c ON e.CourseID = c.CourseID
WHERE s.Name = 'Sara';
15. 5. Scenario: Find students in the 'Computer Science' department enrolled in any course.
Answer:
SELECT DISTINCT s.Name
FROM Students s
JOIN Enrollments e ON s.StudentID = e.StudentID
WHERE s.Department = 'Computer Science';
16. 6. Scenario: List all courses that have at least one student enrolled.
Answer:
SELECT DISTINCT c.CourseName
FROM Courses c
JOIN Enrollments e ON c.CourseID = e.CourseID;
17. 7. Scenario: Get students and their grades for the course 'Data Structures'.
Answer:
SELECT s.Name, e.Grade
FROM Students s
JOIN Enrollments e ON s.StudentID = e.StudentID
JOIN Courses c ON c.CourseID = e.CourseID
WHERE c.CourseName = 'Data Structures';
18. 8. Scenario: List students with their departments and enrolled course names.
Answer:
SELECT s.Name, s.Department, c.CourseName
FROM Students s
JOIN Enrollments e ON s.StudentID = e.StudentID
JOIN Courses c ON c.CourseID = e.CourseID;
19. 9. Scenario: List students who are not enrolled in any course.
Answer:
SELECT Name
FROM Students
WHERE StudentID NOT IN (SELECT StudentID FROM Enrollments);
20. 10. Scenario: List courses not taken by any student.
Answer:
SELECT CourseName
FROM Courses
WHERE CourseID NOT IN (SELECT CourseID FROM Enrollments);
21. 11. Scenario: Find students with grades other than 'A'.
Answer:
SELECT s.Name, e.Grade
FROM Students s
JOIN Enrollments e ON s.StudentID = e.StudentID
WHERE e.Grade <> 'A';
22. 12. Scenario: Get course names with more than one student enrolled.
Answer:
SELECT c.CourseName
FROM Courses c
JOIN Enrollments e ON c.CourseID = e.CourseID
GROUP BY c.CourseName
HAVING COUNT(e.StudentID) > 1;
23. 13. Scenario: Show the total number of courses each student is enrolled in.
Answer:
SELECT s.Name, COUNT(e.CourseID) AS TotalCourses
FROM Students s
JOIN Enrollments e ON s.StudentID = e.StudentID
GROUP BY s.Name;
24. 14. Scenario: Get names and departments of students enrolled in 'Computer Networks'.
Answer:
SELECT s.Name, s.Department
FROM Students s
JOIN Enrollments e ON s.StudentID = e.StudentID
JOIN Courses c ON c.CourseID = e.CourseID
WHERE c.CourseName = 'Computer Networks';
25. 15. Scenario: List all student-course-grade details sorted by student name.
Answer:
SELECT s.Name, c.CourseName, e.Grade
FROM Students s
JOIN Enrollments e ON s.StudentID = e.StudentID
JOIN Courses c ON c.CourseID = e.CourseID
ORDER BY s.Name;
26. 16. Scenario: Find the average age of students enrolled in 'Database Systems'.
Answer:
SELECT AVG(s.Age)
FROM Students s
JOIN Enrollments e ON s.StudentID = e.StudentID
JOIN Courses c ON c.CourseID = e.CourseID
WHERE c.CourseName = 'Database Systems';
27. 17. Scenario: Show the grade distribution for the course 'Data Structures'.
Answer:
SELECT e.Grade, COUNT(*) AS Count
FROM Enrollments e
JOIN Courses c ON c.CourseID = e.CourseID
WHERE c.CourseName = 'Data Structures'
GROUP BY e.Grade;
28. 18. Scenario: Get student details and courses only if the grade is not null.
Answer:
SELECT s.Name, c.CourseName, e.Grade
FROM Students s
JOIN Enrollments e ON s.StudentID = e.StudentID
JOIN Courses c ON c.CourseID = e.CourseID
WHERE e.Grade IS NOT NULL;
29. 19. Scenario: List courses and number of students enrolled in each.
Answer:
SELECT c.CourseName, COUNT(e.StudentID) AS NumStudents
FROM Courses c
LEFT JOIN Enrollments e ON c.CourseID = e.CourseID
GROUP BY c.CourseName;
30. 20. Scenario: Find names of students who have taken both 'Database Systems' and 'Data
Structures'.
Answer:
SELECT s.Name
FROM Students s
JOIN Enrollments e1 ON s.StudentID = e1.StudentID
JOIN Courses c1 ON e1.CourseID = c1.CourseID
JOIN Enrollments e2 ON s.StudentID = e2.StudentID
JOIN Courses c2 ON e2.CourseID = c2.CourseID
WHERE c1.CourseName = 'Database Systems'
AND c2.CourseName = 'Data Structures';
