Retrieve right year in sql [closed]

Closed. This question needs details or clarity. It is not currently accepting answers.

---

开发者_开发百科

Want to improve this question? Add details and clarify the problem by editing this post.

Closed 8 years ago.

Improve this question

Goal:

"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). "

Problem:

Don't know how to solve it.

The code can be found in "http://sqlzoo.net/1b.htm", headline 3a.

---

 SELECT
   distinct yr
 FROM
   nobel
 WHERE
   yr not in (select yr from nobel where subject in ('Chemistry')) AND
   subject in ('Physics')

One option using subqueries. The subquery gets all the years where there was a chemistry prize which is used to to excude those years with the not in statement.

The distinct yr accounts for the fact that there could have been two prizes awarded.

There's a bunch of different ways to make this work. Take the code above and try making a join with the subquery work. That'll give you more practice with subqueries and using them in joins which is a useful thing.