Which statement is true regarding the execution of the following two queries?

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?

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.



Leave a Reply 8

Your email address will not be published. Required fields are marked *


persist

persist

both seem to work. Would sure appreciate if someone could help me understand what is wrong with the second query. thanks loads.

Ivan Ticona

Ivan Ticona

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

Justyna

Justyna

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%’.

Preethi Thiagarajan

Preethi Thiagarajan

nice:)

Surendar

Surendar

Between will inclusive the values specified in it.

Justyna

Justyna

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.

donald

donald

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.

Norro

Norro

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.