More SQL help.

[Andwise]

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.

[ScallioXTX]

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

[Dr John]

Never exclude a chemist!

Jeez.

[Andwise]

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

Thanks for the answer, it was very helpful.

Anyone else have any solutions?

[r937]

Anyone else have any solutions?

okay, since you asked...

3a. In which years was the Physics prize awarded but no Chemistry prize. (WARNING - this question is way too hard for this level, you will need to use sub queries or joins).

oh no i won't

here's my query --

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

and here are the results --

Well Done, that is correct.
yr
1917
1919
1924
1933

[Andwise]

okay, since you asked...



oh no i won't

here's my query --

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

and here are the results --

Are things like this covered in your book?

Thanks a lot of the answer, it is greatly appreciated.

[SpacePhoenix]

This is what is covered in Rudy's book.