student(sid, sname, sex, age, year, gpa)
dept(dname, numphds)
prof(pname, dname)
course(cno, cname, dname)
major(dname, sid)
section(dname, cno, sectno, pname)
enroll(sid, grade, dname, cno, sectno)
We ask that you write the following queries in SQL. Each question is worth 2 points.
Print the sid and name of students majoring in 'Civil Engineering' who have enrolled in at least one course offered by the 'Chemical Engineering' department.
Wrong major. We want to find students whose average grade on the courses offered by all the departments the student is majoring in is less than the student's average grade on the courses offered by all other departments. For these students, we want to print their sid, name, major, and GPA as well as the dname, cno and grade of all the courses they have enrolled. We are only interested in students who have enrolled in at least one course offered by any of the departments they are majoring in and at least one course offered by any other department.
Unpopular courses. Find the dname, cno and enrollment of all courses with enrollment less than 8% of the total student population. Your answer should include courses with zero enrollment. [Hint: You will need to use outer joins; see class notes on the topic and for more the Oracle's online manual on Outer Joins and Using Outer Joins: Examples.]
Most popular professors. For each department with more than 8 students majoring in the department, print the following information about the professor(s) that teach a course section with the highest enrollment among all sections within the department: the professor name and his/her department, as well as the cno, sectno and enrollment of that section.
Rising stars. We want to collect statistics about rising stars: these are students who are younger than the average age of students majoring in the same department and who have a GPA that is at least 20% higher than the average GPA of students majoring in the same department. For each department with at least 3 students majoring in the department, print the department name, the total number of students majoring in the department and the average age and average GPA of all students majoring in this department, followed by the same information for the rising stars of this department (i.e., the number of rising stars in the department as well as their average age and average GPA). Consider only those departments that have at least one rising star majoring in the department.