I need help with this two quieries. here is the relational tables students(sid,n
ID: 3806533 • Letter: I
Question
I need help with this two quieries.
here is the relational tables
students(sid,name,age,gpa) // sid is the primary key
courses(cid,deptid, description) // cid,deptid is the primary key
professors(ssn,name,address,phone,deptid) // ssn is the primary key
enrollment(sid,cid,section,grade) cid makes reference to the courses table. // sid, cid, and section is the primary key
teaches(cid,section,ssn). cid, section makes reference to the enrollment table // cid, section is the primary key
I need help with these 2 quieries.
a. List ssn and name of professors that work for the cs department, but are not teaching any cs courses.
b. List the number of courses offered by each department. Just the number of courses (not sections).
Explanation / Answer
A)
select ssn,name
from proffessors p,teaches t
where deptid='cs' and p.ssn != t.ssn
B)
select count(*),deptid from professors where courses.deptid=professors.deptid group by deptid