1)The schema of the database is provided below ( keys are in bold) student ( sid

Question

1)The schema of the database is provided below (keys are in bold)

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)

a) Print the names of students who are taking both a Computer Sciences course and a Mathematics course.

b) Print the age difference between the oldest and youngest Computer Sciences major(s).

c) For each department that has one or more majors with a GPA under 1.0, print the name of the department and the average GPA of its majors.

d) Print the ids, names, and GPAs of the students who are currently taking all of the Civil Engineering courses.

Explanation / Answer

a) Print the names of students who are taking both a Computer Sciences course and a Mathematics course.
SELECT student.sid, student.sname
FROM student
JOIN enroll e1 ON e1.sid=student.sid
JOIN course c1 ON c1.cno=e1.cno
JOIN enroll e2 ON e2.sid=student.sid
JOIN course c2 on c2.cno = e2.cno
WHERE c1.dname LIKE '%Mathematics%'
AND c2.dname LIKE '%Computer Science%'
GROUP BY student.sid

b) Print the age difference between the oldest and youngest Computer Sciences major(s).
select((select max(s.age) from student s, major m where s.sid = m.sid and m.dname = 'Computer Sciences') - (select min(s1.age) from student s1, major m1 where s1.sid=m1.sid and m1.dname = 'Computer Sciences')) as Difference;

c) For each department that has one or more majors with a GPA under 1.0, print the name of the department and the average GPA of its majors.
select major.dname, avg(student.gpa) as "AvgGPA" from student, major
where student.sid = major.sid and student.gpa < 1.0
group by major.dname

d) Print the ids, names, and GPAs of the students who are currently taking all of the Civil Engineering courses.

select s.sid, s.sname, s.gpa
from student s
inner join enroll e
on s.sid = e.sid
where e.dname = 'Civil Engineering'
group by sid
having count(distinct cno) =
(select count(cno) from course where dname = 'Civil Engineering');

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

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