This assignment uses an actual PostgreSQL database with data about a university. 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 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 (see instructions below). Each question is worth 2 points.

1) Print the name and age of the student(s) with the highest GPA among all students who are at the same age or older.

2) Students not dedicated to their major. Print the id, name and major(s) of each student whose name starts with letter K and has not enrolled in any course offered by any of the departments the student is majoring in.

3) 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. Order the final result by GPA in descending order.

4) Difficult courses. For each department offering more than two courses, print the department name, course number, and course average grade of each course that has an average grade that is at least 7% less than the average grade obtained by students in all courses offered by the same department.

5) Department stats.For each department offering at least one course in which at least one student is enrolled, print the department name, the total enrollment of all courses offered by the department, the number of students enrolled in at least one course offered by this department, and their average, minimum, maximum age and average, minimum, maximum gpa.

What to do

Write one SQL query for each question above. Run your SQL queries on one of our PostgreSQL servers (choose any one, they are identical) using the web interface that we have set up at: http://w4111p1.cs.columbia.edu/