Win a copy of Functional Reactive Programming this week in the Other Languages forum!

# Help understanding a SQL certification practice test question

Janeice DelVecchio
Saloon Keeper
Posts: 1809
12
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
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?

Sheriff
Posts: 14691
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:
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 ....]

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.

Janeice DelVecchio
Saloon Keeper
Posts: 1809
12
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.