CODEWITHSUNDEEP
mysqlsqldatabasegroup-byhaving
The Zuidz
The Zuidz

Reputation: 698

How do i write this SQL query for an ALL condition?

I need to build an SQL query in MySQL to solve a problem as part of an assignment for class. I've been working at this problem for awhile, but I'm having a hard time figuring out how to structure this query properly. I'm rather new to the SQL language and databases in general and I'm stumped on this question. I have posted what I've come up with so far, but unfortunately I have not been able to get the results I'm looking for. If anyone could give me some guidance on how to accomplish this I would greatly appreciate it.

Here's what my table structure looks like:

course(cid, fid, room)

enroll(cid, sid, grade)

faculty(fid, fname, dept, rank)

student(sid, sname, major, credits)

Here's the query I need to build:

Show the faculty id and faculty name for all faculty that have taught all computer science majors (major = 'CSC').

Here's what I've tried so far:

select f.fid, f.fname
    from faculty f
    join course c 
        on f.fid = c.fid
    join enroll e 
        on c.cid = e.cid
    join student s 
        on e.sid = s.sid
    where s.sid = ALL
        (select sid
            from student
            where major = 'CSC');

select f.fid, f.fname
    from faculty f
    join course c 
        on f.fid = c.fid
    join enroll e 
        on c.cid = e.cid
    join student s 
        on e.sid = s.sid
    group by f.fid, s.sid
    having s.sid = ALL
        (select sid
            from student
            where major = 'CSC'));

The logical hurdle I'm having a hard time understanding is how to make sure that the faculty member is teaching ALL of the current CSC majors. You can see that I've tried to add some logic to check each record returned, but I'm afraid I may be misunderstanding the syntax. These queries will run, but they return empty sets. Thanks for the help.

Upvotes: 0

Views: 195

Answers (3)

willycs40
willycs40

Reputation: 63

I agree the question may be unclear, and they might just be after all faculty that have taught any CSC major. However, just in case you still need all the faculty that have taught all CSC major, this should work:

The following query tells us the pairs of faculty and CSC majors:

select f.fid, s.sid
from faculty f
inner join course c
on f.fid = c.fid
inner join enroll e
on e.cid = c.cid
inner join student s
on e.sid = s.sid
where s.major = 'CSC'
group by f.fid, s.sid

Therefore, if we know the count of students who are computer science majors:

select count(1)
from student s
where s.major = 'CSC'

Then we can add up the number of CSC majors taught by each faculty member, and check it's equal to the total number of CSC majors:

select b.fid, b.fname
from (
    select a.fid, a.fname, count(1) as taught_count
    from (
        select f.fid, f.fname, s.sid
        from faculty f
        inner join course c
        on f.fid = c.fid
        inner join enroll e
        on e.cid = c.cid
        inner join student s
        on e.sid = s.sid
        where s.major = 'CSC'
        group by f.fid, s.sid
    ) a
    group by a.fid, a.fname
) b
where b.taught_count = (
    select count(1)
    from student s
    where s.major = 'CSC'
)

Upvotes: 1

GMazzacua
GMazzacua

Reputation: 118

Try with 

select f.fid, f.fname
    from faculty f
    join course c 
        on f.fid = c.fid
    join enroll e 
        on c.cid = e.cid
    join student s 
        on e.sid = s.sid
    where s.sid IN (select sid from student where major = 'CSC');

Upvotes: 1

Christian Barron
Christian Barron

Reputation: 2755

What you've done looks pretty good. I think you may just be over thinking it. This should logically give you what you're looking for:

Select f.fid, f.fname
   from faculty f
   join course c on c.fid = f.fid
   join enroll e on e.cid = c.cid
   join student s on s.sid = e.sid
Where major = 'CSC'
group by f.fid, f.fname

Upvotes: 1

Related Questions