1、查询“001”课程比“002”课程成绩高的所有学生的学号
select sc1.S#,sc1.C# from SC sc1
where sc1.C#='001' and sc1.score < (select sc2.score from SC sc2
where sc2.C#='002' and sc1.S#=sc2.S#)

2.查询平均成绩大于60分的同学的学号和平均成绩
select S#, AVG(score) from SC group by S# having AVG(score)>60

select S#, AVG(score) from SC group by S# having AVG(score)>60 order by AVG(score) DESC

3、查询所有同学的学号、姓名、选课数、总成绩；
select stu.S#, stu.Sname, COUNT(sc.C#),SUM(sc.score) from Student stu,SC sc
where stu.S#=sc.S# group by stu.S#,stu.Sname

4、查询姓“李”的老师的个数；
select COUNT(*) from Teacher where Tname like '李%'

5、查询没学过“叶平”老师课的同学的学号、姓名；

select stu.S#,stu.Sname from SC sc,Student stu
where stu.S#=sc.S# and C#
in(select C# from Course where T#
not in (select T# from Teacher where tname='叶平'))

6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
select S#,Sname from Student
where S# in (select S# from SC where C# in( '001','002')
group by S# having count(S#)=2)

7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；

select stu.S#,stu.Sname from SC sc,Student stu
where stu.S#=sc.S# and sc.C# in(select c.C# from Course c
where c.T# in (select T# from Teacher where Tname='叶平'))

8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；
select S#,Sname from Student where S#
in(select sc1.S# from SC sc1,SC sc2
where sc1.S#=sc2.S#
and sc1.C#='001' and sc2.C#='002'and sc2.Score

9、查询所有课程成绩小于60分的同学的学号、姓名；

select stu.S#,stu.Sname from Student stu,SC sc
where sc.score<60 and stu.S#=sc.S#

30、查询同名同性学生名单，并统计同名人数

Select Sname,Ssex,count(*) as num from Student
group by Sname,Ssex having count(*)>1

31、1981年出生的学生名单(注：Student表中Sage列的类型是datetime)

select Sname,Sage from Student where Sage=2013-1981

Select * from Student Where year(Sage)=1981

32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列

select
c.C#,
(select AVG(score) from SC where C#= c.C#) as avgValue
from (select C# from SC group by C#) as c
order by avgValue,c.C# desc

33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩

select
c.S#, stu.Sname,
(select AVG(score) from SC where S#= c.S#) as avgValue
from (select S# from SC group by S#) as c
inner join Student stu on stu.S#=c.S#
where (select AVG(score) from SC where S#= c.S#) > 85

34、查询课程名称为“数据库”，且分数低于60的学生姓名和分数

/*34、查询课程名称为“数据库”，且分数低于的学生姓名和分数*/

select Cname from Course where Cname='数据库'

select score from SC where score < 60

select stu.Sname,sc.score from Student stu,SC sc
where stu.S#=sc.S# and sc.C#=(select top 1 C# from Course where Cname='数据库')
and sc.score <60

select stu.Sname,s.score from Student stu
inner join SC s on s.S# = stu.S#
inner join Course c on c.C# = s.C#
where c.Cname='数据库' and s.score <60

35、查询所有学生的选课情况；

select stu.S#,stu.Sname ,cou.Cname from Student stu,SC SC ,Course cou
where stu.S#=SC.S# and SC.C#=cou.C# group by stu.S#,stu.Sname,cou.Cname

36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；

select stu.Sname ,cou.Cname,s.score from Student stu,SC s,Course cou
where stu.S#=s.S# and s.C#=cou.C# and s.score>70
group by stu.S#,stu.Sname,cou.Cname,s.score

37、查询不及格的课程，并按课程号从大到小排列

select stu.Sname,cou.C#,cou.Cname,s.score from Student stu,SC s,Course cou
where stu.S#=s.S# and s.C#=cou.C# and s.score<60 order by cou.C# DESC

38、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名；

select stu.S#,stu.Sname from Student stu,SC s
where stu.S#=s.S# and s.C#='003' and s.score>80

39、求选了课程的学生人数

select C#, count(distinct S#) from SC group by C# order by C#

select count(*) from (select S# from SC SC1 where SC1.S# in
(select S# from SC SC2 group by S#) group by S#)
消息 102，级别 15，状态 1，第 2 行
')' 附近有语法错误

select count(s3.S#) from
(select s1.S# from SC s1 where s1.S# in
(select s2.S# from SC s2 group by s2.S#)) as s3
group by s3.S#

select count(*) as COUNTNUM from
(select s1.C# from SC s1 where s1.C# in
(select s2.C# from SC s2 group by s2.C#)) as s3
group by s3.C#

40、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩

/* 40查询选修"叶平"老师所授课程的学生中,成绩最高的学生姓名及其成绩*/
select Stu.Sname,SC.score from Student Stu
inner join SC on SC.S#=stu.S#
inner join (
select SC.C#,MAX(score) as mscore from SC inner join Course c on sc.C#=c.C# inner join Teacher t on t.T#=c.T# and Tname='叶平' group by SC.C#) as g on g.mscore=SC.score AND SC.C#=g.C#
inner join Course c on sc.C#=c.C# inner join Teacher t on t.T#=c.T# and Tname='叶平'

/*查询叶平老师教的每门课程最高成绩是多少*/
select SC.C#,MAX(score) as HighScore from SC inner join Course c on SC.C#=c.C#
inner join Teacher t on t.T#=c.T# and Tname='叶平' group by SC.C#

/*查询叶平老师教的课程最高成绩是多少*/
select MAX(score) as HighScore from SC inner join Course c on SC.C#=c.C#
inner join Teacher t on t.T#=c.T# and Tname='叶平'

/* 40查询选修"叶平"老师所授课程的学生中,学生姓名及其成绩,按照降序排序*/
select stu.Sname,s.score from Student stu,SC s
where stu.S#=s.S# and s.C# in
(select c.C# from Course c where c.T# in
(select distinct t.T# from Teacher t where t.Tname='叶平') )
order by s.score DESC

41、查询各个课程及相应的选修人数
/*41、查询各个课程及相应的选修人数*/
select s1.C#,cou.Cname,count(s1.C#)
from SC s1,Course cou
where cou.C#=s1.C#
group by s1.C#,cou.Cname

select cou.Cname,cou.C#,count(cou.C#) as 人数 from Course cou
inner join SC s on s.C#=cou.C# group by cou.C#,cou.Cname

42、查询不同课程成绩相同的学生的学号、课程号、学生成绩

/*42、查询不同课程成绩相同的学生的学号、课程号、学生成绩（学号重复了）*/
select scr.*,sc.C# from (
select st.S#,(select max(score) from SC where S#=st.S#) as score
from (
select s# from SC group by s#) as st
where (select max(score) from SC where S#=st.S#)=(select min(score) from SC where S#=st.S#)) as scr
inner join SC on scr.S# = SC.S# AND scr.score = SC.score

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29056818/viewspace-768456/ ，如需转载，请注明出处，否则将追究法律责任。

ITPub博客

数据库：SQLServer SQL语句练习

最新文章