pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHardCORE QUERY HELP!!!
6+ messages / 6 participants
[nested] [flat]
* HardCORE QUERY HELP!!!
@ 2003-03-02 04:20 Metnetsky <matt@uberstats.com>
0 siblings, 4 replies; 6+ messages in thread
From: Metnetsky @ 2003-03-02 04:20 UTC (permalink / raw)
To: pgsql-sql
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);
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: HardCORE QUERY HELP!!!
@ 2003-03-03 06:25 Rajesh Kumar Mallah <mallah@trade-india.com>
parent: Metnetsky <matt@uberstats.com>
3 siblings, 0 replies; 6+ messages in thread
From: Rajesh Kumar Mallah @ 2003-03-03 06:25 UTC (permalink / raw)
To: Metnetsky <matt@uberstats.com>; pgsql-sql; +Cc: techies@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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: HardCORE QUERY HELP!!!
@ 2003-03-03 12:41 Tomasz Myrta <jasiek@klaster.net>
parent: Metnetsky <matt@uberstats.com>
3 siblings, 0 replies; 6+ messages in thread
From: Tomasz Myrta @ 2003-03-03 12:41 UTC (permalink / raw)
To: Metnetsky <matt@uberstats.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: HardCORE QUERY HELP!!!
@ 2003-03-03 18:24 Erwin Moller <erwin@_removespam_dexus.nl>
parent: Metnetsky <matt@uberstats.com>
3 siblings, 0 replies; 6+ messages in thread
From: Erwin Moller @ 2003-03-03 18:24 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: HardCORE QUERY HELP!!!
@ 2003-03-03 20:46 Greg Stark <gsstark@mit.edu>
parent: Metnetsky <matt@uberstats.com>
3 siblings, 1 reply; 6+ messages in thread
From: Greg Stark @ 2003-03-03 20:46 UTC (permalink / raw)
To: Metnetsky <matt@uberstats.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: HardCORE QUERY HELP!!!
@ 2003-03-03 20:57 Matthew Metnetsky <matt@uberstats.com>
parent: Greg Stark <gsstark@mit.edu>
0 siblings, 0 replies; 6+ messages in thread
From: Matthew Metnetsky @ 2003-03-03 20:57 UTC (permalink / raw)
To: Greg Stark <gsstark@mit.edu>; +Cc: pgsql-sql
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
>
Attachments:
signature.asc (application/pgp-signature, 188B) download
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2003-03-03 20:57 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-02 04:20 HardCORE QUERY HELP!!! Metnetsky <matt@uberstats.com>
2003-03-03 06:25 ` Rajesh Kumar Mallah <mallah@trade-india.com>
2003-03-03 12:41 ` Tomasz Myrta <jasiek@klaster.net>
2003-03-03 18:24 ` Erwin Moller <erwin@_removespam_dexus.nl>
2003-03-03 20:46 ` Greg Stark <gsstark@mit.edu>
2003-03-03 20:57 ` Matthew Metnetsky <matt@uberstats.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox