DBMS Queries Exercises

Learning DBMS (Database Management System) queries mostly means getting fluent with SQL. The 80/20 rule applies: a handful of query patterns—SELECT, filtering, joins, grouping, subqueries—solve most real problems. Below is a focused set of exercises with a small sample schema and concise solutions to check your work.

Sample Schema (use these tables)

students(student_id, name, dept_id, cgpa, join_year)
departments(dept_id, dept_name)
courses(course_id, title, credits, dept_id)
enrollments(enroll_id, student_id, course_id, grade, semester)

Assume: grade like ‘A’,’B’,’C’,’D’,’F’. semester like ‘2024-Fall’.

---

Warm-Up (Basics)

1. List all students.
2. Show student names and CGPA for those with CGPA ≥ 8.0.
3. Display unique semesters from enrollments (no duplicates).
4. Sort courses by credits (high to low).
5. Count total students.
6. Find students who joined in 2023.
7. Rename columns in output (e.g., student name as Student).

---

Joins (Most-used patterns)

8. List student names with their department names.
9. Show course titles with department names.
10. All students and their enrolled courses (include students with no enrollments).
11. For each enrollment, show student name, course title, and grade.
12. Find students enrolled in courses outside their own department (cross-dept learning).

---

Aggregation & Grouping

13. How many students per department?
14. Average CGPA per department (desc by avg).
15. Total enrollments per semester.
16. For each course, count distinct students enrolled.
17. Top 3 courses by enrollment count.

---

Filtering With Aggregates (HAVING)

18. Departments with more than 50 students.
19. Courses with average grade ‘A’ or ‘B’ (assume A=4,B=3,C=2,D=1,F=0).
20. Students who took at least 5 courses.

---

Subqueries

21. Students with CGPA above overall average.
22. Courses with enrollment above the overall course average enrollment.
23. Students who never received an ‘F’.
24. Students who enrolled in every course of their department (set division pattern).

---

Window Functions (If supported)

25. Rank students by CGPA within each department.
26. Find each student’s latest semester grade per course (use row_number).

---

Data Quality / Admin

27. Find duplicate student names (same name across different IDs).
28. Find enrollments that reference non-existent students or courses (integrity check via anti-join).

---

Reference Solutions (concise)

Examples:

1

SELECT * FROM students;

2

SELECT name, cgpa FROM students WHERE cgpa >= 8.0;

3

SELECT DISTINCT semester FROM enrollments;

4

SELECT * FROM courses ORDER BY credits DESC;

5

SELECT COUNT(*) AS total_students FROM students;

6

SELECT * FROM students WHERE join_year = 2023;

7

SELECT name AS Student, cgpa AS CGPA FROM students;

8

SELECT s.name, d.dept_name
FROM students s
JOIN departments d ON s.dept_id = d.dept_id;

9

SELECT c.title, d.dept_name
FROM courses c
JOIN departments d ON c.dept_id = d.dept_id;

10

SELECT s.name, c.title
FROM students s
LEFT JOIN enrollments e ON s.student_id = e.student_id
LEFT JOIN courses c ON e.course_id = c.course_id;

11

SELECT s.name, c.title, e.grade
FROM enrollments e
JOIN students s  ON e.student_id = s.student_id
JOIN courses  c  ON e.course_id = c.course_id;

12

SELECT DISTINCT s.name
FROM enrollments e
JOIN students s ON e.student_id = s.student_id
JOIN courses c  ON e.course_id = c.course_id
WHERE s.dept_id <> c.dept_id;

13

SELECT d.dept_name, COUNT(*) AS student_count
FROM students s
JOIN departments d ON s.dept_id = d.dept_id
GROUP BY d.dept_name;

14

SELECT d.dept_name, AVG(s.cgpa) AS avg_cgpa
FROM students s
JOIN departments d ON s.dept_id = d.dept_id
GROUP BY d.dept_name
ORDER BY avg_cgpa DESC;

15

SELECT semester, COUNT(*) AS total_enrollments
FROM enrollments
GROUP BY semester;

16

SELECT c.title, COUNT(DISTINCT e.student_id) AS student_count
FROM courses c
LEFT JOIN enrollments e ON c.course_id = e.course_id
GROUP BY c.title;

17

SELECT c.title, COUNT(*) AS enrollments
FROM enrollments e
JOIN courses c ON e.course_id = c.course_id
GROUP BY c.title
ORDER BY enrollments DESC
FETCH FIRST 3 ROWS ONLY; -- or LIMIT 3

18

SELECT d.dept_name, COUNT(*) AS student_count
FROM students s
JOIN departments d ON s.dept_id = d.dept_id
GROUP BY d.dept_name
HAVING COUNT(*) > 50;

19 (map grades to points)

SELECT c.title, AVG(
  CASE grade WHEN 'A' THEN 4 WHEN 'B' THEN 3 WHEN 'C' THEN 2 WHEN 'D' THEN 1 ELSE 0 END
) AS avg_points
FROM enrollments e
JOIN courses c ON e.course_id = c.course_id
GROUP BY c.title
HAVING AVG(
  CASE grade WHEN 'A' THEN 4 WHEN 'B' THEN 3 WHEN 'C' THEN 2 WHEN 'D' THEN 1 ELSE 0 END
) >= 3.0;

20

SELECT s.name, COUNT(*) AS courses_taken
FROM enrollments e
JOIN students s ON e.student_id = s.student_id
GROUP BY s.name
HAVING COUNT(*) >= 5;

21

SELECT * FROM students
WHERE cgpa > (SELECT AVG(cgpa) FROM students);

22

SELECT c.title
FROM courses c
JOIN enrollments e ON c.course_id = e.course_id
GROUP BY c.course_id, c.title
HAVING COUNT(*) >
 (SELECT AVG(cnt) FROM (
    SELECT COUNT(*) AS cnt
    FROM enrollments
    GROUP BY course_id
 ) t);

23

SELECT s.name
FROM students s
WHERE NOT EXISTS (
  SELECT 1 FROM enrollments e
  WHERE e.student_id = s.student_id AND e.grade = 'F'
);

24

SELECT s.name
FROM students s
JOIN departments d ON s.dept_id = d.dept_id
WHERE NOT EXISTS (
  SELECT 1 FROM courses c
  WHERE c.dept_id = d.dept_id
  AND NOT EXISTS (
    SELECT 1 FROM enrollments e
    WHERE e.student_id = s.student_id AND e.course_id = c.course_id
  )
);

25 (window rank)

SELECT name, dept_id, cgpa,
RANK() OVER (PARTITION BY dept_id ORDER BY cgpa DESC) AS dept_rank
FROM students;

26 (latest per course)

SELECT *
FROM (
  SELECT e.*, ROW_NUMBER() OVER (
    PARTITION BY student_id, course_id ORDER BY semester DESC
  ) AS rn
  FROM enrollments e
) t
WHERE rn = 1;

27

SELECT name, COUNT(*) AS dup_count
FROM students
GROUP BY name
HAVING COUNT(*) > 1;

28 (anti-joins)

SELECT e.*
FROM enrollments e
LEFT JOIN students s ON e.student_id = s.student_id
LEFT JOIN courses  c ON e.course_id  = c.course_id
WHERE s.student_id IS NULL OR c.course_id IS NULL;