Thread: 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

Metnetsky <matt@uberstats.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@uberstats.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
>

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