Re: 2 SQL test questions

In article <3A11B0B0.B3468035_at_wt.net>,
  girlgeek_at_wt.net wrote:
> I just returned from taking the SQL & PL/SQL exam. (Passed - Whew!)
>
> Two questions puzzle me. Maybe someone else has an idea of the
 correct
> answers:
>
> 1.Create a report that will show net profit per product if the cost
 is
> increased 10% and the sale price is increased 25%. The product table
> follows
>
> Products table
> Id number(3) pk
> Sale number (8,2)
> Cost number (8,2)
>
> Given the query:
>
> Select id, sale * 1.25 - cost * 1.1 net
> From products;
>
> Which happens:
>
> a. will supply only requested info
> b. will not supply requested info
> c. needs a function in the select clause
> d. The order of the operations needs to be reversed
>
> I was stuck between a & c. The query should work, but it would be
> better with a NVL function in case some
> rows have a null for cost or sale. What is your take?
>
> 2. Mr King is President of the company. Four managers report to him
 and
> all other employees report to these four managers.
>
> The query:
>
> select e.name
> from emp e
> where e.id not in
> (select m.mgr
> from emp m);
>
> returns 'no rows selected' Why?
>
> a. all employees have a manager
> b. no employees have a manager
> c. a null is returned from the subquery
> d. not in is not allowed in a subquery
>
> Any ideas?
>
> Thanks,
> Claudia
>

2. The subquery will return all employee id's who are managers. The NOT IN means the main query is attempting to retrieve employee id's who are not managers. You would think that the answer would be that all employees are managers.

a is definitely wrong.
d is definitely wrong

b is almost equivalent to c. They are technically equivalent if the table accepts nulls for id column (all employees having no manager would have given you a null for the subquery). But encountering this in real life will not prompt you to ask why.

c will always be true. If one of the returned id from the subquery is null, you'd get that 'no rows selected'.

I'd go with c, because a lot of people will be puzzled by if they encounter it, and hence will ask the question Why?

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Nov 14 2000 - 16:47:06 CST