常用的sql语句

--Student(S#,Sname,Sage,Ssex) 学生表
--Course(C#,Cname,T#) 课程表
--SC(S#,C#,Score) 成绩表
--Teacher(T#,Tname) 教师表

1，查询“001”课程比“002”课程高的所有学生的学号

select a.S# 
from (select S#,score from SC where C#='001') as a,
(select S#,score from SC where C#='002') as b 
where a.S#=b.S# and a.score>b.score

2，查询平均成绩大于60分的同学的学号和平均成绩

select  S#,avg(score) as AVG
from SC
group by S#
having avg(score)>60 

3，查询所有同学的学号、姓名、选课数、总成绩

select a.S#,a.Sname,count(b.C#) as CourseCnt,sum(b.score) as TtlScore
from Student as a
left join SC as b on a.S#=b.S#
group by a.S#,a.Sname

---------------------------------------------------
select a.S#,a.Sname,b.CourseCnt,b.TtlScore
from Student as a
left join (select S#,count(C#) as CourseCnt,sum(score) as TtlScore
from SC group by S#) as b on a.S#=b.S#

4，查询姓“李”的老师的个数

select count(T#) 
from Teacher
where Tname like '李%'

--或者条件为 where left(Tname,1)='李'

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

select S#,Sname
from Student 
where S# not in (
select distinct(s.S#)
from Course as c,SC as s,Teacher as t
where c.C#=s.C# and c.T#=t.T# and t.Tname='叶平' )

6，查询学过“001”并且也学过“002”课程的同学的学号、姓名

select  a.S#,a.Sname
from Student as a,
(select distinct S#
from SC
where C# in('001','002')
group by S#
having count(distinct C#)=2) as b
where a.S#=b.S#

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

select S#,Sname
from Student
where S# in(
select s.S#
from SC as s,Course as c,Teacher as t
where s.C#=c.C# and c.T#=t.T# and t.Tname='叶平'
group by s.S#
having count(distinct c.C#)=(
select count(distinct kc.C#)
from Course as kc,Teacher as jc
where kc.T#=jc.T# and jc.Tname='叶平')
)

8，查询课程编号“002”的成绩比课程编号“001”课程低的所有学生的学号、姓名

select S#,Sname 
from ( 
select xs.S#,xs.Sname,s.score,(
select score
from SC as cj 
where cj.S#=xs.S# and cj.C#='002'
) as score_2
from SC as s,Student as xs
where s.S#=xs.S# and s.C#='001'
) as S1 
where score>score_2

--与第一题类似

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

select S#,Sname 
from Student
where S# not in (select S#
from SC
where score>=60)
)

----当并不是所有学生参加所有科目的考试需采用其他方法

10，查询没有学全所有课的同学的学号、姓名

select xs.S#,xs.Sname
from Student as xs,SC as cj
where xs.S#=cj.S#
group by xs.S#,xs.Sname
having count(*)=(
select count(*)
from Course
)

11，查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名

select xs.S#,xs.Sname
from Student as xs,SC as cj
where xs.S#=cj.S# and cj.C# in(
select C# 
from SC
where S#='1001'
)

12，查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名

select sx.S#,sx.Sname
from Student as sx,SC as cj
where sx.S#=cj.S# and cj.C# in(
select  C# from SC where S#='001'
)

13，把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩

update s set Score=(select avg(score) from SC where C#=c.C#)
from SC as s,Course as c,Teacher as t
where s.C#=c.C# and t.T#=c.T# and t.Tname='叶平'

14，查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名

select xs.S#,xs.Sname
from Student as xs,SC as cj
where xs.S#=cj.S# and cj.C# in (select C# from SC where S#='1002')
group by xs.S#,xs.Sname
having count(*)=(select count(*) from SC where S#='1002')

15，删除学习“叶平”老师课的SC表记录

delete s
from SC as s,Course as c,Teacher as t
where s.C#=c.C# and c.T#=t.T# and T.Tname='叶平'

16，向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“003”课程的同学学号；2号课的平均成绩

insert SC
select S#,'002',(select avg(score) from SC where C#='002')
from Student as s 
where s.S# not in (select S# from SC where C#='003') 

17，按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显示：学生ID、数据库、企业管理、英语、有效平均分

--行转列
select S# as '学生ID',
sum(case when C#='004' then score else 0 end) as '数据库',
sum(case when C#='001' then score else 0 end) as '企业管理',
sum(case when C#='006' then score else 0 end) as '英语',
count(*) as '有效课程',avg(score) as '平均成绩'
from SC
group by S#
order by S#

18，查询各科成绩最高和最低的分，如以下形式显示：课程ID、最高分、最低分

select C# as '课程ID',max(score) as '最高分',min(score) as '最低分'
from SC
group by C#
order by C#

19，按各科平均成绩从低到高和及格率的百分数从高到低顺序

--注意有些课程未作为考试科目
select c.C#,c.Cname,isnull(avg(s.score),0) as '平均成绩'，100.0*sum(case when isnull(s.score,0)>=60 then 1 else 0 end)/(case when count(*)=0 then 1 else count(*) end) as '及格百分数'
from Course as c
left join SC as s on c.C#=s.C#
group by c.C#,c.Cname
order by 平均成绩 asc,及格百分数 desc

20，查询如下课程平均成绩和及格率的百分数：企业管理（001），马克思（002），数据库（003）

select sum(case when C#='001' then avg else 0 end) as '企业管理平均分',sum(case when C#='001' then percent else 0 end) as '企业管理及格百分数',
sum(case when C#='002' then avg else 0 end) as '马克思平均分',sum(case when C#='002' then percent else 0 end) as '马克思及格百分数',
sum(case when C#='003' then avg else 0 end) as '数据库平均分',sum(case when C#='003' then percent else 0 end) as '数据库及格百分数',
from ( 
select C#,avg(Score) as avg,sum(case when score>0 then 1 else 0 end)/count(*) as percent
from SC
where C# in ('001','002','003')
group by C#
)

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

select sum(case when C#='001' then score else 0 end)*1.0/sum(case when C#='001' then 1 else 0 end) as '企业管理平均分',sum(case when C#='001' and score>=60 then 1 else 0 end)*1.0/sum(case when C#='001' then 1 else 0 end) as '企业管理及格百分数',
sum(case when C#='002' then score else 0 end)*1.0/sum(case when C#='002' then 1 else 0 end) as '马克思平均分',sum(case when C#='002' and score>=60 then 1 else 0 end)*1.0/sum(case when C#='002' then 1 else 0 end) as '马克思及格百分数',
sum(case when C#='003' then score else 0 end)*1.0/sum(case when C#='003' then 1 else 0 end) as '数据库平均分',sum(case when C#='003' and score>=60 then 1 else 0 end)*1.0/sum(case when C#='003' then 1 else 0 end) as '数据库及格百分数'from SC

21，查询不同老师所教不同课程平均分从高到低显示

select t.T#,t.Tname,c.C#,c.Cname,avg(score) as AVG
from SC as s,Course as c,Teacher as t
where s.C#=c.C# and c.T#=t.T#
group by t.T#,t.Tname,c.C#,c.Cname
order by avg(score) desc

22，查询如下课程成绩第3名到第6名的学生成绩单：企业管理（001），马克思（002），数据库（003）

select a.S#,a.Sname,sum(case when C#='001' then score else 0 end) as '企业管理',
sum(case when C#='002' then score else 0 end) as '马克思',
sum(case when C#='003' then score else 0 end) as '数据库',
avg(score) '平均成绩'
from (
select *,row_number() over(partition by S# order by sum(score) desc) as rId
from SC
where C# in ('001','002','003')
) as a,Student as b
where a.S#=b.S# and b.rId between 3 and 6
group by a.S#,a.Sname
order by a.S#

23，统计列印各科成绩，各分数段人数：课程ID，课程名称，[100-80]，[80-60]，[<60]

select c.C#,c.Cname,
sum(case when score>80 and score<=100 then 1 else 0 end) as '[100-80]',
sum(case when score>60 and score<=80 then 1 else 0 end) as '[80-60]',
sum(case when score<=60 then 1 else 0 end) as '[<60]',
from SC as s,Course as c
where s.C#=c.C# 
group by c.C#,c.Cname
order by c.C#

24，查询学生平均成绩及名次

select S#,avg(score) as '平均分',row_number() over(order by avg(score) desc) as '名次'
from SC
group by S#

25，查询各科成绩前三名的记录：（不考虑成绩并列情况）

select s.S#,s.Sname,c.C#,c.Cname,a.Score,a.rId
from (
select *,row_number() over(partition by C# order by score desc) as rId
from SC
) as a,Student as s,Course as c
where rId<=3 and s.S#=a.S# and a.C#=c.C#
order by a.C#,a.rId

26，查询每门课程被选修的学生数

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

27，查询出只选修了一门课程的全部学生的学号和姓名

select xs.S#,xs.Sname
from SC as cj,Student as xs
where cj.S#=xs.S#
group by xs.S#,xs.Sname
having count(C#)=1

28，查询男生、女生人数

select count(*)
from Student
where Ssex='男'

----------------------------------------
select count(*)
from Student
where Ssex='女'

29，查询姓“张”的学生名单

select * 
from Student 
where Sname like '张%'

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

select Sname,count(*)
from Student
group by Sname
having count(*)>1

31，1981年出生的学生名单（注：Student表中Sage列的类型是datetime）

select *
from Student
where year(Sage)=1981

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

select C#,avg(score)
from sc
group by C#
order by avg(score),C# desc

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

select xs.S#,xs,Sname,avg(score)
from SC as cj,Student as xs
where cj.S#=xs.S#
group by xs.S#,xs.Sname
having avg(score)>85

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

select xs.Sname,isnull(score,0)
from SC as cj,Course as c,Student as xs
where cj.C#=c.C# and cj.S#=xs.S# and c.Cname='数据库' and cj.score<60

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

select xs.S#,xs.Sname,c.C#,c.Cname
from SC as cj,Student as xs,Course as c
where cj.C#=c.C# and cj.S#=xs.S#

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

select xs.Sname,c.Cname,cj.Score
from SC as cj,Student as xs,Course as c
where cj.C#=c.C# and cj.S#=xs.S# and cj.score>70

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

select C#
from SC
where score<60 
order by C# desc

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

select xs.S#,xs.Sname
from SC as cj,Student as xs
where cj.S#=xs.S# and cj.C#='003' and cj.score>80

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

select count(distinct S#) from sc

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

select xs.Sname,cj.Score
from SC as cj,Course as c,Teacher as t,Student as xs
where cj.C#=c.C# and c.T#=t.T# and t.Tname='叶平' and cj.S#=xs.S# and cj.score=(select max(score) from SC where C#=c.C#)

41，查询各个课程及相应的选修人数

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

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

select distinct a.S#,a.C#,a.Score
from SC as a,SC as b
where a.C#!=b.C# and a.score=b.score

43，查询每门功课成绩最好的前两名

select S#,C#,score
from (
select *,row_number() over(partition by C# order by score desc) as rId
from SC
) as a 
where rid<=2
order by C#,rid

44，统计每门课程的学生选修人数（超过10人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列和课程号升序排列

select C#,count(*)
from SC
group by C#
having count(*)>10
order by count(*) desc,C#

45，检索至少选修两门课程的学生学号

select S#
from SC
group by S#
having count(*)>=2

46，查询全部学生都选修的课程的课程号和课程名

select c.C#,c.Cname
from SC as s,Course as c
where s.C#=c.C#
group by  c.C#,c.Cname
having count(S#)=(select count(distinct S#) from SC)

47，查询没学过“叶平”老师讲授的任一门课程的学生姓名

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

48，查询两门以上不及格课程的同学的学号及其平均成绩

select S#,avg(score)
from SC
where score<60
group by S#
having count(*)>2

49，检索“004”课程分数小于60，按分数降序排列的同学学号

select S#
from SC
where C#='004' and score<60
order by score desc

50，删除“002”同学的“001”课程的成绩

delete from SC where S#='002' and C#='001'