View the Exhibit and examine the structure of the CUSTOMERS table.
You want to generate a report showing the last names and credit limits of all customers whose last names start with A, B, or C, and credit limit is below 10, 000.
Evaluate the following two queries:
SQL> SELECT cust_last_name, cust_credit_limit FROM customers
WHERE (UPPER(cust_last_name) LIKE ‘A%’ OR
UPPER(cust_last_name) LIKE ‘B%’ OR UPPER(cust_last_name) LIKE ‘C%’)
AND cust_credit_limit < 10000;
SQL>SELECT cust_last_name, cust_credit_limit FROM customers
WHERE UPPER(cust_last_name) BETWEEN ‘A’ AND ‘C’
AND cust_credit_limit < 10000;
Which statement is true regarding the execution of the above queries?
A.
Only the first query gives the correct result.
B.
Only the second query gives the correct result.
C.
Both execute successfully and give the same result.
D.
Both execute successfully but do not give the required result.
both seem to work. Would sure appreciate if someone could help me understand what is wrong with the second query. thanks loads.
SQL> select ename from emp where ename between ‘A’ and ‘C’;
ENAME
———-
ALLEN
BLAKE
ADAMS
SQL> select ename from emp where ename like ‘A%’ or ename like ‘B%’ or ename lik
e ‘C%’;
ENAME
———-
ALLEN
BLAKE
CLARK
ADAMS
SQL>
Clark start with C but not chown with the command:
select ename from emp where ename between ‘A’ and ‘C’;
so the answer for this question is the command :
SQL> SELECT cust_last_name, cust_credit_limit FROM customers
WHERE (UPPER(cust_last_name) LIKE ‘A%’ OR
UPPER(cust_last_name) LIKE ‘B%’ OR UPPER(cust_last_name) LIKE ‘C%’)
AND cust_credit_limit < 10000;
whitch is A
It is right.
Between ‘A’ and ‘C’ means: AA…, AB…, ..,AZ..,…, BA..,BB…,…, BZ..,C
If you put any sign after C it is not between A and C. It is then – starting with C, meaning like ‘C%’.
nice:)
Between will inclusive the values specified in it.
Yes, using “between” you select also rows for the values specified in ethe condition. However ‘Ca’,’CA’ etc. are not between A and C but between A and D.
The first query ONLY gives correct result if you interpret that cust_last_names beginning with ‘A’ and ‘B’ DO NOT have to have credit_limit < 10000.
Poor articulation again.
Indeed the correct answer is A. Only the first query gives the correct result.
SELECT LAST_NAME FROM EMPLOYEES WHERE UPPER(LAST_NAME) BETWEEN ‘A’ AND ‘C’ ORDER BY LAST_NAME;
Abel
Ande
Atkinson
Austin
Baer
Baida
Banda
Bates
Bell
Bernstein
Bissot
Bloom
Bull
SELECT LAST_NAME FROM EMPLOYEES WHERE UPPER(LAST_NAME) LIKE ‘A%’ OR UPPER(LAST_NAME) LIKE ‘B%’ OR UPPER(LAST_NAME) LIKE ‘C%’ ORDER BY LAST_NAME;
Abel
Ande
Atkinson
Austin
Baer
Baida
Banda
Bates
Bell
Bernstein
Bissot
Bloom
Bull
Cabrio
Cambrault
Cambrault
Chen
Chung
Colmenares
It seems that BETWEEN (in Oracle) selects fields between the test values (A), including the first test value and EXCLUDING the last test value (C).
According to 3schools:
Notice that the BETWEEN operator can produce different result in different databases!
– In some databases, BETWEEN selects fields that are between and excluding the test values.
– In other databases, BETWEEN selects fields that are between and including the test values.
– And in other databases, BETWEEN selects fields between the test values, including the first test value and excluding the last test value.