Some questions about SQL Oracle

Question

I need a help about this questions in SQL Oracle :

Question1 about Hierarchical retrieved: I think that the response is D and E :

Select * from Employees
Start With Employee_ID=100
Connect By PRIOR Employee_ID=Manager_ID;

Without add the Where clause !

Question 2 about synonyms : I think that the response is D.

Question 3 about indexes : I think that the response is D.

Thanks in advance. Best regards.

Answer 1

1. The result of your query would include employee 100, who presumably doesn't report to themselves - the question is only looking for people under them. You could use a where clause to exclude that user. (The data shown doesn't have an employee 100; but may just be a subset of the data.)

2. The green highlighting suggests the question setter agrees with you that D is correct; but you seem to have chosen B. Which would also work, but only if OE had the create public synonym system privilege, which hasn't been stated. OE wouldn't be able to do that by default.

3. For D the list_price > 1000 condition cannot be evaluated using that index. It's feasible there could be an index on list price and Oracle would use both indexes, but much more likely it would use a full table scan. For C the index can be used to check both the values - you can think of it as (UPPER(product_name) = 'LASERPRO' OR UPPER(product_name) = 'Cable'). It can't match Cable of course, as that is mixed case not all uppercase, but that doesn't prevent the index being used to try to find a match.