From: Rajesh Kumar Mallah <mallah@trade-india.com>
Organization: Infocom Network Limited
To: Metnetsky <matt@uberstats.com>, pgsql-sql@postgresql.org
Subject: Re: HardCORE QUERY HELP!!!
Date: Mon, 3 Mar 2003 11:55:58 +0530
User-Agent: KMail/1.5
References: <hOednbvBOI8QG_yjXTWcqQ@giganews.com>
In-Reply-To: <hOednbvBOI8QG_yjXTWcqQ@giganews.com>
Cc: techies@trade-india.com
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200303031155.58203.mallah@trade-india.com>


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.