10 Replies - 516 Views - Last Post: 10 November 2013 - 11:17 AM Rate Topic: -----

#1 nellykvist  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 43
  • Joined: 18-September 13

Please check my task

Posted 10 November 2013 - 09:22 AM

I'm using phpMyAdmin and I have a task to do, so please tell me if this sql statement answers to this question...

Show the name, email and city of the users, which surename is Austin or name "Lara" and "Daniel", whereby they have a salary between 100000 and 300000 and they are not from "Liverpool".
By the way, don't you think teacher made a mistake her condition, where she say: "or name Name Lara and Daniel".
Logically user can't have two names (and there is no a column with additional name or father name), so I put instead OR operator...Am I correct ?

SELECT NAME, EMAIL, CITY
FROM `contacts` 
WHERE (SURENAME = 'Austin' OR (NAME = 'Lara' OR NAME = 'Daniel'))
AND 
   SALARY BETWEEN 100000 AND 300000 
AND 
   CITY NOT IN ('Liverpool')



However, I look at the table and none of this rows from a table has this condition, so as a result I got:

NAME | EMAIL | CITY
-------------------
NULL NULL NULL

Is this ok, if for result I get NULL ?

This post has been edited by nellykvist: 10 November 2013 - 09:24 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Please check my task

#2 Ryano121  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1363
  • View blog
  • Posts: 3,002
  • Joined: 30-January 11

Re: Please check my task

Posted 10 November 2013 - 09:27 AM

Surely you are only dealing with surnames in the WHERE clause, not name?

WHERE (SURNAME = 'Austin' OR SURNAME = 'Lara' OR SURNAME = 'Daniel')

Was This Post Helpful? 0
  • +
  • -

#3 nellykvist  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 43
  • Joined: 18-September 13

Re: Please check my task

Posted 10 November 2013 - 09:38 AM

View PostRyano121, on 10 November 2013 - 09:27 AM, said:

Surely you are only dealing with surnames in the WHERE clause, not name?

WHERE (SURNAME = 'Austin' OR SURNAME = 'Lara' OR SURNAME = 'Daniel')

I'm dealing with surename and or names, where:
(surename is Austin or name "Lara" and "Daniel")

So, you would do the same,
SURNAME = 'Austin' OR NAME = 'Lara' OR NAME = 'Daniel'
right ?
And tell me is it fine, to show me a result with a row and results NULL ?

This post has been edited by nellykvist: 10 November 2013 - 09:40 AM

Was This Post Helpful? 0
  • +
  • -

#4 Ryano121  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1363
  • View blog
  • Posts: 3,002
  • Joined: 30-January 11

Re: Please check my task

Posted 10 November 2013 - 09:56 AM

Ah I get you now.

It seems strange to me that you get NULL back. I would think you should just get no results at all returned.
Was This Post Helpful? 0
  • +
  • -

#5 nellykvist  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 43
  • Joined: 18-September 13

Re: Please check my task

Posted 10 November 2013 - 10:02 AM

Yea, it's strange to me as well.. but still it's ok to not show any results, since there's no rows which answers to this condition, here is screenshot:

Posted Image
Was This Post Helpful? 0
  • +
  • -

#6 nellykvist  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 43
  • Joined: 18-September 13

Re: Please check my task

Posted 10 November 2013 - 10:10 AM

Now I tried this, in which case statement should return false, because there's no ID with value '6':

SELECT * 
FROM  `imenik` 
WHERE ID =  '6'



And it returns:

Posted Image
Was This Post Helpful? 0
  • +
  • -

#7 Ryano121  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1363
  • View blog
  • Posts: 3,002
  • Joined: 30-January 11

Re: Please check my task

Posted 10 November 2013 - 10:12 AM

Yeah that's what I would expect to happen in the other query as well if no records match.
Was This Post Helpful? 0
  • +
  • -

#8 nellykvist  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 43
  • Joined: 18-September 13

Re: Please check my task

Posted 10 November 2013 - 10:24 AM

I found out that I create a temp column by calling function MIN(SALARY), so that's the reason... If I had MIN_SALLARY column, it would instead return "empty result"...

This post has been edited by nellykvist: 10 November 2013 - 10:27 AM

Was This Post Helpful? 0
  • +
  • -

#9 Ryano121  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1363
  • View blog
  • Posts: 3,002
  • Joined: 30-January 11

Re: Please check my task

Posted 10 November 2013 - 10:27 AM

Well that wasn't in the query you posted...
Was This Post Helpful? 0
  • +
  • -

#10 nellykvist  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 43
  • Joined: 18-September 13

Re: Please check my task

Posted 10 November 2013 - 10:49 AM

Yes, but I came for "which surename is Austin or name "Lara" and "Daniel".. However, thanks for your time.
Was This Post Helpful? 0
  • +
  • -

#11 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3730
  • View blog
  • Posts: 6,017
  • Joined: 08-June 10

Re: Please check my task

Posted 10 November 2013 - 11:17 AM

Whatever your intentions, if you could please make sure that the code you post and the results you say it gives match, we'd appreciate it. Nobody likes wasting time on a problem where they don't even have the right details. - You can always rename identifiers if you are worried about giving away some secret info you don't want seen.


About the name thing. I can't tell you if your teacher made a mistake or not, but I can tell you that AND has higher precedence than OR, so if in fact the equation reads as: sn=x OR n=Y AND n=Z then that would be equal to: sn=x OR (n=Y AND n=Z). Therefore, either the surname should be Austin, or the name should be both Lara and Daniel. - Of course that doesn't make a whole lot of sense, but that is what the question technically asks for.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1