This assignment uses a database with data about a university; the database resides in our Oracle servers. The schema of the database is provided below. Keys are in bold face and underlined, field types are omitted; assume that appropriate foreign key constraints have been defined in the full version of the schema (for example, a dname in the prof table does indeed references a department that exists in the dept table):
- 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 who have enrolled in at least one course offered by the 'Chemical Engineering' department and at least one course offered by the 'Industrial Engineering' department.
Print the name and age of the student(s) with the highest GPA among all students of the same age.
Top students per major. For each department with more than 8 students majoring in the department, print the following information about the student(s) with the highest GPA within the department: the student id, student name and GPA, and the department name the student is major in.
Difficult courses. For each department offering more than 2 courses, print the department name, course number, and course average grade of each course that has an average grade that is at least 5% less than the average grade obtained by students in all courses offered by the same department.
Top 2 unpopular courses. Print the department name, course number, and course enrollment of each course that has an enrollment equal to the lowest or second to the lowest enrollment of all courses offered by the same department. The enrollment of a course is the sum of the enrollment of all its sections. Note: for this query, you should consider all courses, including those with zero enrollment. [Hint: You will need to use outer joins for this query; see class notes on the subject and for more the Oracle's online manual on Outer Joins and Using Outer Joins: Examples.]
What to do
- Write one SQL query for each question above.
- Run your SQL queries on one of our Oracle servers (choose any one, they are all identical) using the web interface that we have set up at:
- https://w4111a.cs.columbia.edu/~af2728/hw2.php
- https://w4111e.cs.columbia.edu/~af2728/hw2.php
- https://w4111h.cs.columbia.edu/~af2728/hw2.php
- https://w4111i.cs.columbia.edu/~af2728/hw2.php
- https://w4111l.cs.columbia.edu/~af2728/hw2.php
- https://w4111d.cs.columbia.edu/~af2728/hw2.php