sql questions

[ballack]

how to write sql to list departments for which no employeees exists?

and also

finding most recently hired employee in each department.order by hiredate.

[vijayaditya]

select deptno from dept where deptno not in (select deptno from emp)

[jkoopmann]

SQL> create table dept (deptno number, deptname char(10));
SQL> insert into dept values (1,'one');
SQL> insert into dept values (2, 'two');
SQL> insert into dept values (3, 'three');

SQL> create table emp (empid number, ename char(10), deptno number, hiredate date);
SQL> insert into emp values (10,'john',1,sysdate-10);
SQL> insert into emp values (11,'mary',1,sysdate);
SQL> insert into emp values (12,'alex',2,sysdate-5);

As prior post shows for 'not employees in a department'
SQL> select deptno from dept where deptno not in (select deptno from emp);
DEPTNO
----------
3

For the most recently hired employee in each department, order by hiredate

select a.deptno, b.deptname, a.empid, a.ename, a.hiredate
from emp a, dept b
where a.deptno = b.deptno
and a.hiredate = (select max(c.hiredate) from emp c
where c.deptno = a.deptno)
order by a.hiredate;

DEPTNO DEPTNAME EMPID ENAME HIREDATE
---------- ---------- ---------- ---------- ---------
2 two 12 alex 29-APR-06
1 one 11 mary 04-MAY-06