Trust Coding : Programming: University SQL Subquery Solutions with Analysis

University Database SQL Subquery Exercises

Consider a university database with the following relations:

Students(StudentID, Name, Department, CGPA)
Courses(CourseID, CourseName, Department, Credits)
Faculty(FacultyID, FacultyName, Department, Designation)
Teaches(FacultyID, CourseID)

Using subqueries, write SQL queries to answer the following questions. For each query, provide a brief analysis or justification of the results.

Tasks

Faculty Teaching Maximum Credit Course:
- Find the faculty member(s) who teach the course with the maximum credit.
- Explain why this information is useful for academic planning.
High-CGPA CSE Students:
- Find the names of all CSE students whose CGPA is higher than the average CGPA of BBA students.
- Analyze what this indicates about departmental performance comparisons.
Courses Taught by CSE Faculty:
- List the courses taught by faculty members from the CSE department.
- Discuss how this information can be useful for course allocation and scheduling.
Department with Maximum Faculty:
- Identify the department that has the maximum number of faculty members.
- Explain how this helps in strategic academic decisions such as hiring or workload management.
Faculty Teaching All CSE Courses:
- Find the faculty member(s) who teach all CSE courses.
- Analyze what this implies about faculty specialization and departmental expertise.

Instructions

Use subqueries wherever applicable.
Include brief justification or analysis for each result.
Present queries in a clear and structured format for readability.
Highlight how each query can help in university management and academic decision-making.

University SQL Subquery Exercises with Analysis

University SQL Subquery Solutions

Consider the university database with the following relations:

Students(StudentID, Name, Department, CGPA)
Courses(CourseID, CourseName, Department, Credits)
Faculty(FacultyID, FacultyName, Department, Designation)
Teaches(FacultyID, CourseID)

1. Faculty Teaching Maximum Credit Course

SELECT F.FacultyName
FROM Faculty F
WHERE F.FacultyID IN (
    SELECT T.FacultyID
    FROM Teaches T
    WHERE T.CourseID = (
        SELECT CourseID
        FROM Courses
        WHERE Credits = (SELECT MAX(Credits) FROM Courses)
    )
);

Analysis: Identifying faculty teaching high-credit courses aids in workload distribution and academic planning.

2. High-CGPA CSE Students

SELECT Name
FROM Students
WHERE Department = 'CSE'
  AND CGPA > (SELECT AVG(CGPA)
                FROM Students
                WHERE Department = 'BBA');

Analysis: Shows CSE students performing above average compared to BBA, useful for departmental benchmarking.

3. Courses Taught by CSE Faculty

SELECT DISTINCT C.CourseName
FROM Courses C
WHERE C.CourseID IN (
    SELECT T.CourseID
    FROM Teaches T
    JOIN Faculty F ON T.FacultyID = F.FacultyID
    WHERE F.Department = 'CSE'
);

Analysis: Useful for scheduling and managing course allocations for CSE faculty members.

4. Department with Maximum Faculty

SELECT Department
FROM Faculty
WHERE (SELECT COUNT(*) FROM Faculty F2 WHERE F2.Department = Faculty.Department) =
      (SELECT MAX(FacCount) FROM (SELECT COUNT(*) AS FacCount FROM Faculty GROUP BY Department));

Analysis: Helps identify departments with the largest faculty size, useful for resource planning and hiring.

5. Faculty Teaching All CSE Courses

SELECT F.FacultyName
FROM Faculty F
WHERE NOT EXISTS (
    SELECT C.CourseID
    FROM Courses C
    WHERE C.Department = 'CSE'
      AND NOT EXISTS (
          SELECT T.CourseID
          FROM Teaches T
          WHERE T.FacultyID = F.FacultyID
            AND T.CourseID = C.CourseID
      )
);

Analysis: Faculty teaching all CSE courses demonstrate high expertise and departmental involvement, useful for mentorship and curriculum planning.

Trust Coding : Programming

University SQL Subquery Solutions with Analysis