help 001

[jmniu]

1.Single row functions can use in Where clause

A. User_supplied literal
B. Arithmetic express
C. Column name
D. Column alias
E. Column position

what is the answer? (I don't understand the question)

2. The PART table contains these columns:
ID NUMBER(7) PK
COST NUMBER(7,2)
PRODUCT_ID NUMBER(7)
Evaluate these two SQL statements:
(1). SELECT ROUND (MAX(cost),2),
ROUND (MIN(cost),2), ROUND(SUM(cost),2),
ROUND (AVG(cost),2)
FROM part;
(2). SELECT product_id, ROUND (MAX(cost),2),
ROUND (MIN(cost),2), ROUND(SUM(cost),2),
ROUND (AVG(cost),2)
FROM part;
GROUP BY product_id;
How will the results differ?
A. The results will be the same, but the display will
differ.
B. Statement 1 will only display one row of results,
statement 2 could display more than one.
C. Statement 1 will display a result for each part,
statement 2 will display a result for each product.
D. One of the statements will generate an error.

The answer is B, but I think it's C. Looks like C is more reasonable.

Please help me, many thanks,

[Prettyman]

1. "Where" you can use single row function.
2. All are group functions in your select list so only one row return for the grouped value for all parts!

[jmniu]

another one:
3.evaluate this command:
select id_number,description, manufacturer_id
from inventory
where price >7.00
order by manufacturer_id;

all the columns of the invetory table have non_unique indexes, which
clause will most likely use an index?

A. select id_number,description,manufacture_id
B. from inventory
C. where price >7.00
D. order by manufacturer_id

[Prettyman]

Surely C

[jmniu]

4.
select id_number, description
from inventory
where id_number=
(select id_number
from inventory
where quantity>500 or price>5.00)

which clause will cause an error?
A.select id_number, description
B.where id_number=
C.select id_number
D.where quantity>500 or price>5.00
E.none

I don't know which one I should choose.
B is reasonable because if change "=" to "in"
C is reasonable because if run the query, it shows:ERROR at line 4:
ORA-01427: single-row subquery returns more than one row
D is reasonable because if change where clause to return on row in subquery.

Can anybody tell me which is the answer? Many thanks,

[Prettyman]

The only error is ERROR at line 4:
ORA-01427: single-row subquery returns more than one row
So select C.

[Rookie]

I am afraid your answer is incorrect.
No - The error is in the Where clause. You can't use an = operator when the possiblity exists for a return of more than one row.

[jgmagnus]

I think Prettyboy was referring to answer B even though he stated 'C'. You CAN use an '=' sign but that has the possibility of showing the error for the reason you stated Rookie.

you would normally use the IN operator in these cases.

[Prettyman]

I have tried it in SQL*Plus. Error line 4, so C.

[jmniu]

I still don't know which one is correct.

B is correct because general speaking we would like to change "=" to "in"
C is correct because SQL*PLUS return error is line 4.
D is correct because if change query condition to get single
row return(in STS the answer is D)

Well, I didn't have this question in my exam.