More SQL help

Databases
1

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.

2 

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 :slight_smile:

3

Never exclude a chemist!

Jeez.

4

Thanks for the answer, it was very helpful.

Anyone else have any solutions?

5

okay, since you asked… :slight_smile:

oh no i won’t :smiley:

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 –

:smiley:

6

Are things like this covered in your book? :smiley:

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

7

This is what is covered in Rudy’s book.