Assignment Title: SQL Query Writing Practice

SQL Practice Exercises

Practice your SQL skills with these exercises based on a university database schema. The database tracks students, courses, and their enrollments with grades.

Schema Overview:

students
Columns:
- id (INT)
- name (VARCHAR)
- age (INT)
- gender (VARCHAR)
- department (VARCHAR)

courses
Columns:
- id (INT)
- title (VARCHAR)
- credits (INT)

enrollments
Columns:
- student_id (INT)
- course_id (INT)
- grade (CHAR(2))

Beginner Level Exercises

1. Display all student names and their departments

SELECT name, department
FROM students;

2. Find students older than 20 years

SELECT name, age
FROM students
WHERE age > 20;

3. List all students in the "Computer Science" department

SELECT name
FROM students
WHERE department = 'Computer Science';

4. Retrieve all course titles from the courses table

SELECT title
FROM courses;

5. Find all students whose name starts with "A"

SELECT name
FROM students
WHERE name LIKE 'A%';

Intermediate Level Exercises

6. Show all students along with the courses they are enrolled in

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

7. List students who scored grade 'A' in any course

SELECT DISTINCT s.name
FROM students s
JOIN enrollments e ON s.id = e.student_id
WHERE e.grade = 'A';

8. Count how many students are enrolled in each department

SELECT department, COUNT(*) as student_count
FROM students
GROUP BY department;

9. Retrieve students who have not enrolled in any course

SELECT s.name
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id
WHERE e.student_id IS NULL;

10. Display the total number of students enrolled in each course

SELECT c.title, COUNT(e.student_id) as enrolled_students
FROM courses c
LEFT JOIN enrollments e ON c.id = e.course_id
GROUP BY c.title;

Advanced Level Exercises

11. List all students and the total number of credits they are enrolled in

SELECT s.name, SUM(c.credits) as total_credits
FROM students s
JOIN enrollments e ON s.id = e.student_id
JOIN courses c ON e.course_id = c.id
GROUP BY s.name;

12. Show students who are taking more than 2 courses

SELECT s.name, COUNT(e.course_id) as course_count
FROM students s
JOIN enrollments e ON s.id = e.student_id
GROUP BY s.name
HAVING COUNT(e.course_id) > 2;

13. Display the average grade per course

SELECT c.title, AVG(e.grade) as average_grade
FROM courses c
JOIN enrollments e ON c.id = e.course_id
GROUP BY c.title;

14. Find departments with more than 5 students enrolled in at least one course

SELECT s.department
FROM students s
JOIN enrollments e ON s.id = e.student_id
GROUP BY s.department
HAVING COUNT(DISTINCT s.id) > 5;

15. Display students who are taking all available courses

SELECT s.name
FROM students s
WHERE NOT EXISTS (
    SELECT c.id FROM courses c
    WHERE NOT EXISTS (
        SELECT 1 FROM enrollments e
        WHERE e.student_id = s.id AND e.course_id = c.id
    )
);

Bonus Challenge

16. Display the top 3 students based on number of 'A' grades

SELECT s.name, COUNT(*) as a_grades
FROM students s
JOIN enrollments e ON s.id = e.student_id
WHERE e.grade = 'A'
GROUP BY s.name
ORDER BY a_grades DESC
LIMIT 3;

17. Show students enrolled in the same courses as student ID = 2

SELECT DISTINCT s.name
FROM students s
JOIN enrollments e ON s.id = e.student_id
WHERE e.course_id IN (
    SELECT course_id FROM enrollments WHERE student_id = 2
)
AND s.id != 2;

18. Correlated subquery to find students enrolled in most courses

SELECT s.name
FROM students s
WHERE (
    SELECT COUNT(*) 
    FROM enrollments e 
    WHERE e.student_id = s.id
) = (
    SELECT MAX(course_count)
    FROM (
        SELECT COUNT(*) as course_count
        FROM enrollments
        GROUP BY student_id
    ) counts
);

Conclusion

These exercises cover a range of SQL skills from basic queries to advanced joins and subqueries. Practice them to improve your SQL proficiency and database querying abilities.