Query problem in Oracle 9i

arbab · July 16th, 2009, 11:57 AM

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?

philip_cole · July 16th, 2009, 04:58 PM

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, TURNERin 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

peace95 · July 16th, 2009, 07:00 PM

rubab:

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: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 yeilds7521 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.

Hope this helps.