dont understand this query

[php_novice2007]

Hi all,

 

I've got the following two tables schemas:

 

emp(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO), primary key is EMPNO

dept(DEPTNO, DNAME), primary key is DEPTNO

 

DEPTNO is a foreign key in the Emp table.

 

Anyway, the query is to find employees whose salary is greater than the average salary in their department.

 

I've got a view created called empd(EMPNO, ENAME, JOB, SAL, COMM, DNAME).

 

The answer for this query from my teacher is this:

 

SELECT e1.ename, e1. dname, e1.sal FROM empd e1 where e1. sal > (SELECT avg(sal)

FROM emp where e1.dname = dname)

 

I have tried this query on the database and it does work, but I don't understand why it should work. In the inner query, I am assuming that "sal" and "dname" are columns of emp? If "dname" is an attribute of the emp table than the query makes sense, but it is not an attribute, so why does this query work?

 

(I have sent an email to my teacher asking this but he hasn't replied and I've got a test tomorrow  )

 

 

[Barand]

You are selecting from the view, empd, which does have a DNAME col

 

I'd do this

SELECT e1.ename, d1.dname, e1.sal
FROM emp e1
INNER JOIN dept d1 ON e1.deptno = d1.deptno
INNER JOIN (SELECT deptno, AVG(sal) as avsal FROM emp GROUP BY deptno) as X
  ON e1.deptno = X.deptno
WHERE e1.sal > X.avsal