MS SQL SERVER 学生成绩问题（高手请进)

2008-10-16 05:25:25 266 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

超经典SQL练习题，做完这些你的ＳＱＬ就过关了 SQL 联系题　使用方法：我用的数据库是 Ms SQL Server 2008 ,练习时应当自己建数据，自己先思考，切勿急躁翻答案！否则效果减半，做完这些，恭喜你，你的 SQL 就算过关了。阅读详情

用一条SQL语句查询出二门以上（包含二门）不及格的学生姓名以及平均成绩，表内容如下：
S 学生表(Sno,Sname)
SC课程关系表(Sno,Cno,Scgrade)
C课程表(Cno,Cteacher)
s
Sno Sname
1 张三
2 李四
3 王五
4 赵六
5 孔子
6 老子

C
Cno Cteacher
1 嘟嘟
2 吱吱
3 李明

SC
Sno Cno Scgrade
1 1 89.5
1 2 78.6
1 3 46.5
2 1 98
2 2 78
3 1 58.5
3 2 86.5
3 3 59.5
4 1 78
5 1 68

...全文

MS SQL Server fill factor 面试题第1章：引言大家好！听说牛客的大佬们很会选offer，男生bg武大本硕测绘，本地人，有房但家里没多少米，现在手上的二选一招行武汉分行的fintech培训生，hr画饼画的很大，实。美的：已签三方，base佛山，月收入高500，年终少，公积金基数和比例都低，而且地点不是很喜欢，以后估计也要跳江浙沪，岗位是电控软件工程师，感觉以后也只能去家电。女生，想留天津，阿里云测开，北京，开的40，七八点能下班，不太喜欢岗位和业务北京银行总行第一年25第五年40，7点下班，带户口天津移动>15，具体不清楚，855。阅读详情

yzgnick

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

wxg22526451 2008-10-16

打赏
举报

select  a.Sname,

	avgScgrade=(select AVG(Scgrade) from @SC where Sno=a.Sno)

from  @s a 

join  @SC b 

on a.Sno=b.Sno and b.Scgrade<60 

group by a.Sno,Sname having count(*)>=2 

/*

Sname avgScgrade

----- ---------------------------------------

王五    68.166666

*/

等不到来世 2008-10-16

打赏
举报

create table S (Sno int,Sname varchar(10)) 

create table SC(Sno int,Cno int,Scgrade numeric(5,1))

go

insert into S values(1,'张三') 

insert into S values(2,'李四') 

insert into S values(3,'王五') 

insert into S values(4,'赵六') 

insert into S values(5,'孔子') 

insert into S values(6,'老子') 



insert into SC values(1,1,89.5) 

insert into SC values(1,2,78.6) 

insert into SC values(1,3,46.5) 

insert into SC values(2,1,98  )

insert into SC values(2,2,78  )

insert into SC values(3,1,58.5) 

insert into SC values(3,2,86.5) 

insert into SC values(3,3,59.5) 

insert into SC values(4,1,78  )

insert into SC values(5,1,68  ) 

go





select S.Sname,cast(b.avggrade as decimal(10,1))

from S join (select Sno from SC where Scgrade<60 group by Sno having count(*)>1) a on S.Sno=a.Sno

join (select Sno,avggrade=avg(Scgrade) from SC group by Sno) b on b.Sno=a.Sno



/*

----------

王五	68.2

*/



drop table S,C,SC

时光瞄 2008-10-16

打赏
举报

晕，代码贴漏了

select sname,avg(grade) from S a,C b where a.sno=b.sno and (select count(*) from C where sno=b.sno and 60>b.grade)>=2 

 group by sname

时光瞄 2008-10-16

打赏
举报

select sname,avg(grade) from S a,C b where a.sno=b.sno and (select count(*) from C where sno=b.sno and 60>b.grade)>=2 

 group by sname

律己修心 2008-10-16

打赏
举报

select Sname,cast(avg(Scgrade) as numeric(5,2)) as Scgrade from S,SC

where S.Sno=SC.Sno and (select count(1) from SC where Sno=S.Sno and Scgrade<60)>1

group by Sname order by Sname

律己修心 2008-10-16

打赏
举报

这个已经测试过



select Sname,cast(avg(Scgrade) as numeric(5,2)) as Scgrade 

from S inner join SC on S.Sno=SC.Sno

where S.Sno in (select Sno from SC where Scgrade <60 group by Sno having count(1)>1)

group by Sname

order by Sname

律己修心 2008-10-16

打赏
举报

select Sname avg(Scgrade) as Scgrade 

from S inner join SC on S.Sno=SC.Sno

