HardCORE QUERY HELP!!!

Q: list employee name, job, manager name and salary for those whose salary
is more than their manager

The quetion may seem kinda dumb, it's for a class and my professor has a
thing for brain teaser type questions. I attempted doing this with rename
and a natural join but I can't get the rename to work I think. Any help
would be much appreciated.
Thanx

Table Definition:
create table emp
(empno numeric(4) not null,
ename varchar(10) not null,
job varchar(10) not null,
mgr numeric(4),
hiredate date,
sal numeric(6) not null,
comm numeric(4),
deptno numeric(4) not null,
primary key(empno));

Example Data:
values(7369,'Martinez','Clerk',7902,'1990-12-17',2800,null,20);
values(7499,'Jeter','Sales',7698,'1991-02-20',3600,1300,30);
values(7521,'Knoblauch','Sales',7698,'1998-02-22',3250,1500,30);
values(7566,'Torre','Manager',7839,'1991-04-02',4975,null,20);
values(7654,'Strawberry','Sales',7698,'1994-09-28',2250,2400,30);
values(7698,'Dimago','Manager',7839,'1995-05-01', 4850, null,30);
values(7782,'Williams','Manager',7839,'1997-06-09',4450,null,10);

I think your data is insufficient,
there is no such employee in provided sample data.
the query used is included.

test=# SELECT * from emp;
+-------+------------+---------+------+------------+------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+------------+---------+------+------------+------+------+--------+
| 7369 | Martinez | Clerk | 7902 | 1990-12-17 | 2800 | | 20 |
| 7499 | Jeter | Sales | 7698 | 1991-02-20 | 3600 | 1300 | 30 |
| 7521 | Knoblauch | Sales | 7698 | 1998-02-22 | 3250 | 1500 | 30 |
| 7566 | Torre | Manager | 7839 | 1991-04-02 | 4975 | | 20 |
| 7654 | Strawberry | Sales | 7698 | 1994-09-28 | 2250 | 2400 | 30 |
| 7698 | Dimago | Manager | 7839 | 1995-05-01 | 4850 | | 30 |
| 7782 | Williams | Manager | 7839 | 1997-06-09 | 4450 | | 10 |
+-------+------------+---------+------+------------+------+------+--------+
(7 rows)

Time: 1.21 ms
test=# SELECT a.* from emp a where sal > ( select sal from emp where empno=a.mgr);
+-------+-------+-----+-----+----------+-----+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+-------+-----+-----+----------+-----+------+--------+
+-------+-------+-----+-----+----------+-----+------+--------+
(0 rows)

Time: 1.39 ms
test=#

On Sunday 02 March 2003 09:50 am, Metnetsky wrote:
> Q: list employee name, job, manager name and salary for those whose salary
> is more than their manager
>
> The quetion may seem kinda dumb, it's for a class and my professor has a
> thing for brain teaser type questions. I attempted doing this with rename
> and a natural join but I can't get the rename to work I think. Any help
> would be much appreciated.
> Thanx
>
>
> Table Definition:
> create table emp
> (empno numeric(4) not null,
> ename varchar(10) not null,
> job varchar(10) not null,
> mgr numeric(4),
> hiredate date,
> sal numeric(6) not null,
> comm numeric(4),
> deptno numeric(4) not null,
> primary key(empno));
>
> Example Data:
> values(7369,'Martinez','Clerk',7902,'1990-12-17',2800,null,20);
> values(7499,'Jeter','Sales',7698,'1991-02-20',3600,1300,30);
> values(7521,'Knoblauch','Sales',7698,'1998-02-22',3250,1500,30);
> values(7566,'Torre','Manager',7839,'1991-04-02',4975,null,20);
> values(7654,'Strawberry','Sales',7698,'1994-09-28',2250,2400,30);
> values(7698,'Dimago','Manager',7839,'1995-05-01', 4850, null,30);
> values(7782,'Williams','Manager',7839,'1997-06-09',4450,null,10);
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

--

Regds
Mallah

----------------------------------------
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)

Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.

Metnetsky wrote:
> Q: list employee name, job, manager name and salary for those whose salary
> is more than their manager
>
> The quetion may seem kinda dumb, it's for a class and my professor has a
> thing for brain teaser type questions. I attempted doing this with rename
> and a natural join but I can't get the rename to work I think. Any help
> would be much appreciated.
> Thanx
One more version of query you need:

select
e.ename,
e.job,
m.ename as managername,
e.salary
from
emp as e
join emp as m on (e.mgr=m.empno and e.salary>m.salary);

As Rajesh said - for your example data there is no result for such query.

Regards,
Tomasz Myrta

This is your prof speaking:
"DO YOUR OWN HOMEWORK YOU CHEATER!"

Seriously,

I'll give you a hint:
Try using
SELECT * FROM emp WHERE
(
sal >
(SELECT sal FROM emp AS EMP2
WHERE (EMP2.mgr=emp.mgr))
)

or something like that.
Use a ALIAS for the same table to distinguish them.

Hmm I am doing your homework.
Did I deserve a beer now?
;-)

Regards,
Erwin

Metnetsky <matt(at)uberstats(dot)com> writes:

> It's for a class and my professor has a thing for brain teaser type
> questions.

Incidentally, TAs and Profs aren't stupid, and have been known to check on
newsgroups and mailing lists for students asking for people to do their
homework for them.

--
greg

Then they'll be happy not to have to do any work, considering I put
notices on my assignments stating that I received help and from where.

~ Metnetsky

On Mon, 2003-03-03 at 15:46, Greg Stark wrote:
>
> Metnetsky <matt(at)uberstats(dot)com> writes:
>
> > It's for a class and my professor has a thing for brain teaser type
> > questions.
>
> Incidentally, TAs and Profs aren't stupid, and have been known to check on
> newsgroups and mailing lists for students asking for people to do their
> homework for them.
>
> --
> greg
>