aspose file tools*
The moose likes Oracle/OAS and the fly likes Help understanding a SQL certification practice test question Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Help understanding a SQL certification practice test question" Watch "Help understanding a SQL certification practice test question" New topic
Author

Help understanding a SQL certification practice test question

Janeice DelVecchio
Saloon Keeper

Joined: Sep 14, 2009
Posts: 1613
    
  10

This question is in the Oracle 1Z0-047 practice questions on their website.... It looks a lot like the questions on the exam. I know this because I failed, once (because of questions exactly like this). Now I am going to spend more time here asking things when I don't understand.


1. View the Exhibit and examine the structure of the EMP and SALGRADE tables. You want to display the names of all employees whose salaries belong to GRADE 5. Which SQL statements give the required output? (Choose all that apply)
A.SELECT ename
FROM emp JOIN salgrade
USING (sal BETWEEN losal AND hisal) AND grade = 5;

B.SELECT ename
FROM emp e JOIN salgrade s
ON (e.sal BETWEEN s.losal AND s.hisal AND s.grade = 5);

C.SELECT ename
FROM emp e JOIN salgrade s
ON (e.sal BETWEEN s.losal AND s.hisal) AND s.grade = 5;

D.SELECT ename
FROM emp e JOIN salgrade s
ON (e.sal BETWEEN s.losal AND s.hisal) WHERE s.grade=5;

E.SELECT ename
FROM emp e JOIN salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal AND s.grade = 5;



Okay, here's my questions....
1. Why do we need to go by hisal or losal at all? Can't we just use grade?
2. What, exactly, is the difference between each answer? Really they look the same to me.
2b. Is there a concept I'm completely missing out on? Some resource I can use to help me grasp this easier?


When you do things right, people won't be sure you've done anything at all.
Christophe Verré
Sheriff

Joined: Nov 24, 2005
Posts: 14687
    
  16

1. Because you have to make some kind of relation between EMP and SALGRADE. The salary of the employee (e.sal) determines its grade (s.grade). The salary has to be between the lower salary price (s.losal) and the higher salary price (s.hisal)

Picture yourself some data:
SALGRADE
1 10,000 11,999
2 12,000 12,999
3 13,000 13,999
4 14,000 14,999
5 15,000 15,999

EMP
1 Janeice Bartender 2009/09/14 13,500 5

To get employee 'Janeice' grade, you'll have to find out where her salary (13,500) lies. It's between 13,000 and 13,999, so her grade is 3.
Now imagine there are much more employees, and you only want those whose grade is 5. You'll still have to check where their salary lies, and check that the grade is 5.


About the syntax, INNER JOIN statements can be written two different ways :
1. ...[INNER] JOIN ON condition [WHERE ....]
2. ...[INNER] JOIN USING ( column1, column2... ) [WHERE ....]

Answer A's syntax is clearly not right. USING should be followed by columns, not by a condition.
Answer B looks fine. The join condition is unnecessarily put between brackets.
Answer C looks fine too. Same as B, with unnecessarily brackets placed elsewhere.
Answer D looks fine too. A WHERE condition can follow the JOIN clause. The resulting condition is the same as B and C.
Answer E is not a valid syntax. The inner join has to be followed by either ON or USING.

[My Blog]
All roads lead to JavaRanch
Janeice DelVecchio
Saloon Keeper

Joined: Sep 14, 2009
Posts: 1613
    
  10

Christophe -

This helps me see something, I think, that is thoroughly important.

I have been making things really, really, really difficult. Instead of worrying about the general syntax (use of using or on), I have been trying to figure out order of operations and how the changes in parenthesis make things different. I stared at this question, knowing the answer, but not seeing why because I was tearing things apart too deeply.

B C and D look the same because they are the same. The others look the same too, but clearly the concept is the use of USING and ON and simple join syntax.

Thanks so much.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Help understanding a SQL certification practice test question
 
Similar Threads
How to get a Table Schema from oracle database to Java Programs...?
using aliases
second highest value
Finding an average as part of a join (Ora 11)