0

Started solving problems given on sqlzoo.net http://sqlzoo.net/wiki/SELECT_from_Nobel_Tutorial

The 8th question given on this tutorial has been solved before on this page Simple SELECT SQL query not working in extensive detail but I don't understand why my answer is incorrect and giving me wrong results.

My query:

    Select distinct yr 
    From (Select distinct yr, subject From nobel Where subject!='Chemistry')X
    Where subject='Physics'

Can someone please help explain why my query is not correct?

Community
  • 1
  • 1
Binu
  • 1

2 Answers2

1

Change your != to <>.

Edit: Removing the X actually isn't necessary, as it's acting as a table alias. But I would fix it to make it more clear by adding a space and/or adding 'AS'.

e.g.

(select ... from ...) X

(select ... from ...) AS X

Also, I see why the second where clause is there now. :P

Mike B.
  • 1,422
  • 12
  • 8
  • I used <> and AS X but the website says the answer has too many rows i.e., it is incorrect. Select distinct yr From (Select distinct yr, subject From nobel Where subject<>'Chemistry') AS X Where subject='Physics' – Binu Mar 31 '15 at 23:05
  • When you say `(select distinct yr, subject from nobel where subject <> 'Chemistry')`, you're going to get too many years. That query is assuming that only one prize is given per year, which looks to not be the case... for example, if there was a Chemistry prize in 1901, you exclude that... but if there is also a Literature prize in 1901, you're going to get 1901 in your result set. Basically, you're excluding **records** with a Chemistry prize, but you're not necessarily excluding **years** with a Chemistry prize. – Mike B. Mar 31 '15 at 23:16
  • Ah! I see. "Too many rows" makes sense now. Thank you for the explanation! :D – Binu Mar 31 '15 at 23:25
0

Why do you need the X in the query and why you're using != instead of <>?

From (Select distinct yr, subject From nobel Where subject!='Chemistry')X

Just change it to the following code:

select distinct yr 
    from (select distinct yr, subject From nobel where subject <> 'Chemistry')
    Where subject='Physics'
Yair Nevet
  • 12,725
  • 14
  • 66
  • 108
  • When I don't write Alias name for the sub-query returned table , the result box on the website says alias required. And I have tried it with <>, its not working. – Binu Mar 31 '15 at 23:09