Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Practicing SQL

Status
Not open for further replies.

MarcLara

Technical User
Dec 7, 2001
54
US
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
 
SELECT ename, deptno, sal
FROM emp a
WHERE sal > (SELECT avg(sal)
FROM emp b
WHERE a.deptno = b.deptno);
 
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?
 
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]
 
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..?

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top