Redian新闻
>
How to write this SQL? Urgent!!!
avatar
How to write this SQL? Urgent!!!# Database - 数据库
c*l
1
If I have two relations:
Employee(SSN, salary, name) SSN is primary key
Position(pID, pName, state) pID is primary key, state is the US state in
which a position released
And one relationship which link the two tables:
EP(SSN, pID, tDate) SSN, pID is primary key. tDate is the date on which
position was taken
Now I wanna write a SQL to retrieve data that meet requirement below from the
database: List the last four positions that took place in Illinois
Please give my some ideas, thank
avatar
n*a
2
The question can be interpreted by different ways.
I took it mean that find the lastest four dates and
give me every position.
In Oracle 8.1.6 upper, you could do:
select pName from (
select position.pName,
dense_rank() over (order by EP.tDate desc nulls last) dr
from EP, Position
where EP.pID=position.pID and Position.state='Illinois'
) where dr <= 4;
If your database supports inline view and rownum, you could do
select pName from (
select position.pName
from EP, Position
where EP.pID=position.

【在 c*********l 的大作中提到】
: If I have two relations:
: Employee(SSN, salary, name) SSN is primary key
: Position(pID, pName, state) pID is primary key, state is the US state in
: which a position released
: And one relationship which link the two tables:
: EP(SSN, pID, tDate) SSN, pID is primary key. tDate is the date on which
: position was taken
: Now I wanna write a SQL to retrieve data that meet requirement below from the
: database: List the last four positions that took place in Illinois
: Please give my some ideas, thank

相关阅读
logo
联系我们隐私协议©2024 redian.news
Redian新闻
Redian.news刊载任何文章,不代表同意其说法或描述,仅为提供更多信息,也不构成任何建议。文章信息的合法性及真实性由其作者负责,与Redian.news及其运营公司无关。欢迎投稿,如发现稿件侵权,或作者不愿在本网发表文章,请版权拥有者通知本网处理。