Oracle 9i Query Problem Involving Where,and & Between.

Aoa! 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.

'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? I want to know how

Thank You in advance.

Reply: 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. Reference:

http://www.c-sharpcorner.com/Forums/ShowMessages.aspx?ThreadID=61558 Hi As the book says, King does not fall between Quentin and Zyryrab - these names are the last names of the employees. If you were to remove the ename condition in the statment so you are just getting employees with sal > 900, then KING would appear. Similarly, if you were to change the ename clause to get between 'ANDREWS' and 'VINCENT', you would get KING, SCOTT, TURNER in the results, but not WARD, as K (for KING) is between A (for Andrews) and V (for VINCENT) but W (for WARD) is not. List the names in alphabetical order and it should be clearer ANDREWS - start of range KING SCOTT TURNER VINCENT - end of range WARD - out of range Hope this makes sense Phil In response to your question, I will reference "Boolean Algebra" or what is sometimes called "The Truth Table". In "Boolean Algebra" you will ALWAYS HAVE 1 of 2 RESPONSES: 1 or 0; T(rue) or F(alse); M(ale) or F(emale); O(n) or O(ff), etc. Code:

The Truth Table is as follows, whih I will use T=True and F=False:

•

CASE= > the "AND" Condition, where A,B = an expression or value: o • o

•

If A is T AND B is T, then A AND B are T. If A=1 AND B=1, then A and B are T(rue); If A is F AND B is F, then A AND B are T.

Evaluation of Expressions in the "AND" operand MUST Evaluate to TRUE, which means BOTH expressions MUST evaluate to TRUE or BOTH expressions MUST evaluate to FALSE;

• •

• • • • •

When either of the two expressions evaluate to TRUE or FALSE, then BOTH in the AND Condition are FALSE. In your example starting with the WHERE-Clause: WHERE sal >900 AND ename between 'QUENTIN' and 'ZYRYRAB' Evaluation 1, from left to right (no parentheses to state otherwise). Let A= sal>900. In this evaluation the employee KING was a part of this group, which made this evaluation TRUE; AND is the operand; Evaluation 2, Let B= ename between 'QUENTIN' and 'ZYRYRAB', For the evaluation of this statement excludes KING becuase the ename starts with the english alphabet letter "K" and this exclusion makes the evaluation also TRUE; Back to the Truth Table: If A=True and B=True, then A AND B are True,which yeilds o

• •

7521 WARD 1250, 788 SCOTT 3000, 7844 TURNER 1500

The Truth Table also considers the "OR" Operand and the "! - NOT" Operands, but they were not your question.

Reference: http://p2p.wrox.com/book-beginning-c-2005-databases/75286-query-problem-oracle9i.html#post244027 My probing: 1- select empno,ename,sal from emp where sal>900 and ename between 'Q' and 'Z'; EMPNO ENAME SAL ---------- ---------- ---------7521 WARD 1250 7788 SCOTT 3000 7844 TURNER 1500 3 rows selected. 2- select empno,ename,sal from emp where ename between 'RUENTIN' and 'ZYRYRAB'; EMPNO ENAME

SAL

---------- ---------- ---------7369 SMITH 800 7521 WARD 1250 7788 SCOTT 3000 7844 TURNER 1500 4 rows selected. 3- select empno,ename,sal from emp where ename between 'SUENTIN' and 'ZYRYRAB'; EMPNO ENAME SAL ---------- ---------- ---------7521 WARD 1250 7844 TURNER 1500 2 rows selected. 4- select empno,ename,sal from emp where ename between 'sUENTIN' and 'ZYRYRAB'; no rows selected 5- select empno,ename,sal from emp where ename between 'AUENTIN' and 'ZYRYRAB'; EMPNO ENAME SAL ---------- ---------- ---------7369 SMITH 800 7521 WARD 1250 7566 JONES 2975 7654 MARTIN 1250 7698 BLAKE 2850 7782 CLARK 2450 7788 SCOTT 3000 7839 KING 5000 7844 TURNER 1500 7900 JAMES 950 7902 FORD 3000 7934 MILLER 1300 12 rows selected.

6- select empno,ename,sal from emp where ename between 'A' and 'Z'; EMPNO ENAME SAL ---------- ---------- ---------7369 SMITH 800 7499 ALLEN 1600 7521 WARD 1250 7566 JONES 2975 7654 MARTIN 1250 7698 BLAKE 2850 7782 CLARK 2450 7788 SCOTT 3000 7839 KING 5000 7844 TURNER 1500 7876 ADAMS 1100 7900 JAMES 950 7902 FORD 3000 7934 MILLER 1300 14 rows selected.