Re: HardCORE QUERY HELP!!!

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.