h2g2 The Hitchhiker's Guide to the Galaxy: Earth Edition

Hi everyone,
I have a question which says : -

"Find the SPID,surname and forename of all students taking COMP207 but not taking COMP201" and goes on to suggest that it would be wise to use relational algebra

The tables are : -

COURSES(COURSE, NAME, DIRECTOR)
FACULTIES(faccode,name)
REGISTER(spid,unit)
SLOTDETAILS(SLOT, SLOTDAY, SLOTBEG, SLOTEND, SLOTLABS)
STUDENTS(SPID, USERID, SURNAME, FORENAME, INITIALS, TITLE, FACCODE, COURSE, TPID, SLOTDAY)
TEACHES(unit,tpid)
TUTORS(tpid,name)
UNITS(unit,name)

now how I've tried to do it is :-

select s.spid,s.surname,s.forename,r.unit from diz.students s, diz.register r where exists (select s.spid, s.surname, s.forename, r.unit from diz.students s, diz.register r where r.spid=s.spid and r.unit='COMP 207') and minus (select s.spid, s.surname, s.forename, r.unit from diz.students s, diz.register r where r.spid=s.spid and r.unit='COMP 201');


However, using this method I still recieve values with 'COMP 201' in them. I was wondering whether someone would be able to tell me where I am going wrong, and if anyone can help. I would be very greatful if you could as this is the last question in a long list of questions.

Terran

You've got two Spid fields, one in Students and one in register. How d'you expect us to do your coursework if you don't let us know which one you want?

And which table/field hold the course thingy?

You don't have to do my coursework (Though I obviously wouldn't complain if you did ), just a nudge in the right direction. The two spid fields are used to link the multiple tables together, hence r.spid==s.spid (spid is the key). I'm not quite sure why you want to know why only one would be used.

What do you mean by "course thingy"?

You're wanting to find which students are taking COMP207 and not COMP203 (or whatever the numbers are), we need to know which fields in which tables hold this information to be able to query against it.

I was wanting to know which Spid you were after, because you were looking to retrieve it. Some databases have two or more tables that have fields with the same name, but not the same information in them.

ah! COMP207 and all the other course codes are in table diz.REGISTER in the field unit . With reference to the spid, they are both the same, and are used to link the two tables I am using (Register and Students)

So, you're looking to retrieve all the Spids that have Comp207 but not Comp201, or whatever the numbers are, and then to find the student details for that?

Easy enough

Is this Open Uni by the way?

"So, you're looking to retrieve all the Spids that have Comp207 but not Comp201, or whatever the numbers are, and then to find the student details for that?"

Not exactly, they have to have COMP207 in them, but all the other modules which they do (without COMP201) have to be in there as well.

"Is this Open Uni by the way?"
No, Liverpool Uni. Just out of curiousity what made you say that you thought it was?

Okay, here's I how start going about this. I'm not saying it's right, and it'll probably need some GROUP By sorting out...


SELECT STUDENTS.SPID, STUDENTS.SURNAME, STUDENTS.FORNAME, REGISTER.unit FROM STUDENTS INNER JOIN REGISTER on STUDENTS.SPID = REGISTER.SPID WHERE UNIT = 'COMP207' AND UNIT <> 'COMP201'

I've messed about with a little but there is an error pointing here :-

FROM STUDENTS INNER JOIN REGISTER on STUDENTS.SPID = REGISTER.SPID
*
"SQL command not properly ended"

I'm not sure why it would come up with this error.

The * should be in line with join

I've fixed the problem and am ready to hand in the work. Thanks for all your help

You're welcome