h******t
发帖数: 27 | 1 how many students participate in the most popular student activity
what's the name of this actiity
ACTIVITY (
actid number ,
activity_name varchar2(25)
);
PARTICIPATES_IN (
stuid number,
actid number
); | w*****g
发帖数: 198 | 2 another one? :)
is this your homework assignment for database class?
【在 h******t 的大作中提到】
: how many students participate in the most popular student activity
: what's the name of this actiity
: ACTIVITY (
: actid number ,
: activity_name varchar2(25)
: );
: PARTICIPATES_IN (
: stuid number,
: actid number
: );
| h******t
发帖数: 27 | 3
come on, help me, due tomorrow
【在 w*****g 的大作中提到】
: another one? :)
: is this your homework assignment for database class?
| p*****y
发帖数: 69 | 4 总觉得这种做法不太好.
【在 h******t 的大作中提到】
:
: come on, help me, due tomorrow
| s*i
发帖数: 5025 | 5 creat view A-N(actid,c)
as select actid, count(*)
from participates_in
group by actid;
select activity_name, Max(c)
from A-N, ACTIVITY
where A-N.actid=activity.actid;
【在 h******t 的大作中提到】
:
: come on, help me, due tomorrow
| h******t
发帖数: 27 | 6
不懂就问,有啥不好
不懂装懂不好,太害羞也不好
【在 p*****y 的大作中提到】
: 总觉得这种做法不太好.
| h******t
发帖数: 27 | 7 I got in this way, never thought use view
thanks sui
SELECT C1, activity_name
FROM (SELECT COUNT(stuid) C1, activity_name
FROM (SELECT Participates_in.stuid, Activity.activity_name
FROM Participates_in , Activity
WHERE Participates_in.actid=Activity.actid)
GROUP BY activity_name)
WHERE C1= (SELECT MAX(C2) FROM (SELECT COUNT(stuid) C2
FROM Participates_in GROUP BY Participates_in.actid));
【在 s*i 的大作中提到】
: creat view A-N(actid,c)
: as select actid, count(*)
: from participates_in
: group by actid;
: select activity_name, Max(c)
: from A-N, ACTIVITY
: where A-N.actid=activity.actid;
| q*j
发帖数: 156 | 8 Is there any requirements to use just one query? "create view" is
considered as one query itself.
If it's homework question, you'd better check the requirement, otherwise,
"create view" is a more intuitive way to do the query.
【在 h******t 的大作中提到】
: I got in this way, never thought use view
: thanks sui
: SELECT C1, activity_name
: FROM (SELECT COUNT(stuid) C1, activity_name
: FROM (SELECT Participates_in.stuid, Activity.activity_name
: FROM Participates_in , Activity
: WHERE Participates_in.actid=Activity.actid)
: GROUP BY activity_name)
: WHERE C1= (SELECT MAX(C2) FROM (SELECT COUNT(stuid) C2
: FROM Participates_in GROUP BY Participates_in.actid));
| s*i
发帖数: 5025 | 9 I remember we can not embed another SELECT clause in FROM clause!
However, I guess you can JOIN some tables in FROM clause.
【在 h******t 的大作中提到】
: I got in this way, never thought use view
: thanks sui
: SELECT C1, activity_name
: FROM (SELECT COUNT(stuid) C1, activity_name
: FROM (SELECT Participates_in.stuid, Activity.activity_name
: FROM Participates_in , Activity
: WHERE Participates_in.actid=Activity.actid)
: GROUP BY activity_name)
: WHERE C1= (SELECT MAX(C2) FROM (SELECT COUNT(stuid) C2
: FROM Participates_in GROUP BY Participates_in.actid));
| q*j
发帖数: 156 | 10 MySQL can't, but other commercial DB can.
【在 s*i 的大作中提到】
: I remember we can not embed another SELECT clause in FROM clause!
: However, I guess you can JOIN some tables in FROM clause.
| s*i
发帖数: 5025 | 11 I don't even believe ORACLE can.
【在 q*j 的大作中提到】
: MySQL can't, but other commercial DB can.
| h******t
发帖数: 27 | 12
I use Oracle, and it works
【在 s*i 的大作中提到】
: I don't even believe ORACLE can.
|
|