where Sno in (select Sno from SC where Scgrade <60 group by Sno having count(1)>1)

group by Sname

order by Sname

Garnett_KG 2008-10-16

打赏
举报



DECLARE @s TABLE(SNo INT,Sname varchar(10))

INSERT INTO @s SELECT 

1 , '张三' UNION ALL SELECT

2 , '李四' UNION ALL SELECT

3 , '王五' UNION ALL SELECT

4 , '赵六' UNION ALL SELECT

5 , '孔子' UNION ALL SELECT

6 , '老子' 



DECLARE @C TABLE (Cno INT, Cteacher VARCHAR(10))

INSERT INTO @c SELECT 

1 , '嘟嘟' UNION ALL SELECT 

2 , '吱吱' UNION ALL SELECT

3 , '李明' 





DECLARE @SC  TABLE (SNo INT,Cno INT,Scgrade decimal(15,1))

INSERT INTO @Sc SELECT 

1   , 1 , 89.5  UNION ALL SELECT

1   , 1 , 89.5  UNION ALL SELECT

1   , 1 , 89.5  UNION ALL SELECT

1   , 2 , 78.6 UNION ALL SELECT

1   , 3 , 46.5 UNION ALL SELECT

2   , 1 , 98 UNION ALL SELECT

2   , 2 , 78 UNION ALL SELECT

3   , 1 , 58.5 UNION ALL SELECT

3   , 2 , 86.5 UNION ALL SELECT

3   , 3 , 59.5 UNION ALL SELECT

4   , 1 , 78 UNION ALL SELECT

5   ,1  ,68 



SELECT SName,AVGScgrade

FROM @s s INNER JOIN 

	(

	SELECT SC.SNo,ROUND(AVG(Scgrade),1) as AVGScgrade

	FROM @c c LEFT JOIN @SC SC

			ON C.CNo=SC.CNo

	GROUP BY SC.SNo

	HAVING SUM(CASE WHEN ISNULL(sc.Scgrade,0)<60 THEN 1 ELSE 0 END)>=2

	) b ON s.SNo=b.SNo

RPFly 2008-10-16

打赏
举报

Select S.Sname,avg(Scgrade)
From S
Join SC On S.Sno=SC.sno
where Scgrade <60
Group By S.Sname
Having count(1)>1

ljhcy99 2008-10-16

打赏
举报

Select S.Sname, AVG. score
From S,
(Select top 2 with ties avg(Scgrade) As score, Sno
From SC
Where SC. Cno in(select Cno from C)
Group by Sno
Having score<60
)As AVG

Where S.Sno=AVG.Sno

子陌红尘 2008-10-16

打赏
举报

create table S (Sno int,Sname varchar(10)) 

create table SC(Sno int,Cno int,Scgrade numeric(5,1)) 

create table C (Cno int,Cteacher varchar(10)) 

go

insert into S values(1,'张三') 

insert into S values(2,'李四') 

insert into S values(3,'王五') 

insert into S values(4,'赵六') 

insert into S values(5,'孔子') 

insert into S values(6,'老子') 



insert into C values(1,'嘟嘟') 

insert into C values(2,'吱吱') 

insert into C values(3,'李明') 



insert into SC values(1,1,89.5) 

insert into SC values(1,2,78.6) 

insert into SC values(1,3,46.5) 

insert into SC values(2,1,98  )

insert into SC values(2,2,78  )

insert into SC values(3,1,58.5) 

insert into SC values(3,2,86.5) 

insert into SC values(3,3,59.5) 

insert into SC values(4,1,78  )

insert into SC values(5,1,68  ) 

go



select  

    a.Sno,a.Sname,avg(b.Scgrade) as [Avg]

from 

    S a,SC b,C 

where 

    a.Sno=b.Sno and b.Cno=c.Cno

    and

    (select count(1) from SC where Sno=a.Sno and Scgrade<60)>1

group by

    a.Sno,a.Sname

go



/*

Sno         Sname      Avg                                      

----------- ---------- ---------------------------------------- 

3           王五         68.166666

*/



drop table S,C,SC

go

ws_hgo 2008-10-16

打赏
举报

我不是高手
但是
我看看

子陌红尘 2008-10-16

打赏
举报

try:



select  

    a.Sno,a.Sname,avg(b.Scgrade) as [avg]

from 

    S a,SC b,C 

where 

    a.Sno=b.Sno and b.Cno=c.Cno

    and

    (select count(1) from SC where Sno=a.Sno and Scgrade<60)>1

group by

    a.Sno,a.Sname

MS SQL SERVER 学生成绩问题（高手请进)

相关推荐