June 7, 2012 at 10:36 am
I have one final assignment before I can actually start enjoying my summer break. I've been trying to figure it out for the last 3 hours.
If any one could give me some pointer I'd highly appreciate it!
The assignment is:
Find "namn" and "antalplatser" for "Rum" that has been used by all "Lärare" that is from one "ort"
The table names are in swedish, but translated the question is: Find rooms that all teachers that were born in LA have held a lecture in
My professor hinted that I should use double negation, but still, I can't figure it out.
The relationship diagram:
June 7, 2012 at 11:02 am
The usual way to do that is to find the total count of teachers born in LA, then the count per room of teachers that were born in LA. Where the two counts are equal, you have the right rooms. That's the easy way.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 7, 2012 at 11:06 am
Thank you for your response!
I was actually thinking about doing the query like this, the problem is that this is an entry level class, and we haven't been taught how to do that.
I found this piece of code that does roughly what I want, do you think I could change this around and get it to work for my query?
-- Get part numbers for each part which is
-- supplied to all projects in London
P is Part table, J is Project table, SPJ is supplier part for project
SELECT pnum FROM P WHERE NOT EXISTS ( SELECT * FROM J WHERE city = 'London' AND NOT EXISTS ( SELECT * FROM SPJ WHERE spj.pnum = p.pnum AND spj.jnum= j.jnum));
June 7, 2012 at 12:57 pm
Your innermost query will be a list of all the teachers from LA.
Assuming a standard many-to-many structure, where you have a table-in-the-middle that has teachers and rooms in it, you then need a list of all rooms that don't have a connection to any one of those teachers.
So, yeah, nested Not Exists is probably what your teacher is looking for. He did mention "double-negative" to you.
The problem with that query, unless the specification is suffering a bit, is that if you have a room where all the teachers from LA have lectured, but it has also had teachers from Prague, the query won't return that room. At least, it looks like it won't, to me. You'll need to test it to make sure.
If it were my homework (which would also mean it would be something like 1987 and SQL Server wouldn't exist yet, but we'll ignore that), I'd start out by creating a simple Rooms table, with two or three rows, a Teachers table with at least 3 rows (2 from LA, 1 from wherever else strikes your fancy), and a Lectures table that joins the two. Then try modifying the query you found to fit the tables. Cities becomes rooms, projects becomes lectures, parts is teachers. See if you can make it fit.
Then try modifying the Lectures data to fit different combinations. What if all teachers have held lectures in all rooms? What if there isn't a room that fits the bill (maybe one teacher from LA always lectures in Room 1 while the other always lectures in Room 2)? What if all teachers use Room 1 (like my Prague example above)? What if ... figure out a few other permutations, get creative, and see if you can break the query various ways.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply