Practicing SQL

[MarcLara]

I am trying to create a SELECT statement that displays ename, deptno, sal from the scott.emp table on people who earn more than the average sal in their department. NOT the total average of all the sal but the average from each departments. Please help

 

[carp]

SELECT ename, deptno, sal
FROM emp a
WHERE sal > (SELECT avg(sal)
FROM emp b
WHERE a.deptno = b.deptno);

 

[MarcLara]

Thanks Carp... You the man
I am pretty new to Oracle and I appreciate you guys helping people like me.
OK so here's another one if you don't mind or for anybody.
I ran against scott.emp

SELECT worker.ename
FROM emp worker
WHERE worker.empno not in
(SELECT manager.mgr
FROM emp manager)

why am I getting "no rows selected"
I understand that if I take off the "not" in the where clause I would get something. This means that they are a manager for somebody. So shouldn't the SQL above show the people that are not managers?

 

[VSU]

It 'cause exists MGR IS NULL

[tt]
SELECT worker.ename
FROM emp worker
WHERE worker.empno not in
(SELECT manager.mgr
FROM emp manager WHERE mgr IS NOT NULL)
^^^^^^^^^^^^^^^^^^^^^
[/tt]

 

[myheadsashed]

How do I delete selective data from a table using a Sql Script...?

There is a lot of false data in the table that requires purging/deleting...? This is due to testing of the System over a period of months.!

Is there an easy way of deleting data as I cannot drop the table and recreate it as there is valid data within the table I need..?