[mjzsakae]

My qn is at the bottom of the pic..

Its from an exam paper.. Really no ideal how to write the SQL statement.. Pls help.. Thanks..

Additional:
1. Write a query to display the name, job and salary for all employees who work in the ACCOUNTING department.

My ans: (pls check for me.. thanks guys)

SELECT e.ename, e.job, sal
FROM emp e, dept d
WHERE d.deptno = e.deptno
AND d.deptno IN (10)

2. Write a query to display the employee number, name, salary for all employees who earn more than the average salary of the ACCOUNTING department.

My ans:

SELECT e.empno, e.ename, e.sal
FROM emp e
WHERE e.sal >
(
SELECT AVG(sal)
FROM emp e, dept d
WHERE e.deptno = d.deptno
)

[icyheart]

SELECT e.name, d.loc
FROM emp e, dept d
WHERE e.deptno = d.deptno

no idea how to get managers' name. im still learning =p

[bangbangbogi]

u need to use a join statement

[mjzsakae]

lOlx.. getting is the manager name is the problem i am facing too -.-"
the employee table is self referencing..

bangbangbogi.. please provide solution.. the query statements.. thanks.. =x

[SaLvAtoOo]

select e.ename, d.loc, e1.ename
from emp as e left outer join emp as e1 on e.empno = e1.mgr, dept d
where e.deptno = d.deptno

should b something like that..
u need outer join to include the president in the results...

[mjzsakae]

Originally Posted by SaLvAtoOo

select e.ename, d.loc, e1.ename
from emp as e left outer join emp as e1 on e.empno = e1.mgr, dept d
where e.deptno = d.deptno ...

thanks salvatooo.. nice one but chim.. haha

i added 2 more qns with my own answers.. can please check for me? thanks.. shld be much more easier compared to the first qn.. haha

[programmer]

I did not check if my syntax is correct but I think the structure is right. For your reference...

my ans for q1)
SELECT E.ENAME, E.JOB, E.SAL
FROM emp E, dept D
WHERE E.DEPTNO = D.DEPTNO && D.DNAME = 'ACCOUNTING'


for q2)
SELECT e.empno, e.ename, e.sal
FROM emp e
WHERE e.sal >
(
SELECT AVG(sal)
FROM emp e, dept d
WHERE e.deptno = d.deptno && D.DNAME = 'ACCOUNTING'
)

[mjzsakae]

programmer.. qn1 i tink both yr way and my way is applicable ba..
qn2 u r correct, i forgot to indicate AND d.deptno IN (10)

thanks.. =)

[FanaticX]

hmm for question 1 i thought should be

SELECT emp.ENAME, emp.JOB, emp.SAL FROM emp WHERE emp.DEPTNO = 10

cause DEPTNO 10 is refering as ACCOUNTING as DNAME, so i assume its a foreign to primary key thing. Correct me if i'm wrong cause i not expert in SQL.