This assignment uses an actual PostgreSQL database with data about a university.

Question

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.

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.

Explanation / Answer

Hi,

Answered 1 to 3 questions.

Ans 1- select a.sname,a.age from student a
join (select age,max(gpa) val from student group by age) b
on a.age>=b.age;

Ans 2- select s.sid,sname,dname from student s join major m
on s.sid=m.sid
join enroll e
on m.dname=e.dname
and s.sid not in(select sid from enroll)
and substr(sname,1,1)='K';

Ans 3-
select sid,sname,gpa,b.dname from student s1 join
(select max(s.gpa) val,m.dname from student s join major m
on s.sid=m.sid
group by m.dname) b
on s1.gpa=b.val
and b.dname in(select dname,count(*) from major group by dname having count(*)>8);

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.