考试题：这个SQL语句怎么写？

2003-06-17 04:49:21 90 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

inforum 墨衍会员 · AI 创作全网分发关注 + 加入社区

250道《数据库》在线考试题，练过一遍，直接飞起！ 250道数据库相关试题看完成绩直接飞起！！！！！阅读详情

设
S(SNO，SNAME， AGE， SEX)，其属性分别表示学号、学生姓名、年龄、性别。
SC(SNO，CNO，GRADE)，其属性分别表示学号、课程号、成绩。
请教："各门功课比王小华好的有哪些同学"怎么做？

...全文

【没有网络，安装python包】如何从 PyPI 下载并安装 Plotly 的离线包在某些情况下，您可能需要在没有互联网连接的环境中安装 Python 包，例如在飞机上、在某些受限制的网络环境中或在需要离线安装的场景下。以下是如何在 PyPI（Python Package Index）下载 Plotly 包并进行离线安装的详细步骤。阅读详情

inforum

数据库相关 2,508 成员 88,437 内容

+ 关注 + 加入社区

切换为时间正序

inforum 2003-06-18

打赏
举报

要求学生姓名这样做：
select sname from S where sno in
(select b.sno from S a , SC b
where a.SNO=b.SNO and a.sno=b.sno and b.grade >
(select grade
from S c , SC d
where c.sno=d.sno and c.sname='王小华' and d.cno=b.cno )
group by b.sno
HAVING count(b.CNO)>= ( SELECT COUNT(*) FROM SC,S WHERE S.SNO = SC.SNO AND S.SNAME = '王小华' )
)

inforum 2003-06-17

打赏
举报

不用left join 也可：
select b.sno, count(b.CNO) from S a , SC b
where a.sno=b.sno and b.grade >
(select grade
from S c , SC d
where c.sno=d.sno and c.sname='王小华' and d.cno=b.cno )
group by b.sno
HAVING count(b.CNO)>= ( SELECT COUNT(*) FROM SC,S WHERE S.SNO = SC.SNO AND S.SNAME = '王小华' )

现在结帖！

inforum 2003-06-17

打赏
举报

结果有了，谢谢2位！
综合了2位，最后答案如下：
select b.sno, count(b.CNO) from S a left join SC b on a.sno=b.sno
where b.grade >
(select grade
from S c left join SC d on c.sno=d.sno
where c.sname='王小华' and d.cno=b.cno )
group by b.sno
HAVING count(b.CNO)>= ( SELECT COUNT(*) FROM SC,S WHERE S.SNO = SC.SNO AND S.SNAME = '王小华' )

linzhisong 2003-06-17

打赏
举报

select * from c where
(select count(*) from s a left join sc b on a.sno=b.sno
where b.grade >(select grade from s c left join sc d on c.sno=d.sno where s.sname='小华' and d.cno=b.cno and a.s.sno=c.sno))=(SELECT COUNT(*) FROM SC,S WHERE S.SNO = SC.SNO AND S.SNAME = '王小华')

inforum 2003-06-17

打赏
举报

to firetoucher(风焱) :
OK! 给U短信!THX

firetoucher 2003-06-17

打赏
举报

to inforum(坛中人,来捧个场！)
有结果，给我短信，不管正不正确

inforum 2003-06-17

打赏
举报

to linzhisong(無聊) ( ) :
你的代码之列出了部分功课，我要的是各门功课比王小华好

再来看看firetoucher(风焱)的代码，请稍候...

firetoucher 2003-06-17

打赏
举报

SELECT SNAME FROM
S,
(SELECT SNO,COUNT(*) AS C FROM SC ,
(SELECT SNO,CNO,GRADE FROM SC,S WHERE S.SNO = SC.SNO AND S.SNAME = '王小华') A
WHERE SC.CNO = A.CNO AND SC.GRADE > A.GRADE) B
WHERE S.SNO = B.SNO AND B.C = (SELECT COUNT(*) FROM SC,S WHERE S.SNO = SC.SNO AND S.SNAME = '王小华')

linzhisong 2003-06-17

打赏
举报

select * from s a left join sc b on a.sno=b.sno

where b.grade >(select grade from s c left join sc d on c.sno=d.sno where s.sname='小华' and d.cno=b.cno)

考试题：这个SQL语句怎么写？

相关推荐