SQL Queries for Student Enrollment Analysis
Uploaded by
20BCS073AadilMohammadHusainSQL Queries for Student Enrollment Analysis
Uploaded by
20BCS073AadilMohammadHusainCommon questions
Powered by AITo identify juniors enrolled in a class taught by I. Teach, join the 'Enrolled', 'Class', 'Faculty', and 'Student' tables. Specifically, join on 'snum' to 'snum' between 'Enrolled' and 'Student', 'cname' to 'cname' between 'Enrolled' and 'Class', and 'fid' to 'fid' between 'Class' and 'Faculty'. Ensure 'level' is 'JR' and 'fname' is 'I. Teach'. This results in: SELECT DISTINCT sname FROM Student JOIN Enrolled ON Student.snum = Enrolled.snum JOIN Class ON Enrolled.cname = Class.cname JOIN Faculty ON Class.fid = Faculty.fid WHERE Student.level = 'JR' AND Faculty.fname = 'I. Teach';
To find the oldest student's age who is either a History major or enrolled in a class taught by I. Teach, create a subquery to select ages where 'major' is 'History'. Another subquery should select ages where 'snum' is part of a class taught by I. Teach by joining 'Enrolled', 'Class', and 'Faculty'. Use the 'UNION' of both subqueries to eliminate duplicates and finally select the 'MAX' of these ages. The query is: SELECT MAX(age) FROM (SELECT age FROM Student WHERE major = 'History' UNION SELECT Student.age FROM Student JOIN Enrolled ON Student.snum = Enrolled.snum JOIN Class ON Enrolled.cname = Class.cname JOIN Faculty ON Class.fid = Faculty.fid WHERE Faculty.fname = 'I. Teach') AS history_or_teach;
First, determine the maximum class count per student using the 'COUNT' function on 'snum' in 'Enrolled', grouping by 'snum'. Next, filter students matching this maximum count. Use a subquery for determining max: WITH ClassCount AS (SELECT snum, COUNT(cname) as num_classes FROM Enrolled GROUP BY snum) SELECT sname FROM Student WHERE snum IN (SELECT snum FROM ClassCount WHERE num_classes = (SELECT MAX(num_classes) FROM ClassCount)); This derives students with peak class enrollments.
To compute average ages for each level, group by 'level' in the 'Student' table and apply the 'AVG' function on 'age'. The SQL query is: SELECT level, AVG(age) as average_age FROM Student GROUP BY level; This provides a breakdown of average ages per academic level.
To find students enrolled in two classes meeting at the same time, self-join the 'Class' table on 'meets_at' ensuring different classes. Join with 'Enrolled' twice to have two class enrollments per student. Use the 'WHERE' clause to match meeting times and group by student numbers filtering 'HAVING' count greater than or equal to two. The query is: SELECT DISTINCT sname FROM Student WHERE snum IN (SELECT e1.snum FROM Enrolled e1 JOIN Enrolled e2 ON e1.snum = e2.snum JOIN Class c1 ON e1.cname = c1.cname JOIN Class c2 ON e2.cname = c2.cname WHERE e1.cname <> e2.cname AND c1.meets_at = c2.meets_at GROUP BY e1.snum HAVING COUNT(DISTINCT e1.cname) >= 2)
Exclude juniors by adding a 'WHERE' clause to filter 'level' excluding 'JR'. Group by 'level' and calculate 'AVG' on 'age'. This is formulated in the query: SELECT level, AVG(age) as average_age FROM Student WHERE level <> 'JR' GROUP BY level; It calculates average ages excluding the junior level.
To find faculty teaching in all class rooms, first determine all unique rooms using the 'Class' table. Count rooms the faculty teaches in by joining 'Class' with 'Faculty' and grouping by 'fid'. Compare this with the total unique room count. The query is: SELECT DISTINCT fname FROM Faculty WHERE NOT EXISTS (SELECT room FROM Class EXCEPT SELECT room FROM Class WHERE Class.fid = Faculty.fid GROUP BY room); This query checks for non-existence of rooms not taught by each faculty member.
To find faculty who have only taught in room R128, join 'Class' to 'Faculty' on 'fid' and group the results by 'fid'. Include faculty with unique 'room' counts of 1 and where 'room' equals R128. The SQL is: SELECT fname FROM Faculty JOIN Class ON Faculty.fid = Class.fid GROUP BY fname HAVING COUNT(DISTINCT room) = 1 AND MIN(room) = 'R128';
To determine the names of classes that meet in room R128 or have 5 or more students enrolled, perform a left join between 'Class' and 'Enrolled' on 'cname'. Group the results by 'cname' and apply the COUNT function to select classes with a count of 5 or more. Use a 'WHERE' clause to filter classes meeting in room R128. The query is: SELECT cname FROM Class LEFT JOIN Enrolled ON Class.cname = Enrolled.cname GROUP BY Class.cname HAVING COUNT(Enrolled.snum) >= 5 OR room = 'R128';
To identify faculty members whose total class enrollments are less than five, join 'Class', 'Enrolled', and 'Faculty', grouping by 'fid' in 'Faculty'. Use 'COUNT' on 'snum' to determine enrollments per faculty. Filter the results having counts fewer than five. The query formulation is: SELECT fname FROM Faculty JOIN Class ON Faculty.fid = Class.fid JOIN Enrolled ON Class.cname = Enrolled.cname GROUP BY Faculty.fname HAVING COUNT(Enrolled.snum) < 5;