View the Exhibit and examine the data in the PRODUCTS table.
You need to display product names from the PRODUCTS table that belong to the ‘Software/Other ‘
category with minimum prices as either $2000 or $4000 and no unit of measure.
You issue the following query:
SQL>SELECT prod_name, prod_category, prod_min_price
FROM products
WHERE prod_category LIKE ‘%Other%’ AND (prod_min_price = 2000 OR
prod_min_price = 4000) AND prod_unit_of_measure <> ”;
Which statement is true regarding the above query?
A.
It executes successfully but returns no result.
B.
It executes successfully and returns the required result.
C.
It generates an error because the condition specified for PROD_UNIT_OF_MEASURE is not valid.
D.
It generates an error because the condition specified for the PROD_CATEGORY column is not valid.
the ending stray double quote keeps from executing; otherwise it would be C yes?
Why the option B is not correct. I think the query will return 3rd row
wat does ” stand for?
I Guss they assume like empty.’ ‘
probably it should be two single quotes. Not Null but Empty.
” is considered as null.
To give correct output should be
WHERE prod_category LIKE ‘%Other%’ AND (prod_min_price = 2000 OR
prod_min_price = 4000) AND prod_unit_of_measure in NULL
because it should be “no unit of measure”.
IS NULL is different from empty.
it should be ‘is null’, I made a typo.
so the query is right or not?
I think the above query is right if it was considered as single quotes at the end and the right answer would be opt B
The query is correct (there is no syntax errors) but will return no rows. There is logical mistake.
So what was the logical mistake in that query?
From the exibition above I understand that prod_unit_of_measure has values
– Nos.
– no value not a space or something like that.
In such case it is NULL then so you can use ‘IS NULL’ or ‘IS NOT NULL’
condictions.
A null is a value that is unavailable, unassigned, unknown or inapplicable. Null is not same as zero or blank space. Zero is a number and blank space is a character. Columns of any data type can contain null values.
Can you have prod_unit_of_measure = ” ?
Does (prod_unit_of_measure IS NULL) is same as (prod_unit_of_measure =”)?
to make it correct we change to = ?
Is
prod_unit_of_measure IS NULL
same as
prod_unit_of_measure =”
?
no
no its not same as null it works as empty.
then please explain, thanks.
NULL — No Space is allotted
EMPTY — Default space will be allotted.
See my comment above: in case Null values you can use only ‘IS NULL’ or ‘IS NOT NULL’ condictions.
Ok I see now. Thank you very much for your help
I also think that B is the correct answer as it will return 3rd row
In SQL ” IS NULL is TRUE. Because and AND operators produce NULL if any of their values are NULL, then the whole WHERE clause in the query will always result in NULL.
So no, 3rd row will not be returned.
I think the correct is B beacuse it meet the WHERE condition however pro_unit_of_masure “
ha ha ha
Why this query should return no row?
Row 3 meets all the where conditions:
PROD_UNIT_OF_MESURE = ‘Nos.’ which is ”
PROD_UNIT_OF_MESURE = ‘Nos.’ which is different from ”
the product category part,the second % should be omitted. that is why it results none.
my question is how the prod_unit_of_measure part uses for null result (”)
the answer should be C.
i mean
my question is how the prod_unit_of_measure part uses for null result(”)
the answer should be C.
For this condition :
(prod_unit_of_measure ” )
i think it is interpreted like if p_u_o_m is equat to the character ” so it returns no result
but executed succesfully.
Sry it is completely false :/ it is différent not equal
stand for (Not Equal To).When you compare nonnull expressions by using this operator, the result is TRUE when the left operand is not equal to the right operand. Otherwise, the result is FALSE. If we check column = ” then query executes successfully but displays not result.
The question says:
“… and no unit of measure”
The querye says:
… AND prod_unit_of_measure ”;
This way the query return the 3 row (PROD_ID = 103)
THe result it´s wrong… the question need one result and the querie return other result. This way the “A” alternative is right!
A) It executes sucessfully but returns no result.
The point is not “NULL” or ” or “… the point in this question is the enunciate.
2 points I found in the oracle documentation and other training material for this exam.
1. character values with a length of zero is treated as null in release 11gR2.
(please note that this may not continue to be true in future releases i.e. do not treat empty strings the same as nulls in your code)
2. To test for NULL, use only the comparison conditions IS NULL and IS NOT NULL otherwise the result is UNKNOWN i.e. a query with
WHERE comm_pct NULL;
returns no rows.
Therefore A is the answer.
oh dear it came out wrong….
please replace ….
WHERE comm_pct NULL;
with …
WHERE comm_pct NULL;
there should be a not equal sign before the NULL
Oracle Treats EMPTY values(”) as NULL values See the documentation: http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements005.htm . So empty values in Oracle 11g does not exist, and to return the 4th row(have no unit of measure) you need to use the ‘IS NULL’. So the right query is:
SELECT prod_name, prod_category, prod_min_price
FROM products
WHERE prod_category LIKE ‘%Other%’ AND (prod_min_price = 2000 OR prod_min_price = 4000) AND prod_unit_of_measure IS NULL;
Obs: The following INSERT command are equivalent:
INSERT INTO Products VALUES(104, ‘Documentation Set – Spanish’, ‘Software/Other’, 4000, null);
INSERT INTO Products VALUES(104, ‘Documentation Set – Spanish’, ‘Software/Other’, 4000, ”);
The correct answer id letter A, cause if you compare an empty value(NULL value) using the operator the result is UNKNOWN
* DIFFERENT operator the result…
I see a lot of confusion above. The only correct answer is A.
See:
> CREATE TABLE A (ID NUMBER, NAME VARCHAR2(15));
> INSERT INTO A VALUES (1, ”);
> INSERT INTO A VALUES (2, ”);
> INSERT INTO A VALUES (3, null);
> INSERT INTO A VALUES (4, ‘John’);
> SELECT * FROM A WHERE NAME = ”;
“no rows selected”
> SELECT * FROM A WHERE NAME ”;
“no rows selected”
> SELECT * FROM A WHERE NAME IS NULL;
“3 rows selected”
> SELECT * FROM A WHERE NAME IS NOT NULL;
“1 row selected”
Conclusion: While inserting, Oracle treats ” and NULL same, but while we projecting rows we have to use “IS NULL” or “IS NOT NULL”.
Wow cuz this is great job! Congrats and keep it up
gametips http://www.athletesquare.com/mediawiki/index.php?title=User:ArdisR91663
Correct Answer is A as ” and null both treated as same. ” is not a space .
you can try the below query
select 1 from dual where ” is null;
it will give 1 row in return.
So where we use or = operator in comparing with null it will give no result.
So option 1 is correct.
A
JAJJAJAJAJ, VAYA PREGUNTA. LA RESPUESTA CORRECTA ES LA A, PERO NOS HACE CONFUNDIR LA DIFERENCIA ENTRE ” Y IS NULL: LA CONSULTA ANTERIOR NO MUESTRA NINGÚN REGISTRO, POR LO TANTO LA RESPUESTA A ENCAJA PERFECTO “EJECUTA PERO NO MUESTRA EL RESULTADO SOLICITADO”. OTRA COSA ES QUE EN VEZ DE COLOCAR ” EN EL WHERE PARA RECONOCER Y EVALUAR DE MANERA EFECTIVA LA CONDICIÓN Y MOSTRAR EL RESULTADO QUE SE REQUIERE, SE DEBE COLOCAR WHERE NOMBRE_COLUMNA IS NULL.