Query problem in Oracle 9i

Jul 16 2009 12:01 PM
I am having problem of understanding query in book of Oracle 9i, the query is this: select empno,ename,sal from emp where sal>900 and ename between 'QUENTIN' and 'ZYRYRAB'; EMPNO ENAME SAL ---------- ---------- ---------- 7521 WARD 1250 7788 SCOTT 3000 7844 TURNER 1500 3 rows selected. I want to know how 'QUENTIN' and 'ZYRYRAB' works, because I simply don't get how'QUENTIN' and 'ZYRYRAB' eliminates KING which makes more money than WARD,SCOTT , TURNER the book says that KING's last name does not fall between 'QUENTIN' and 'ZYRYRAB', 1st of all I have not yet seen last name or full name all I in the tables are just single names then what book is talking about last name? And also'QUENTIN' and 'ZYRYRAB' works I simply don't get. Could u plz help me understanding the above mention problem?

Answers (3)

0
sanjay donda

sanjay donda

  • 0
  • 5
  • 0
Apr 29 2010 10:38 AM
(1) eliminates KING because u use AND operation in WHERE clause. AND means it both the condition is true the and only then u will get that row otherwise not....
(2) if last name is not there then the query takes the LASTNAME of KING as a NULL value.... that is y u r not getting that row......
(3) 'QUENTIN' means the executor creates a ASCII value of this string first it will check the first character with the other like assume that it is 'KING'   then it compares like "Q < K"  or not if it is yes then it checks next character like "U < I" and so on...
if all the character is greater then the 'QUENTIN' and less then the 'ZYRYRAB' then and only then u will get that value....... 
0
Syed Arbab Ahmed

Syed Arbab Ahmed

  • 0
  • 104
  • 0
Jul 17 2009 12:35 PM
When I tried this select ename from emp where ename between 'a' and 'z'; no rows selected but I still have problems in order to understand how 'QUENTIN' and 'ZYRYRAB' works in select empno,ename,sal from emp where sal>900 and ename between 'QUENTIN' and 'ZYRYRAB'; EMPNO ENAME SAL ---------- ---------- ---------- 7521 WARD 1250 7788 SCOTT 3000 7844 TURNER 1500 3 rows selected.
0
naura pax

naura pax

  • 0
  • 1.2k
  • 88.5k
Jul 17 2009 5:29 AM

Hi,
  the between is used for name field, which hasalphanumeric datatype, and
the ordering is done on the basis of alphabet .
For eg run this query-



create table #tmp (name varchar(10))
insert into #tmp
select 'a' union all
select 'b' union all
select 'c' union all
select 'd' union all
select 'e' union all
select 'f union all
--So between
select name from #tmp where name between 'a' and 'd'



--will return
a b c d. It is not related with the salary of employee for that you'll have to write separate query.