Someone kindly poined out http://sqlzoo.net when I asked about other interactive SQL resourses.
I was moving right along and feeling pretty good about what I have learned until I hit question 3a. on http://sqlzoo.net/1b.htm.
In an attempt to get he correct answer I have been doing quite a bit of research and cannot figure it out. I may be missing something but can’t find the answer on the page.
Basically I have to figure out In which years was the Physics prize awarded but no Chemistry prize. This is to be a select statement from a single table. I have been reading about recursive joins and subqueries but cannot come up with the best answer.
As always, any help that can be provided is appreciated.
SELECT DISTINCT
yr
FROM
nobel n1
WHERE
n1.subject="Physics" AND n1.yr
NOT IN (
SELECT DISTINCT
yr
FROM
nobel n2
WHERE
n2.subject="Chemistry"
)
Select all distinct years where a Physics prize was awarded, but exclude the years where a Chemistry prize was awarded.
This leafs all the years where a Physics prize was awarded, but no Chemistry prize.
PS. Something tells me there is a better query (more efficient) possible to obtain the same answer, I just don’t know what it is
SELECT yr
FROM nobel
GROUP
BY yr
HAVING COUNT(CASE WHEN subject ='Physics'
THEN 'humpty' END) > 0
AND COUNT(CASE WHEN subject ='Chemistry'
THEN 'dumpty' END) = 0