SQL Subquery and joins

Question

There are 2 tables available

EMP(empname PK, empno, sal, comm, deptno, hiredate, job, mgr)

DEPT(depnto, dname, loc)

The queries are

a)Display the ename and dname who is earning the first highest salary

They have given a statement this has to be done using only subqueries and Join

So i started like this:

select A.ename, B.dname from emp A, dept B where A.deptno=B.deptno

and i tried various perm&comb, but i couldn't get the join statement...

This is not a homework problem, i am just trying to solve the exercise problem given in my textbook..Thanks in advance

Answer 1

You can try following query:

SELECT emp.ename,dept.dname FROM emp
        JOIN dept ON emp.deptno=dept.deptno
        WHERE emp.sal=(SELECT MAX(sal) FROM emp)

Answer 2

To get the record with the max salary I've Ordered by e.sal DESC. This will order the records in terms of salary, with the highest at the top (DESC = Descending, and therefore highest-lowest).

Then I've used TOP 1 to only return 1 record.

To get the dname I've joined the tables relating the 2 deptno columns.

SELECT TOP 1 e.empname
            ,d.dname
FROM [EMP] e
JOIN [DEPT] d ON d.deptno=e.deptno
ORDER BY e.sal DESC

I hope this helps